Count number of months before/after a specified date

Cleversheet
Cleversheet ✭✭✭✭✭✭
edited 09/11/23 in Show & Tell

(Posted to full community earlier but adding here to bring this little trick to the attention of my HiEd colleagues.)

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.