Count number of months before/after a specified date

Options

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 within 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 of 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.

Comments

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    Options

    BTW, here's how this formula might be used or adapted:

    Example 1 (what it was created for): Every month the battery status for each AED on our campus needs to be checked by a designated Monitor whose desk is nearby. This formula enables us, for historical statistical review, to see how many months each of the AEDs has been in the system since installation, so we can compare that number against the number of reports submitted, and thus observe which AED Monitors have been submitting their reports consistently.

    Example 2: Each employee is required to submit a monthly sales report. Mary was hired on 1/30/19 Tom was hired on 4/30/19 so they formula calculates that Mary should have submitted 27 reports and Tom 24.

    Example 3: Formula could be set so that if you have a Start Date field and an End Date field you'll display the number of months between those dates. You'd need to finesse it based on whether you wanted the count to be inclusive—or not—of the start and/or end months.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!