Confident Cells

Age and length of service in Excel: DATEDIF

20 November 2026 · 2 min read

To work out age in whole years, use =DATEDIF(date of birth, today, "Y"). The same formula with a start date gives length of service. DATEDIF counts complete years, so someone born on 14 March 1990 is 36 on 20 November 2026, not 37.

Age in whole years

Ada was born on 14 March 1990. How old is she on 20 November 2026?

  1. 2026 − 1990 = 36.
  2. Her birthday in March has already happened this year, so she has had all 36 birthdays.
  3. She is 36.

Someone born on 5 December 1990 is still 35 on 20 November 2026, because their birthday hasn't come round yet. Taking one year from the other would wrongly say 36. DATEDIF gets this right:

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

B2 is the date of birth, F1 is the date you're working out the age on, and "Y" asks for complete years. To always use today's date, put =TODAY() in F1.

ABCDEF
1NameBornAgeAge on20/11/2026
2Ada14/03/199036
3Ben05/12/199035
4Cara20/11/200026
C2 holds =DATEDIF(B2,$F$1,"Y"), copied down. Ben's birthday is in December, so he is 35. Cara turns 26 on the day itself.

Length of service in years and months

Dev started on 1 September 2019. On 20 November 2026 that is 7 complete years, plus the 2 complete months from September to November.

To show it as one piece of text, join them with &: =DATEDIF(B2,$F$1,"Y")&" years "&DATEDIF(B2,$F$1,"YM")&" months".

ABCDEFG
1NameStartedYearsMonths overService to20/11/2026
2Dev01/09/201972
3Eve15/01/2024210
Dev: 7 years and 2 months. Eve: 2 years and 10 months.

Common mistakes

Dividing the days by 365

=(F1-B2)/365 gives decimals like 36.71, and leap years make it slip a day out near birthdays. DATEDIF counts real birthdays.

#NUM! from DATEDIF

The start date is after the end date. Check the order: date of birth (or start date) first, then the later date.

DATEDIF doesn't appear as you type

Excel keeps DATEDIF hidden from its function list, but it still works. Type it out in full, with the unit in double quotes.

#VALUE!

One of the dates is really text, often from a pasted list. See numbers and dates stored as text.

Try it now

Your turn

In C2, work out each person's age in whole years on the date in F1. Your formula will be copied down to C7.

ABCDEF
1NameBornAgeAge on20/11/2026
2Ada14/03/1990
3Ben05/12/1990
4Cara20/11/2000
5Dan30/06/1985
6Ella28/12/1999
7Femi01/01/2004