Age formula: by hand, in Excel and Google Sheets

The formula for exact age, written out in words, for spreadsheets and for code — with the common shortcut that gives wrong answers. Check any result with the calculator above.

Today, unless you pick another date.
Enter a date of birth to see the exact age.
</> Embed this calculator

Free to use on your website, blog or school page. Copy the code and paste it into your page’s HTML.

Age formula: by hand, in Excel and Google Sheets

Photo: Pexels / Pixabay

The age formula in words

Years = current year − birth year, minus 1 if this year’s birthday hasn’t come yet
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 formula excel: A person&apos;s hand writing in a notebook with a pen.

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 wantFormula
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"

Microsoft advises against DATEDIF’s "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.

A person typing on a laptop with a tablet and phone nearby.

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.

A hand pressing a calculator button, with a business suit in the background.

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.

Sources

Photo of Yasir Hafeez
Written and checked by Yasir Hafeez. Last reviewed October 16, 2026. Results are calculated with fixed rules and tested against an independent date library; see how we check.

More age tools

From the blog