How to Calculate Age Between Two Dates in Excel (6 Methods)

To calculate the age between two dates, we have made a dataset based on the Date of Birth of employees of a company and the Current Date.

Method 1 – Using the DATEDIF Function to Calculate the Age Between Two Dates

We can use the formula to find Age in Years in cell E5 like this:

=DATEDIF(C5,D5,”Y”)

C5 refers to the Date of Birth of Jane and D5 refers to the Current Time. Y indicates that the age will show as years only.

The DATEDIF function’s syntax is DATEDIF(start date,end date,unit).

Press ENTER.

Use the Fill Handle to find Age in Years from cell E6 to E14. To do so, drag down the cursor starting at cell E5 towards the bottom-right corner.

The result will look like this:

Method 2 – Using the YEARFRAC Function to Calculate the Age Between Two Dates

We can use the YEARFRAC function when we need to find the actual or fractional age. To do this enter the following formula in cell E5:

=YEARFRAC(C5,D5,1)

Here, 1 refers to the Basis of the argument.

After pressing ENTER, we can see that the age is 29.99452405 in fractional years.

By using the Fill Handle we can find the age differences from cells C6 to D6.

Formula Explanation

The Basis is mainly a parameter by which we count the number of fractional years. It can have one of the five values described below:

We can calculate [(30 days per month)/(360 days per year)] according to US or European rules for Basis 0 or 4.
We can take [(actual days)/(actual days in the year)], [(actual days)/360], or [(actual days)/365] with Basis equal to 1, 2, or 3.
The variables Start Date and End Date are required but Basis is optional. Excelconsiders the basis is 0 if we omit the Basis

Method 3 – Using the DATEDIF Function and Arithmetic Operations to Calculate Age in Completed and Fractional Months

In cell E5 of the below picture, we can apply the DATEDIF function to calculate the Age in Actual months by using:

=DATEDIF(C5,D5,”M”)

Here, M refers that the formula will return the age in Months.

Press ENTER to find 359 months in cell E5.

We can do this by using the arithmetic formula below:

=+(D5-C5)/30

Here, +=(B2-A2)/30 returns the same output as the formula =(B2-A2)/30. Hence, we can ignore the “plus” sign of this formula.

This allows us to find the actual or fractional age:

We can then use the Fill Handle to complete the column:

Method 4 – Combining CONCATENATE and DATEDIF Functions to Calculate Age in Years, Months, and Days

When we want to calculate ages in exact years, months, and days, we can use a combination of the CONCATENATE and DATEDIF functions. Copy the formula below to cell E5.

=CONCATENATE(DATEDIF(C5,D5,”Y”),” Years “, DATEDIF(C5,D5,”YM”),” Months and “ DATEDIF(C5,D5,”MD”),” Days “)

Using the CONCATENATE function, we have concatenated (combined) three DATEDIF formulae in the formula entered above. After each DATEDIF formula, we have entered the text strings Years, Months, and Days. This means that the results of each DATEDIF formula will be combined as text strings in the final output.

Press ENTER to find the Actual Age in cell E5.

Use the Fill Handle to find the age in the other cells:

Method 5 – Using DATEDIF and TODAY Functions to Calculate Age with the Current Time

5.1. Using the Additional Time Column

To change the time in the dataset to the original Current Time, we should apply the TODAY function below in the D5 cell. Here, in the E5 cell, we have already applied the DATEDIF function.

=TODAY()

Press ENTER.

We have found the original Current Time. The Age in Years in the E5 cell has also changed accordingly, from 29 to 32.

Using the Fill Handle in both the D and E columns, we found the ages in every cell according to Current Time.

5.2. Without Using Separate Time Columns

We can use a combination of the DATEDIF and TIME functions to calculate age without the help of a time column. To do this, we need to apply the formula below in cell D5:

=DATEDIF(C5,TODAY(),”Y”)

Here, we have used TODAY()

By pressing Enter, we get the age as 32.

Use the Fill Handle to get ages in every cell from D5 to D14:

Method 6 – Applying VBA to Calculate the Age Between Two Dates in Excel

Go to the Developer tab > Visual Basic.

Second, click on Insert > then Module.

A blank Module will appear:

Copy and paste the following VBA code below inside the Module:

Sub Calculate_RetirementAge()

Range("E5").Formula = "=DATEDIF(C5,D5,""y"")"

Range("E6").Formula = "=DATEDIF(C6,D6,""y"")"

Range("E7").Formula = "=DATEDIF(C7,D7,""y"")"

Range("E8").Formula = "=DATEDIF(C8,D8,""y"")"

Range("E9").Formula = "=DATEDIF(C9,D9,""y"")"

Range("E10").Formula = "=DATEDIF(C10,D10,""y"")"

Range("E11").Formula = "=DATEDIF(C11,D11,""y"")"

Range("E12").Formula = "=DATEDIF(C12,D12,""y"")"

Range("E13").Formula = "=DATEDIF(C13,D13,""y"")"

Range("E14").Formula = "=DATEDIF(C14,D14,""y"")"

End Sub

Click on Run > then Run Sub/UserForm.

We’ll get the output below:

Things to Remember

We can’t apply only the DATEDIF function where we need to find the ages in fractional form
To find fractional age, we need to apply the YEARFRAC function, or we can also use Arithmetic Formula
We have to use a combination of the CONCATENATE and DATEDIF functions when calculating years, months, and days i.e. all of them
For dynamic use of finding ages, we need to apply the TODAY funcion

Download Practice Workbook

Calculating Age Between Two Dates.xlsm

<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!