Subtracting Dates Issue

Options

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

Best Answer

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓
    Options

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

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓
    Options

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

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Khari Shiver
    Khari Shiver ✭✭✭✭✭
    Options

    The latter worked! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!