
Photo: Pexels / Pixabay
The age formula in words
Months = complete months since the last birthday
Days = days since the last monthly birthday
For a birth date of 20 November 1988, on 16 October 2026: 2026 − 1988 = 38, minus 1 because 20 November hasn’t come yet = 37 years; 20 November 2025 to 20 September 2026 = 10 months; 20 September to 16 October = 26 days. The full method, including the month-end rule, is in how age is calculated.

Age in Excel
Put the date of birth in cell A2. These formulas use today’s date; replace TODAY() with a cell reference to get the age on another date.
| You want | Formula |
|---|---|
| Age in complete years | =DATEDIF(A2,TODAY(),"Y") |
| Months after the last birthday | =DATEDIF(A2,TODAY(),"YM") |
| Days after the last monthly birthday | =TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M")) |
| Age in total months | =DATEDIF(A2,TODAY(),"M") |
| Age in days | =TODAY()-A2 |
| Age in years with decimals | =YEARFRAC(A2,TODAY(),1) |
To show it all in one cell, for example “37 years, 10 months, 26 days”:
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&(TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M")))&" days"
"MD" argument, which has known limitations and can return a negative number, a zero or an inaccurate result. The EDATE formula above avoids it. EDATE moves 31 January plus one month to the last day of February — the same rule our calculator uses.Age in Google Sheets
The same formulas work in Google Sheets: DATEDIF, EDATE, YEARFRAC and TODAY all exist with the same arguments. If a formula shows a date instead of a number (for age in days), change the cell’s format to Number.

The shortcut to avoid
A popular formula is =INT((TODAY()-A2)/365). It looks fine, but years are 365 or 366 days long. After a few leap years the count runs ahead, so someone can appear to turn 30 a few days before their birthday. Dividing by 365.25 is closer but still wrong on some dates. Use DATEDIF, which counts calendar years.

Age in code
In most programming languages, compare the month and day instead of dividing days:
Python: age = today.year - born.year - ((today.month, today.day) < (born.month, born.day))
JavaScript: let age = t.getFullYear() - b.getFullYear(); if (t.getMonth() < b.getMonth() || (t.getMonth() === b.getMonth() && t.getDate() < b.getDate())) age--;
With these, someone born on 29 February turns a year older on 1 March in common years. If your rules say 28 February, add a special case — see born on 29 February.
Check your formula
Enter the same birth date and “age on” date in the calculator above. If your spreadsheet disagrees, the usual culprits are the 365-day shortcut, the “MD” argument, or a date stored as text instead of a real date.
Interpreting DATEDIF Outputs
The DATEDIF function provides precise age components. Understanding each argument helps interpret results. "Y" returns the number of full years completed. This means someone turning 30 tomorrow shows 29 today. It counts calendar year boundaries.
The "YM" argument calculates months past the last birthday. This value resets to zero on each birthday. So, if a birthday was last month, it shows one. It never exceeds 11, as 12 months constitutes a new year.
For days, the EDATE combination formula is best. It gives days past the last monthly anniversary. This ensures accuracy, especially around month ends. Always review all three components together for a full age picture.
Common Mistakes and Solutions
One frequent error is using incorrect date formats. Dates can appear correct but be stored as text. This causes #VALUE! errors. Always confirm cells are formatted as 'Date' before using age formulas.
Another pitfall is using `TODAY()` when a fixed comparison date is needed. `TODAY()` updates daily. If you need age as of a specific past date, reference a cell with that date instead. For example, use `B2` instead of `TODAY()`.
Sometimes users forget to make cell references absolute. When copying formulas down a column, `A2` might become `A3`, but `B2` (the target date) should remain `B$2`. Use dollar signs ($) to fix row or column references. This prevents incorrect date comparisons.
Practical Tips for Age Calculations
For large datasets, use named ranges for clarity. Instead of `A2:A100`, name the column 'BirthDates'. Then formulas become easier to read. For example, `DATEDIF(BirthDates,TODAY(),"Y")` is clear.
Consider regional date settings. Some systems use MM/DD/YYYY, others DD/MM/YYYY. Ensure your input dates match your spreadsheet's locale. Mismatches lead to incorrect date interpretation.
If you need to calculate age on a specific future date, simply replace `TODAY()` with that future date. This allows for projections. This method is flexible for various scenarios. Always double-check your date inputs.
When Other Methods Fit Better
Sometimes, a simpler age calculation is sufficient. For instance, if you only need the age in full years, the DATEDIF function with the "Y" argument is ideal. This avoids unnecessary complexity. It is perfect for broad demographic analysis.
When precise age in days is required, directly subtracting dates works best. For example, =TODAY()-A2 gives total days since birth. This is useful for tracking durations or deadlines. However, it does not account for years and months. If you need age for legal or medical contexts, always use the full DATEDIF and EDATE combination. This ensures maximum accuracy.
Frequently asked questions
What is the formula to calculate age in Excel?
=DATEDIF(A2,TODAY(),”Y”) gives age in complete years when the date of birth is in A2. Add “YM” for the extra months.
Does DATEDIF work in Google Sheets?
Yes. Google Sheets supports DATEDIF with the same arguments, as well as EDATE and YEARFRAC.
Why is (TODAY()-A2)/365 wrong?
Years are not all 365 days long. Over the years the leap days add up, so the result can show a new age a day or more before the birthday.
How do I calculate age in days in Excel?
=TODAY()-A2, formatted as a number, gives the exact number of days since the date of birth.
