In this tutorial we will look at three methods to calculate age in cell C2, being cell A2 the date of birth and B2 today's date, calculated through the Excel function TODAY.
1st method: calculate the current age counting the days
The easiest way to calculate the age in Excel and the one that usually come to mind first is to subtract the date of birth to the current date. This will give us a number of days and after we divide by 365 days. The formula would be something like this:
age = (current date - date of birth) / 365
However, this method has the problem that completely ignores leap years, so you may have a certain degree of error and not be very accurate.
To remedy this we could divide by 365.25, because we have a year with 366 days (leap year) every 4 years and 365.25 is the average of four years.
We could also apply the INT function for removing decimals.
So that the above formula would be more like this:
age = Integer (current date - date of birth) / 365.25
In Excel formula to the cell C2 is:
= INT((B2-A2)/365.25)
2nd method: calculate age with YEARFRAC function
The second way to calculate the age in Excel is using YEARFRAC function. This function will return the fraction of the year representing the number of whole days between start_date and end_date.
age = YEARFRAC (Date_of_Birth; current_date)
In this case the result is much more accurate, since the function itself takes into account the characteristics of the years between two dates. As before, use the INT function for removing decimals.
age = INT(YEARFRAC(Date_of_Birth; current_date))
In our example of Excel looks like this:
=INT(YEARFRAC(A10,B10))
3rd method: calculate age with DATEDIF function
The last of the methods to calculate age using Excel is using DATEDIF function. This would be the most accurate method to estimate age with Excel. To do that we must fill the parameters with Start_date (date of birth), End_date (current date) and the type of data you want to obtain, in this case, years ("y").
This function has the following syntax: = DATEDIF (date1, date2, type)
Type refers to what type of data is to be calculates. By varying the type we can calculate the following:
| Value | Description |
|---|---|
| “Y” | Returns the period difference as complete years. |
| “M” | Returns the period difference as complete months. |
| “D” | Returns the number of days in the period. |
| “MD” | Returns the difference between the days in ‘Start_Date’ and ‘End_Date’. Here the months and years of the dates are ignored. |
| “YM” | Returns the difference between the months in ‘Start_Date’ and ‘End_Date’. Here the days and years of the dates are ignored |
| “YD” | Returns the difference between the days of ‘Start_Date’ and ‘End_Date’. Here the years of the dates are ignored. |
Age using this function could be calculated as follows:
age = DATEDIF(Date_of_Birth; current_date; "y")
In our example the resulting formula would be:
=DATEDIF(A2, B2, "y")
Well we have now see 3 methods to calculate Age in Excel.
Download
To download the example file click on the following link:
Calculate AGE
No comments:
Post a Comment