How many months between two dates

MONTH functions

Did you know we have a YouTube channel with weekly videos dedicated to Excel, Power Query and Power BI?

By Neale Blackwood

How many months? If you need to calculate the number of whole months between two dates there is a formula that can do it. This might be used to see how many months a project will last.

One way is to use the DATEDIF function. This doesn’t show up in your list of functions but don’t worry … it’s still there! It’s a compatibility function that harks back to Lotus 1-2-3 days.

Just write:

=DATEDIF(start_date, end_date, units)

Where:

start_date is a cell reference containing your start date
end_date is a cell reference containing your end date
units is either “M”, “Y”, or “D” depending on whether you want the number of months, years or days

e.g. if cell A1 has the start date and B1 has the end date, you could write

=DATEDIF(A1, B1, “m”)

If you want something that only uses current functions, the following formula will also calculate the number of months between the dates.

=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

If you need to know the number of months involved in the project, add one to the formula.

Alternatively if you want a simpler approach that only gives the absolute number of months

= YEARFRAC(A1,B1) * 12

If you don’t want a decimal then wrap it in a ROUNDUP

= ROUNDUP ( YEARFRAC(A1,B1) * 12 , 0)