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

Using several techniques in Excel, we may compute a person’s actual age. We can use years, months, days, hours, and so on to compute the age. We must supply the beginning and ending dates in order to calculate the age. By learning how to calculate age between two dates, aside from age, we can calculate the duration of a project, the time difference between two specified dates, the number of years an organization has been in existence, and so on. In this article, we’ll discuss how to calculate age in excel between two dates.


Download Practice Workbook


6 Methods to Calculate Age Between Two Dates in Excel

To calculate the age between two dates, we have made a dataset based on the Date of Birth of some employees of a company and the Current Date. We have taken the Current Date as a typical date. The dataset is like this.

how to calculate age between two dates in Excel

We’ll now discuss different methods to calculate age between two dates.


1. Applying DATEDIF Function to Calculate Age Between Two Dates in Excel

We can use the DATEDIF function when we need to calculate the difference between two dates. It can be either years or months or days. By using this function we will find only the perfect date interval but not the actual or fractional date interval. We can write the formula to find Age in Years in cell E5 like this.

=DATEDIF(C5,D5,”Y”)

Here, C5 refers to the Date of Birth of Jane and D5 refers to the Current Time which we have taken arbitrarily. Y indicates that the age will show as years only.

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

how to calculate age between two dates in Excel using DATEDIF Function

Secondly, by pressing ENTER we find the value of age as 29 like this.

how to calculate age between two dates in Excel using DATEDIF Function

In this step, we can use the Fill Handle to find Age in Years from cell E6 to E14. For this, we just simply need to drag down the cursor of the E5 cell by holding the right end corner.

how to calculate age between two dates in Excel using DATEDIF Function

As a result, we’ll find the result like this.

how to calculate age between two dates in Excel using DATEDIF Function

Read More: How to Calculate Age from Birthday in Excel (8 Easy Methods)


2. Using YEARFRAC Function to Calculate Age Between Two Dates

In practical cases, we can use the YEARFRAC function where we need to find the actual or fractional age. We can write the formula in the E5 cell like this.

=YEARFRAC(C5,D5,1)

Here, 1 refers to the Basis of the argument.

how to calculate age between two dates in Excel using YEARFRAC Function

After pressing ENTER, we find the age as 29.99452405.

how to calculate age between two dates in Excel using YEARFRAC Function

Subsequently, by using Fill Handle we find the ages from cells C6 to D6.

how to calculate age between two dates in Excel using YEARFRAC Function

Formula Explanation

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

  • We can calculate [(30 days per month)/(360 days per year)] according to US or European rules for Basis equal to 0 or 4.
  • Again, 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.
  • We must require the variables Start Date and End Date but Basis is optional. Excel thinks that the basis is 0 if we omit the Basis

Read More: Excel formula to Calculate Age on a Specific Date


3. Utilizing DATEDIF and Arithmetic Operations to Calculate Age in Completed and Fractional Months

We can apply the DATEDIF function to calculate the perfect age in months. Also, we can find it by using a simple Arithmetic Formula. In cell E5 of the below picture, we can apply the DATEDIF function to calculate the  Age in Actual months like this.

=DATEDIF(C5,D5,”M”)

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

how to calculate age between two dates in Excel using DATEDIF and Arithmetic Function

Secondly, by pressing ENTERwe find 359 months in the E5 cell.

how to calculate age between two dates in Excel using DATEDIF and Arithmetic Function

We can simply 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.

how to calculate age between two dates in Excel using DATEDIF and Arithmetic Function

As a result, we can find the actual or fractional age like this.

how to calculate age between two dates in Excel using DATEDIF and Arithmetic Function

And finally, by using Fill Handle we find all the like this.

DATEDIF and ARITHMETIC Formula


4. Using CONCATENATE and DATEDIF Functions to Calculate Age in Years, Months, and Days.

We can use the combination of CONCATENATE and DATEDIF functions when we want to calculate ages in exact years, months, and days. In the E5 cell, we can place the formula like this.

=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 with the text strings in the final output.

how to calculate age between two dates in Excel using CONCATENATE and DATEDIF Function

Again, by pressing ENTER, we find the Actual Age in cell E5.

how to calculate age between two dates in Excel using CONCATENATE and DATEDIF Function

Repeatedly, we need to use the Fill Handle to find the age in the other cells.

As a result, we get the output like this.

how to calculate age between two dates in Excel using CONCATENATE and DATEDIF Function

Read More: How to Calculate Age in Excel in Years and Months (5 Easy Ways)


5. Utilizing DATEDIF and TODAY Functions to Calculate Age with Current Time

If we want to use the original current time and subsequently change the age with the day-by-day time change, we can do this by two handy systems.


5.1 Using Different Time Column 

We can change the time in the dataset with the original Current Time. For doing this, we should apply the TODAY function below in the D5 cell. Here, in the E5 cell, we have already applied the DATEDIF function.

=TODAY()

how to calculate age betwen two dates using TODAY Function

Secondly, we need to press ENTER.

how to calculate age betwen two dates using TODAY Function

As a result, we have found the original Current Time. And the Age in Years in the E5 cell has also changed accordingly, from 29 to 32.

After using Fill Handle to both D and E columns, we finally found ages in every cell according to Current Time.

how to calculate age between two dates in Excel using TODAY Function


5.2 Without Using Different Time Column 

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

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

Here, we have used TODAY() instead of using another time cell reference. This means the last time denoted cell reference is Today.

Using DATEDIF and TODAY Function

Similarly, by pressing Enter, we get the age as 32.

Finally, we need to use the Fill Handle to get ages in every cell from D5 to D14.

As a result, we find the output like this.

Using DATEDIF and TODAY Function


6. Applying VBA to Calculate Age Between Two Dates in Excel

Applying VBA is another method by which we can calculate age. For this, we need to follow the steps below.

First, go to the Developer tab > the Visual Basic.

Using VBA

Second, click on Insert > then Module.

Using VBA

As a result, a blank Module will appear like this.

Using VBA

Third, we need to copy and paste the 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

Using VBA

 After that, click on Run > then Run Sub/UserForm.

Using VBA

And finally, we’ll get the output below.

Using VBA

Read More: Excel VBA: Calculate Age from Date of Birth


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 the combination of CONCATENATE and DATEDIF functions when we need to calculate years, months, and days i.e. all of them.
  • For dynamic use of finding ages, we need to apply the TODAY

Conclusion

Excel has different effective formulas to find ages or time intervals. In this article, we have tried to discuss all the functions which are used to find ages.


Related Articles

Towhid
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo