DATEDIF Spreadsheet Examples: Years, Months, and Days

In Excel for Microsoft 365, use DATEDIF(start_date,end_date,unit) to calculate the elapsed time between two dates in completed years, months, or days. For example, if A2 contains March 15, 2020 and B2 contains November 2, 2024, =DATEDIF(A2,B2,"Y") returns 4, while =DATEDIF(A2,B2,"D") returns 1,693.

What DATEDIF Calculates

DATEDIF measures the interval between a starting date and an ending date. Its key feature is that the year and month units count completed intervals, rather than simply subtracting calendar-year or calendar-month numbers. That makes it useful for age calculations, service anniversaries, elapsed contract periods, and other questions such as “How many full months have passed?”

The function has three arguments: a start date, an end date, and a unit in quotation marks. The unit tells Excel what part of the interval to return. The end date must be on or after the start date; otherwise, Excel returns the #NUM! error. Use actual Excel date values in the cells, not text that only looks like a date. When typing dates, your computer’s regional settings can affect which date formats Excel recognizes.

DATEDIF is retained in Excel for compatibility, but it is unusual: it may not appear in formula autocomplete or in the function list as readily as newer functions. You can still type it directly into a cell. The examples below use comma argument separators, as in Excel for Microsoft 365 on Windows.

The Six DATEDIF Units

Enter the unit as text inside double quotation marks. These are the supported unit codes and their intended meanings:

"Y" returns the number of complete years.
"M" returns the number of complete months in the entire interval, including months across multiple years.
"D" returns the total number of elapsed days.
"YM" returns the month remainder after complete years are excluded.
"YD" returns the day remainder after complete years are excluded.
"MD" returns the day remainder after complete months are excluded, but it can give unreliable results in some date combinations.

These units answer different questions. For a period of four years and seven months, the "M" result is 55 complete months, while "YM" is 7. The latter is not the total month count; it is only the month component remaining after whole years are removed.

Example: Calculate Completed Years or Age

Suppose A2 contains a birth date of June 15, 2000, and B2 contains the date the age should be measured, November 2, 2026. With those dates in A2 and B2, enter =DATEDIF(A2,B2,"Y") to return 26 completed years. The result changes to 27 only when the specified end date reaches June 15, 2027.

For a live age calculation, keep the birth date in A2 and use =DATEDIF(A2,TODAY(),"Y"). It returns the person’s completed age as of the current date and updates when the workbook recalculates. TODAY is a volatile function, so the displayed result can change over time without the birth date changing. For a report that must remain reproducible, use a fixed “as of” date in another cell instead.

An age calculation is only as reliable as its dates. Check that the birth date is a true date value, and decide whether you need age on today’s date or on a particular reporting date. If a supplied end date might precede the birth date, validate the inputs before relying on the result.

Example: Count Total Months and Days

Use the same example interval, with A2 containing March 15, 2020 and B2 containing November 2, 2024. With those dates in A2 and B2, =DATEDIF(A2,B2,"M") returns 55 complete months. This is useful for measuring completed billing periods or tenure in months, but it does not say that 55 calendar month names lie between the dates.

With the same cell layout, =DATEDIF(A2,B2,"D") returns 1,693 elapsed days. The start date is not counted as a full elapsed day: moving from one date to the next produces a difference of one day. Leap days are included because Excel is comparing date serial values, not estimating a year as a fixed number of days.

For a remaining-days component after complete months, it is safer to calculate the date reached after those months and subtract it from the end date. With A2 as March 15, 2020 and B2 as November 2, 2024, =B2-EDATE(A2,DATEDIF(A2,B2,"M")) returns 18. DATEDIF counts 55 complete months; EDATE advances March 15, 2020 by 55 months to October 15, 2024; the remaining elapsed interval is 18 days.

Example: Display Years, Months, and Days Together

A common request is a result such as “4 years, 7 months, 18 days.” Use the start and end dates in A2 and B2, as in the March 15, 2020 to November 2, 2024 example. The following formula combines completed years, the leftover months, and a remaining-days calculation based on EDATE:

With A2 containing March 15, 2020 and B2 containing November 2, 2024, enter =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&(B2-EDATE(A2,DATEDIF(A2,B2,"M")))&" days". It returns “4 years, 7 months, 18 days.” In HTML, & represents Excel’s concatenation operator; when entered in Excel, use the ordinary ampersand character.

