An interesting workaround allows you to calculate age in Google Sheets using a date of birth.
The formula allows users to subtract the current day from the date of birth to get either the age in years, months, days, or all three.
Calculate Age in Years in Google Sheets
The formula in question is called the DATEDIF formula. This formula takes in 3 inputs:
- Start Date: This is the date of birth
- End Date: This is todays date
- Unit: Whether the output should be in years, months, or days
Let’s get started with an example to demonstrate how this formula can be used to calculate age in years in Google Sheets.
In the example dataset below we have two columns, one with names and the other column with the corresponding date of births.
In the third column we will be calculating the age in years by using the formula below.
=DATEDIF(B2, TODAY(), “Y”)
As you can see below, this populates the cell C2 with the age in years. We can extend this list by hovering the mouse over the bottom of the cell and dragging down.
Similarly we can get the age in months or days by changing the formula to the one below.
=DATEDIF(B2, TODAY(), “M”) or = DATEDIF(B2, TODAY(), “D”)
Calculate Exact Age in Google Sheets
If you want to calculate an age that is more precise than just the year, you can make some modifications to the DATEDIF formula.
Through these modifications it is possible for you to calculate the exact age in Google Sheets (Years, Months, Days).
Here is how:
The formula can be modified to the following to get the exact age in one column. Simply replace B2 with the cell that contains the date of birth in your dataset.
=DATEDIF(B2, TODAY(), “Y”) & ” Years, ” & DATEDIF(B2, TODAY(), “YM”) & ” Months, and ” & DATEDIF(B2, TODAY(), “MD”) & ” Days”
This formula will nicely output the exact age in years, months, and days.
