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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.3K Get Help
- 446 Global Discussions
- 144 Industry Talk
- 477 Announcements
- 5K Ideas & Feature Requests
- 85 Brandfolder
- 151 Just for fun
- 72 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 302 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!