In Google Sheets, the best way to calculate the current age using the date of birth is by using the DATEDIF function. This function allows you to differentiate between two dates in different units. In the below example, we have the date of birth in the cell A1.\
And to get the age by using this date, you can use the below formula, which uses DATEDIF three times.
=DATEDIF(A1,TODAY(),"Y")&" Year(s), "&DATEDIF(A1,TODAY(),"YM")&" month(s), "&DATEDIF(A1,TODAY(),"MD")&" day(s)."When you enter this formula, you can enter the date of birth in cell A1, or you can also enter it directly into the formula. And if you want to learn how this formula works, continue reading.
Get the Age Year
- Enter the DATEDIF in a cell.
- In the start_date argument, refer to the date of birth which you have in cell A1.
- For the end date argument, enter the TODAY function.
- And in the end, unit argument, enter “Y”.
- In the end, close the function and hit enter to get the result.
=DATEDIF(A1,TODAY(),"Y")
Get Age Months After Year
Once you get the total years, you get the total number of months. And for this, you can use the below formula.
=DATEDIF(A1,TODAY(),"YM")In this formula, you need to use the unit “YM”. And this formula returns 5 in the result, meaning five months after the 32 years. 32 years and 5 months.
Get the Days After Months and Years
In the end, you need to get the days that are left after years and months. And for this, you need to use the DATEDIF again with the unit “MD”.
=DATEDIF(A1,TODAY(),"MD")Using ” MD ” gives you the days left after years and months.
And in the end, you can combine all three formulas to get the exact age in a single cell.
=DATEDIF(A1,TODAY(),"Y")&" Year(s), "&DATEDIF(A1,TODAY(),"YM")&" month(s), "&DATEDIF(A1,TODAY(),"MD")&" day(s)."