How to Calculate Age from Date of Birth in Google Sheets

Calculating a person’s age based on their date of birth in Google Sheets is useful to know. Although there are no specific functions to do this, we share three methods using other functions.

3 Methods of calculating age from the date of birth in Google Sheets:

  • Use the DATEDIF function
  • Use the YEARFRAC function
  • Use the logical formula

Method #1: Use the DATEDIF Function

This method allows you to choose to display the years, months, days, or everything together.

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 A2.

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

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 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: Use the Logical Formula

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 the current 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

You can use any of the three methods above to calculate someone’s age from their date of birth.

Leave a Comment