How to Calculate Age in Google Sheets

If the date of birth is in cell A2, the simplest way to calculate age in Google Sheets is:

=DATEDIF(A2,TODAY(),"Y")

This formula returns the person’s age in completed years. In the formula, A2 is the date of birth, TODAY() supplies the current date, and "Y" tells Google Sheets to return the number of whole years between those two dates.

Quick Google Sheets Age Formula Reference

Use the formula that matches the type of age calculation you need.

How to Calculate Age From Date of Birth in Google Sheets

For a basic age calculation, you only need a date-of-birth column and an age column.

Step 1: Enter the Date of Birth

Enter the date of birth in a cell, such as A2. Make sure Google Sheets recognizes the value as a real date rather than ordinary text.

For example, your sheet might contain:

A2 = 15/01/1990

The way the date is displayed can vary depending on your spreadsheet’s locale. If a date such as 03/04/2000 could be interpreted in more than one way, check the spreadsheet’s date settings before relying on the result.

Step 2: Enter the Age Formula

In the age cell, enter:

=DATEDIF(A2,TODAY(),"Y")

Press Enter. Google Sheets will return the number of complete years between the date of birth and the current date.

This is normally the most useful formula when you simply need a person’s current age as a whole number.

Step 3: Copy the Formula Down

If your sheet contains several people, copy the formula into the rows below. Because A2 is a relative cell reference, it will automatically change to A3, A4, and so on.

You can drag the fill handle down the column or double-click it when the neighboring data makes that convenient. For a sheet that regularly receives new rows, an ARRAYFORMULA can remove the need to copy formulas manually.

How the DATEDIF Age Formula Works

DATEDIF calculates the difference between two dates in a unit that you specify.

DATEDIF Syntax

The basic syntax is:

=DATEDIF(start_date,end_date,unit)

• start_date: the earlier date, which is normally the date of birth.
• end_date: the date on which you want to know the person’s age. For current age, this is normally TODAY().
• unit: tells Google Sheets whether you want years, months, days, or a remaining component.

What Y, M, D, YM, MD, and YD Mean

The distinction between these units matters. "M" and "YM" do not return the same thing. If someone is 25 years and 7 months old, "M" returns their total completed months, while "YM" returns only the 7 months left after the completed years are removed.

The same principle applies to "D" and "MD". "D" returns total elapsed days, while "MD" provides the remaining day component used in a years-months-days age breakdown.

Calculate Exact Age in Years, Months, and Days

If you need more detail than a whole-number age, you can calculate the completed years, remaining months, and remaining days separately and combine them.

Completed Years

=DATEDIF(A2,TODAY(),"Y")

This returns the number of whole years that have passed since the date of birth.

Remaining Months

=DATEDIF(A2,TODAY(),"YM")

This returns the whole months remaining after complete years have been removed from the interval.

Remaining Days

=DATEDIF(A2,TODAY(),"MD")

This gives the remaining days after whole months have been removed.

Combine Them in One Cell

To display all three components together, use:

=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"

The result will appear in a readable form such as 36 years, 7 months, 19 days, depending on the date of birth and the date on which the spreadsheet recalculates.

This is different from a decimal age. It separates chronological age into completed calendar components instead of expressing part of a year as a decimal fraction.

Calculate Age on a Specific Date

You do not have to calculate age as of today. Replace TODAY() with another date whenever you need someone’s age on a past or future date.

Use Another Cell as the Reference Date

If the date of birth is in A2 and the target date is in B2, use:

=DATEDIF(A2,B2,"Y")

This is useful for questions such as:

• How old was someone on a historical date?
• How old will someone be on an application deadline?
• What will their age be on an enrollment cutoff date?
• How old will they be on a future event date?

If you only need a one-time answer rather than a spreadsheet formula, you can also use our age on a specific date calculator.

Use a Fixed Date With DATE()

If you want the target date built directly into the formula, use the DATE(year,month,day) function.

=DATEDIF(A2,DATE(2030,1,1),"Y")

This calculates the person’s completed age on January 1, 2030.

A fixed date will not move forward automatically. That makes it more appropriate than TODAY() for historical reports, eligibility cutoffs, archived records, or future-date planning.

Calculate Age in Other Formats

Completed years are not always the format you need. Google Sheets can also express the interval as total months, total days, or fractional years.

Age in Total Months

Use the "M" unit to return the total number of completed months between the DOB and the end date:

=DATEDIF(A2,TODAY(),"M")

Do not confuse this with "YM". The "M" unit counts whole months across the entire interval. "YM" returns only the month component left after whole years have been removed.

For example, a person who is 2 years and 5 completed months old may have a total age of 29 completed months. In that situation, "M" would return 29 while "YM" would return 5.

Age in Total Days

To calculate the total number of days between the date of birth and today, use:

=DATEDIF(A2,TODAY(),"D")

You can also subtract one valid date from another:

=TODAY()-A2

Because Google Sheets stores dates as date values, subtracting two valid dates produces the elapsed number of days. If the result displays as another calendar date instead of a number, change the result cell’s number format to Number.

