Number of Months 2 dates Span

Lauren Y

Hi - I'm trying to figure out a formula to determine the number of months 2 dates span. NOT the number of months between two dates. For example, row 19 is 3 (Dec, Jan, Feb), Row 20 is 1 and Row 28 is 2.

KPH

Hi Lauren

What is the longest duration between start and end? We can use MONTH to determine the month number of the start and end date and subtract one from the other. We'd need to use an IF to create a workaround for dates that span one new year (2-11 is not 4 for example). Do we also need a workaround for the possibility that the dates could span 2 years?

KPH

Hi Lauren

I am going offline shortly, so will talk you through some options. They get progressively more complex. You might only need the first formula. Or you might need the last one (if so, reading the first couple should help explain parts used in the third).

If the start and finish dates are always in the same year

If the start and finish dates are in the same year the formula is a simple:

=MONTH([Finish Date]@row) - MONTH([Start Date]@row) + 1

This takes the month number of the finish date, subtracts the month number of the start date, and adds 1 for the current month.

The output is shown in column 4 here:

You can see it only works if the start and finish dates are in the same year.

If the start and finish dates span one new year

We can wrap an IF function around the formula we had in step one to only evaluate this formula IF the YEAR of the start date is the same as the YEAR of the finish date.

=IF(YEAR([Start Date]@row) = YEAR([Finish Date]@row), MONTH([Finish Date]@row) - MONTH([Start Date]@row) + 1, "new formula needed")

The output now looks like this:

For dates that span the new year, we need a different formula.

This formula finds the number of months between the start date and the end of the year (12 minus the month number of the Start Date). It then adds the number of months from the start of the year to the finish date (which is just the month number of the Finish Date) and then adds 1 for the current month.

=12 - MONTH([Start Date]@row) + MONTH([Finish Date]@row) + 1

We can use this instead of the "new formula needed" message in our IF formula and now have this:

=IF(YEAR([Start Date]@row) = YEAR([Finish Date]@row), MONTH([Finish Date]@row) - MONTH([Start Date]@row) + 1, 12 - MONTH([Start Date]@row) + MONTH([Finish Date]@row) + 1)

The output now looks like this:

This is good as long as you don't have any finish dates that are 2 (or more) new years after the start date.

If your dates can span more than one new year

If there is a possibility that your dates span more than one new year, we are better off using a slightly more complex formula and not using the IF. This formula starts by finding the number of months between the Start Date and the end of the year and adds to it the number of months into the new year that the Finish Date is. Plus 1 for the current month. This is just like we had above (and is shown below not in bold). The part in bold shows how the formula takes the YEAR of the Finish Date, subtracts the Year of the Start Date, and subtracts 1 for the current year. It multiplies this by 12 (these are the months in the full years between the end of the Start Date year and the start of the Finish Date year) and adds this to the number of months calculated by the first part.

The formula looks like this:

=12 - MONTH([Start Date]@row) + MONTH([Finish Date]@row) + 1 + (12 * (YEAR([Finish Date]@row) - YEAR([Start Date]@row) - 1))

The output is shown in Column 5:

I hope this makes sense and achieves what you need.

Lauren Y

Thank you! Dates will span 5 or more years so I definitely need the more extensive formula.

KPH

Happy to help @Lauren Y