Excel DATEDIF Formula Examples for Age and Date Gaps

Excel’s DATEDIF function calculates the number of complete years, months, or days between two dates: use =DATEDIF(A2,B2,"Y") for complete years, =DATEDIF(A2,B2,"M") for complete months, or =DATEDIF(A2,B2,"D") for total days. Put the earlier date in A2 and the later date in B2; for example, dates of June 15, 1990 and November 2, 2026 return 36 years, 436 complete months, and 13,300 days, respectively.

What DATEDIF calculates

DATEDIF compares two Excel dates and returns a count based on the unit you specify. It is useful for age calculations, service anniversaries, elapsed time, and other situations where a simple subtraction does not answer the question. The function has three arguments: a start date, an end date, and a unit code in quotation marks.

The start date must be earlier than or equal to the end date. Excel stores real dates as serial numbers, so the cells should contain valid date values rather than text that merely looks like a date. For clarity, the examples below use A2 for the start date and B2 for the end date. With A2 set to June 15, 1990 and B2 set to November 2, 2026, the examples return:

"Y": 36 complete years.
"M": 436 complete months.
"D": 13,300 elapsed days.
"YM": 4 months after the last complete year.
"YD": 140 days after the last complete year.

DATEDIF is an older compatibility function. It works in Microsoft 365 for Windows, but it may not appear in Excel’s function autocomplete or in the Insert Function list. You can still type it directly into a cell. Its absence from autocomplete does not mean the formula is unavailable.

Examples for calculating age

For age in completed years, calculate the difference between a birth date and an as-of date using the Y unit. Assume A2 contains June 15, 1990 and B2 contains November 2, 2026. Enter =DATEDIF(A2,B2,"Y"); the result is 36. The result changes to 37 only when the end date reaches the person’s next birthday.

To calculate someone’s age today rather than on a fixed date, assume A2 contains the birth date June 15, 1990. Enter =DATEDIF(A2,TODAY(),"Y"); with today’s date set to November 2, 2026, the result is 36. TODAY() updates when Excel recalculates, so this formula is appropriate for a live age display. For a report that must remain tied to a particular date, use a date in a cell such as B2 instead.

To show age as years and remaining months, assume A2 contains June 15, 1990 and B2 contains November 2, 2026. Enter =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months"; the displayed result is 36 years, 4 months. The Y calculation supplies complete years, while YM counts the additional whole months after those years. This text result is convenient for display, but it is text rather than a numeric age value, so it is not suited to arithmetic without further calculation.

Examples for months, days, and mixed units

The M unit returns complete months, not a rounded estimate of the number of months. Assume A2 contains January 15, 2024 and B2 contains March 14, 2024. Enter =DATEDIF(A2,B2,"M"); the result is 1, because one full month has passed but two full months have not. If B2 is March 15, the result becomes 2.

For the total number of days between two dates, assume A2 contains October 1, 2026 and B2 contains October 20, 2026. Enter =DATEDIF(A2,B2,"D"); the result is 19. This is elapsed time: it does not count both the starting day and ending day as separate full days. If a work rule or event count requires inclusive counting, that is a different convention and should be handled explicitly rather than assumed.

The YM unit can report leftover whole months after complete years. With A2 equal to June 15, 1990 and B2 equal to November 2, 2026, enter =DATEDIF(A2,B2,"YM"); the result is 4. YD reports leftover days after complete years: with those same dates, =DATEDIF(A2,B2,"YD") returns 140. These units are useful for breaking a time span into parts, but they do not return a total duration. For example, YM is not the total number of months; use M for that.

DATEDIF unit codes at a glance

"Y" counts complete years between the dates.
"M" counts complete months between the dates.
"D" counts elapsed days between the dates.
"YM" counts complete months after subtracting complete years.
"YD" counts days after subtracting complete years.
"MD" is intended to return the difference between the day numbers while ignoring months and years. Treat this unit cautiously: it can produce unexpected results, particularly around month ends.

The unit must be enclosed in straight double quotation marks. A misspelled unit or missing quotation mark can cause an error. For ordinary totals, Y, M, and D are usually the clearest choices. Use YM or YD only when a remainder after whole years is specifically what you need.

How to enter the formula and prepare dates

