To calculate age in Excel accurately, count completed years between a date of birth and today rather than subtracting one year from another. Excel stores dates as serial numbers, so the arithmetic is easy, but the quick methods often count a birthday before it has happened. The four formulas below handle that correctly.
Updated September 2026. Formulas assume the date of birth is in cell A2.
How Excel sees dates
Microsoft documents that Excel stores dates as sequential serial numbers, with 1 January 1900 as serial number 1. Subtracting two dates therefore gives the number of days between them. The difficulty is turning days into years, because years are not all the same length and a person only gains a year on their birthday.
Make sure the birth dates are real dates, not text. A quick check is to change the cell format to Number: a true date becomes a whole number, so 15 March 1990 shows as 32947, while text stays as it is.
The 4 formulas to calculate age in Excel
- Whole years today: =DATEDIF(A2,TODAY(),”Y”)
- Years, months and days: a combined DATEDIF and EDATE formula
- Age as a decimal: =YEARFRAC(A2,TODAY(),1)
- Age on a specific date: =DATEDIF(A2,B2,”Y”)
1. Whole years with DATEDIF and TODAY
=DATEDIF(A2,TODAY(),”Y”)
DATEDIF takes a start date, an end date and a unit. Microsoft lists “Y” as the number of complete years in the period, which is exactly what age means. TODAY() returns the current date and, according to Microsoft, updates whenever the workbook recalculates, so the age stays current without editing. If ages stop changing, check that calculation is set to automatic.
Example: a date of birth of 15 March 1990 gives 36 on 15 September 2026.
2. Years, months and days
=DATEDIF(A2,TODAY(),”Y”)&” years, “&DATEDIF(A2,TODAY(),”YM”)&” months, “&TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),”M”))&” days”
The “YM” unit returns the months left over after whole years. For the days, you might expect to use the “MD” unit, but Microsoft warns that “MD” may return a negative number, a zero, or an inaccurate result. The formula above avoids it: DATEDIF with “M” counts the complete months, EDATE moves the birth date forward by that many months, and subtracting that from today leaves the remaining days.
Example: a date of birth of 30 November 1988 gives 37 years, 9 months, 16 days on 15 September 2026. The complete months total 453, which moves the birth date to 30 August 2026, and 15 September is 16 days later.
3. Age as a decimal with YEARFRAC
=YEARFRAC(A2,TODAY(),1)
YEARFRAC returns the fraction of a year between two dates. The third argument sets the day-count basis, and it matters. Microsoft documents that the default, basis 0, is the US 30/360 convention used in finance, while basis 1 is actual/actual, which counts real days. For ages, use basis 1. Microsoft also notes that YEARFRAC may return an incorrect result with basis 0 when the start date is the last day of February.
Use this when you need a figure such as 36.5 for analysis. For a whole-number age, formula 1 is the cleaner choice, because it counts birthdays directly rather than rounding a fraction.
4. Age on a specific date
=DATEDIF(A2,B2,”Y”)
Replace TODAY() with a cell holding the date you care about, such as the start of a school year or a policy date. You can also type the date into the formula with the DATE function, for example =DATEDIF(A2,DATE(2026,12,31),”Y”). Microsoft recommends entering dates with DATE rather than as text to avoid calculation problems.
Applying a formula to a whole list
Type the formula once in the first row, then fill it down the column. Because A2 is a relative reference, it becomes A3, A4 and so on in each row. If every age should be measured against one date, such as a cut-off in cell B1, lock that cell with dollar signs so it does not move: =DATEDIF(A2,$B$1,”Y”). A blank birth date cell will produce a misleading age, so it is worth wrapping the formula in an IF test, for example =IF(A2=””,””,DATEDIF(A2,TODAY(),”Y”)), which leaves the result empty until a date is entered.
Common mistakes when you calculate age in Excel
- Subtracting years only. Microsoft shows =YEAR(TODAY())-YEAR(A2) as a quick method, but it ignores the month and day. For someone born on 30 November 1988 it returns 38 on 15 September 2026, when they are still 37.
- Dividing days by 365.25. =(TODAY()-A2)/365.25 approximates leap years and is fine for rough analysis, but around birthdays it can land on the wrong side of a whole number. Wrap it in INT only if a small error is acceptable.
- Dates in the wrong order. Microsoft notes that DATEDIF returns #NUM! if the start date is later than the end date, which usually means a birth date was typed in the future or the arguments are swapped.
- A result that looks like a date. If an age shows as a date in early 1900, the cell has a date format. Change it to Number or General.
- Text instead of dates. Imported birth dates are often text. Convert them before using any of these formulas.
Google Sheets and quick checks
Google Sheets supports DATEDIF with the same units, “Y”, “M”, “D”, “MD”, “YM” and “YD”, so formulas 1, 2 and 4 carry across. Google describes the counting rule plainly: months and years are only counted once the end date reaches the same day of the month.
To check a single result without a spreadsheet, our age calculator shows exact age in years, months and days, and the date difference calculator works for any two dates. For the logic behind exact ages, see our guide on how to calculate exact age. If you need day counts rather than ages, our guides to days between dates in Excel and working days between dates cover those formulas.
Common questions
What is the formula to calculate age in Excel? Use =DATEDIF(A2,TODAY(),”Y”) with the date of birth in A2. It returns completed years and updates as the date changes.
Can I calculate age in Excel for a whole column of birth dates? Yes. Enter the formula in the first row and fill it down. The A2 reference adjusts for each row; lock any shared date cell with dollar signs, such as $B$1.
How do I calculate age in years, months and days? Combine DATEDIF with the “Y” and “YM” units for years and months, and subtract EDATE(A2,DATEDIF(A2,TODAY(),”M”)) from TODAY() for days, avoiding the unreliable “MD” unit.
Why does my age formula give #NUM!? DATEDIF returns #NUM! when the start date is after the end date. Check that the birth date comes first and is not a future date.
Sources and further reading
Where the figures and rules above come from, so you can check them:
- DATEDIF units and the “MD” known issue: Microsoft Support
- YEARFRAC basis options and remarks: Microsoft Support
- TODAY function and recalculation: Microsoft Support
- Calculate age examples: Microsoft Support
- DATEDIF in Google Sheets: Google Docs Editors Help
Photo credits: Business Working by Marc Chouinard, CC0, via Stocksnap. birthday cake candles that spell by Rawpixel, CC0, via Rawpixel.
Join the discussion