You may have wondered how long to be a certain age, for example, how many days or months to the age of 80. This is very similar to calculate the time between two dates, except you need to figure out the date of today and the date at a certain age.
There are a couple of ways you can calculate depending on what units you need.
For example: To the age of 80 years old
1. If you want to present in days, please copy the formula below and change the date of birth:
=DATEDIF(TODAY(),DATE(YEAR(A2)+80,MONTH(A2),DAY(A2)),"D")
Where A2 is the cell with birthday, 80 is the age you want to check.
2. If you want to present in months, please copy the formula below and change the date of birth:
=DATEDIF(TODAY(),DATE(YEAR(A2)+80,MONTH(A2),DAY(A2)),"M")
Where A2 is the cell with birthday, 80 is the age you want to check.
3. If you want to present in years and it is about yourself, you do not need a formula because you already know it. However, if you have a column of dates, please use the formula.
=DATEDIF(TODAY(),DATE(YEAR(A2)+80,MONTH(A2),DAY(A2)),"Y")
4. When presenting in years or months, you actually miss the days that not make up a full month. The more accurate way is to present in years, months and days. If that is the format you want, please use the following formula.
=DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2),DAY(A2)),"Y")&" Years "& DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2), DAY(A2)),"YM")&" Months "& DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2), DAY(A2)),"MD") & " Days"
This is to calculate the years, months and days from today to age 80 using the DATEDIF function and then combine together using the CONCAT Function.
The only problem for this formula is that years, months and days are all plurals, and it seems not correct when it is 1 year or 1 month. Please use the following formula if that is the case.
=DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2), DAY(A2)),"Y")&IF(DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2), DAY(A2)),"Y")=1, " Year "," Years ")& DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2), DAY(A2)),"YM")&IF(DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2), DAY(A2)),"YM")=1, " Month ", " Months ")&DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2), DAY(A2)),"MD")&IF(DATEDIF(TODAY(), DATE(YEAR(A2)+80, MONTH(A2), DAY(A2)),"MD")=1," Day"," Days")
The formula is very long but the basic idea is to have the date for the age first, then to get the time between that date and today.
Where "DATE(YEAR(A2)+80, MONTH(A2), DAY(A2))" is the date at the age of 80. If the difference is 1, then to use signaler formats, otherwise to use the plural formats.
