Calculating Age in Excel and Google Sheets: Formulas That Work
The spreadsheet formulas for exact age and date differences, the pitfalls of simple division, and when a dedicated calculator is easier.
Spreadsheets are the natural home for lists of dates of birth — class registers, staff records, membership lists. Here are reliable formulas and the traps to avoid.
The tempting but flawed method
A common shortcut is to subtract the birth date from today and divide by 365: =(TODAY()-A2)/365. It is quick, but because years are 365 or 366 days, the result drifts and can round to the wrong whole number around birthdays. Dividing by 365.25 is better but still approximate.
DATEDIF: the exact approach
Both Excel and Google Sheets support DATEDIF(start, end, unit), which returns whole completed units.
=DATEDIF(A2, TODAY(), "Y")returns completed years.=DATEDIF(A2, TODAY(), "YM")returns months beyond the last full year.=DATEDIF(A2, TODAY(), "MD")returns days beyond the last full month.
Combine them for a readable exact age:
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"
Note that Excel does not list DATEDIF in its function wizard, though it works, and the "MD" unit has known quirks in some Excel versions. Test with dates near month ends.
YEARFRAC for decimal ages
=YEARFRAC(A2, TODAY(), 1) gives age as a decimal using actual day counts. It's useful for statistics but not for exact-age fields.
Age on a fixed date
Replace TODAY() with a cell reference, such as $E$1, holding the reference date. Every row then computes age as of the same date, which is exactly what eligibility lists need.
Watch for text dates
If a date is stored as text, formulas fail or give odd results. Convert with DATEVALUE or re-enter in a consistent format, and beware of day/month order when importing from different regions.
Testing your formulas
Include a few hand-checked cases: someone whose birthday is today, someone born on 29 February, and someone born on the 31st of a month. If the formula handles these, it is probably sound.
Avoiding volatile formulas
TODAY() recalculates every time the file opens, so the results change from day to day. For a snapshot such as a register at a particular date, put a fixed date in a cell instead.
When a calculator is simpler
For one-off questions, opening a spreadsheet is overkill. A calculator app gives the same years, months and days from a single date entry, without the setup.
Age Calculator
Calculate your exact age in years, months and days, and find your next birthday
More about Age Calculator
Counting Anniversaries and Time Together in Years, Months and Days
How to work out how long you've been together, married or friends, and how to plan the next anniversary with an accurate countdown.
Read article →DD/MM/YYYY or MM/DD/YYYY? Date Formats and the Mistakes They Cause
How date formats differ around the world, why ambiguous dates lead to wrong ages and rejected forms, and simple ways to write dates unambiguously.
Read article →How the Gregorian Calendar Was Created and Why It Skipped Days
The history behind today's calendar, how the Julian calendar drifted, the ten days that vanished in 1582, and why some countries switched centuries later.
Read article →Online Age Verification: Why Websites Ask and What They Check
Why websites and apps ask for your date of birth, how age gates differ from real verification, and how to protect your privacy while proving your age.
Read article →