How to calculate age from date of birth in Google Sheets

LAST UPDATED
September 4, 2024
Jason Gong
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.

Instead of manually calculating age from a birth date, you can use Bardeen to automate enriching and updating data in Google Sheets. Bardeen can automatically calculate age from DOB and update rows with the age:

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

Bardeen can also classify, qualify or enrich rows in other ways to add more context to your Google Sheet:

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)
__wf_reserved_inherit

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.

Bardeen can help automate importing data from various sources into Google Sheets, saving you time and effort. Try these playbooks:

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.

Bardeen can also help you use data in Google Sheets to generate personalized content, further automating your workflows:

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.

Bardeen can help automate tasks that involve getting data from various sources and saving it to Google Sheets, saving you time from manual data entry:

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.

__wf_reserved_inherit

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
Schedule a demo

Related frequently asked questions

Embed Links in Notion Pictures Easily in 4 Steps

Learn how to easily embed links in Notion pictures in 4 simple steps, using the image caption feature for clickable URLs.

Read more
Import Data from Images to Google Sheets: A Step-by-Step Guide

Learn how to import data from images into Google Sheets using OCR technology, Excel's mobile app, online tools, or Google Workspace add-ons.

Read more
Convert Google Sheets to CSV: A Step-by-Step Guide

Learn how to convert Google Sheets to CSV for better data manipulation and sharing. A step-by-step guide on exporting, importing, and editing CSV files.

Read more
Assign Permission Sets to Users in Salesforce: A Guide

Learn why you can't directly assign permission sets to profiles in Salesforce and discover effective workarounds for managing user permissions.

Read more
Insert Symbols in Google Sheets: Easy Methods (2024)

Discover how to insert symbols like degree, euro, and delta in Google Sheets using shortcuts, CHAR function, and copy/paste methods.

Read more
Add Picklist Values to Salesforce Record Types: A Guide

Learn how to add picklist values to Salesforce record types to enhance data integrity and user experience by presenting relevant options.

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.