Decimal Age With YEARFRAC

If you need age expressed as a decimal, such as 25.6 years, use YEARFRAC:

=YEARFRAC(A2,TODAY(),1)

The third argument controls the day-count convention. Using 1 selects Actual/Actual, which calculates the fractional year using the actual number of days between the dates and the actual number of days in the intervening years.

This detail matters because omitting the third argument does not mean “use exact calendar days.” Google Sheets defaults YEARFRAC to a 30/360 convention when the argument is omitted. That convention is commonly associated with financial calculations and can produce a slightly different decimal value.

Use DATEDIF when you need completed chronological components such as 25 years, 7 months, and 4 days. Use YEARFRAC when you specifically need the interval expressed as a fractional number of years.

Calculate Age for Multiple People

One of the main advantages of Google Sheets is that you can calculate ages for an entire list instead of entering each date into a calculator separately.

Copy the Formula Down

Suppose dates of birth are stored in column A and ages belong in column B.

Enter this in B2:

=DATEDIF(A2,TODAY(),"Y")

Then copy or drag the formula down. Each row will automatically reference its corresponding DOB.

Calculate an Entire Column With ARRAYFORMULA

If dates of birth are stored from A2 downward, you can calculate an entire age column from one formula:

=ARRAYFORMULA(IF(A2:A="","",DATEDIF(A2:A,TODAY(),"Y")))

This formula does two jobs:

• DATEDIF(A2:A,TODAY(),"Y") calculates completed years for the DOB range.
• IF(A2:A="","",...) keeps rows blank when no date of birth has been entered.

Because the range is open-ended, new DOB entries added farther down the column can receive an age result without manually copying the formula into each new row.

For a small static list, copying the standard DATEDIF formula is simpler. For an ongoing roster or dataset, the array approach can reduce maintenance.

Fix Common Google Sheets Age Formula Problems

Most incorrect age results are caused by the input dates, reference order, cell formatting, or blank rows rather than by the basic age formula itself.

Ignore Blank DOB Cells

A robust spreadsheet should not try to calculate an age when the DOB cell is empty.

Use:

=IF(A2="","",DATEDIF(A2,TODAY(),"Y"))

If A2 is blank, the formula returns a blank result. When a DOB is present, it calculates the completed age normally.

Fix Dates Stored as Text

A value can look like a date without being stored as a valid Google Sheets date value. This often happens after importing CSV files, copying data from another system, or working with inconsistent regional date formats.

If A2 contains a text string that Google Sheets recognizes as a date, DATEVALUE can convert it:

=DATEDIF(DATEVALUE(A2),TODAY(),"Y")

Use DATEVALUE only when the source really is text. If A2 already contains a valid date value, reference A2 directly.

Date strings recognized by DATEVALUE can depend on regional and language settings, so converting an ambiguous text date does not remove the need to confirm what the date is supposed to mean.

Check Date Format and Spreadsheet Locale

Dates such as 03/04/2000 are ambiguous. One locale may display or interpret the value as March 4, while another may treat it as April 3.

Google Sheets applies locale-specific defaults for date and number formatting across the spreadsheet. If DOB data comes from different countries or systems, confirm the sheet’s locale and the meaning of the source format before calculating ages.

When you need to build an unambiguous date inside a formula, use DATE(year,month,day). For example:

=DATE(2000,4,3)

This explicitly means April 3, 2000 because the arguments are always year, month, and day.

Fix #NUM! and Invalid Date Order

DATEDIF expects the start date to come before the end date. For an age calculation, the DOB should therefore be earlier than today or the target date.

If a future DOB is entered accidentally, or the start and end references are reversed, correct the dates rather than trying to hide the problem with formatting.

You can optionally use IFERROR in a user-facing sheet where you prefer a clear message:

=IFERROR(DATEDIF(A2,TODAY(),"Y"),"Check date")

This improves presentation, but it is still important to correct the underlying invalid date when possible.

Result Appears as a Date Instead of an Age

DATEDIF returns a number, but a cell can still display that number as a date if the cell already has Date formatting applied.

For example, a result that should simply be 8 could appear as an early-1900 calendar date when the output cell is formatted as a date.

How to Verify an Age Calculation

A simple manual check can catch an incorrect date of birth, a wrong reference date, or a spreadsheet formatting problem.

1. Subtract the birth year from the reference year.
2. Check whether the birthday had already occurred by the reference date.
3. If it had not, subtract one more year.

For example, with a date of birth of March 15, 1990 and a reference date of September 4, 2026: 2026 – 1990 = 36, and the birthday had already occurred, so the age is 36.

With a reference date of February 1, 2026, the birthday had not yet occurred, so the age is 35.

=DATEDIF(DATE(1990,3,15),DATE(2026,9,4),”Y”)

This check is especially useful when a formula result looks off by one year.

How Google Sheets Handles Leap-Year Birthdays

A February 29 date of birth can be entered as a valid date and used in the same date functions as other birthdays. DATEDIF compares the actual DOB with the selected end date and calculates the requested whole years, months, or days.

