How to calculate age (convert birthdate to age) quickly in Excel?

Imagine you're working with a list of birth dates in Excel and need to show their precise current ages. How can you efficiently achieve this? This article provides tips for easily calculating age from a date of birth in Excel.

Method A:
Calculate age from date of birth with formulas

Method B:
Easily calculate age from date of birth without remembering formulas

Calculate age from date of birth with formulas

In this section, we've outlined various formulas to calculate age from the date of birth under different scenarios:

• Calculate age as the number of full years
  • By INT function (Basic)
  • By YEARFRAC function (More accurate)
  • By DATEDIF function (Can display age in various time units)
• Calculate age in Year + Month + Day format
  • By DATEDIF function

Convert birthdate to age with INT function

We can calculate age by subtracting the birth date from the current date. Then, by using the INT function, we can display the age in complete years as follows:

Select a blank cell where you want the age to be displayed, input the below formula and press the Enter key. Then, select the result cell and drag its fill handle (the small green square in the lower-right corner of the cell) down to get all results.

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

Convert birthdate to age with the YEARFRAC function

The YEARFRAC function is more accurate for calculating age from a date of birth because it considers the exact number of days in each year, including leap years. By setting the third argument to 1, YEARFRAC uses the actual day count in its calculation, ensuring precision, especially in cases involving leap years.

In an empty cell where you want the age calculated, enter the formula below and press Enter. To replicate this calculation for other data, drag the fill handle of the result cell downward.

=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0)

Convert birthdate to age with the DATEDIF function

The DATEDIF function in Excel can be used to calculate age from a date of birth. By using "y" in the unit argument of the function, it calculates the number of complete years between two dates.

Select an empty cell to display the age, input the formula provided below, and press the Enter key. Then, click on the cell with the result and drag its fill handle downwards to apply the formula to other cells and obtain all results.

=DATEDIF(A2,NOW(),"y")

Display age in Year + Month + Day format with the DATEDIF function

To calculate age in the format of Year + Month + Day, combine three DATEDIF formulas in Excel, each with a different unit argument.

Select an empty cell to display the age, input the formula provided below, and press the Enter key. Then, click on the cell with the result and drag its fill handle downwards to apply the formula to other cells and obtain all results.

=DATEDIF(A2,TODAY(),"Y") & " Years, " & DATEDIF(A2,TODAY(),"YM") & " Months, " & DATEDIF(A2,TODAY(),"MD") & " Days"

Easily calculate age from date of birth without remembering formulas

Kutools for Excel's "Date & Time Helper" feature simplifies age calculations, allowing you to easily convert birthdates to age with just a few clicks, without the need to remember formulas. It offers the flexibility to calculate age up to the current day or a specific date, and provides results in various formats like years, months, weeks, days, or a combination of years + months + days. This user-friendly tool streamlines the process, making it quick and accessible for all Excel users.

1. Select a blank cell to display the age. Then click "Kutools" > "Formula Helper" > "Date & Time Helper".
   
2. In the "Date & Time Helper" dialog box, please configure as follows.
   1. Go to the "Age" tab;
   2. In the "Date of Birth" box, select the cell containing the birth date you wish to calculate the age from;
   3. Choose the "Today" option in the To section;
   4. Choose the desired age output format, like years, months, days, or a combination, from the "Output result type" drop-down list;
   5. Click the OK button.
      

Result

Then the age is populated in the selected cell. Select the result cell and then drag its fill handle all the way down to get all ages.

Related Articles:

• Convert date to weekday name or month name
• Change American date format