How to Use DATEDIF in Excel: Calculate Age and Date Differences

shape
shape
shape
shape
shape
shape
shape
shape
How to Use DATEDIF in Excel: Calculate Age and Date Differences - featured image

DATEDIF is a handy Excel function for finding the gap between two dates in complete years, months or days. It is the standard way to calculate a person’s age, an employee’s length of service, the age of an invoice or the time left on a contract. The function is a little unusual, because it does not appear in the formula suggestions, but it works in all modern versions of Excel. This guide explains the syntax, lists all six units, gives worked examples you can check by hand, shows how to build an age or tenure in years, months and days, explains the known problem with the MD unit and a safer alternative, and lists the causes of the #NUM! and #VALUE! errors.

Updated November 2026

In this guide

  1. DATEDIF syntax and units
  2. Worked example 1: years, months and days
  3. Calculate age in years, months and days
  4. Employee length of service
  5. The problem with the MD unit
  6. Full formula: years, months and days together
  7. DATEDIF compared with simple subtraction
  8. Count days overdue or days left
  9. Why DATEDIF returns an error
  10. Using DATEDIF across a whole column
  11. Quick recap
  12. Practice problems
  13. Common mistakes

DATEDIF syntax and units

The syntax is =DATEDIF(start_date, end_date, unit). The start date must be earlier than the end date, otherwise Excel returns #NUM!. The unit is text in quotation marks. Microsoft documents the DATEDIF function, including the six units shown below.

UnitReturnsExample result
“Y”Complete years6
“M”Complete months82
“D”Total days2499
“YM”Months after complete years10
“YD”Days after complete years, ignoring years307
“MD”Days after complete months (avoid)3

The example results use a start date of 15 January 2020 and an end date of 18 November 2026.

Worked example 1: years, months and days

Put 15-Jan-2020 in A2 and 18-Nov-2026 in B2. The formula =DATEDIF(A2,B2,"Y") returns 6, because six full years have passed, to 15 January 2026. The formula =DATEDIF(A2,B2,"YM") returns 10, the whole months from 15 January to 15 November. The total number of whole months is =DATEDIF(A2,B2,"M"), which returns 82, because 6 times 12 plus 10 equals 82. The total days are =DATEDIF(A2,B2,"D"), which returns 2,499. You can check the days: six years from 15 January 2020 contain 2,192 days, including two leap days in 2020 and 2024, and the remaining 15 January to 18 November 2026 is 307 days. Adding them gives 2,499.

Calculate age in years, months and days

To build an age text, join the units: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". For a person born on 10 May 1990, with the end date 18 November 2026, the result is 36 years and 6 months, since 10 May to 10 November is six months. The extra 8 days are 10 November to 18 November. Because TODAY updates every day, the age stays current whenever the file is opened. If you need a fixed report date, put it in a cell and use that cell instead of TODAY, so the report does not change when it is reopened.

Employee length of service

Human resources files often need tenure. With the hire date in A2 and the report date in B2, use =DATEDIF(A2,B2,"Y") for full years of service and =DATEDIF(A2,B2,"M") for total months, which is often used for leave and bonus rules. For an employee hired on 1 March 2023 and a report date of 1 March 2026, the years are 3 and the months are 36. You can build bands such as under 1 year, 1 to 3 years and over 3 years with an IF function. Our IF statements guide shows nested examples.

The problem with the MD unit

Microsoft advises against the “MD” unit, because it has known limits and can return a negative number, a zero or an inaccurate result in some cases. A safer way to get the leftover days is to subtract the date that is the full number of months after the start date: =B2-EDATE(A2,DATEDIF(A2,B2,"M")). For 15 January 2020 and 18 November 2026, the months are 82, EDATE moves the start to 15 November 2026, and the difference with 18 November is 3 days. Use this formula for the days part of an age or tenure, and combine it with “Y” and “YM” for the other parts.

Full formula: years, months and days together

Putting it together with the safe days formula: =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&(B2-EDATE(A2,DATEDIF(A2,B2,"M")))&" days". For our example, the result is 6 years, 10 months, 3 days. Check it by counting: 6 years to 15 January 2026, then 10 months to 15 November 2026, then 3 days to 18 November 2026. This version works in every month, including months with different lengths, which is where the MD unit tends to fail.

DATEDIF compared with simple subtraction

