How to / Dates

How to calculate age from a date of birth in Excel

Use =DATEDIF(B2, C2, "Y"), with the date of birth in B2 and the date to measure to in C2. For today, use TODAY() in place of C2.

Age in complete years

ABCDE
1NameBornOn
2Ana1990-05-202026-03-0135
3Ben2000-03-012026-03-01

In E2: =DATEDIF(B2, C2, "Y") → 35

Born 20 May 1990; on 1 March 2026 the 36th birthday has not come yet.

How it works

  1. DATEDIF counts the complete units between two dates.
  2. "Y" asks for complete years, so the age only goes up on the birthday.
  3. Excel does not list DATEDIF when you start typing it, but it works in every version.

Variations

  1. On the birthday itself

    ABCDE
    1NameBornOn
    2Ana1990-05-202026-03-0126
    3Ben2000-03-012026-03-01

    In E2: =DATEDIF(B3, C3, "Y") → 26

    Ben was born on 1 March 2000, and turns 26 on the day in C3.

  2. Years and months

    ABCDE
    1NameBornOn
    2Ana1990-05-202026-03-0135 years, 9 months
    3Ben2000-03-012026-03-01

    In E2: =DATEDIF(B2, C2, "Y")&" years, "&DATEDIF(B2, C2, "YM")&" months" → 35 years, 9 months

    "YM" is the months left over after the complete years.

  3. With YEARFRAC

    ABCDE
    1NameBornOn
    2Ana1990-05-202026-03-0135
    3Ben2000-03-012026-03-01

    In E2: =INT(YEARFRAC(B2, C2, 1)) → 35

  4. Age in complete months

    ABCDE
    1NameBornOn
    2Ana1990-05-202026-03-01429
    3Ben2000-03-012026-03-01

    In E2: =DATEDIF(B2, C2, "M") → 429

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related