Calculate the number of months between two dates
I am relatively novice in Smartsheet formulas, but decent in Excel... I cannot find a way to simply calculate the number of months between two dates, for me then to multiply the count of months by a $ value. For instance:
So I would like to just assume all months are equal in 30 days, so difference between 1/1/2020 and 4/1/2020 is 3, then multiply that by Project MRC to get lost revenue.
I have tried dozens of formulas in Smartsheet and no dice, everything is #UNPARCEABLE
Thoughts?
Answers
-
Have you tried...
=INT(([Actual Completion]@row - [Projected Completion]@row) / 30)
?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Here's a generic solution for counting the months from today (or other referenced date)...
In column [Months from Today] this column-formula will show the number of months before/-after the value in [TestDate], where prior months are positive and future months are negative. If [TestDate] is in the current month the result is zero (0).
=IF([TestDate]@row < TODAY(), (VALUE(MONTH(TODAY()))) + (13 - VALUE(MONTH([TestDate]@row))) + ((VALUE(YEAR(TODAY())) - VALUE(YEAR([TestDate]@row)) - 1) * 12) - 1, (VALUE(MONTH(TODAY()) - 12)) + (-VALUE(MONTH([TestDate]@row))) + ((VALUE(YEAR(TODAY())) + 1 - VALUE(YEAR([TestDate]@row))) * 12))
To compare a given date against a particular date instead of against today, replace all instances of TODAY() with that date’s cell (or summary field) reference.
Note that this does not take into account the number days, only month values. Thus from 4/30/21 to 5/1/21 yields 1 "month" even though there's only one day difference between those dates.
-
I know this is an older post but in case someone else comes looking like I did, this worked for me. I realize this doesn't taken into account Leap Years but I didn't have to be precise, just had to get close, so I used the assumption of an average of 30.425 Days/Month. Ignore the decimals and this should be correct for any 65 year period.
=(([Start Date]1 - [End Date]1) / 30.425)
Screenshot of my SmartSheet with formula over the output from a Date Difference Calculator Website.
-
@J Parker Thank you for this formula! It worked and is much simpler than the others.
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!