Google Sheets Age Formula: DATEDIF and ARRAYFORMULA
Google Sheets supports the same DATEDIF function Excel uses — and unlike Excel it documents it rather than hiding it. For a single row, the age formula is identical in both applications.
Where Sheets pulls ahead is whole-column work: one array formula can fill an age for every row of a list, which is exactly the case where copying a formula down becomes a chore.
The Single-Cell Formula
With a birth date in A2:
=DATEDIF(A2, TODAY(), "Y")Sheets documents all six units, and they behave as they do in Excel: "Y" for completed years, "M" for completed months, and "YM" or "MD" for the remainders that let you write an age as years, months and days.
One Formula for a Whole Column
Instead of copying a formula down fifty rows, wrap an array-safe calculation in ARRAYFORMULA:
=ARRAYFORMULA(IF(LEN(A2:A), YEAR(TODAY())-YEAR(A2:A)-(TEXT(TODAY(),"MMDD")<TEXT(A2:A,"MMDD")), ))The formula needs unpacking, because the trick is in the middle. YEAR(TODAY())-YEAR(A2:A) is the naive year subtraction that overstates an age before the birthday arrives. The comparison TEXT(TODAY(),"MMDD")<TEXT(A2:A,"MMDD") asks whether today's month-and-day comes before the birthday's, and Sheets coerces that TRUE or FALSE into 1 or 0 — so the formula subtracts one exactly when the birthday has not happened yet.
The IF(LEN(A2:A), …, ) wrapper leaves blank rows blank rather than filling them with 1900-style nonsense.
The Misconception: ARRAYFORMULA Fixes Any Function
It is natural to assume you can wrap the single-cell formula instead: =ARRAYFORMULA(DATEDIF(A2:A, TODAY(), "Y")). In practice this does not spill down the column the way year arithmetic does — you typically get a single value at the top of the range rather than one result per row, because DATEDIF is not array-aware.
That is not a reason to abandon DATEDIF. Either keep it in a per-row formula and fill down in the normal way, or use the YEAR and TEXT construction above when you genuinely need one formula to cover a growing list.
Worked Example: A Roster of Birth Dates
With birth dates in A2:A4, the array formula produces an age for each row. Checking one row by hand: a birth date of 1990-06-15 read on 2026-09-20 gives 36, because the June birthday has already passed this year.
| Birth date | Age on 20 Sep 2026 | Note |
|---|---|---|
| 15 Jun 1990 | 36 | Birthday has passed |
| 1 Dec 1990 | 35 | Birthday still to come |
| 22 Sep 1993 | 32 | Turns 33 two days later |
The middle row is the reason the corrected formula exists: naive year subtraction reports 36 for a person who is still 35, and the error persists for most of the year for anyone born in the last quarter.
Sheets Numbers Dates Differently From Excel
Both applications store a date as a whole number of days, but they start counting at different points and disagree about one historical day. Sheets treats 30 December 1899 as day zero and does not reproduce Excel's phantom 29 February 1900.
The result is a clean rule: for any date from 1 March 1900 onward, the serial number in Sheets is exactly one less than the serial number in Excel for the same calendar day. Pasted dates are unaffected, because each application re-interprets the value. Pasted raw serial numbers land one day out, which is what happens when someone copies a column formatted as numbers instead of dates.
Formatting: Why a Day Count Appears as a Date
Sheets inherits formatting the same way Excel does. Put a difference of days into a cell that already carries a date format and it will render as a date, so a correct value of 4 appears as a day in January 1900.
Use Format > Number > Number to switch the cells back. If you are building the sheet from scratch, format the result column as a number before entering the formulas; it is far less confusing than discovering the problem after the column is full.
Why the Array Formula Is Worth It for QUERY
A numeric age column is not just tidy, it is functional. QUERY, FILTER and SORT all work on numbers, and a text value such as "36 years, 3 months" cannot be compared or sorted meaningfully.
=QUERY(A2:C, "select A, C where C > 30 order by C desc")That single line would list everyone over thirty, oldest first — which is a genuinely useful thing to be able to do with a list of people, and impossible if the age column contains sentences.
Sharing a Sheet That Contains Birth Dates
A spreadsheet of birth dates is personal data the moment it has names beside it, and the sharing model is the risk rather than the formula. Set the sheet to private by default, share with named accounts rather than "anyone with the link", and avoid pasting real dates into a public example you intend to publish.
For one-off conversions that do not need to live in a document at all, calculate in the browser instead: the age calculator takes a birth date and returns the same figures without creating a file that has to be stored, shared or deleted.
Check a single age in seconds, with nothing stored and nothing to share.
Open the Age Calculator →Frequently Asked Questions
Does Google Sheets have an age function?
There is no dedicated AGE function, but Sheets supports DATEDIF and documents it, so the standard age formula works without the compatibility caveats that apply in Excel.
How do I calculate age for a whole column at once?
Use ARRAYFORMULA with YEAR and TEXT arithmetic, for example comparing the month-and-day of today with the month-and-day of each birth date. That spills an age down every populated row.
Why does ARRAYFORMULA with DATEDIF not fill the column?
DATEDIF is not array-aware, so it does not return one result per row. Use it in a normal per-row formula, or switch to the YEAR and TEXT construction when you need a single array formula.
Why is my day count showing as a date?
The cell has a date format inherited from the surrounding column. Change it to Format, then Number, and the underlying day count appears.
Do Sheets and Excel calculate dates identically?
For modern dates the results agree. The serial numbers differ by one for dates from 1 March 1900 onward, because Sheets does not repeat the 1900 leap year error.
Can I sort by age in Sheets?
Yes, if the age column holds numbers. Text such as "36 years" sorts alphabetically and is effectively useless, which is a good reason to keep a numeric age column.
How do I leave blank rows blank?
Wrap the calculation in IF with a LEN test on the birth date column. Without that guard, empty rows receive a value based on the year 1900 and pollute any totals or charts.
Does TODAY update automatically in Sheets?
Yes, it is volatile and recalculates whenever the sheet recalculates, so ages stay current without editing. Record the date you read a figure if you plan to quote it.
Are birth dates in a shared sheet a privacy concern?
They are personal data as soon as names are attached. Keep the sheet private, share with named accounts, and avoid publishing examples that contain real dates.
Are the dates I enter on this site kept private?
Yes. Everything runs in your browser — nothing is sent to a server and nothing is saved.
Sources
- Google Docs Editors Help: DATEDIF
- Microsoft Learn: Excel incorrectly assumes that the year 1900 is a leap year
- Google Docs Editors Help: NETWORKDAYS