#INVALID DATA TYPE on an IF( function

I have a formula that I'm trying to get to solve the following situation:

If someone has worked for less than 2 years (<730 days), the "destroy on" date should be their hire date plus 3 years (ex. hired on 1/13/2021, destroy on 1/13/2024). If someone has worked for 2 or more years (730+ days), the "destroy on" date should be the termination date plus 1 year (ex. terminated on 6/17/2020, destroy on 6/17/2021).

=IF([+/-2yrs]@row >= 730, DATE([Termination Date]@row + 1, [Termination Date]@row , [Termination Date]@row ), DATE([Hire Date/Rehire Date]@row + 3, [Hire Date/Rehire Date]@row , [Hire Date/Rehire Date]@row ))

My formula keeps on returning an #INVALID DATA TYPE error. Any ideas in what could be causing this, and if it's possible for the formula I'm trying to write to even work?

Find more posts tagged with

Human Resources

IF(

Accepted answers

jjg279

Hmmm… Would this formula do it:

=IF(TermDate@row - HireDate@row < 730, HireDate@row + 1095, TermDate@row + 365)

All three columns are date columns.

All comments

jjg279

Are all of your columns "Date" type columns?

Shannon H.

https://community.smartsheet.com/discussion/comment/491342#Comment_491342

The Hire and Termination Date columns are, but the +/-2yrs column is a number column due to the conditional formatting and formula ( =NETDAYS([Hire Date/Rehire Date]@row , [Termination Date]@row ) ) I have on it. Would that also have to be a "Date" column?

jjg279

Hmmm… Would this formula do it:

=IF(TermDate@row - HireDate@row < 730, HireDate@row + 1095, TermDate@row + 365)

All three columns are date columns.

Shannon H.

https://community.smartsheet.com/discussion/comment/491345#Comment_491345

This worked! Thank you so much. It seems like I was just overcomplicating things a bit on my end.

jjg279

Totally feel that! And most welcome!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.