Quick answer
Put the birth date in A2, then type =DATEDIF(A2,TODAY(),"Y") in the next cell. That one formula returns the completed age in whole years. The rest of this guide extends it: exact years, months and days, ages on fixed dates, whole-column formulas, and a fix for every error.
Need to calculate age in Google Sheets? Whether you are tracking employee ages, student birthdays, or customer demographics, the right formula gives you exact years, months, and days instantly. This guide covers DATEDIF, YEARFRAC, and manual methods with copy-paste examples.
It is the Sheets guide; for the Excel version of the same formulas, see how to calculate age in Excel, and for the hand method behind the formulas, see how to calculate age from a date of birth manually.
Quick Formula Reference
Find your situation in the left column and copy the matching formula. Every formula below is copy-paste ready, with straight quotes.
| What you need | Formula |
|---|---|
| Today’s age in whole years | =DATEDIF(A2,TODAY(),"Y") |
| Full breakdown: years, months and days | =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, and "&DATEDIF(A2,TODAY(),"MD")&" days" |
| Age on a date stored in B2 | =DATEDIF(A2,B2,"Y") |
| Age on a fixed date (Jan 1, 2030) | =DATEDIF(A2,DATE(2030,1,1),"Y") |
| Total completed months | =DATEDIF(A2,TODAY(),"M") |
| Total days lived | =DATEDIF(A2,TODAY(),"D") |
| Decimal age (36.47 years) | =YEARFRAC(A2,TODAY(),1) |
| Skip blank DOB cells | =IF(A2="","",DATEDIF(A2,TODAY(),"Y")) |
| Age for an entire DOB column | =ARRAYFORMULA(IF(A2:A="","",DATEDIF(A2:A,TODAY(),"Y"))) |
| Friendly message instead of an error | =IF(A2="","",IFERROR(DATEDIF(A2,TODAY(),"Y"),"Check DOB")) |
Calculate Age Step by Step
You need two columns: one for dates of birth, one for ages.
Step 1: Enter the date of birth
Type the birth date into a cell, for example A2. Sheets must recognize it as a real date, not plain text. If you typed 15/01/1990 and Sheets treats it as text, the formulas below will fail.
(The troubleshooting section shows how to fix that.) Watch out for ambiguous dates. 03/04/2000 means March 4 in US-style sheets and April 3 in most European-style sheets. Check File > Settings > Locale before trusting results built on dates like these.
Step 2: Enter the age formula
In the age cell, type: =DATEDIF(A2,TODAY(),"Y") Press Enter. Sheets returns the number of complete years between the birth date and today. Someone whose birthday is next month does not get credit for that year yet. That is the behavior you want for a real age.
Step 3: Copy the formula down
Drag the fill handle (the small square at the cell’s bottom-right corner) down the column. Because A2 is a relative reference, each row automatically points at its own birth date: A3, A4, and so on. For a list that keeps growing, the ARRAYFORMULA method below removes the need to copy anything.
How DATEDIF Works
DATEDIF measures the gap between two dates in whatever unit you ask for: =DATEDIF(start_date, end_date, unit) start_date: the earlier date. For age math, the date of birth. end_date: the date you want the age on. Usually TODAY(). unit: years, months, days, or a leftover component (explained next).
| Unit | Returns | Use it for |
|---|---|---|
"Y" |
Whole years between the dates | Age in completed years |
"M" |
Whole months across the whole interval | Total age in months |
"D" |
Total days between the dates | Age in days |
"YM" |
Months left over after whole years are removed | The months part of an exact age |
"MD" |
Days left after whole months are taken out | The days piece of a full age breakdown |
"YD" |
Days between the dates ignoring whole years | Anniversary-style day counts |
The pairs that trip people up are "M" versus "YM", and "D" versus "MD". A person aged 2 years and 5 months has 29 total completed months. "M" returns 29. "YM" returns 5, the months remaining once the 2 full years are taken out.
Same logic for days: "D" counts every day in the interval, "MD" counts only the leftover days for a years-months-days breakdown.
Why TODAY() keeps the age current
TODAY() returns the current date with no time attached, so the formula recalculates to a new age the morning after each birthday without anyone touching the sheet. Do not use NOW() here: it includes the time of day, which adds nothing to an age calculation and can cause confusion.
One caveat: Sheets refreshes TODAY() when the sheet is edited or reopened, not on a schedule. A sheet left open past midnight can show yesterday’s ages until something triggers a recalculation. Under File > Settings > Calculation, set recalculation to “On change and every minute” if same-day accuracy matters.
Exact Age in Years, Months, and Days
For a full breakdown, run three DATEDIF calculations and join them: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, and "&DATEDIF(A2,TODAY(),"MD")&" days" A typical result looks like 36 years, 5 months, and 20 days. This is calendar-exact: it follows real months of different lengths instead of dividing by 365.
Worked example with a fixed date
Fixed dates make examples checkable, so use one here. Birth date: March 15, 1990. Reference date: September 4, 2026. =DATEDIF(DATE(1990,3,15),DATE(2026,9,4),"Y")&" years, "&DATEDIF(DATE(1990,3,15),DATE(2026,9,4),"YM")&" months, and "&DATEDIF(DATE(1990,3,15),DATE(2026,9,4),"MD")&" days" Result: 36 years, 5 months, and 20 days.
Check it by hand: 2026 minus 1990 is 36, and the March 15 birthday already passed by September 4, so 36 full years. Five more whole months reach August 15. Twenty more days reach September 4. The formula agrees. The same span is 13,322 days, and the decimal age is 36.47 years.
Age on a Specific Date
Replace TODAY() with any other date to get an age on a birthday that already happened, an application deadline, or a future event.
Reference date in another cell
If the birth date is in A2 and the target date is in B2: =DATEDIF(A2,B2,"Y") Use the Y/YM/MD combination from above instead of "Y" alone when you need the exact breakdown on that date too.
Fixed date inside the formula
Build the date directly with DATE(year, month, day): =DATEDIF(A2,DATE(2030,1,1),"Y") This gives the completed age on January 1, 2030. A hard-coded date never drifts forward, which makes it the right choice for historical reports, eligibility cutoffs, and archived records. For a one-off answer with no spreadsheet involved, our age on a specific date calculator is faster.
Other Age Formats
Total months: =DATEDIF(A2,TODAY(),"M") Counts whole months across the entire interval. Remember: "M" is the total, "YM" is the leftover after years.
Total days: =DATEDIF(A2,TODAY(),"D") Or simply subtract, since Sheets stores dates as numbers: =TODAY()-A2 If the answer shows up as a calendar date instead of a number, the result cell is formatted as a date. Change it to Number format.
Decimal age with YEARFRAC: =YEARFRAC(A2,TODAY(),1) Returns age like 36.47 years. The third argument is the day-count basis, and 1 means actual days over actual days in the year.
Do not leave it out: the default basis 0 uses a 30/360 financial convention that treats every month as 30 days, which gives a slightly wrong age. Use DATEDIF for calendar ages people recognize; use YEARFRAC only when you genuinely need a fractional year.
Age for a Whole List
Autofill for short lists
Enter =DATEDIF(A2,TODAY(),"Y") in B2, grab the fill handle, and drag down. Each row picks up its own birth date. Simple and fine for lists that do not change much.
ARRAYFORMULA for growing lists
One formula in the top cell can fill the entire column: =ARRAYFORMULA(IF(A2:A="","",DATEDIF(A2:A,TODAY(),"Y"))) The IF wrapper keeps rows blank until a birth date is entered, so new people added at the bottom get ages automatically with no copying.
On very large sheets, bound the range (for example A2:A1000) instead of referencing the whole column: TODAY() recalculates often, and an unbounded range makes every recalculation slower than it needs to be.
Troubleshooting: Every Common Error, Fixed
Almost every wrong age traces back to the input data, the date order, or cell formatting, not the formula itself. Find your symptom below.
| Symptom | Cause | Fix |
|---|---|---|
| #NUM! | Start date is later than end date (future DOB, or reversed cell references) | Check the DOB and target date order. For a friendlier display use =IFERROR(DATEDIF(A2,TODAY(),"Y"),"Check date"), then fix the underlying date. |
| #VALUE! | A referenced cell is not a valid date (often text that looks like a date) | Confirm the cell holds a real date value. If it is text Sheets recognizes, wrap it: =DATEDIF(DATEVALUE(A2),TODAY(),"Y"). |
| #ERROR! | Typo in the formula or a missing quote around the unit | Compare against =DATEDIF(A2,TODAY(),"Y") character by character. The unit must be quoted: "Y", not Y. |
| Result shows as a calendar date (e.g. Jan 8, 1900) | Result cell is formatted as Date | Select the cell, go to Format > Number > Number. |
| Blank rows show 0 or an error | No blank-cell guard | Wrap the formula: =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). |
| Month and day look swapped | Locale interprets 03/04/2000 differently than intended | Check File > Settings > Locale. In formulas, build dates with DATE(2000,4,3) to remove ambiguity. |
| Age is off by one year | Birthday has not occurred yet this year, or the DOB is wrong | Run the manual check below. DATEDIF counts completed years only, which is correct behavior. |
| Age did not update overnight | TODAY() refreshes on edit or reopen, not on a timer | Change any cell, reopen the file, or set recalculation to every minute under File > Settings > Calculation. |
Guard against blanks and bad data in one formula
=IF(A2="","",IFERROR(DATEDIF(A2,TODAY(),"Y"),"Check DOB")) The IF keeps empty rows blank; IFERROR catches everything else and shows a readable message. Do not use this to hide bad data permanently: if several rows say “Check DOB”, inspect those birth dates instead of assuming the math is fine.
Stop bad dates at the door with Data Validation
Select the DOB column, then Data > Data validation. Choose Date with is valid date to reject text and impossible dates like February 30. Set the behavior to Reject input so bad entries are blocked, not just flagged.
For stricter control, use Custom formula is with: =AND(ISDATE(A2),A2<=TODAY(),A2>=DATE(1900,1,1)) This requires a real date that is not in the future and not before 1900.
Note that validation only applies to new entries: scan existing data with a helper column of =ISDATE(A2) and look for FALSE results. Pasting follows the same rule, so re-run the check after any bulk import.
Verify Your Calculation by Hand
A thirty-second manual check catches bad birth dates, wrong reference dates, and formatting problems: Subtract the birth year from the reference year. Ask whether the birthday already occurred by the reference date. If not, subtract one more year.
Birth date March 15, 1990, reference September 4, 2026: 2026 minus 1990 is 36, the birthday passed, so the age is 36. With a reference of February 1, 2026, the birthday had not happened yet, so the age is 35.
When a formula result looks off by one, this check almost always reveals why. For the full hand method, see how to calculate age from a date of birth manually.
Leap-Year Birthdays
A February 29 birth date works in the same formulas as any other date. DATEDIF compares the actual dates and counts whole years, months, or days normally.
The subtle part is which substitute date (February 28 or March 1) applies in non-leap years for a given legal or administrative purpose. Do not hard-code that choice into a formula without knowing which rule governs your use case. Our leap-year birthday guide covers the calendar details.
Which Formula Should You Use?
| Your goal | Use | Why |
|---|---|---|
| Current age in completed years | DATEDIF with "Y" |
Returns whole years directly |
| Exact age in years, months, days | DATEDIF with "Y", "YM", "MD" |
Separates age into calendar components |
| Age on a past or future date | DATEDIF with a target cell or DATE() | Replaces the moving current date |
| Total completed months | DATEDIF with "M" |
Counts whole months across the interval |
| Total elapsed days | DATEDIF with "D", or =TODAY()-A2 |
Day difference, no calendar breakdown |
| Fractional age (36.47) | YEARFRAC with basis 1 | Expresses the interval as a decimal year |
| Ages for a growing roster | ARRAYFORMULA with DATEDIF | One formula fills every row |
Sheets vs an Online Age Calculator
Google Sheets wins when you maintain a list of people, work with many birth records, or want ages updating automatically inside a spreadsheet you already use.
An online calculator is simpler for checking a single date of birth with no spreadsheet to maintain. For a quick one-off answer, use our age calculator. For lists, reports, and repeat calculations, the formula belongs in Sheets.
One Job, Three Stages: The Upgrade Ladder
Competitors document each method. None frames them as an upgrade ladder with the maintenance payoff of each step. Same task: ages for a column of birth dates.
| Stage | Formula | Formulas used | Payoff |
|---|---|---|---|
| 1. Fill down | =DATEDIF(A2,TODAY(),"Y") in B2, dragged down |
N (one per row) | Simple; breaks when rows are added |
| 2. ARRAYFORMULA | =ARRAYFORMULA(IF(LEN(A2:A20),DATEDIF(A2:A20,TODAY(),"Y"),"")) |
1 | Auto-expands; blanks stay blank |
| 3. LET one-cell | =ARRAYFORMULA(IF(A2:A="","",LET(y,DATEDIF(A2:A,TODAY(),"Y"),m,DATEDIF(A2:A,TODAY(),"YM"),d,DATEDIF(A2:A,TODAY(),"MD"),y&" years, "&m&" months, and "&d&" days"))) |
1 | Full Y/M/D strings for the whole column |
Worked Example: A1 = October 21, 2006
Today: February 16, 2025. =DATEDIF(A1,TODAY(),"Y")&" Years, "&DATEDIF(A1,TODAY(),"YM")&" Months, and "&DATEDIF(A1,TODAY(),"MD")&" Days" = 18 Years, 3 Months, and 26 Days. Check: 18th birthday was Oct 21, 2024. Oct 21 to Jan 21 = 3 months. Jan 21 to Feb 16 = 26 days. The same span is 6,693 days.
Whole-Column Example
Birth dates in A2:A20. In B2: =ARRAYFORMULA(IF(LEN(A2:A20), DATEDIF(A2:A20, TODAY(), "Y"), "")) Empty rows stay blank. New rows calculate automatically.
Google Sheets Facts
DATEDIF is documented in Sheets now
Typing =DATEDIF brings up the autocomplete suggestion, and the function sits in the Insert > Function > Date list between DATE and DATEVALUE. Google’s own function list includes DATEDIF in the Date category, and the official help page documents the syntax and all six unit codes.
Older guides still call it hidden, because that was true for years; the newest evidence says otherwise. Excel still keeps it undocumented, so it must be typed exactly there. The same six unit codes and the same “MD” reliability caveat apply.
YEARFRAC basis in Sheets
Identical 0-4 day-count conventions. Default 0 is 30/360. Pass 1 for actual days.
Fixed-date calculations
Replace TODAY() with an absolute reference like $D$1 for cutoff dates and eligibility snapshots.
TODAY() recalculation
Ages refresh when the sheet is edited or reopened. Nothing is cached on a schedule; set recalculation to every minute under File > Settings > Calculation if same-day accuracy matters.
Locale date entry
Sheets parses by spreadsheet locale. Mismatched locales cause the same silent day/month swap as Excel.
How This Guide Compares
The three guides disagree on one point: whether DATEDIF is still a hidden function. The table below scores each guide on the features that decide it.
| Feature | This guide | SheetsBootcamp | SpreadsheetPoint | TheBricks |
|---|---|---|---|---|
| DATEDIF documented status, stated with the newest evidence | Yes | Yes | Not shown | Not shown |
| All six DATEDIF units explained | Yes | Yes | Not shown | Not shown |
| Exact Y/M/D breakdown formula, copy-paste ready | Yes | Yes | Yes | Yes |
| Whole-column ARRAYFORMULA method | Yes | Not shown | Not shown | Not shown |
| Upgrade ladder from fill-down to LET | Yes | Not shown | Not shown | Not shown |
| 8-symptom troubleshooting table | Yes | Yes | Not shown | Not shown |
| Hand-verification method | Yes | Not shown | Not shown | Not shown |
| Sheets vs Excel comparison | Yes | Not shown | Not shown | Not shown |
| Free to read, no subscription needed | Yes | Yes | Yes | Yes |
What this guide does not yet do: no copy-paste template or sheet is embedded in the page; the formulas are copy-paste ready in the text. SheetsBootcamp, SpreadsheetPoint and TheBricks embed no working sheet either.
No rival guide gives you the upgrade ladder from fill-down to LET, the 8-symptom troubleshooting table with the blank and bad-data guard, and the hand-verification method, all covered above.
Sources
- Google Sheets: DATEDIF (the official syntax, units, and sample usage)
- Microsoft Excel: DATEDIF function (the Excel side, including the MD and YD quirks)
- Sheets Bootcamp: DATEDIF in Google Sheets (the newest evidence that DATEDIF autocompletes in Sheets)
Frequently Asked Questions
Does Google Sheets have an AGE function?
No. Sheets has no built-in AGE() function. DATEDIF is the direct replacement for calendar ages, and YEARFRAC covers fractional years.
How does DATEDIF in Google Sheets differ from DATEDIF in Excel?
For age math they compute the same thing: both take a start date, an end date, and a unit and return the completed difference. The practical differences: Sheets documents DATEDIF officially with autocomplete as you type, while Excel keeps it undocumented (a Lotus 1-2-3 leftover) so it must be typed exactly.
Sheets recalculates TODAY() when the sheet is edited or reopened, and you can set recalculation to every minute; desktop Excel recalculates on open or on demand, which changes when TODAY()-based ages refresh.
One result-level difference matters: Excel’s “MD” and “YD” units have long-standing quirks that can return wrong day counts across months of different lengths or around leap years. Completed-year “Y” results agree between the apps; only detailed breakdowns can differ. Our Excel age guide covers that side.
Is there a formula that gives the same result in both Sheets and Excel?
Yes. =DATEDIF(A2,TODAY(),"Y") returns the same completed-year age in both, because the “Y” unit is consistent. =YEARFRAC(A2,TODAY(),1) also agrees, since basis 1 means actual calendar days. Keep the basis: the default basis 0 is the financial 30/360 convention. Avoid "MD", "YM", and "YD" in files that move between the apps.
Does TODAY() return the same date for every viewer in every timezone?
No. TODAY() follows the spreadsheet’s timezone (File > Settings), not each viewer’s local clock. A sheet set to New York time reports the same TODAY() to a collaborator in Tokyo, so around midnight the two can sit on different dates and a birthday age can change a few hours early or late.
When the exact date decides something, like a cutoff, set the spreadsheet timezone on purpose or use a fixed reference date instead of TODAY(). Excel works the other way: its TODAY() reads each computer’s own clock, so one shared Excel file can display a different age to each viewer near midnight.
Why doesn’t the age update right after midnight?
Sheets refreshes volatile functions like TODAY() when the sheet is edited or reopened, not on a schedule. Leave a sheet open overnight and it can still show yesterday’s ages in the morning.
Changing any cell, reopening the file, or switching recalculation to “On change and every minute” under File > Settings > Calculation brings it current.
How do I stop invalid birth dates from being entered?
Use Data Validation on the DOB column: Data > Data validation, criteria Date / is valid date, behavior Reject input. This blocks text entries and impossible dates before they reach your formulas. Combine it with the =IF(A2="","",...) wrapper so empty cells stay blank.
What happens when someone pastes a bad date into a validated column?
The same rule applies to pasted data: Reject input blocks invalid pastes, Show warning accepts them with a red triangle flag. After any bulk paste or import, re-run a helper column of =ISDATE(A2) to catch anything that slipped through.
Related Age Calculation Guides
How to Calculate Age in Excel: The Excel version of the same formulas, with the MD and YD quirks.
How to Calculate Age From Date of Birth: Working out an age from a birth date, step by step.
What Is Chronological Age: Chronological age, and the completed-years rule behind it.
Exact Age vs Completed Age vs Running Age: The three ages, and which one a form or a law wants.
Calendar Age vs Total Days Lived: How a calendar age and a day count differ, and why.
Month-End Birthday Age Calculation: The month-end rule, and what happens to the 31st.
Methodology: The conventions this site follows for every age it calculates.