The AGE CALCULATION function with example in Excel

Calculating age in Excel can be done using several different functions, but the most common and accurate method involves using the DATEDIF function. The DATEDIF function calculates the difference between two dates in various units, including years, months, and days.

Syntax

scss
DATEDIF(start_date, end_date, unit)

• start_date: The person's birth date.
• end_date: The current date or another date to which you want to calculate the age.
• unit: The type of difference you want to calculate. Use "Y" for years, "M" for months, "D" for days.

Example

Suppose you have a birthdate in cell A1, and you want to calculate the age as of today.

1. Enter the birthdate:

   • In cell A1, enter 1980-05-20 (or any other birthdate).

2. Use the DATEDIF Function:

   • In cell B1, enter the following formula:

     less
     =DATEDIF(A1, TODAY(), "Y")
     

   • A1: Reference to the cell containing the birthdate.

   • TODAY(): Function that returns the current date.

   • "Y": Unit to calculate the difference in years.

This formula calculates the age by finding the difference in years between the birthdate and today's date.

Explanation

• TODAY() returns the current date.
• DATEDIF(A1, TODAY(), "Y") calculates the number of complete years between the date in cell A1 and today's date.

Additional Examples

1. Age in Years and Months: To calculate the age in years and months, you can use a combination of DATEDIF functions.

   • In cell B1, calculate years:

     less
     =DATEDIF(A1, TODAY(), "Y")
     

   • In cell C1, calculate months:

     less
     =DATEDIF(A1, TODAY(), "YM")
     

   This will give you the age in years in cell B1 and the remaining months in cell C1.

2. Age in Years, Months, and Days: To be even more precise and calculate the age in years, months, and days:

   • In cell B1, calculate years:

     less
     =DATEDIF(A1, TODAY(), "Y")
     

   • In cell C1, calculate months:

     less
     =DATEDIF(A1, TODAY(), "YM")
     

   • In cell D1, calculate days:

     less
     =DATEDIF(A1, TODAY(), "MD")
     

   Now you have the age broken down into years, months, and days.

Combining in One Cell

You can combine the years, months, and days into a single cell for a complete age statement.

• In cell B1, enter:

  arduino
  =DATEDIF(A1, TODAY(), "Y") & " Years, " & DATEDIF(A1, TODAY(), "YM") & " Months, and " & DATEDIF(A1, TODAY(), "MD") & " Days"
  

This formula will output something like "44 Years, 0 Months, and 6 Days" if the birthdate in A1 was 1980-05-20 and today's date is 2024-05-26.