Enter the earlier date in one cell and the later date in another, such as A2 and B2. In Microsoft 365 for Windows, you can type dates into cells and use Home > Number Format to choose a date display if needed.
Select the cell where you want the result, type the formula beginning with an equals sign, and press Enter.
Check that the dates are stored as dates. Changing a cell’s number format may change how a value appears, but it does not convert text into a valid date.
For a reusable calculation, copy the formula down alongside other date pairs. Relative references such as A2 and B2 adjust to A3 and B3 when copied down.

Date entry can depend on regional settings. A typed value such as 03/04/2026 may mean March 4 or April 3, depending on the system’s date convention. If a workbook will be shared, use unambiguous entry methods, such as selecting a date with the calendar control where available, or build dates with Excel’s DATE function. When importing data, check that dates have not arrived as text. On Mac and Excel for the web, the formula itself is the same; the menus and date-entry controls can look different from the Windows ribbon.

Common errors and month-end surprises

A #NUM! error commonly means the start date is later than the end date. Check the two cells’ values and their order. If users may reverse the dates, decide whether the workbook should reject that input or calculate an absolute gap; do not silently swap dates unless that is the intended business rule. Blank cells can also create misleading results, so consider how the sheet should behave before filling a formula down a long column.

If A2 is the start date and B2 is the end date, this formula displays a blank until both cells have values: =IF(OR(A2="",B2=""),"",DATEDIF(A2,B2,"Y")). For a valid pair of dates, it returns the same complete-year count as the basic Y formula; if either cell is empty, it returns an empty display. It does not prevent an error when the dates are reversed, so validate their order separately if that can happen.

Month-end dates deserve particular attention because calendar months have different lengths. A complete month is not always the same as 30 days, and a date at the end of a month may not have a matching day number in the next month. Test the exact start and end dates that matter to your schedule, especially when calculating billing periods, eligibility dates, or service time. Do not assume that DATEDIF’s month result is equivalent to dividing elapsed days by 30.

The MD unit is another potential source of confusion. Although it is intended to ignore the months and years, Microsoft’s documentation cautions that it can return a negative number or an incorrect result in some situations. Avoid relying on MD for high-stakes or month-end-sensitive calculations. If the required definition is unclear, specify whether you need elapsed days, a calendar-day remainder, or an inclusive count, then build and verify a formula for that precise rule.

DATEDIF compared with date subtraction

Subtracting one date from another gives elapsed days, while DATEDIF lets you request completed years or months. Assume A2 contains October 1, 2026 and B2 contains October 20, 2026. Enter =B2-A2; the result is 19 days, the same elapsed-day count as DATEDIF with D. If the result appears as another date, change the result cell’s number format to General or Number.

For age, subtracting birth year from the current year can be one too high before the birthday. DATEDIF with Y accounts for whether the birthday has occurred by the chosen end date. For reporting, another function or a custom formula may be preferable when you need a specific day-count convention, fractional years, or a precisely defined treatment of month ends. The important distinction is that DATEDIF reports completed calendar units; it does not estimate a fractional month or year.

Frequently Asked Questions

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is a legacy compatibility function and may not appear in autocomplete or the function wizard. In Microsoft 365 for Windows, type the formula directly into the cell, including the equals sign, parentheses, and quoted unit code.

Does DATEDIF count the start date or end date?
For the D unit, it returns the elapsed difference between the two dates. October 1 to October 20 returns 19, not 20. If a task requires counting both endpoint dates, use a clearly stated inclusive-count rule.

How do I calculate age in years and months?
Use the Y unit for complete years and YM for the additional whole months. With a birth date in A2 and an as-of date in B2, combine DATEDIF(A2,B2,"Y") and DATEDIF(A2,B2,"YM") in a display formula, as shown above.

Can DATEDIF calculate a future date or a negative duration?
It requires the start date to be earlier than or equal to the end date. Reversing the dates can produce #NUM!; it is not a signed-duration function. Choose the order or error handling that matches the purpose of the calculation.

Will the formula work in Excel for Mac or Excel for the web?
The DATEDIF formula syntax is the same in current Excel versions for Mac and Excel for the web. Interface details such as date formatting and where commands appear can differ, but the unit codes and argument order do not change.

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.