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?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Have you tried...

=INT(([Actual Completion]@row - [Projected Completion]@row) / 30)

Cleversheet

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.

J Parker

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.

Christina W

@J Parker Thank you for this formula! It worked and is much simpler than the others.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.