How to calculate age in Excel
To calculate age in Excel, use DATEDIF with the date of birth, today's date and the unit "Y": =DATEDIF(A2,TODAY(),"Y"). It counts whole completed years, so the result is the age a person would say out loud.
A plain subtraction gives days, not years, and dividing by 365 gives the wrong age near a birthday. This guide shows the reliable formulas and how to read the result. Practice them in the dates lessons at the end.
1. Age in whole years
DATEDIF takes a start date, an end date and a unit. With "Y" it returns the number of complete years between the two dates. To get the age today, use TODAY as the end date. The example below uses a fixed end date so that the result never changes.
Example: Age on 15 March 2025
| A | B | C | |
|---|---|---|---|
| 1 | Born | On date | Age |
| 2 | 1990-05-20 | 2025-03-15 | 34 |
Born on 20 May 1990. On 15 March 2025 the 35th birthday has not arrived yet, so the age is 34.
2. Age in years and months
The unit "YM" returns the months left over after the complete years, and "MD" returns the days left over after the months. Join the pieces with & and the text you want to show.
Example: Years and months
| A | B | C | |
|---|---|---|---|
| 1 | Born | On date | Age |
| 2 | 1990-05-20 | 2025-03-15 | 34 years, 9 months |
34 complete years end on 20 May 2024, and 9 more complete months end on 20 February 2025.
3. A shorter formula that is nearly right
Without DATEDIF, take the days between the dates, divide by 365.25 and cut off the decimals with INT. This works for most dates, but it can be one off right on a birthday, so prefer DATEDIF when the exact age matters, such as for an eligibility rule.
Example: Age with INT
| A | B | C | |
|---|---|---|---|
| 1 | Born | On date | Age |
| 2 | 1990-05-20 | 2025-03-15 | 34 |
The dates are 12,718 days apart. Divided by 365.25 that is 34.82, and INT cuts it to 34.
4. Make it a column for a whole list
Put the date of birth in column A and the formula in column B, then fill it down. Keep the dates real dates, not text: if a date is left-aligned in its cell, Excel is reading it as text and DATEDIF will return #VALUE!. The start date must also be earlier than the end date, or DATEDIF returns #NUM!.
Common mistakes
Dividing days by 365. A person born 35 years ago can show as 34 because of leap days.
Fix: Use DATEDIF with "Y", or divide by 365.25 and take the INT.
The result shows as a date, such as 1/2/1900. The cell has a date format.
Fix: Set the format of the result cell to General or Number.
Looking for DATEDIF in the function list. Excel does not list it, but it works when typed.
Fix: Type the whole formula by hand, with the unit in quotation marks.
Frequently asked questions
How do I calculate age at a specific date?
Replace TODAY() with a cell that holds that date, or with DATE(2026,12,31).
Does the age update every day?
Yes. TODAY recalculates whenever the workbook opens or changes, so the age is always current.
How do I get the number of days between two dates instead?
Subtract one date from the other, or see the guide on calculating days between dates.
Related functions
- DATEDIFDATEDIF counts complete years, months or days between two dates, ideal for ages and service length.
- TODAYTODAY returns the current date and NOW adds the time.
- MONTHMONTH, YEAR and DAY return one part of a date as a number.
- EOMONTHEOMONTH returns the last day of a month; EDATE the same day a number of months later or earlier.
More on age and date differences
The guide gives the age in whole years and in years and months. This piece adds how to calculate an age at a past or future date, how to find the next birthday, and how to avoid the usual date traps.
Age at any date, not just today
TODAY is only one possible end date. To find the age at the start of a school year, at the end of a contract or on a past date, put that date in a cell and use the cell as the end date. This also makes the sheet repeatable: the age does not change when you open the file next year.
=DATEDIF(A2,F1,"Y")
=DATEDIF(A2,DATE(2026,9,1),"Y")Days until the next birthday
Build this birthday in the current year with DATE, using the year of today and the month and day of the birth date. If that date has already passed, add one year. The difference between the result and today is the number of days left.
=DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))Traps to avoid
- A date typed as text, such as 20.5.1990 in a setup that expects slashes, is not a date. It is left-aligned, and the formulas return #VALUE!.
- Two-digit years are guessed by Excel. A birth year typed as 45 may become 1945 or 2045, so type four digits.
- February 29 birthdays move to March 1 in non-leap years in most calculations. Decide which day you mean before you build a rule on it.
- The start date must be earlier than the end date. Otherwise DATEDIF returns #NUM!.
More date work is in [the dates lesson](/learn/dates) and in [the guide on days between dates](/guides/calculate-days-between-dates-in-excel).