The part that can require more care is how a February 29 birthday should be interpreted in a non-leap year for a particular legal, administrative, or personal use case. That interpretation should not be hard-coded into a spreadsheet formula without knowing the rule that applies.

For a fuller explanation of the calendar issue, see our guide to how age is calculated for leap-year birthdays.

Which Google Sheets Age Formula Should You Use?

The best formula depends on the type of result you actually need.

Google Sheets vs an Online Age Calculator

Google Sheets is usually the better option when you are maintaining a list of people, working with many DOB records, or want ages to update automatically inside an existing spreadsheet.

An online calculator is simpler when you only need to check one date of birth and do not need to store or maintain a spreadsheet.

For a quick one-off calculation, use our age calculator. For lists, reports, records, and repeat calculations, keeping the formula in Google Sheets is usually more practical.

Frequently Asked Questions

Does Google Sheets have an AGE function?

Google Sheets does not currently provide a dedicated AGE() function in its standard function list. For ordinary age calculations, DATEDIF is the most direct option. YEARFRAC can be used when you need age expressed as a fractional number of years.

How does DATEDIF in Google Sheets differ from DATEDIF in Excel?

For age math they are the same function: both take a start date, an end date, and a unit (“Y”, “M”, “D”, “YM”, “MD”, or “YD”) and return the completed difference in that unit. The differences are practical. Google Sheets documents DATEDIF in its official function list, shows the syntax as you type, and autocompletes it. Excel still accepts DATEDIF but keeps it undocumented for compatibility with Lotus 1-2-3, so it never appears in Excel’s function wizard or autocomplete and must be typed exactly. A second practical difference is recalculation: Google Sheets recalculates continuously in the cloud, while a desktop Excel file recalculates when opened or on demand, which affects when TODAY()-based ages refresh. One result-level difference is worth knowing: Excel’s “MD” and “YD” units have long-standing quirks, so day counts can be wrong when dates fall in months of different lengths or around leap years. Completed-year “Y” results agree between the apps; only detailed year/month/day breakdowns can differ.

Is there an age formula that gives the same result in both Google Sheets and Excel?

Yes. =DATEDIF(A2,TODAY(),”Y”) returns the same completed-year age in both apps, because the “Y” unit is consistent. =YEARFRAC(A2,TODAY(),1) also agrees, since basis 1 means actual calendar days, do not omit the basis, because the default basis 0 is the financial 30/360 convention. Avoid “MD”, “YM”, and “YD” in workbooks that will be opened in both apps.

Does TODAY() return the same date for every viewer in every timezone?

No. TODAY() uses the timezone of the spreadsheet, not the timezone of each person viewing it. In Google Sheets the spreadsheet’s timezone is set under File > Settings, and that single timezone applies to every collaborator. A sheet set to New York time shows the same TODAY() value to a viewer in Tokyo, so near midnight the viewer’s local date and the sheet’s date can disagree by a day, and an age calculated on a birthday can flip a few hours early or late. If the reference date matters, such as for an eligibility cutoff, set the spreadsheet timezone deliberately or replace TODAY() with a fixed reference date. Excel behaves the opposite way: its TODAY() uses the computer’s local timezone, so a shared Excel file can show different ages to different viewers near midnight.

Why doesn’t my sheet show the correct age right after midnight?

Google Sheets recalculates volatile functions like TODAY() when the sheet changes or reopens, not continuously. A sheet left open past midnight can lag a day behind until something triggers a recalculation. To refresh more often, go to File > Settings > Calculation and set Recalculation to “On change and every minute” (or “every hour”).

How can I use Data Validation to prevent invalid dates of birth?

Select the date-of-birth column, then choose Data > Data validation. Set the criteria to Date and pick a rule such as “is valid date”, which rejects text entries and impossible dates like February 30, or “is between” two bounds, such as not before 1900 and not in the future. Set the behavior to “Reject input” so invalid entries are blocked rather than only warned about. This stops the most common DATEDIF failures, including text dates and start dates later than the end date, before they reach the formula. Combine it with the IF(A2=””,””,…) wrapper so empty cells stay blank while real entries must be valid dates. For stricter control, you can use “Custom formula is” with =AND(ISDATE(A2),A2<=TODAY(),A2>=DATE(1900,1,1)) to require a real date that is not in the future and not before 1900. Under “On invalid data,” “Show warning” still accepts an invalid entry but flags the cell with a red triangle, while “Reject input” blocks it. Note that validation applies to new entries only: to scan dates already in the column, use a helper column with =ISDATE(A2) and look for FALSE results.

What happens when someone pastes a bad date into a validated DOB column?

The validation rule stays on the column, so pasting follows the same setting: “Reject input” blocks invalid pasted dates, while “Show warning” accepts them with a red triangle. After any bulk import or paste, re-run the =ISDATE(A2) check to catch anything that slipped through.

Related Age Calculation Guides

• How to Calculate Age
• Age Calculator Formula Explained
• How to Calculate Age in Excel
• Chronological Age Explained