This is a text result, which is convenient for a label but not ideal for further arithmetic. For calculations or sorting, keep years, months, and days in separate numeric cells. Also note that a “years, months, days” breakdown is a calendar-based description, not a single duration expressed in a fixed number of days.

How to Enter and Check the Formula

In Excel for Microsoft 365 on Windows, enter or paste a formula into a blank cell and press Enter. For a quick check, create two date cells and test a simple one-day interval before applying the formula to a large list. You can also select a formula cell and inspect the formula bar to confirm that the unit is in quotation marks and that the start and end references are in the intended order.

Put the earlier date in the start-date cell and the later date in the end-date cell.
Choose the unit that matches the question: completed years, total months, or elapsed days.
Type the formula with cell references and a quoted unit code.
Check the result against a known pair of dates, especially around birthdays, month ends, and leap days.

If a result displays as a date instead of a number, the formula may be correct but the result cell may have a date format. Select the cell and choose Home > Number Format, then select General or Number. On Mac, Excel has the same DATEDIF function and formula structure; ribbon labels and placement can differ slightly by release. Excel for the web also supports ordinary DATEDIF formulas, although its interface is browser-based.

Common DATEDIF Problems and Edge Cases

The most frequent mistake is expecting "M" to return the number of calendar months touched by an interval. It returns complete months. For instance, a period beginning late in one month and ending before the matching day in the next month may not count as one completed month. This behavior matters for month-end dates, where a casual subtraction of month numbers can give a different answer from a completed-month calculation.

Another common issue is using "MD" to build a years-months-days breakdown. Microsoft warns that this unit can produce inaccurate results in some situations. It is better to calculate the leftover days by subtracting the date produced by EDATE after adding the number of completed months, as shown above. Still, month-end dates deserve a deliberate test: functions that advance dates by months must handle dates such as January 31, which has no matching day in February.

#NUM!: The end date is earlier than the start date, or one of the inputs is not a valid date value.
#VALUE!: An argument may be text that Excel cannot interpret as a date or a valid unit.
Unexpected zero: The interval may not yet contain a complete year or month, even if it crosses into another calendar year or month.
Formula appears as text: The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format and re-enter the formula.

DATEDIF does not automatically make a calculation inclusive of both endpoints. For a day count that includes both the starting and ending date, a simple elapsed-day result is often increased by one, but only if that is the counting rule your task requires. Decide the rule before changing the formula; inclusive counting is not interchangeable with elapsed time.

DATEDIF Compared with Other Date Formulas

DATEDIF is convenient when the required output is a completed interval. For total elapsed days, direct subtraction is simpler: with the start date in A2 and end date in B2, =B2-A2 returns the same elapsed-day difference as DATEDIF with the "D" unit. Subtraction can also be easier to inspect when you want a numeric result for later calculations.

For month-based calculations, DATEDIF’s "M" unit has a specific meaning: fully completed months. EDATE is useful when you need the date reached after a number of months, such as a renewal date. YEARFRAC can express a year fraction, but its result depends on its basis setting and is not the same as an integer count of completed years. Choose based on the question, rather than treating date functions as interchangeable.

For a readable label that must handle complex anniversary rules, separate helper columns can be easier to audit than one long concatenated formula. They also make it simpler to spot whether an unexpected answer comes from the source date, the chosen unit, or the way remaining days are calculated.

Frequently Asked Questions

Why does DATEDIF not appear in Excel’s autocomplete?
DATEDIF is retained for compatibility and may not be suggested like many current functions. Type the function name and its arguments directly; the formula can still calculate normally.

Does DATEDIF count the start date?
No. It measures the elapsed interval between the dates. For example, two consecutive dates have a difference of one day, not two. Add one only when your specific task calls for inclusive day counting.

What is the difference between the M and YM units?
"M" returns all complete months in the interval. "YM" returns only the months left after complete years are removed. For four years and seven months, those results are 55 and 7, respectively.

Can DATEDIF calculate a future age or anniversary?
Yes, if the end date is on or after the start date. Put the future date in the end-date cell and use "Y" for completed years. A future anniversary that has not yet occurred will not be counted as a completed year.

Why does DATEDIF return #NUM!?
The usual cause is that the end date is earlier than the start date. Check the order of the cell references and confirm both cells contain real Excel dates rather than unrecognized text.

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.