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)
?
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!