How to Calculate Months Between Two Dates

There are three ways to count the months between dates in Excel, and each answers a slightly different question. DATEDIF counts complete months, YEARFRAC gives months with a decimal, and a YEAR and MONTH formula counts calendar months. This guide shows all three on the same contract dates, so you can pick the right one for tenure, billing or project length.

Which formula should you use?#

Choose the formula by the question you are answering. Here are all three on a contract that runs from 15 January 2023 to 10 March 2024:

The results differ because the contract ends on the 10th, five days before the 15th. DATEDIF doesn't count the last, unfinished month. The calendar formula ignores days and just counts how many times the month changes. YEARFRAC sits in between.

The methods don't always disagree. A contract from 1 February 2024 to 31 August 2024 gives 6 with both DATEDIF and the calendar formula, because it starts on the first day of a month. Most people would call that 7 months of work, though, since it covers February to August in full. That's the inclusive count, covered below. Decide which answer your report needs before you choose the formula.

If you need days rather than months, see how to count the days between two dates.

How to count whole months with DATEDIF#

DATEDIF counts complete months between a start date and an end date. The syntax is:

=DATEDIF(start_date, end_date, "m")

The start date comes first and the unit goes in double quotes. For the contract above:

=DATEDIF(B2,C2,"m")

The result is 13. From 15 January 2023, the 13th full month ends on 15 February 2024. The 14th month would end on 15 March 2024, which is after the end date, so it doesn't count.

DATEDIF is a hidden function. Excel doesn't list it in AutoComplete or show its arguments as you type, but it works in every current version on Windows and Mac. Just type the full formula. The DATEDIF function guide lists every unit.

DATEDIF returns #NUM! if the start date is after the end date. If you see that error, the two dates are the wrong way round.

How to show years and months together#

Combine two DATEDIF units to show a length like "1 year, 1 month". The "y" unit counts whole years, and "ym" counts the months left over after those years:

=DATEDIF(B2,C2,"y")&" years, "&DATEDIF(B2,C2,"ym")&" months"

For the contract, this returns 1 years, 1 months: 13 months is 1 full year plus 1 more month. The wording isn't perfect for 1, but it's clear and common in HR reports. The same approach is used for employee service time in the guide to calculating age and tenure.

How to get months with decimals using YEARFRAC#

YEARFRAC returns the fraction of a year between two dates. Multiply by 12 to turn it into months:

=YEARFRAC(B2,C2)*12

For the contract, the result is 13.83 months. This is useful when you bill or pro-rate by month and need the part month too.

By default YEARFRAC uses a 30/360 day count, which treats every month as 30 days. Add a third argument to change the method: =YEARFRAC(B2,C2,1)*12 uses the actual number of days, which gives about 13.79 here. For a whole number, wrap the result in ROUND, ROUNDDOWN or ROUNDUP. The YEARFRAC guide explains each basis option.

How to count calendar months between dates#

Use YEAR and MONTH when only the month matters, not the day. This formula counts how many month boundaries lie between the dates:

=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)

For January 2023 to March 2024, that's 1 year × 12, plus 3 − 1, which is 14. It returns 14 whether the contract started on the 1st or the 31st of January.

This is the right choice for reports by month, such as "how many monthly invoices fall between these dates". To count both the first and the last month, add 1. January to March then counts as 3 months, not 2.

What about the end of the month?#

DATEDIF can give surprising results near the end of a month. From 31 January 2024 to 29 February 2024, =DATEDIF(B2,C2,"m") returns 0. February has no 31st, so Excel decides a full month hasn't passed.

If your dates are often month-ends, the calendar formula is more predictable. When you need a date a set number of months later, rather than a count, use EDATE: =EDATE(B2,6) returns the date six months after B2. The guide to adding days, months and years to a date covers EDATE in detail.

Common mistakes with months between dates#

Most problems come from argument order or from the dates themselves. Check these:

1. Start and end swapped. DATEDIF needs the earlier date first, or it returns #NUM!.
2. Unit without quotes. Write "m", not m. Without quotes, Excel returns #NAME?.
3. Text dates. If a date is stored as text, the formulas return #VALUE!. Convert it to a real date first.
4. Result looks like a date. If 13 shows as 13/01/1900, format the cell as General or Number.
5. Counting up to today. Use TODAY() as the end date, as in =DATEDIF(B2,TODAY(),"m"). The result changes every day. For counting down to a future date, see days until a deadline.

Practise it#

Date differences come up in HR, finance and project questions in many job tests, and DATEDIF's argument order trips people up under time pressure. Practise counting days and months between dates in a live spreadsheet: you type the formula and it checks your answer instantly. Or try the exercise on this page first.