If you want to calculate how old someone is using their date of birth, there are a few different ways you can do this in Google Sheets.
You can set up a basic formula that will calculate the difference between two dates to do this.
In this tutorial, I will show you how to calculate age in Google Sheets.
Table of Contents
To find out the age of someone using their birthday, we will be using the DATEDIF function.
This function calculates years, months, or days between two dates.
The syntax of this function is:
=DATEDIF(start_date, end_date, unit)
- start_date – The start date used in calculating the date difference. Can be a cell reference, a function returning a date, or a number
- end_date – The end date used in calculating the date difference. Can be a cell reference, a function returning a date, or a number
- unit – This parameter is an abbreviation for the unit of time. These values are accepted in this function: “Y”,”M”,”D” ,”MD”,”YM”,”YD”
- “Y”: this means the number of whole years between start_date and end_date
- “M”: this means the number of whole months between start_date and end_date
- “D”: this means the number of days between start_date and end_date
- “MD”: this means the number of days between start_date and end_date after subtracting whole months
- “YM”: this means the number of whole months between start_date and end_date after subtracting whole years
- “YD”: this means the number of days between start_date and end_date, assuming start_date and end_date are no more than one year apart
Calculate Age in Google Sheets – Years Only
Now that we have taken a look at the basics of the DATEDIF function, let’s look at how to use it to calculate age in years only.
The formula I will be using is:
=DATEDIF(start_date, end_date, “Y”)
As we covered earlier, using “Y” as my unit of time will calculate the number of whole years between my start date and end date, so this will give me how old someone is in years.
Here are the steps to do this:
1. Select the cell where you want to calculate the age
2. Press the equals sign (=) on your keyboard and then type “DATEDIF” and press Tab to enter the function
3. Next we need to enter our three parameters, select the cell that contains your first date, add a comma (,), then select your second date, add another comma (,), and then place a Y inside quotation marks (“Y”). Add your closing parenthesis and press Enter.
4. Your age should now calculate for the data you have entered
5. If you have additional dates in your spreadsheet you can copy and paste the formula down to repeat the formula on those dates.
Calculate Age – Years, Months, and Days
Calculating just the years is a pretty simple formula, but when you want to calculate years, months, and days the formula gets a little more complex.
First let’s look at months and days individually, and then we will look at combining them all.
To calculate months we want to use this formula:
=DATEDIF(start_date, end_date, “YM”)
Using YM as our unit value will give us the whole months between our start date and end date after subtracting years.
Here is an example of how this is used with our previous data:
The next thing we need to look at is calculating the days. For that we will be using this formula:
=DATEDIF(start_date, end_date, “MD”)
Using MD as our unit will give us the number of days after subtracting whole months. This is what we want.
Here is an example of this is used with our previous data:
Now instead of doing three different formulas, we want to combine these all together into one formula to calculate the years, months, and days together in one cell.
Here is the syntax of the formula we will be using:
=DATEDIF(start_date, end_date,”Y”)&” Years “&DATEDIF(start_date, end_date,”YM”)&” Months “&DATEDIF(start_date, end_date,”MD”)&” Days”
Basically, all you are doing with this is combining the three previous formulas we have covered into one formula by using the & symbol. We are also adding the text in to label our numbers by writing the text values of Years, Months, and Days, in quotation marks with the & symbol.
Here is how this formula is used with our example data:
As long as you understand how to use the DATEDIF function, calculating age or any other date difference is relatively easy in Google Sheets.
But when you want to calculate multiple units of time you will have to nest together multiple functions into one.
Now that you have learned this, there are probably a lot of other useful ways you can use this in your spreadsheet with different dates.