RSS

Number of Months Between Dates

14 Oct
Number of Months Between Dates

Have you ever wanted to calculate the number of months between two dates in a formula field or in a Flow? Happily, the solution is fairly straightforward.

Here are a couple formulas you can use. In these examples I’m using StartDate as the older date and EndDate as the most recent date.

The basic formula:

IF(YEAR(EndDate) = YEAR(StartDate),
MONTH(EndDate) – MONTH(StartDate) + 1,
(YEAR(EndDate) – YEAR(StartDate) – 1) * 12
+ 12 – MONTH(StartDate) + 1
+ MONTH(EndDate))

Let’s test it out


First Test – Dates in Same Year:

StartDate = 2/8/14
EndDate = 10/14/14

  1. YEAR(10/14/14) = YEAR(2/8/14) so the first part of the IF statement is evaluated
  2. MONTH(10/14/14) – MONTH(2/8/14) + 1 = 10 – 2 + 1 = 9

Second Test – Dates in Adjacent Years:

StartDate = 2/8/14
EndDate = 10/14/15

  1. YEAR(10/14/15) <> YEAR(2/8/14) so the second part of the IF statement is evaluated
  2. YEAR(10/14/15) – YEAR(2/8/14) – 1 = 0
  3. 0 * 12 = 0
  4. 12 – MONTH(2/8/14)  + 1 = 12 – 2 + 1 = 11
  5. MONTH(10/14/15) = 10
  6. 0 + 11 + 10 = 21

Third Test – Dates in Non-Adjacent Years:

StartDate = 2/8/13
EndDate = 10/14/15

  1. YEAR(10/14/15) <> YEAR(2/8/13) so the second part of the IF statement is evaluated
  2. YEAR(10/14/15) – YEAR(2/8/13) – 1 = 1
  3. 1 * 12 = 12
  4. 12 – MONTH(2/8/14)  + 1 = 12 – 2 + 1 = 11
  5. MONTH(10/14/15) = 10
  6. 12 + 11 + 10 = 33

Fourth Test – Dates in Same Month and Year:

StartDate = 10/8/14
EndDate = 10/14/14

  1. YEAR(10/14/14) = YEAR(2/8/14) so the first part of the IF statement is evaluated
  2. MONTH(10/14/14) – MONTH(10/8/14) + 1 = 10 – 10 + 1 = 1

So far so good.

But what if you want to count only the full months that had passed?

For example, what if your StartDate was 2/20/14 and EndDate was 10/3/14 which means that 9 full months have not yet passed and you don’t want to count that last month until it has been fully realized? In this example the basic formula would yield 9 months, but you want the outcome to be 8 months (at least until the EndDate is on or after the 20th of the month).

It turns out you’ll need to tweak the basic formula twice–once to test separately for the dates being in the same month and year, and once to add this statement:

– IF(DAY(EndDate) < DAY(StartDate), 1, 0)

The final formula will look like this:

IF(YEAR(EndDate) – YEAR(StartDate) + MONTH(EndDate) – MONTH(StartDate) = 0, 0,
IF(YEAR(EndDate) = YEAR(StartDate),
MONTH(EndDate) – MONTH(StartDate) + 1,
(YEAR(EndDate) – YEAR(StartDate) – 1) * 12
+ 12 – MONTH(StartDate) + 1
+ MONTH(EndDate)
)
– IF(DAY(EndDate) < DAY(StartDate), 1, 0)
)


Fifth Test – Dates in Non-Adjacent Years Not Counting Incomplete Months:

StartDate = 2/28/13
EndDate = 10/4/15

  1. YEAR(10/14/15) – YEAR(2/28/13) + MONTH(10/14/15) – MONTH(2/28/13) <> 0 so the second part of the outer IF statement is evaluated
  2. YEAR(10/4/15) <> YEAR(2/28/13) so the second part of the inner IF statement is evaluated
  3. YEAR(10/4/15) – YEAR(2/28/13) – 1 = 1
  4. 1 * 12 = 12
  5. 12 – MONTH(2/28/14)  + 1 = 12 – 2 + 1 = 11
  6. MONTH(10/4/15) = 10
  7. 12 + 11 + 10 = 33
  8. DAY(10/4/15) < DAY(2/28/14) so IF statement will return 1
  9. 33 – 1 = 32

Sixth Test – Dates in Non-Adjacent Years Not Counting Incomplete Months:

StartDate = 2/28/13
EndDate = 10/31/15

  1. YEAR(10/14/15) – YEAR(2/28/13) + MONTH(10/14/15) – MONTH(2/28/13) <> 0 so the second part of the outer IF statement is evaluated
  2. YEAR(10/31/15) <> YEAR(2/28/13) so the second part of the inner IF statement is evaluated
  3. YEAR(10/31/15) – YEAR(2/28/13) – 1 = 1
  4. 1 * 12 = 12
  5. 12 – MONTH(2/28/14)  + 1 = 12 – 2 + 1 = 11
  6. MONTH(10/31/15) = 10
  7. 12 + 11 + 10 = 33
  8. DAY(10/31/15) >= DAY(2/28/14) so IF statement will return 0
  9. 33 – 0 = 33

Last Test – Dates in the Same Month and Year

StartDate = 10/7/15
EndDate = 10/28/15

  1. YEAR(10/28/15) – YEAR(10/7/15) + MONTH(10/28/15) – MONTH(10/7/15) = 0 so the outer IF statement returns 0

Cheers!

Advertisements
 
Leave a comment

Posted by on October 14, 2015 in Formulas

 

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: