TodayFree forever - no sign-up, no paid plans

How to calculate months between dates in Excel with DATEDIF

By Daniel Mercer · Updated November 17, 2026

Open the Months Between Dates Calculator

Excel has hundreds of functions, and not one of them in the menus will tell you how many months sit between two dates. The function that does it, DATEDIF, is hidden: it does not appear in the function wizard, it does not autocomplete, and if you go looking for it in Excel's help you will barely find a mention. It works perfectly, though, and it follows exactly the calendar rule - full months to the monthly anniversary, leftover days counted separately - that careful counters use. This guide shows the formulas, works a full example, and covers the two errors everyone hits.

The basic formula

Put your start date in cell A1 and your end date in cell B1. Then:

=DATEDIF(A1,B1,"M") returns the number of complete months between the dates.

=DATEDIF(A1,B1,"MD") returns the leftover days after those complete months.

=DATEDIF(A1,B1,"D") returns the total days, useful as a check.

The third argument is called the unit, and it goes in quotation marks. "M" is the one you want for months. The other units - "Y", "YM", "YD" and the rest - each answer a different question, and our companion guide on DATEDIF units takes them one at a time with a single shared example.

Calculating months between dates in Excel with DATEDIF for a birthday span

Worked example

Set A1 to January 15, 2026 and B1 to August 25, 2026. The formulas return:

FormulaResultWhy
=DATEDIF(A1,B1,"M")7The 15th comes round seven times: Feb 15, Mar 15, Apr 15, May 15, Jun 15, Jul 15, Aug 15
=DATEDIF(A1,B1,"MD")10From the last anniversary, Aug 15, to Aug 25 is 10 days
=DATEDIF(A1,B1,"D")222The full span in days - your check figure

To present the answer the way a person would say it, combine the first two:

=DATEDIF(A1,B1,"M")&" months and "&DATEDIF(A1,B1,"MD")&" days" produces the text 7 months and 10 days. That two-part form is the honest one, for the reasons laid out in our guide on full months vs partial months. You can check every figure here against the months between dates calculator, which uses the same anniversary rule and shows the same 7 months and 10 days for these dates.

Why DATEDIF is hidden

DATEDIF is a survivor from Lotus 1-2-3, the spreadsheet Excel replaced in the 1990s. Microsoft kept the function so that ancient Lotus files would still calculate, but never promoted it into the modern function library - no wizard entry, no autocomplete, minimal documentation. It is kept for Lotus compatibility and nothing about that makes it unreliable: it is supported in every current version of Excel, on Windows, Mac and the web version, and it is the only built-in way to get a calendar-true month count. Google Sheets took the opposite approach and documents DATEDIF openly, with identical syntax - see our Google Sheets guide if your file lives there.

The two errors everyone hits

#NUM! means the count cannot be done. In practice it is one of two things: the start date is later than the end date (DATEDIF refuses to count backwards), or one of the cells contains text dressed up as a date - common when dates are pasted from another system. The test: select the cell and apply a long date format. A real date reformats; text just sits there. Re-enter text dates with the DATE function, for example =DATE(2026,1,15), which no locale setting can misread.

The strange MD result. Very old versions of Excel had a known flaw where the "MD" unit could return a negative or oversized leftover in rare month-end cases. Current versions calculate it correctly, but if you ever see an MD result over 30, do not trust it - rebuild the leftover yourself: count the complete months with "M", add them to the start date with EDATE, and subtract: =B1-EDATE(A1,DATEDIF(A1,B1,"M")). That formula is immune to the old bug because EDATE handles the month-end clamping for you.

A live count with TODAY()

One pattern worth stealing: replace the end date with TODAY() and the formula becomes a live counter that updates every day. =DATEDIF(A1,TODAY(),"M") in a staff list gives each employee's completed months of service as of this morning, and pairing it with the "MD" version shows the days into the current month as well. The same trick drives countdown and count-up trackers - months until a contract ends, months since a project started - with no manual updating. Just remember that TODAY() recalculates whenever the file opens, so if you need a frozen record for a report, copy the results and paste them back as values before you send it.

DATEDIF or a calculator?

Inside a spreadsheet, DATEDIF is the right tool - the answer updates when the dates change and can feed other formulas. For a one-off count, or for checking what a spreadsheet is telling you, an independent tool is the better habit: type the same two dates into the months between dates calculator and confirm the figures match before the number goes into anything that matters. The day-count check helps too - the days between calculator on the homepage shows the raw span (222 days in the example above), and a month answer that cannot be reconciled with the day count is a month answer with a typo in it.

Frequently asked questions

Why does DATEDIF not appear in Excel's function list?

DATEDIF is a legacy function Excel keeps for compatibility with Lotus 1-2-3, the spreadsheet it replaced. Microsoft never added it to the function wizard or the autocomplete list, but it is fully supported and calculates correctly in every current version of Excel. You just have to type it in full.

What does =DATEDIF(A1,B1,"M") actually return?

The number of complete months between the two dates. A month only counts when the same day of the month has come round again, so January 15 to April 14 returns 2, not 3, even though the span is 89 days. Any leftover days are ignored by the M unit - use MD to see them.

How do I get months and days in one cell?

Combine two DATEDIF calls with text: =DATEDIF(A1,B1,"M")&" months and "&DATEDIF(A1,B1,"MD")&" days". For January 15, 2026 to August 25, 2026 that produces "7 months and 10 days". For a full years, months and days answer, combine the Y, YM and MD units the same way.

Why do I get #NUM! from DATEDIF?

Almost always because the start date is later than the end date, or because one of the cells holds text that merely looks like a date. DATEDIF cannot count backwards. Swap the dates, and check that both cells are real dates by changing their format to a long date - text will not reformat.

Does DATEDIF work in Google Sheets too?

Yes. Google Sheets supports DATEDIF with exactly the same syntax and the same six units, and unlike Excel it documents the function openly. A formula written for Excel moves to Sheets unchanged, which makes DATEDIF the safest choice for files that are shared between the two.

Related guides

DATEDIF units explained - Y, M, D, YM, MD and YD

All six units, one shared example, and what each number really means.

How to calculate months between dates in Google Sheets

The same job in Sheets, plus a fallback formula that avoids DATEDIF.

How to calculate months between two dates

The anniversary rule behind the formulas, worked through by hand.

Common months-between mistakes - rounding and partial months

Where spreadsheet month counts go wrong, and how to catch it.

Daniel Mercer

Daniel Mercer builds and maintains the calculators on this site. He spent ten years in payroll and operations roles counting business days, notice periods and deadlines by hand, and writes these guides the way he would explain the count to a colleague - the rule first, a worked example second, and the common mistake called out before it happens.