DATEDIF Excel Function: Examples and Common Issues

Excel’s DATEDIF function calculates the time between two dates in years, months, or days. For example, with a start date in A2 and an end date in B2, =DATEDIF(A2,B2,"Y") returns the number of complete years between them; changing the final argument to “M” or “D” returns complete months or elapsed days.

DATEDIF syntax and background

The function’s syntax is =DATEDIF(start_date,end_date,unit). The start date must be earlier than or equal to the end date, and the unit tells Excel what kind of difference to calculate. DATEDIF is available in Microsoft 365 for Windows, although Excel may not display it in the function autocomplete or provide the same level of help for it as for newer functions. It still works when entered correctly.

DATEDIF originated as a compatibility function for Lotus 1-2-3 spreadsheets. That history helps explain why it can feel less discoverable than other Excel functions. You can type it directly into a cell or into the formula bar; you do not need to enable an add-in. In Microsoft 365 for Mac and Excel for the web, the function is also available, though interface details and autocomplete behavior can vary.

Dates in Excel are normally stored as serial numbers, so formulas work best when the cells contain actual dates rather than text that merely looks like a date. Enter dates in cells, then refer to those cells in DATEDIF. The examples below use commas between arguments, as in standard Microsoft 365 formulas.

What each DATEDIF unit means

The unit must be text in quotation marks. It controls whether DATEDIF reports a complete interval or a component remaining after larger intervals are counted. The six recognized units are:

"Y" counts complete years between the dates.
"M" counts complete months, including all complete years as months.
"D" counts the total number of elapsed days.
"YM" counts remaining months after complete years are excluded.
"MD" counts remaining days after complete months and years are excluded. Microsoft cautions that this unit can produce inaccurate results in some situations.
"YD" counts days after complete years are excluded.

For instance, a span of four years and five months produces four with unit Y, 53 with unit M, and five with unit YM. The M result includes the months in those four years: four times 12, plus five. This distinction is important when a question asks for “months between dates.” If it means total whole months, use M; if it means the leftover month portion after whole years, use YM.

Basic examples for years, months, and days

Suppose A2 contains 20-Aug-1990 and B2 contains 15-Jun-2024. With those dates in A2 and B2, the following formulas show how units change the answer:

=DATEDIF(A2,B2,"Y") returns 33, because the 34th anniversary, 20-Aug-2024, has not arrived by 15-Jun-2024.
=DATEDIF(A2,B2,"M") returns 405 complete months. That is 33 complete years, or 396 months, plus nine more complete months.
=DATEDIF(A2,B2,"YM") returns 9, the remaining complete months after those 33 years are set aside.
=DATEDIF(A2,B2,"MD") returns 26 for this example: the day component runs from the 20th to the 15th, with the preceding month’s days accounted for. Treat MD with care because of its documented edge cases.

For a short project interval, put 1-Feb-2024 in A2 and 1-Mar-2024 in B2. With that layout, =DATEDIF(A2,B2,"D") returns 29, because 2024 is a leap year and February has 29 days. With the same dates, =DATEDIF(A2,B2,"M") returns 1: one complete month has elapsed.

Build an age or duration display

A common use is showing someone’s age as years, months, and days rather than as a single number. Put the birth date in A2 and the date to calculate age on in B2. For the example dates above, enter:

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"

It displays 33 years, 9 months, 26 days. The first two components use complete years and leftover months; the final component uses MD and inherits its limitations. For a casual age display this may be useful, but verify results around month ends and leap days before relying on the day component in a formal record. If the end date should always be today, B2 can contain =TODAY(); that formula updates when Excel recalculates the workbook. With B2 holding today’s date, the displayed age changes over time.

For a project tracker, DATEDIF can provide a numeric day count while a separate column contains a readable label. If the project starts in A2 on 1-Feb-2024 and ends in B2 on 1-Mar-2024, =DATEDIF(A2,B2,"D") returns 29 elapsed days. That is not the same as counting both calendar dates as work or attendance days. To count both endpoints in a simple calendar-day range, use the date difference plus one. With the same A2 and B2 layout, =B2-A2+1 returns 30 dates, from February 1 through March 1 inclusive. Neither method excludes weekends or holidays.

