How to Calculate Age from Date of Birth in Google Sheets

To calculate someone’s age in Google Sheets, put the date of birth in A2 and use:

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

This returns the number of complete years, so the age only goes up on the birthday itself. Below you will find how to calculate the age on a specific date, fill a whole column, handle empty cells and February 29 birthdays, and why YEARFRAC can give the wrong age.

Tested in Google Sheets. On November 9, 2026 we entered the formulas on this page in Google Sheets, including empty birth dates, February 29 birthdays and the YEARFRAC comparison, and compared the results with values calculated independently. How we test

How the formula works

DATEDIF(start_date, end_date, "Y") counts the whole years between two dates. With the date of birth as the start and TODAY() as the end, that is the person’s current age. Because TODAY updates automatically, the age changes on each birthday without editing the sheet.

For example, on November 10, 2026:

Date of birth Age Why
5/12/1990 36 36th birthday was in May 2026
11/10/2001 25 The birthday is today
11/11/2001 24 The birthday is tomorrow

Age on a specific date

To calculate the age on a fixed date, such as the end of a school year or a registration deadline, replace TODAY with a cell that holds that date. With the date in E1:

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

The $ signs keep the reference fixed when you copy the formula down. Someone born on May 12, 1990 is 36 on June 30, 2026.

Age in years, months and days

To show an exact age such as “36 years, 5 months, 29 days”, combine three DATEDIF units:

=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days"

Each unit is explained in DATEDIF in Google Sheets: all 6 units. For an age in completed months only, use =DATEDIF(A2, TODAY(), "M").

Fill a whole column and skip empty cells

If a date of birth is missing, DATEDIF treats the empty cell as day 0 (December 30, 1899) and returns a meaningless age: in our test it returned 126 on November 10, 2026. Wrap the formula in IF to leave those rows blank:

=IF(A2="", "", DATEDIF(A2, TODAY(), "Y"))

To calculate the whole column with a single formula in B2:

=ARRAYFORMULA(IF(A2:A="", "", DATEDIF(A2:A, TODAY(), "Y")))

Birthdays on February 29

In years without a February 29, DATEDIF counts the new year of age from March 1. Someone born on February 29, 2004 is still 22 on February 28, 2027 and turns 23 on March 1, 2027. In leap years the birthday is February 29 as usual: the same person turns 24 on February 29, 2028.

If your organization counts these birthdays on February 28 instead, you need a custom formula; DATEDIF will not do it.

Why not YEARFRAC?

Another common formula is =ROUNDDOWN(YEARFRAC(A2, TODAY()), 0). Be careful with it: YEARFRAC is a financial function, and by default it uses a 30/360 day count, where every month has 30 days. Around birthdays that can add a year too early.

For someone born on January 31, 1990, on January 30, 2026 (the day before the 36th birthday):

Formula Result
=DATEDIF(A2, B2, "Y") 35 (correct)
=ROUNDDOWN(YEARFRAC(A2, B2), 0) 36 (one day early)

Found in testing: we entered both formulas in Google Sheets for this exact case. DATEDIF returned 35 and ROUNDDOWN(YEARFRAC(…), 0) returned 36, a full day before the birthday. With the actual/actual convention (YEARFRAC(…, 1)) the result was 35.

If you do use YEARFRAC, Google recommends the actual/actual convention for non-financial calculations: =ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0). DATEDIF with “Y” is still the simplest choice for ages.

Group people by age

Once you have the age in column B, IFS can assign an age group:

=IFS(B2="", "", B2<18, "Under 18", B2<65, "18-64", TRUE, "65+")

Errors and odd results

#NUM! error

The date of birth is later than the end date, for example a typo such as 2062 instead of 2026. DATEDIF does not return negative ages.

#VALUE! error

The date of birth is text that Google Sheets cannot read as a date, often because it was typed in a different regional format (day first instead of month first). Check your locale in File → Settings.

The age shows as a date

The cell has a date format. Change it to Format → Number → Number.

Related guides