Age and length of service in Excel: DATEDIF
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?
- 2026 − 1990 = 36.
- Her birthday in March has already happened this year, so she has had all 36 birthdays.
- 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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Born | Age | Age on | 20/11/2026 | |
| 2 | Ada | 14/03/1990 | 36 | |||
| 3 | Ben | 05/12/1990 | 35 | |||
| 4 | Cara | 20/11/2000 | 26 |
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.
=DATEDIF(B2,$F$1,"Y")gives the whole years: 7.=DATEDIF(B2,$F$1,"YM")gives the months left over after the whole years: 2.=DATEDIF(B2,$F$1,"M")gives the total in months: 86.
To show it as one piece of text, join them with &: =DATEDIF(B2,$F$1,"Y")&" years "&DATEDIF(B2,$F$1,"YM")&" months".
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Started | Years | Months over | Service to | 20/11/2026 | |
| 2 | Dev | 01/09/2019 | 7 | 2 | |||
| 3 | Eve | 15/01/2024 | 2 | 10 |
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
In C2, work out each person's age in whole years on the date in F1. Your formula will be copied down to C7.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Born | Age | Age on | 20/11/2026 | |
| 2 | Ada | 14/03/1990 | ||||
| 3 | Ben | 05/12/1990 | ||||
| 4 | Cara | 20/11/2000 | ||||
| 5 | Dan | 30/06/1985 | ||||
| 6 | Ella | 28/12/1999 | ||||
| 7 | Femi | 01/01/2004 |