Choosing between M, YM, and date subtraction

Choose the unit according to the question you need to answer. For “How many complete months have passed?” use M. For “How many months remain after whole years?” use YM. For “How many days elapsed?” use D. These results are not interchangeable, and they can differ from counting month names on a calendar. For example, dates at opposite ends of a month may span two named months but less than one complete month.

DATEDIF’s complete-month logic can surprise people when the starting day number is later than the ending day number. A period from January 31 to February 29 is not necessarily treated as a completed month by DATEDIF M, because the corresponding day of the month has not been reached according to its calculation. If your business rule treats the last day of one month to the last day of another as a full month, test that rule explicitly rather than assuming DATEDIF matches it.

Simple subtraction is often clearer when you only need elapsed days: subtract the earlier date from the later date. DATEDIF with D expresses the same kind of elapsed-day difference, but may be easier to read when the rest of a formula already uses DATEDIF. For approximate year lengths or prorated calculations, YEARFRAC is a different function with basis options; it calculates a fraction of a year, not a whole-number age. Pick the method that matches the definition you have been given.

Common errors and edge cases

If DATEDIF returns an error or an unexpected number, check the inputs and the intended meaning before changing the formula. Common causes include:

Dates are text: A cell may display something date-like but contain text. Excel may interpret dates differently depending on regional settings. Enter dates as real date values, or convert imported text to dates before using the function.
The dates are reversed: The start date must not be later than the end date. A reversed interval commonly returns #NUM!. Swap the references or correct the underlying dates if the order is accidental.
The unit is misspelled or unquoted: Use one of the six supported text units, such as "Y" or "YM". A unit that Excel does not recognize can return an error.
A result appears one unit short: DATEDIF counts complete years or months, not the number of calendar-year or calendar-month boundaries crossed. A birthday or monthly anniversary that has not yet arrived does not count as a complete interval.
Leap-day or month-end dates are involved: Anniversary conventions can be ambiguous, especially for a February 29 birthday in a non-leap year. Decide which date should count as the anniversary and check the result against that rule.

Also distinguish an interval from an inclusive count. From Monday to Tuesday is one elapsed day, although two date labels appear if both endpoints are listed. This is why a schedule that counts the start and finish dates may need one added to the elapsed-day result. For working days, use a workday-counting approach that accounts for weekends and holidays rather than DATEDIF D.

When another Excel function is a better fit

DATEDIF is handy when the desired result is a whole number of years, months, or days, but it is not a universal date calculator. If you need to add months to a date and handle month ends according to Excel’s date rules, EDATE is designed for that task. If you need a working-day count, NETWORKDAYS can count weekdays and optionally exclude a supplied holiday list. If you need a fraction of a year, YEARFRAC offers that different measure.

These functions answer different questions, so switching functions does not automatically fix a misunderstood requirement. Before building a report, write down whether you want complete months, calendar boundaries crossed, inclusive days, business days, or a fractional year. Then test the chosen calculation on a normal date pair and on edge cases such as month-end dates. A clear definition prevents a technically valid formula from producing a result that readers interpret incorrectly.

Frequently Asked Questions

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is a compatibility function and may not appear in autocomplete or the Insert Function list in the same way as many other functions. You can type the function and its arguments directly into a cell; its absence from suggestions does not mean the formula is unavailable.

Does DATEDIF count the start date?
No. DATEDIF with unit D gives the elapsed-day difference between the dates. If you are counting a list of calendar dates and want both the start and end dates included, add one to that difference, provided that inclusive counting matches your task.

What is the difference between DATEDIF M and YM?
M returns all complete months across the entire interval. YM returns only the leftover months after complete years are removed. A span of four years and five months therefore gives 53 with M and 5 with YM.

Can DATEDIF calculate age as of today?
Yes. Put the birth date in one cell and =TODAY() in the end-date cell, then use DATEDIF with Y for complete years. TODAY updates during recalculation, so the displayed age advances when the date changes.

Why does DATEDIF return #NUM!?
A common reason is that the start date is later than the end date. Check the order and confirm both inputs are valid Excel dates. Also make sure the unit is one of the supported codes and is written correctly.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.