# How to calculate age in years, months and days in Excel

Join three DATEDIFs: `"y"` for years, `"ym"` for the months left over and `"md"` for the days left over.

**Excel:** `=DATEDIF(B2,$F$1,"y")&" years, "&DATEDIF(B2,$F$1,"ym")&" months, "&DATEDIF(B2,$F$1,"md")&" days"` (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 |
| Olivia | 2019-04-12 |  |  |  |  |
| Mateo | 2023-12-30 |  |  |  |  |
| Hana | 2020-10-29 |  |  |  |  |
| Leo | 2024-02-29 |  |  |  |  |
| Zara | 2025-11-05 |  |  |  |  |

Formula in C2, filled to C6: `=DATEDIF(B2,$F$1,"y")&" years, "&DATEDIF(B2,$F$1,"ym")&" months, "&DATEDIF(B2,$F$1,"md")&" days"`

Result: 7 years, 6 months, 17 days; 2 years, 9 months, 29 days; 6 years, 0 months, 0 days; 2 years, 8 months, 0 days; 0 years, 11 months, 24 days

## How it works

`"y"` counts complete years, `"ym"` the complete months after the last birthday, and `"md"` the days after the last monthly anniversary. Joining them with `&` builds the label. Mateo was born on December 30, so on October 29 he is 29 days past his September 30 monthly anniversary.

## Other ways

- `=DATEDIF(B2,$F$1,"y")&" years, "&DATEDIF(B2,$F$1,"ym")&" months, "&$F$1-EDATE(B2,DATEDIF(B2,$F$1,"m"))&" days"`: Counts the leftover days from the last monthly anniversary with EDATE, which avoids the "md" problem near month ends.
- `=DATEDIF(B2,$F$1,"y")&"y "&DATEDIF(B2,$F$1,"ym")&"m"`: A compact label without days, like 7y 6m. (Years and months only.)

## Common mistakes

- Microsoft warns that `"md"` can give wrong results near month ends: from January 31 to March 1 it returns -2. If exact days matter, use the EDATE version.
- The result is text, so you can't sort or average it. Keep a numeric age column, `DATEDIF(B2,$F$1,"y")`, next to it.
- Lock the as-of date as `$F$1`, or each row points at a different cell when filled down.

## Questions

**How do I leave out "0 months" or "0 days"?**

Wrap each part in IF, like `IF(DATEDIF(B2,$F$1,"ym")=0,"",DATEDIF(B2,$F$1,"ym")&" months")`, or join the parts with TEXTJOIN, which skips empty text.

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

Put `=TODAY()` in F1. The ages then update every day.

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

Source: https://spreadsheetpractice.com/formulas/age-in-years-months-and-days