Date Dif Formula

Hi Smartsheet community,
Looking to see if anyone has figured out if it's possible to use the the DATEDIF formula.
When a Start date and end date are used in the formula it would produce the years and months between the date range.
Example
Start date: 1/1/25
End date: 2/1/25
Tenure: 0 years, 1 months
The formula that i use in excel to get this to work is as follow
=DATEDIF(F4,G4,"y") & " years, " & DATEDIF(F4,G4,"ym") & " months"
The formula I used in SS that I get #UNPARSEABLE is
=DATEDIF([Hire Date]@row,[Termination Date]@row,"y") & " years, " & DATEDIF([Hire Date]@row,[Termination Date]@row,"ym") & " months"
Am I missing something in my SS formula? Not sure why this wouldn't work. Thanks in advance.
Best Answer
-
Smartsheet does not have a DATEDIF function at all. We have to write our own formula to mimic. Something along these lines should work:
=(YEAR([End Date]@row) - YEAR([Start Date]@row) - IF(MONTH([End Date]@row) < MONTH([Start Date]@row), 1, 0)) + " years, " + (MONTH([ENd Date]@row) - MONTH([Start Date]@row) + IF(MONTH([End Date]@row) < MONTH([Start Date]@row), 12, 0)) + " months"
Answers
-
Smartsheet does not have a DATEDIF function at all. We have to write our own formula to mimic. Something along these lines should work:
=(YEAR([End Date]@row) - YEAR([Start Date]@row) - IF(MONTH([End Date]@row) < MONTH([Start Date]@row), 1, 0)) + " years, " + (MONTH([ENd Date]@row) - MONTH([Start Date]@row) + IF(MONTH([End Date]@row) < MONTH([Start Date]@row), 12, 0)) + " months"
-
@Paul Newcome I wish I could Vote Up this formula more. That is very detailed and worked perfectly. Very well done. Thank you very much.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!