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.

Change the result cell to a Number format to display the age or elapsed-day value correctly.

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.

Does the age formula update automatically?

Yes, when your formula uses TODAY(), the current-date value updates when Google Sheets recalculates the spreadsheet. This means the age can change automatically when the relevant birthday passes without you editing the formula.

Can I calculate age from two cells containing dates?

Yes. If A2 contains the DOB and B2 contains the date on which you want to calculate age, use:

=DATEDIF(A2,B2,"Y")

You can replace "Y" with another supported unit when you need months, days, or a remainder component.

Why does my age result appear as a date?

The formula may be returning the correct number while the result cell is formatted as a Date. Change the output cell to Number formatting. This is especially common when calculating total days.

Can Google Sheets ignore blank date-of-birth cells?

Yes. Wrap the age calculation in an IF check:

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

This leaves the result blank until a DOB is entered.

Why does the same date look different in another Google Sheet?

The two spreadsheets may use different locales or date formats. Locale affects a spreadsheet’s default date and number formatting, so always confirm the intended meaning of ambiguous dates before calculating age.

Does a February 29 birthday work in Google Sheets?

Yes. February 29 is a valid date in leap years and can be used with Google Sheets date functions. If you need to determine how the birthday should be treated in a non-leap year for a particular rule or use case, see our leap-year birthday age guide.

What is the difference between M and YM in DATEDIF?

"M" returns the total number of whole months between the start and end dates. "YM" returns only the whole months remaining after complete years have been removed. Use "M" for total age in months and "YM" when building an exact years-months-days result.

Related Age Calculation Guides

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