3 Methods of calculating age from the date of birth in Google Sheets:
- Use the DATEDIF function
- Use the YEARFRAC function
- Subtract the Birth Date from Today’s Date
Method #1: Use the DATEDIF Function
Step #1: Open your Google Sheet
Step #2: Pick the results cell
Select a blank cell where you want to output the age.
In our example, it is A2. This is where you’ll enter the formula and get the age.
Step #3: Enter the function
Double click on the selected cell, and type this formula:
=DATEDIF(cell_name, TODAY(), "Y")
- cell_name is the cell that contains the date of birth. Replace it with your date of birth cell. In our example, it is A2.
- Y is only an indicator that we want the results in years.
You can change the “Y” parameter to get months or days as well.
- To calculate age from the date of birth in years, enter the function =DATEDIF(cell_name, TODAY(), “Y”)
- To calculate age from the date of birth in months, enter the function =DATEDIF(cell_name, TODAY(), “M”)
- To calculate age from the date of birth in days, enter the function =DATEDIF(cell_name,TODAY(),”D”)
- To calculate the exact age in years, months, and days, enter the function =(DATEDIF(cell_name,TODAY(),”Y”) & “Years” & DATEDIF(cell_name,TODAY(),”M”) & “Months” & DATEDIF(cell_name,TODAY(),”D”) & “Days”)
Step #4: Press Enter
Once you enter the respective function, press Enter on your keyboard.
You can now see the age displayed in cell B2.
Method #2: Use the YEARFRAC Function
This is a simple method to calculate age from the date of birth. The results will be in fractions, such as 26.635, but we will show you a trick to simplify that.
Step #1: Open your Google Sheet
If you have an existing sheet, open that.
Step #2: Pick the results cell
Select a blank cell where you want to output the age.
In our example, it is A3.
Step #3: Enter the function
Double click on the selected cell, and type this formula:
=YEARFRAC(cell_name, TODAY(), 1)
- cell_name is the cell that contains the date of birth. Replace it with your date of birth cell. In our example, it is A3.
Step #4: Press Enter
Once you enter the function, press Enter on your keyboard.
You will now see the age displayed in decimals.
Tip: To remove the decimals, add int before the function, and place the function in brackets.
Your function would then become =int(YEARFRAC(A3, TODAY(), 1)). This would display the age as a whole number.
Method #3: Subtract the BirthDate and Today’s Date
This method uses mathematical logic. This approach is not optimal since it does not always account for all circumstances.
Step #1: Open your Google Sheet
Open your Google Drive and right-click on a white space. A menu will open.
Click on Google Sheets. This would create a new sheet.
If you have an existing sheet, open that.
Step #2: Pick the results cell
Select a blank cell where you want to see the age. You can confirm your choice in the Name box to the top left. This is where you’ll enter the formula and get the age. In our example, it is A4.
Step #3: Enter the formula
The logic is simple, but not foolproof.
Subtract the birth date from today’s date to get the number of days and divide it by 365 to get the age.
Double click on the selected cell, and type this formula:
=INT((TODAY()-cell_name)/365)
- cell_name is the cell that contains the date of birth. Replace it with your date of birth cell. In our example, it is A4.
Step #4: Press Enter
After typing in the formula, press Enter on your keyboard to see the formula in action.
Conclusion
We hope you found this article useful.