How do I convert this to the number of months instead of days?
How do I convert this number of days calculated between to dates into months?
=NETDAYS([Contract Effective Date]@row, [Contract Expiration Date]@row)
Best Answer
-
Hello @jcepon,
You could use:
=VALUE(YEAR([Contract Expiration Date]@row) + RIGHT("00" + MONTH([Contract Expiration Date]@row); 2)) - VALUE(YEAR([Contract Effective Date]@row) + RIGHT("00" + MONTH([Contract Effective Date]@row); 2))
However: be aware for the transition of years: for example the diff between dec-21 and jan-22 is of course 89 and not 1 with this formula. Of course the formula must be modified taken into account the transition of years, something with MOD(....;12) when Years differ. If anyone has an idea ???
Answers
-
@jcepon Why don't you extract the month from each date and compare them?
MONTH([Contract Effective Date]@row) - MONTH([Contract Expiration Date]@row))
... or the other way 'round.
dm
-
Hello @jcepon,
You could use:
=VALUE(YEAR([Contract Expiration Date]@row) + RIGHT("00" + MONTH([Contract Expiration Date]@row); 2)) - VALUE(YEAR([Contract Effective Date]@row) + RIGHT("00" + MONTH([Contract Effective Date]@row); 2))
However: be aware for the transition of years: for example the diff between dec-21 and jan-22 is of course 89 and not 1 with this formula. Of course the formula must be modified taken into account the transition of years, something with MOD(....;12) when Years differ. If anyone has an idea ???
-
Updated my answer with the Year issue solve:
=(12 * YEAR([Contract Expiration Date]@row) + MONTH([Contract Expiration Date]@row)) - (12 * YEAR([Contract Effective Date]@row) + MONTH([Contract Effective Date]@row))
-
I appreciate it your support so much, that worked the best!!!! Its nice to rely on good team work!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!