askvity

How Do I Calculate Years Old in Excel?

Published in Excel Formulas 3 mins read

You can calculate someone's age in years using Excel with a simple formula that subtracts the birthdate from the current date and then converts the result into years.

Calculating Age in Years

Here's how to do it:

  1. Assumptions:

    • Cell A2 contains the birthdate (e.g., 1990-05-15).
    • Cell B2 contains the current date or the date to which you want to calculate the age. If you want to automatically use the current date, you can use the TODAY() function directly in the formula.

  2. Formula: Use the following formula in a cell where you want to display the age:

    =INT((B2-A2)/365)

    Or, to use the current date dynamically:

    =INT((TODAY()-A2)/365)

  3. Explanation:

    • (B2-A2) or (TODAY()-A2): Calculates the difference between the two dates in days.
    • /365: Divides the number of days by 365 to approximate the number of years. This doesn't account for leap years. For a more accurate calculation that considers leap years, you can use the YEARFRAC function (see below).
    • INT(): Returns the integer portion of the result, giving you the whole number of years. It truncates the decimal portion, providing the age as a whole number.

Calculating Age with Leap Year Consideration (More Accurate)

For greater accuracy, especially when dealing with many dates, use the YEARFRAC function:

=INT(YEARFRAC(A2,B2))

Or, with the current date:

=INT(YEARFRAC(A2,TODAY()))
  • YEARFRAC(A2, B2): Calculates the fraction of a year between the start date (A2) and end date (B2). It automatically accounts for leap years.
  • INT(): Again, this truncates the result to give you the whole number of years.

Example

Birthdate (A2) Current Date (B2) Formula Result (Age)
1990-05-15 2024-08-17 =INT((B2-A2)/365) 34
1990-05-15 2024-08-17 =INT(YEARFRAC(A2,B2)) 34
1985-01-01 TODAY() =INT((TODAY()-A2)/365) Varies based on the current date
1985-01-01 TODAY() =INT(YEARFRAC(A2,TODAY())) Varies based on the current date

Calculating Months (If Needed)

While the question asks for years, calculating the number of remaining months beyond the whole years can also be helpful:

=MOD(YEARFRAC(A2,B2)*12,12)

This formula calculates the fractional years, multiplies by 12 to convert to months, and then uses the MOD function to return the decimal portion (the remainder after division by 12), representing the months beyond the whole years. Using INT() with YEARFRAC() for whole years is the best combination.

In summary, use the INT((B2-A2)/365) formula for a simple calculation or INT(YEARFRAC(A2,B2)) for a more accurate result that considers leap years when calculating age in Excel. For a dynamic calculation always using the current date, substitute B2 with TODAY().

Related Articles