DATEDIF in Excel: Calculate Age, Length of Service or Any Duration

By Emma Updated 2 November 2026 4 min read

DATEDIF: Difference between two dates

DATEDIF counts the number of complete years, months or days between two dates. It is the simplest way to get someone's age or length of service. For example, if B2 holds a date of birth, =DATEDIF(B2,TODAY(),"Y") returns the age in completed years.

One quirk: Excel does not suggest DATEDIF in formula AutoComplete or in the Insert Function dialog. It still works in all current versions, so type it in full, opening bracket included. Microsoft says it is kept for compatibility with Lotus 1-2-3 workbooks.

DATEDIF syntax and units

=DATEDIF(start_date,end_date,"unit")
  • start_date: the earlier date (birth, start of employment, start of a contract).
  • end_date: the later date. Often TODAY() or a cell holding a reference date.
  • unit: a letter code in double quotes that says what to count.

With A2 = 14/03/1988 and B2 = 02/11/2026:

UnitWhat it countsFormulaResult
"Y"Complete years=DATEDIF(A2,B2,"Y")38
"M"Complete months=DATEDIF(A2,B2,"M")463
"D"Days=DATEDIF(A2,B2,"D")14112
"YM"Months left over after complete years=DATEDIF(A2,B2,"YM")7
"MD"Days left over after complete months=DATEDIF(A2,B2,"MD")19
"YD"Days since the last anniversary of the start date=DATEDIF(A2,B2,"YD")233

DATEDIF only counts whole periods: from 14/03/1988 to 02/11/2026 there are 38 years, 7 months and 19 days, so "Y" returns 38, not 39. The unit can be upper or lower case, but it must be in quotes.

Calculate age from a date of birth

For age as of today:

=DATEDIF(B2,TODAY(),"Y")

The result updates every time the workbook recalculates and goes up on the birthday itself. For age on a fixed date (1 September for a school intake, 31 March for a year end), put that date in a cell and lock it with $ signs before filling the formula down:

=DATEDIF(B2,$E$1,"Y")

If you want to type the date inside the formula, use the DATE function rather than a date in quotes, whose meaning depends on regional settings: =DATEDIF(DATE(1988,3,14),TODAY(),"Y").

Calculate length of service

Same idea with the start date. Here the reference date is in E1 (02/11/2026) and the formula in C2 is filled down to C5.

ABCDE
1EmployeeStart dateService (years)Reference date02/11/2026
2Tom20/05/20242
3Sophie15/02/20215
4Amir09/10/20233
5Claire01/09/20197
=DATEDIF(B2,$E$1,"Y")

To count the employees with at least 5 years' service, run COUNTIF on the results: =COUNTIF(C2:C5,">=5") returns 2 (Sophie and Claire). To make those employees stand out in the table itself, conditional formatting can colour every length of service of 5 years or more.

Show a duration in years, months and days

Join three DATEDIF results with the & operator. For Tom (started 20/05/2024, reference date 02/11/2026):

=DATEDIF(B2,$E$1,"Y")&" years, "&DATEDIF(B2,$E$1,"YM")&" months, "&DATEDIF(B2,$E$1,"MD")&" days"

Result: 2 years, 5 months, 13 days. This is text, so you cannot calculate with it. Keep a numeric column next to it if you need to sort or filter.

DATEDIF pitfalls

#NUM!: the start date is after the end date

DATEDIF does not return negative gaps. If the dates are the wrong way round, you get #NUM!. Check the order of the arguments, or if the order can vary, use =DATEDIF(MIN(A2,B2),MAX(A2,B2),"D").

#VALUE!: one of the dates is text

A date imported from another system is sometimes text that only looks like a date. A clue: it sits on the left of the cell, whereas real dates align right. Convert the column first, for example with Data > Text to Columns, choosing the DMY date format in the last step.

#NAME?: missing quotes around the unit

=DATEDIF(A2,B2,Y) returns #NAME?: without quotes, Excel reads Y as an unknown name. Type "Y".

The result shows as a date

If the cell was formatted as a date, an age of 38 appears as 07/02/1900. The calculation is fine: apply the General format (Home tab, Number group).

The "MD" unit is unreliable

Microsoft advises against "MD": in some cases, especially around month ends, it returns a negative number, zero or an inaccurate result. For the days left over after complete months, this formula is safe:

=B2-EDATE(A2,DATEDIF(A2,B2,"M"))

It moves the start date forward by the number of complete months, then counts the remaining days. For Tom it returns 13, the same as "MD". If the cell shows a date, switch it to General.

One day before the anniversary, the year does not count

From 01/09/2019 to 31/08/2026, "Y" returns 6: the 7th year is only complete on 01/09/2026. That is what you want for an age, but bear it in mind if your own rules round to the year.

The same logic applies to months at the end of a month: from 31/01/2026 to 28/02/2026, "M" returns 0, because February never reaches the 31st. If you count contract months that start on the last day of a month, check these cases by hand.

Alternatives to DATEDIF

  • Number of days: plain subtraction is enough. =B2-A2 gives 14112 for our example, the same as "D" (format the cell as General).
  • Years with decimals: =YEARFRAC(A2,B2,1) returns about 38.64. For whole years: =INT(YEARFRAC(A2,B2,1)), which gives 38. Unlike DATEDIF, YEARFRAC is documented and listed in the function wizard.

For age or length of service in whole years, DATEDIF is still the most direct formula. Microsoft's page on the DATEDIF function lists its known limitations.

Emma · Writer, Excel Zone

Emma writes the tutorials on formulas and data cleaning: COUNTIF, SUMIF, DATEDIF, duplicates, drop-down lists. Each method is shown on a small table you can copy.