There are a couple of ways to calculate the age in years when you have the date of birth and the time for the age.
Example: In the following dataset, the birth data is in column A, and the age date is in column B.
Formula 1: The INT Function
The first method is to count the days between the two dates and divided by 365.25 for the years, then use the INT Function to extract the integer as the age in the year.
=INT((B2-A2)/365.25)
- "B2-A2" is to count the days between the two dates.
- Using 365.25 instead of 365 is because of the leap year. There is one leap year every four years and it has 366 days. As a result, the number of days in one year on average is (365*3+366)/4 = 365.25
If you have the date of birth and want to calculate the ages as of today, you can use the Today Function to get today's date.
=INT((TODAY()-A2)/365.25)
Formula 2: The DATEDIF Function
The DATEDIF function counts the number of days, months, or years between two dates.
=DATEDIF(A2, B2, "Y")
Where A2 is the start date and B2 is the end date.
"Y" is the calculation type, please see the complete list of the types and explanations.
- "Y": The number of years;
- "M": The number of months;
- "D": The number of days;
- "MD": The difference when ignoring the years and months;
- "YM": The difference when ignoring the years and days;
- "YD": The difference when ignoring the year.
Formula 3: The YEARFRAC Function
The YEARFRAC Function returns the fraction of the year between the start date and the end date.
=ROUNDDOWN(YEARFRAC(A2,B2,0),0)
- YEARFRAC(A2, B2, 0) returns the years with decimals between the two dates;
- ROUNDDOWN is to have the age.
