Age Calculation

How to Calculate Age in Google Sheets (From Date of Birth)

Google Sheets formulas to calculate age from a date of birth: whole years, years-months-days, age on a date, a whole column at once with ARRAYFORMULA.

How to Calculate Age in Google Sheets (From Date of Birth)
In this article
  1. How to Calculate Age in Google Sheets from Date of Birth (Complete Years)
  2. Automate Age Calculation for a Whole Column with ARRAYFORMULA
  3. Displaying Age in Years, Months, and Days
  4. Other Useful Age-Related Formulas in Google Sheets
  5. Compare Your Results with an Age Calculator
  6. Common Problems and How to Fix Them
  7. Advanced Age Analysis: Grouping and Statistics
  8. Who This Advice Suits (and Who It Doesn't)
  9. Privacy Tip
  10. Sheets versus Excel
  11. Frequently asked questions
  12. Sources

In Google Sheets, =DATEDIF(A2,TODAY(),"Y") returns a person’s age in complete years from the date of birth in cell A2. To calculate age for a whole column at once, use =ARRAYFORMULA(IF(A2:A="", "", DATEDIF(A2:A, TODAY(), "Y"))) in the first cell of an empty column.

How to Calculate Age in Google Sheets from Date of Birth (Complete Years)

The most common need is to find someone’s age in full years, counting only complete 12-month periods. Google Sheets provides the DATEDIF function for this. It calculates the number of full units (years, months, or days) between two dates.

The Core DATEDIF Formula Explained

=DATEDIF(start_date, end_date, unit)
  • start_date: This is the earlier date, which is the date of birth (e.g., cell A2).
  • end_date: This is the later date, usually today’s date. You use the TODAY() function for this, which automatically updates to the current date each time you open or edit your sheet.
  • unit: This specifies the type of unit you want to count. For complete years, use "Y".

To calculate age in Google Sheets, use this formula. For a birth date in cell A2, the formula is:

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

This counts full years between A2 and today. The age increases only on the birthday.

Calculating Age on a Specific Date (Not Today)

Sometimes, you need to calculate age in Google Sheets as of a specific past or future date. This could be for a course start, a competition age cut-off, or an insurance policy effective date. You simply replace TODAY() with your desired date.

  • Using a cell reference: If your specific date is in cell B2, the formula becomes:
    =DATEDIF(A2, B2, "Y")
  • Using a hardcoded date: If you want to use a fixed date directly in the formula, use the DATE(year, month, day) function. For example, to calculate age as of January 1, 2027:
    =DATEDIF(A2, DATE(2027,1,1), "Y")

    Remember to always enter the date in DATE(year, month, day) format to avoid locale-related issues.

Step-by-Step: Entering the Formula

Follow these steps to calculate age for a single individual:

  1. Open your Google Sheet: Navigate to the sheet where you store your data.
  2. Enter the Date of Birth: Type the date of birth into a cell, for example, A2. Make sure Sheets recognizes it as a date (it will typically right-align).
  3. Select an Empty Cell: Choose a different cell where you want the age to appear (e.g., B2).
  4. Type the Formula: In the chosen cell, type =DATEDIF(A2, TODAY(), "Y").
  5. Press Enter: The cell will display the calculated age in complete years.
  6. Drag to Fill (Optional): If you have more dates of birth below, click on the cell containing the formula (B2). Drag the small blue square at the bottom-right corner of the cell downwards to apply the formula to the rest of the column. Sheets will automatically adjust the cell references (e.g., A3, A4, etc.).

Worked Example: Single Age Calculation

Let’s say you have a list of names and birth dates:

Name Date of Birth Age (Calculated)
Alice 1990-05-15
Bob 2000-11-20

Assume Alice’s birth date (1990-05-15) is in cell B2. To calculate her age in cell C2:

  1. Click on cell C2.
  2. Type =DATEDIF(B2, TODAY(), "Y").
  3. Press Enter.

If today is 2024-03-08, cell C2 will display 33. If you were calculating her age as of 2025-01-01, you would use =DATEDIF(B2, DATE(2025,1,1), "Y"), which would result in 34.

Automate Age Calculation for a Whole Column with ARRAYFORMULA

Birth dates often come from Google Forms. New entries always appear at the bottom. Manually dragging formulas down is inefficient. This also causes errors. Use ARRAYFORMULA for automatic application. It applies a formula to an entire range. This includes new rows. It helps calculate age in Google Sheets effectively.

Why Use ARRAYFORMULA?

  • Automation: New data added to the specified range (e.g., from a Google Form) automatically gets the formula applied.
  • Efficiency: You only enter the formula once in the header row, not in every cell.
  • Maintainability: It’s easier to manage and update a single array formula than many individual cell formulas.
  • Clarity: It prevents accidental deletion of formulas in individual cells.
How to Calculate Age in Google Sheets: Person typing on a laptop with a Google Sheet open, coffee, and notebook.

Here is the ARRAYFORMULA for calculating age in a whole column:

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

You place this formula in the first cell of an empty column (e.g., B2). It works as follows:

  • ARRAYFORMULA(...): This wrapper tells Sheets to apply the enclosed formula to an entire range.
  • IF(A2:A="", "", ...): This is a conditional statement. It checks if cells in the range A2 to the end of column A (A2:A) are blank.
    • If a cell in A2:A is blank (A2:A=""), the formula returns an empty string (""), keeping the corresponding age cell blank.
    • If a cell in A2:A is not blank, it proceeds to calculate the age using DATEDIF(A2:A, TODAY(), "Y").
  • DATEDIF(A2:A, TODAY(), "Y"): This is the age calculation, applied to each date in the range A2:A.

New form responses or manual entries in column A immediately calculate age in Google Sheets, specifically in column B. Ages update daily. This is because TODAY() recalculates whenever the sheet opens or you make an edit.

Worked Example: Auto-Updating Age List

Imagine your dates of birth are in column A, starting from A2. You want ages to appear in column B.

  1. In cell B1, type a header like “Age”.
  2. In cell B2, enter the formula: =ARRAYFORMULA(IF(A2:A="", "", DATEDIF(A2:A, TODAY(), "Y"))).
  3. Press Enter.

Column B will now populate with ages for all dates in column A. If you add a new date to A10, B10 will automatically show the age.

Integrating with Google Forms for New Entries

This setup is especially powerful when collecting dates of birth via Google Forms:

  1. Create the Form: In Google Forms, add a “Date” question for the date of birth. Using a date field ensures valid date input, unlike a short-answer text field.
  2. Link to Sheets: In your Google Form, go to the “Responses” tab and click “Link to Sheets”. This creates a new Google Sheet that automatically records all form submissions.
  3. Identify Date Column: In the linked sheet, find the column where the dates of birth land (e.g., column C).
  4. Add Age Column Header: In the first empty column to the right of your form data (e.g., column D), row 1, add a heading such as “Age”.
  5. Enter ARRAYFORMULA: In the first data row of your new age column (e.g., D2), enter the ARRAYFORMULA, adjusting the date of birth column reference. If dates of birth are in column C, use:
    =ARRAYFORMULA(IF(C2:C="","",DATEDIF(C2:C,TODAY(),"Y")))
  6. Optional Fixed Date Age: If you need age on a specific event day, add another column (e.g., E) with a header like “Age on Event Day” and use:
    =ARRAYFORMULA(IF(C2:C="","",DATEDIF(C2:C,DATE(2027,6,1),"Y")))

New form responses will automatically appear in your sheet, and their ages will be calculated immediately without any manual intervention. This ensures your data is always up-to-date and complete for tasks like how to calculate age in Google Sheets.

A person's hand writing on a form with a pen.

Displaying Age in Years, Months, and Days

Sometimes you need a precise age. Show full years, months, and days since the last birthday. You can calculate age in Google Sheets using multiple DATEDIF functions for this.

=DATEDIF(A2,TODAY(),"Y")&" y, "&DATEDIF(A2,TODAY(),"YM")&" m, "&DATEDIF(A2,TODAY(),"MD")&" d"

Let’s break down the units:

  • "Y": Returns the number of complete years between the two dates.
  • "YM": Returns the number of complete months *after* subtracting the complete years. For example, if someone is 30 years and 5 months old, “YM” will return 5.
  • "MD": Returns the number of complete days *after* subtracting the complete years and complete months. For example, if someone is 30 years, 5 months, and 10 days old, “MD” will return 10.

The & symbol concatenates (joins) these results with descriptive text like ” y, “, ” m, “, and ” d” to make the output readable.

Addressing the “MD” Unit Anomaly

The “MD” unit in DATEDIF can misbehave. This is like Excel. It often happens at month ends. This leads to wrong day counts. This is a known quirk. If you need precise day counts since the last month-birthday, an alternative is better to calculate age in Google Sheets.

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

This formula works by:

  1. DATEDIF(A2,TODAY(),"M"): Calculates the total number of full months between the birth date (A2) and today.
  2. EDATE(A2, ...): Adds that total number of months to the birth date (A2). This effectively gives you the date of the last “month-birthday” (e.g., if born Jan 15, 1990 and it’s March 8, 2024, this calculates Feb 15, 2024).
  3. TODAY() - ...: Subtracts this “last month-birthday” date from today’s date. The result is the exact number of days since that specific date.

You can then combine this more accurate day count with the “Y” and “YM” results if needed for full precision.

Result Formula Explanation
Age as a decimal =YEARFRAC(A2, TODAY(), 1) Calculates age as a decimal, useful for more precise averages. The ‘1’ specifies the day count basis (actual days).
Age in total months =DATEDIF(A2, TODAY(), "M") Returns the total number of complete months passed since birth.
Age in total days =TODAY()-A2 (format as number) Subtracts the birth date from today’s date. Sheets stores dates as numbers, so this directly gives the number of days. Format the cell as a number.
Next birthday =EDATE(A2, 12*(DATEDIF(A2,TODAY(),"Y")+1)) Calculates the date of the next upcoming birthday. It adds 12 months for each completed year plus one more year to get to the next birthday.
Birthday this year =DATE(YEAR(TODAY()), MONTH(A2), DAY(A2)) Determines the date of the person’s birthday in the current year. Turns Feb 29 into Mar 1 in common years.
Over 18 today? =IF(DATEDIF(A2,TODAY(),"Y")>=18,"Yes","No") A simple check to see if someone meets an age requirement. Adjust “18” as needed.
Days until next birthday =IF(EDATE(A2, 12*(DATEDIF(A2,TODAY(),"Y")+1))-TODAY() < 0, EDATE(A2, 12*(DATEDIF(A2,TODAY(),"Y")+2))-TODAY(), EDATE(A2, 12*(DATEDIF(A2,TODAY(),"Y")+1))-TODAY()) Calculates the number of days remaining until the next birthday. This handles cases where the birthday has already passed this year.

The formula “birthday this year” uses DATE(YEAR(TODAY()), MONTH(A2), DAY(A2)). It handles leap years. February 29 becomes March 1 in common years. If you prefer February 28 for a February 29 birthday in non-leap years, try EDATE. This variation calculates the next birthday. Then, it checks if it’s within the current year. This is how you calculate age in Google Sheets.

A person typing on a laptop with a notebook and pen nearby.

Compare Your Results with an Age Calculator

It is always a good practice to verify your formulas, especially when dealing with dates and ages, as formatting and locale settings can sometimes cause unexpected results. Use a reliable external tool to double-check your calculations.

Enter a date of birth below and check that your sheet gives the same result — a quick way to catch dates that Sheets has read the wrong way round or to confirm your formula’s accuracy for calculate age in Google Sheets.

Loading the calculator…

Common Problems and How to Fix Them

When you calculate age in Google Sheets, you might encounter issues related to date formatting, locale settings, or formula errors. Here are common problems and their solutions.

Dates Not Recognized (DATEDIF Returns #VALUE! Error)

If DATEDIF returns a #VALUE! error, it often means Google Sheets does not recognize your input as a valid date. This usually happens when the date format you entered doesn’t match the spreadsheet’s locale settings.

An hourglass with blue sand spilling onto a calendar.
  • Locale Mismatch: Sheets reads dates according to the spreadsheet’s locale (e.g., File → Settings → Locale). A US locale interprets “03/04/2010” as March 4th, while a UK locale interprets it as April 3rd. If your input format conflicts with the locale, Sheets treats it as text. Dates Sheets can’t read remain left-aligned in the cell, indicating they are text, not numbers (dates are numbers in Sheets).
  • Fix:
    1. Set the Correct Locale: Before importing or typing dates, go to File > Settings > Locale and set it to match your typical date entry format.
    2. Convert Text to Date: If dates are already entered as text, use the DATEVALUE() function. For example, if “03/04/2010” is in A2 but Sheets sees it as text, you can try =DATEVALUE(A2) to convert it. This only works if the text string is unambiguous to the current locale.
    3. Use ISO 8601 Format: The safest date format for typing or importing is ISO 8601: YYYY-MM-DD (e.g., 2010-04-03). Every locale reads this format the same way, ensuring consistency.
    4. Format Cells as Date: Select the column with dates, go to Format > Number > Date. This helps Sheets interpret existing numbers as dates.

DATEDIF Returns #NUM! Error

A #NUM! error with DATEDIF usually means the start_date is after the end_date. When you calculate age in Google Sheets, this means the birth date follows the current date. Or, it could be after your provided end date.

  • Fix: Double-check your dates. Ensure the date of birth (start_date) is indeed earlier than or equal to TODAY() or your specified end_date.

Age Not Updating Automatically

If you use TODAY() in your formula but the ages don’t update daily:

  • Recalculation Settings: Google Sheets usually recalculates TODAY() (and other volatile functions) when you open the sheet or make an edit. If you leave the sheet open for days without interaction, it might not update.
  • Fix:
    1. Simply open the sheet or make any minor edit (e.g., type something in an empty cell and then delete it).
    2. Check your calculation settings: Go to File > Settings > Calculation. Ensure “Recalculation” is set to “On change and every minute” or “On change and every hour” for more frequent updates, though the default “On change” usually suffices upon opening.

Incorrect Age Due to Rounding (for Average/Median)

When calculating average or median age, using whole-year ages (which DATEDIF(...,"Y") provides) can introduce a slight bias because it always rounds down. This pulls the average about half a year too low.

  • Fix: For precise average or median age calculations, use decimal ages obtained with YEARFRAC.
    =YEARFRAC(A2, TODAY(), 1)

    Then, apply AVERAGE() or MEDIAN() to the column of decimal ages.

Advanced Age Analysis: Grouping and Statistics

Beyond individual age calculation, you can use Sheets to analyze age data for reports, demographics, or other statistical purposes.

Grouping Ages into Bands for Reports

To group people into age bands (e0.g., 18–24, 25–34), you can combine the age calculation with an IFS function or a lookup table.

Assuming the age is in cell B2:

=IFS(B2<18,"Under 18", B2<25,"18–24", B2<35,"25–34", B2<45,"35–44", B2<55,"45–54", B2<65,"55–64", TRUE,"65+")
  • How it works: The IFS function checks conditions in order. It returns the value associated with the first true condition. For example, if B2 is 22, it’s not “<18”, but it is “<25”, so it returns “18–24”.
  • Applying to a column: You can wrap this in an ARRAYFORMULA if your ages are in a range (e.g., B2:B):
    =ARRAYFORMULA(IF(B2:B="", "", IFS(B2:B<18,"Under 18", B2:B<25,"18–24", B2:B<35,"25–34", B2:B<45,"35–44", B2:B<55,"45–54", B2:B<65,"55–64", TRUE,"65+")))
  • Reporting: Once you have a column of age bands, you can create a pivot table based on that column to count how many people fall into each band. This is useful for demographic analysis.

Calculating Average and Median Age

For statistical insights into your group’s age distribution, you can calculate the average (mean) and median age.

Assuming your ages are in column E (e.g., E2:E):

  • Average Age (Mean): =AVERAGE(E2:E)

    This gives you the arithmetic mean of all ages.

  • Median Age: =MEDIAN(E2:E)

    The median is the middle value in a sorted list of ages. It is less affected by extreme ages (outliers) than the average, making it a good measure for skewed distributions.

As noted earlier, for a precise average, use decimal ages calculated with YEARFRAC. Whole-year ages (from DATEDIF(...,"Y")) always round down, which slightly underestimates the true average age.

A calculator on a dark background.

Highlighting Birthdays This Month/Today

You can use conditional formatting to visually identify upcoming birthdays in your sheet.

  • Highlight Birthdays This Month:
    1. Select the column containing the dates of birth (e.g., column C).
    2. Go to Format > Conditional formatting.
    3. Under “Format rules,” choose “Custom formula is” from the “Format cells if…” dropdown.
    4. Enter the formula: =MONTH(C2)=MONTH(TODAY())
    5. Choose a fill color and click “Done.”

    Any cell in column C with a month matching the current month will be highlighted.

  • Highlight Today’s Birthdays:

    For a more specific highlight of only today’s birthdays, use this custom formula:

    =AND(MONTH(C2)=MONTH(TODAY()),DAY(C2)=DAY(TODAY()))

Creating a List of Upcoming Birthdays

To easily see who has a birthday coming up, you can generate a sorted list of next birthdays.

A calendar with dates and a pushpin marking a specific date.
  1. Calculate Next Birthday Dates: In a helper column (e.g., D), add this ARRAYFORMULA. If birth dates are in column C:
    =ARRAYFORMULA(IF(C2:C="","",EDATE(C2:C,12*(DATEDIF(C2:C,TODAY(),"Y")+1))))

    This formula determines the date of the next birthday for each person, even if it’s next year.

  2. Sort by Next Birthday: In a separate part of your sheet, or on a new tab, use the SORT and FILTER functions to create a dynamic list. Assuming names are in A2:A and the next birthday dates are in D2:D:
    =SORT(FILTER({A2:A,D2:D},D2:D<>""),2,TRUE)

    This formula filters out blank rows. It combines names and next birthday dates. Then it sorts them by the next birthday date in ascending order. The first rows of this new list show the most immediate upcoming birthdays. The list updates every day. This is how to calculate age in Google Sheets.

Who This Advice Suits (and Who It Doesn’t)

This guide is ideal for:

  • Individuals: Who want to track personal ages or family birthdays.
  • Small Businesses/Organizations: Managing employee records, class rosters, or event participant lists where age is a factor (e.g., age-restricted events, youth programs).
  • Teachers/Educators: Keeping track of student ages for class management or specific educational program eligibility.
  • Anyone needing dynamic, automatically updating age calculations within a spreadsheet environment.

This advice may not be the best fit for:

  • Large-Scale HR Systems: While Google Sheets can handle a lot, for highly sensitive, large-scale employee data with complex privacy and compliance requirements, dedicated HR software is more appropriate.
  • Public-Facing Age Verification: For website or application age gates, you would use programming languages and databases, not a Google Sheet.
  • One-Off Age Checks: If you just need to check one or two ages quickly, using an online age calculator (like this one) is faster than setting up a sheet.

Privacy Tip

A sheet with names and birth dates is personal data. Handle it with care. Share only with authorized individuals. Share on a need-to-know basis. If you need only aggregated age statistics, delete full birth dates. Store only the ages after calculating. For a one-off age check, use a browser-based calculator. It stores nothing and leaves no digital trace. Use Google Sheets for ongoing needs. Data persistence and automation are beneficial when you calculate age in Google Sheets.

Sheets versus Excel

While Google Sheets and Microsoft Excel share many similarities in date and time functions, there are key differences, especially regarding age calculation methods:

  • Function Parity: DATEDIF, EDATE, YEARFRAC, and NETWORKDAYS generally work the same way in both applications, using the same syntax and producing identical results for common scenarios.
  • Date System Basis: Sheets uses December 30, 1899, as its “day 0” for dates, correctly handling dates in early 1900. Excel has a historical bug where it incorrectly treats 1900 as a leap year, which can cause date calculations for dates before March 1, 1900, to be off by one day.
  • Recalculation Behavior: Both Sheets and Excel recalculate volatile functions like TODAY() when you open the workbook/sheet or make edits. Excel also offers manual recalculation via F9.
  • Array Formulas: Google Sheets has the explicit ARRAYFORMULA function to apply a formula to an entire range. In Microsoft 365, dynamic arrays offer similar automatic spill functionality, while older Excel versions require entering array formulas with Ctrl+Shift+Enter.

Frequently asked questions

How do I calculate age from a date of birth in Google Sheets?

Use the formula =DATEDIF(A2,TODAY(),"Y"), replacing A2 with the cell containing the date of birth.

Why does DATEDIF give an error like #VALUE! or #NUM!?

A #VALUE! error often means Google Sheets cannot recognize the date. This is common when you calculate age in Google Sheets, due to a locale mismatch or incorrect date format (it’s stored as text). Ensure dates are in YYYY-MM-DD format. Or, match the sheet’s locale. A #NUM! error means the start date (date of birth) is later than the end date (e.g., today’s date).

Does the age update automatically in Google Sheets?

Yes. The TODAY() function recalculates when you open the sheet or make an edit. This automatically updates all ages on each birthday or as time progresses, ensuring your age calculations are always current.

Can I calculate age for a whole column at once in Google Sheets?

Yes, use an ARRAYFORMULA. Place =ARRAYFORMULA(IF(A2:A="","",DATEDIF(A2:A,TODAY(),"Y"))) in the first cell of an empty column (e.g., B2), assuming dates of birth are in column A starting from A2.

How do I calculate age on a specific date, not today?

Replace TODAY() in the DATEDIF formula. Use your specific date instead. For example, if the specific date is in cell B2, use =DATEDIF(A2, B2, "Y"). If you hardcode the date, use =DATEDIF(A2, DATE(2027,1,1), "Y").

Sources

  • Google Docs Editors Help: DATEDIF function
  • Next, Google Docs Editors Help: Google Sheets function list (YEARFRAC, EDATE, IFS)
  • Google Docs Editors Help: ARRAYFORMULA function
  • Also, Google Docs Editors Help: DATE function
Photo of Yasir Hafeez

Written by

Yasir Hafeez is the founder and editor of ItsAgeCalculator. He builds the calculators, writes and checks the guides and articles, and tests every result against fixed date rules. He also runs OmniMatrics, a site of everyday calculators and converters.

More articles · Editorial policy · Spotted a mistake? Let us know.

Age tools