If you only need the total days, you can simply subtract: =B2-A2 returns 2,499 for our example when the cell is formatted as a number. For total years, dividing by 365 gives a decimal that is slightly wrong because of leap years, and =YEARFRAC(A2,B2) returns a fraction such as 6.84 years. Use DATEDIF when you need complete years, as for age or eligibility, since a person is 36 only after the full birthday. Use YEARFRAC when you need a fractional year for finance. Use plain subtraction when you only need days, such as the days until a deadline or the age of an invoice, and see our days until December 1st calculator for a ready example.

Count days overdue or days left

For an invoice list, put the due date in A2 and use =MAX(0,TODAY()-A2) to show days overdue, which gives zero for invoices that are not yet due. For a contract end date in B2, =B2-TODAY() shows the days left, and =DATEDIF(TODAY(),B2,"M") shows the complete months left. If the end date is already past, DATEDIF returns #NUM!, so wrap the formula: =IF(B2<TODAY(),"Expired",DATEDIF(TODAY(),B2,"M")&" months left"). Add conditional formatting to turn the cell red when fewer than 30 days remain, so a manager can scan a long list quickly.

Why DATEDIF returns an error

The #NUM! error appears when the start date is later than the end date, so check the order or swap them with MIN and MAX: =DATEDIF(MIN(A2,B2),MAX(A2,B2),"D"). The #VALUE! error appears when a date is stored as text, so convert it with DATEVALUE or follow our guide to converting text to numbers in Excel. A misspelled unit, or a unit without quotation marks, also causes an error. Finally, if the result looks like a date such as 12-Jan-1900, the cell is formatted as a date, so change the format to General or Number.

Using DATEDIF across a whole column

In a real workbook you will rarely calculate one date at a time. Put the start dates in column A and the end date in a single cell, such as $E$1, so you can change the report date once. Then fill =DATEDIF(A2,$E$1,"Y") down the column. If some rows have no start date, wrap the formula to keep the sheet clean: =IF(A2="","",DATEDIF(A2,$E$1,"Y")). To count how many people are over a given age, use COUNTIF on the result column, and see our guide to COUNTIFS for multiple conditions such as department and years of service. For a summary by band, build a small table with the labels Under 1 year, 1 to 3 years and Over 3 years, and use COUNTIFS with the lower and upper limits. This approach turns a plain list of dates into a report that a manager can read at a glance, and because every result is a formula, it updates when the report date or the source data changes. Keep the raw dates in one sheet and the report in another, so nobody overwrites the data by accident.

Quick recap

Use DATEDIF with “Y” for complete years, “M” for complete months, “D” for days and “YM” for months after the years. Avoid “MD” and calculate leftover days with the end date minus EDATE of the start date. Always put the earlier date first, make sure dates are real dates and not text, and wrap formulas with IF to handle blank rows and expired dates.

Practice problems

Try these. Problem 1: from 1 January 2026 to 1 March 2026, the days are 59 because January has 31 days and February has 28. Problem 2: from 31 December 2025 to 1 January 2026, the days are 1. Problem 3: from 5 June 2000 to 5 June 2026, the years are 26 and the months after years are 0. Problem 4: from 20 August 2025 to 19 August 2026, the years are 0 and the months are 11, because the full year is not complete until 20 August. Problem 5: from 10 October 2026 to 18 November 2026, the days are 39, because 10 October to 10 November is 31 days and 8 more days reach 18 November. Check each result against a calendar.

Common mistakes

The most common mistake is to put the later date first, which causes #NUM!. Another is to use “MD” and trust the result. A third is to type dates as text, such as “15.01.2020”, that Excel does not read as dates because of regional settings. A fourth is to calculate age by dividing days by 365, which gives the wrong answer around birthdays. A fifth is to hardcode a date instead of using a cell reference or TODAY. Finally, remember that DATEDIF is not listed in the function wizard, so you must type it by hand, and share a note with colleagues who may not know it exists.

Frequently asked questions

What does DATEDIF do in Excel?

It returns the number of complete years, months or days between two dates.

Why is DATEDIF not in the function list?

It is a legacy function that Excel does not list in the wizard, but it works when you type it.

How do I calculate age in Excel?

Use =DATEDIF(birth_date,TODAY(),”Y”) for complete years.

Why does DATEDIF return #NUM!?

The start date is later than the end date.

Should I use the MD unit?

Microsoft advises against it. Use end date minus EDATE of the start date instead.

Related guides and free tools

Sources: DATEDIF function, Microsoft Support

Leave a Reply

Your email address will not be published. Required fields are marked *