Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

Best Answer

  • ✭✭✭✭✭✭
    Answer ✓

    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

  • ✭✭✭✭✭✭
    Answer ✓

    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

  • ✭✭✭✭✭

    The latter worked! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions