# How to calculate age from a date of birth in Excel

Use =DATEDIF(birth_date, as_of_date, "y"). It returns completed years, so the age only goes up on the birthday.

**Excel:** `=DATEDIF(B2,$F$1,"y")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Name | Date of birth | Age |  | As of | 2026-10-29 |
| Maria | 1990-05-04 |  |  |  |  |
| James | 1985-11-02 |  |  |  |  |
| Aiko | 2001-10-29 |  |  |  |  |
| Lars | 1978-12-31 |  |  |  |  |
| Chidi | 1996-02-29 |  |  |  |  |

Formula in C2, filled to C6: `=DATEDIF(B2,$F$1,"y")`

Result: 36; 40; 25; 47; 30

## How it works

DATEDIF counts complete units between two dates; `"y"` means whole years. James turns 41 on November 2, so on October 29 he is still 40. Using a cell for the "as of" date keeps results stable; put `=TODAY()` in it for a live age.

## Other ways

- `=YEAR($F$1)-YEAR(B2)-(DATE(YEAR($F$1),MONTH(B2),DAY(B2))>$F$1)`: Works without DATEDIF: subtract the years, then take one off if this year's birthday hasn't happened yet.
- `=INT(($F$1-B2)/365.25)`: A common shortcut, but it can be off by one around birthdays. (Approximate.)

## Common mistakes

- Subtracting years alone (`YEAR(F1)-YEAR(B2)`) adds a year for everyone whose birthday is still to come.
- DATEDIF is missing from Excel's autocomplete and help, but it works in every version and in Google Sheets.
- Dividing days by 365 drifts with leap years. Prefer DATEDIF.

## Questions

**How do I show age in years and months?**

Combine two DATEDIFs: `=DATEDIF(B2,F1,"y")&" years, "&DATEDIF(B2,F1,"ym")&" months"`.

**How do I calculate age as of today?**

Use `=DATEDIF(B2,TODAY(),"y")`. It updates every day.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/calculate-age-from-date-of-birth