datedif substitute in smartsheet
Please can someone help with formulas for the attached datedif, which allocates the correct number of months into each year by column.
excel attached, which works!
thank you so much in advance!!
Answers
-
Hi @CLMini
Smartsheet does not currently have a DATEDIF function that will automatically calculate this for you.
The way I would find this calculation is by using the MONTH and YEAR functions to pull the numbers from your dates.
For your TOTAL column:
=MONTH([End Date]@row) + (13 - MONTH([Start Date]@row)) + (((YEAR([End Date]@row) - YEAR([Start Date]@row) - 1) * 12) - 1)
Then for your date-specific columns, you'll need to use an IF statement to identify the year you're looking for. E.g. for 2022, I would first check to see if both dates are in 2022, If they are, we simply need to subtract the end month from the start month:
=IF(AND(YEAR([Start Date]@row) = 2022, YEAR([End Date]@row) = 2022), MONTH([End Date]@row) - MONTH([Start Date]@row),
Then we can check if the End date is in 2022 (but the Start date isn't). If so, we only need the Month number of the End Date:
IF(YEAR([End Date]@row) = 2022, MONTH([End Date]@row),
Finally, if neither of these statements are true, but the Start Date is in 2022, then we can subtract this month off of 12 to find out how many months are in 2022 from the Start of the task.
IF(YEAR([Start Date]@row) = 2022, 12 - MONTH([Start Date]@row),
And if none of these are true (neither date has 2022), then the cell should show 0:
0)))
For a full formula:
=IF(AND(YEAR([Start Date]@row) = 2022, YEAR([End Date]@row) = 2022), MONTH([End Date]@row) - MONTH([Start Date]@row), IF(YEAR([End Date]@row) = 2022, MONTH([End Date]@row), IF(YEAR([Start Date]@row) = 2022, 12 - MONTH([Start Date]@row), 0)))
For your other Year columns you'd just need to swap out the written in 2022 for the next year:
=IF(AND(YEAR([Start Date]@row) = 2023, YEAR([End Date]@row) = 2023), MONTH([End Date]@row) - MONTH([Start Date]@row), IF(YEAR([End Date]@row) = 2023, MONTH([End Date]@row), IF(YEAR([Start Date]@row) = 2023, 12 - MONTH([Start Date]@row), 0)))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!