Shajratul Alam Towhid
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.
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.
We’ll now discuss different methods to calculate age between two dates.
1. Using DATEDIF Function to Calculate the 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, 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).
Secondly, by pressing ENTER we find the value of age as 29 like this.
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.
As a result, we’ll find the result like this.
Read More: How to Calculate Age on a Specific Date with Formula in Excel
2. Using the YEARFRAC Function to Calculate the 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.
After pressing ENTER, we find the age as 29.99452405.
Subsequently, by using Fill Handle we find the ages from cells C6 to D6.
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: How to Calculate Age in Excel in dd/mm/yyyy
3. Using DATEDIF Function 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.
Secondly, by pressing ENTERwe find 359 months in the E5 cell.
We can simply do this by using the Arithmetic Formula below.
=+(D5-C5)/30Here, +=(B2-A2)/30 returns the same output as the formula =(B2-A2)/30. Hence, we can ignore the “plus” sign of this formula.
As a result, we can find the actual or fractional age like this.
And finally, by using Fill Handle we find all the like this.
4. Combining 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.
Again, by pressing ENTER, we find the Actual Age in cell E5.
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.
Read More: How to Calculate Age in Excel in Years and Months
5. Using 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 with two handy systems.
5.1. Using Additional Time Column
We can change the time in the dataset with the original Current Time. To do 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()
Secondly, we need to press ENTER.
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.
5.2. Without Using Separate Time Columns
We can simply use the combination of 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 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.
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.
6. Applying VBA to Calculate the 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.
Second, click on Insert > then Module.
As a result, a blank Module will appear like this.
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
After that, click on Run > then Run Sub/UserForm.
And finally, we’ll get the output below.
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
Download Practice Workbook
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
- How to Convert Date of Birth to Age in Excel
- How to Calculate Current Age in Excel
- How to Calculate Age in Excel for Entire Column
- How to Calculate Age in Excel from ID Number
- How to Calculate Retirement Age in Excel
- Metabolic Age Calculator in Excel
- How to Calculate Average Age in Excel
- How to Group Age Range in Excel with VLOOKUP
- How to Make Age Pyramid in Excel
- How to Create Age and Gender Chart in Excel
- How to Create Age Distribution Graph in Excel
