Subtracting Dates Issue

Hello,

I am trying to write a formula that subtracts two dates from one another IF the year in one of the dates is 2023. Both columns that have dates are formatted as date and I'm getting an invalid data type error.

=IF(YEAR([Last Hire Date]@row = "2024"), (([Proration Date Reference]@row - [Last Hire Date]@row)/365), "1")

The strange thing is the individual portions of the formula work i.e.

=YEAR([Last Hire Date]@row generates a 4 digit year

=([Proration Date Reference]@row - [Last Hire Date]@row)/365) returns an value.

Somehow together they don't work. Any help would be greatly appreciated.

Thanks

Find more posts tagged with

Accepted answers

Jason Albrecht

Hi @Khari Shiver

Looks like there needs to be a closing bracket for the YEAR function? Maybe try this?

=IF(YEAR([Last Hire Date]@row) = "2024", (([Proration Date Reference]@row - [Last Hire Date]@row)/365), "1")

Note: you don't need the extra brackets around the 'If True' portion. The following should also work:

=IF(YEAR([Last Hire Date]@row) = "2024", ([Proration Date Reference]@row - [Last Hire Date]@row)/365, "1")

All comments

Jason Albrecht

Hi @Khari Shiver

Looks like there needs to be a closing bracket for the YEAR function? Maybe try this?

=IF(YEAR([Last Hire Date]@row) = "2024", (([Proration Date Reference]@row - [Last Hire Date]@row)/365), "1")

Note: you don't need the extra brackets around the 'If True' portion. The following should also work:

=IF(YEAR([Last Hire Date]@row) = "2024", ([Proration Date Reference]@row - [Last Hire Date]@row)/365, "1")

Khari Shiver

The latter worked! Thank you!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.