How to calculate age in Microsoft Excel for the entire column

The ability to calculate age in Excel is essential in various fields, including finance, healthcare, and demographics. Accurate age calculations are crucial for precise analysis, decision-making, and forecasting. By utilizing Excel’s automated age calculation tools, you can save time and ensure consistency in your data analysis.

In this tutorial, we will learn how to calculate age in Microsoft Excel for the entire column. In Excel, calculating the age is a very simple and common process. This can be done using multiple functions i.e. YEARFRAC, DATEDIF, and INT functions.

Method 1: Calculating Age using the INT Function

Step 1 – Select a Blank Cell and Place an Equals Sign

  • Select a blank cell where you want to calculate age.
  • Place an Equals sign in the blank cell.

Step 2 – Enter the INT Function

  • Enter the INT function in the cell.
  • The syntax for the formulae using the INT function will be:

             INT((C2 – B2)/365)

  • Where C2 is the cell containing the end date and B2 is the start date.

Step 3 – Press the Enter Key

  • Press the Enter key to calculate the age.

Step 4 – Use Autofill to Calculate the Age for the Entire Column

  • Use Autofill to calculate the age for the entire column.

Method 2: Calculating Age using the DATEDIF Function

Step 1 – Select a Blank Cell and Place an Equals Sign

  • Select a blank cell where you want to calculate age.
  • Place an Equals sign in the blank cell.

Step 2 – Enter the DATEDIF Function

  • Enter the DATEDIF function in the cell.
  • The syntax for the formulae using the DATEDIF function will be:

             DATEDIF(B2,C2, “Y”)

  • Where C2 is the cell containing the end date, we can use the TODAY() function instead to calculate the age till today.
  • B2 is the start date.
  • The third argument specifies the unit for the calculation i.e. Year.

Step 3 – Press the Enter Key

  • Press the Enter key to calculate the age.

Step 4 – Use Autofill to Calculate the Age for the Entire Column

  • Use Autofill to calculate the age for the entire column.

Method 3: Calculating Age using the YEARFRAC Function

Step 1 – Select a Blank Cell and Place an Equals Sign

  • Select a blank cell where you want to calculate age.
  • Place an Equals sign in the blank cell.

Step 2 – Enter the YEARFRAC Function

  • Enter the YEARFRAC function in the cell.
  • The syntax for the formulae using the YEARFRAC function will be:

             YEARFRAC(B2,C2, 1)

  • Where C2 is the cell containing the end date, we can use the TODAY() function instead to calculate the age till today.
  • B2 is the start date.
  • The third argument specifies the unit for the calculation i.e. Year.

Step 3 – Use the ROUNDDOWN Function

  • Use the ROUNDDOWN function to round down the decimal value returned by the YEARFRAC function.
  • The syntax will be: 

ROUNDDOWN(  YEARFRAC(B2,C2,1) ,0)

  • Where the first argument is the YEARFRAC function that returns age in decimals.
  • The second argument i.e. 0 is the number of decimals to which the function will round.

Step 4 – Press the Enter Key

  • Press the Enter key to calculate the age.

Step 5 – Use Autofill to Calculate the Age for the Entire Column

  • Use Autofill to calculate the age for the entire column.

Method 4: Using the DATEIF Function to Calculate Age in Year, Month and Day

Step 1 – Select a Blank Cell and Place an Equals Sign

  • Select a blank cell where you want to calculate age.
  • Place an Equals sign in the blank cell.

Step 2 – Enter the DATEDIF Function

  • Enter the DATEDIF function in the cell.
  • The syntax for the formulae using the DATEDIF function will be:

           DATEDIF(B2,C2, “Y”)&”Years “&DATEDIF(B2,C2,”YM”)&”Months “    &DATEDIF(B2,C2,”MD”)&”Days”

  • Where the first DATEDIF function calculates years.
  • The second DATEDIF function calculates months.
  • The third DATEDIF function calculates days.

Step 3 – Press the Enter Key

  • Press the Enter key to calculate the age.

Step 4 – Use Autofill to Calculate the Age for the Entire Column

  • Use Autofill to calculate the age for the entire column.