The age formula in Excel
Excel stores every date as a day number (1 January 1900 is day 1), so subtracting two dates gives the days between them. For an age in calendar units you want the hidden but fully supported DATEDIF function, which counts whole years, then leftover months, then leftover days. With the date of birth in B4 and the as-of date in B5:
| What | Formula | Result for 15 May 1990 on 14 Sep 2026 |
|---|---|---|
| Whole years | =DATEDIF(B4,B5,"y") | 36 |
| Months after the years | =DATEDIF(B4,B5,"ym") | 3 |
| Days after the months | =DATEDIF(B4,B5,"md") | 30 |
| Total months | =DATEDIF(B4,B5,"m") | 435 |
| Total days | =B5-B4 | 13,271 |
| Total weeks | =INT((B5-B4)/7) | 1,895 |
| Age as text | =DATEDIF(B4,B5,"y")&" years, "&DATEDIF(B4,B5,"ym")&" months" | 36 years, 3 months |
To compute the age today rather than at a fixed date, put =TODAY() in B5. The sheet then updates every time it is opened.
Next birthday in Excel
Build this year's birthday from the as-of year and the birth month and day, then move to next year if it has already passed:
=IF(DATE(YEAR(B5),MONTH(B4),DAY(B4))>=B5, DATE(YEAR(B5),MONTH(B4),DAY(B4)), DATE(YEAR(B5)+1,MONTH(B4),DAY(B4)))
Subtract B5 from that result for the days until the next birthday. For a 29 February birthday, DATE() rolls to 1 March in common years; use MIN(DAY(B4),DAY(EOMONTH(DATE(YEAR(B5),MONTH(B4),1),0))) in place of DAY(B4) if you want 28 February instead, which is what the template does.
Why DATEDIF instead of dividing by 365.25
Dividing days by 365.25 gives a decimal age that is off by a day or two around birthdays and drifts across leap years. DATEDIF counts calendar months and years the way people do: someone born on 31 January is one month old on 28 February, and the "ym" and "md" units never produce negative numbers. It is not listed in Excel's function menu, but it works in every version, in Google Sheets, and in Numbers.
What the template contains
Two blue input cells (date of birth, age at date) and three blocks of formulas: age in years, months, and days; totals in months, weeks, days, and hours; and the next birthday with a countdown. Every cell is a formula you can inspect, and the footer records where it came from. Nothing is locked and there are no macros.