Subtracting Dates Issue

Khari Shiver

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

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")

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!