How to calculate age from date of birth in Google Sheets

Jason Gong
LAST UPDATED
June 6, 2024
TL;DR

Use DATEDIF and TODAY() to calculate age from DOB in Google Sheets.

By the way, we're Bardeen, we build a free AI Agent for doing repetitive tasks.

Since you're working with Google Sheets, check out Bardeen's automation features. It helps automate tasks, making your work faster and easier.

Mastering Google Sheets is essential for maximizing productivity while minimizing frustration. But don’t worry if you’re struggling with converting a date of birth (DOB) to age. By the time you’re done reading this guide, you’ll calculate any age in years, months, or even days.

This is a fantastic feature for maintaining databases or data entry involving extensive lists of DOB. The formulas you’re about to learn greatly enhance data analysis and reporting capabilities.

However, like many Sheets functions, converting a birth date isn’t that obvious. We’ll guide you through the process of converting DOB to age in your Google sheet.

How to convert a birth date to age in Google Sheets

Converting a birth date to age in Google Sheets is achieved using one of these methods:

  • the DATEDIF and TODAY() combination of functions
  • the YEARFRAC function

For detailed age statements, both formulas can also be expanded to include years, months, and days, with the option to calculate age across multiple rows using ARRAYFORMULA.

Both functions can compute the current age based on the DOB provided, but they differ in complexity and the type of result they return:

  • DATEDIF calculates the total number of days, months, or years between two dates
  • the YEARFRAC function calculates the fraction of a year between two given dates

Using DATEDIF to calculate age in Google Sheets

The most commonly used formula for calculating age in Google Sheets is the DATEDIF function. It requires just three parameters:

  • the start date (DOB)
  • the end date (usually the current date)
  • the unit of measurement (years, months, or days)

The basic syntax for calculating age in years is: '=DATEDIF(start_date, end_date, "Y")'

To use the current date as the end date, you can utilize the TODAY() function, making the formula: '=DATEDIF(A1, TODAY(), "Y")'

Assume the DOB is in cell A1; this formula will return the age (in full years) in the blank cell below it.

Do you often use such functions manually? Then, you probably understand the level of attention needed when entering commands. Even a single error can disrupt the entire function, so why not leverage workflow automation? It can do wonders for your personal productivity.

Using YEARFRAC to calculate age in Google Sheets

The YEARFRAC function’s ability to calculate the fraction of a year between two dates makes it particularly useful when you need to calculate the portion of a year that has passed between two dates. The basic syntax for the YEARFRAC function is ‘YEARFRAC(start_date, end_date, [basis])’

Where:

  • start_date: The start date of the period you want to calculate from; and
  • end_date: The end date of the period you want to calculate to.

To use the YEARFRAC function, open your Google Sheets document:

  • Click on the cell where you want the result to appear.
  • Type =YEARFRAC( to begin the function.
  • Enter the start date followed by a comma.
  • Enter the end date followed by a comma.

For example, ‘=YEARFRAC(A1, B1)’ where A1 contains the start date and B1 the end date, will calculate the fraction of the year between the two dates and register the result in the cell you have selected.

Advanced DATEDIF formula to calculate age in any format

For a more detailed age calculation that includes years, months, and days, you shouldn’t find it terribly difficult to expand on the basic formulas. And if you’re into Google Sheets automation, maybe you should try Bardeen.

By combining the DATEDIF function with concatenation, you can create a comprehensive age statement in your Google sheet. The formula for a detailed age calculation is:

'=DATEDIF(A1, TODAY(), "Y") & " Years " & DATEDIF(A1, TODAY(), "YM") & " Months & " & DATEDIF(A1, TODAY(), "MD") & " Days"

This formula calculates the total years; the remaining months after subtracting the years; and the remaining days after subtracting the months.

For calculating age across multiple rows simultaneously, the ARRAYFORMULA function can also be used in combination with the DATEDIF formula:

'=ArrayFormula(DATEDIF(A1:A5, TODAY(), "Y") & " Years " & DATEDIF(A1:A5, TODAY(), "YM") & " Months & " & DATEDIF(A1:A5, TODAY(), "MD") & " Days")'

This formula will calculate the age for each DOB listed in cells A1 through A5, displaying the result in a detailed format for each entry.

While more complex, using such formulas allows efficient age calculation from DOB in Google Sheets.

Automate everything Google Sheets with Bardeen

If you often find yourself juggling complex scenarios, think about automating Google Sheets. It will significantly enhance your productivity and accuracy. Automation allows:

  • real-time updates
  • eliminates the chance of human error

That’s enough to make it an essential tool for managing large datasets or when dealing with sensitive calculations. Better yet, it spares the user from needing to engage in complex manual functions like we’ve explored above.

Bardeen offers powerful automations that can streamline any workflow in Google Sheets. How much time would you save if you could?

1. Automate the enrichment of email contact lists with detailed demographic and firmographic data, saving to Google Sheets. This playbook ensures your data is always rich and up-to-date.

2. Let our AI analyze customer feedback from Google Sheets, and generate detailed persona reports. This helps in understanding your audience better and tailoring your services accordingly.

3. Keep your Google Sheet synced with relevant data about your prospects. Go ahead and test it with any LinkedIn profile:

With playbooks, you can automate a broad range of tasks in Google Sheets and many other apps Bardeen integrates with. Are you ready to download our Chrome extension? It is and will always be free, and you’ll get a 14-day free trial of premium features.

Contents
Automate Google Sheets with Bardeen

Bardeen automates repetitive tasks in Google Sheets, saving you time and effort.

Get Bardeen free

Related frequently asked questions

Import KML Files to Google Sheets: A Step-by-Step Guide

Learn how to import KML files into Google Sheets by converting them to CSV/XLSX formats for easy data analysis and visualization. Step-by-step guide included.

Read more
Transpose Data in Google Sheets: 3 Easy Methods

Learn to transpose vertical rows into horizontal in Google Sheets using TRANSPOSE function, Paste Special, or Coefficient's Formula Builder for better data organization.

Read more
Become a HubSpot Consultant: A Step-by-Step Guide

Learn how to become a HubSpot consultant in 4 steps: assess eligibility, choose a partnership level, complete onboarding, and leverage resources.

Read more
Efficiently Import Multiple CSV Files to Google Sheets: A Step-by-Step Guide

Learn to import multiple CSV files into Google Sheets using Coupler.io or Google Apps Script for automation, saving time and ensuring up-to-date data.

Read more
Accessing Salesforce Data Import Wizard: A Step-by-Step Guide

Learn how to access and use the Salesforce Data Import Wizard for efficient data management, including step-by-step instructions and required permissions.

Read more
Easy Google Sheet to Excel Conversion Guide in 3 Steps

Learn to convert Google Sheets to Excel in three easy steps. Includes converting Google Forms data and macro-enabled Excel files to Google Sheets.

Read more
how does bardeen work?

Your proactive teammate — doing the busywork to save you time

Integrate your apps and websites

Use data and events in one app to automate another. Bardeen supports an increasing library of powerful integrations.

Perform tasks & actions

Bardeen completes tasks in apps and websites you use for work, so you don't have to - filling forms, sending messages, or even crafting detailed reports.

Combine it all to create workflows

Workflows are a series of actions triggered by you or a change in a connected app. They automate repetitive tasks you normally perform manually - saving you time.

get bardeen

Don't just connect your apps, automate them.

200,000+ users and counting use Bardeen to eliminate repetitive tasks

Effortless setup
AI powered workflows
Free to use
Reading time
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
By clicking “Accept”, you agree to the storing of cookies. View our Privacy Policy for more information.