Calculate Age in Excel with precision using built-in date functions and formulas. By subtracting the birth date from the current date, you can easily determine someone’s age. This functionality is invaluable for HR departments, financial analysts, and researchers alike, allowing them to swiftly analyze demographic data or plan for retirement. With Excel’s flexibility, you can customize age calculations to suit various needs, whether it’s determining the age of employees, clients, or project milestones. By mastering this feature, users can streamline workflows, make informed decisions, and enhance data-driven insights. Whether you’re a novice or an Excel expert, mastering the art of calculating age in Excel empowers you to efficiently manage and analyze data, driving productivity and efficiency in your endeavors.
This Tutorial Covers:
- How can we calculate the age in Excel
- Calculating Age in Years
- Using the DATEDIF Function
- Using the YEARFRAC Function
- Using the ROUNDDOWN Function
- Using the TODAY Function
- Determining Age in Years, Months, and Days altogether
- Calculating Age on any Specific/Particular Date
1. How can we calculate the age in Excel?
Excel does not have a special function for calculating age, but numerous other ways exist to compute age in various situations. However, we need to know the person’s actual birthdate. (D.O.B.). Then, to determine the age or distinguish between dates, we provide the date of birth along with the Excel functions DATEDIF and TODAY. Now let’s talk about some typical age determination scenarios:
2. Calculating Age in Years:
When determining someone’s age, we frequently take into account a number of variables. For instance, we might need to figure out how old someone is in years, months, days, or all three at once. However, the most typical task for Excel is figuring out a person’s age in years.
If we know the person’s birthdate, we can use a couple of different Excel functions to determine their age in years. Let’s go over each helpful age formula in greater detail:
-
Using the DATEDIF Function:
The most popular function to determine a person’s age in Excel is the DATEDIF function. The most accurate, convenient, and user-friendly function accepts the user’s date of birth as an input and outputs their age.
The DATEDIF function also commonly converts a person’s age from their date of birth. The main benefit of using the DATEDIF function to calculate age is that it can do it in a variety of formats, including years-only, months-only, days-only, or a combined form of years, months, and dates, etc.
The DATEDIF function’s general syntax is specified as follows:
=DATEDIF(start_date, end_date, unit)
The function needs the following three arguments, as demonstrated here:
start_date: The initial/starting date of the time period for which we wish to determine the difference result is specified using this option. It can be provided as dates, double-quoted text strings, serial numbers, or the output of other operations like DATE.().
end_date: The ending date of the time frame for which we want to determine the difference result is specified using this option. Similar information to the start_date is accepted.
unit: This option enables us to select the kind of result that DATEDIF should return. Depending on the unit type we supply, there are six various output formats that we can obtain. Units Y, M, D, MD, YM, and YD are accessible. In this case, Y denotes Years, M, Months, and D, Days.
It is significant to remember that Y, M, and D, respectively, provide the numbers in whole years, months, and days. As opposed to this, YM only returns the date difference in months and ignores the corresponding days and years, MD only returns the date difference in days and ignores the corresponding months and years, and YD only returns the date difference in days and ignores the corresponding years.
The more well-known syntax for using the DATEDIF function to determine the age in years is:
=DATEDIF(Birth_Date,Specific_Date,”Y”)
We can substitute the current date for Specific_Date to determine the age from the birth date to the present. Additionally, we have the option of substituting the TODAY function for the current date. The appropriate formula to determine a person’s age in years as of today is as follows:
=DATEDIF(Birth_Date,TODAY(),”Y”)
Let’s say we need to determine the current age in years given the birth date in the dataset shown below. The birth date reference in the last formula can then be applied as follows:
=DATEDIF(B2,TODAY(),”Y”)
-
Using the YEARFRAC Function:
Using Excel’s YEARFRAC function to compute age is another helpful strategy. Calculating ages in years can be done using this simple Excel formula. It enables us to retrieve the age between a given birthdate and a given date.
The YEARFRAC function’s general syntax for determining a person’s age is specified as follows:
=YEARFRAC(Birth_Date,Specific_Date)
We can substitute the current date for Specific_Date if we need to figure out how old we are as of today. Alternately, we may do the following to combine the YEARFRAC and TODAY functions:
=YEARFRAC(Birth_Date,TODAY())
The algorithm above always yields values as decimal numbers. This makes it difficult to determine a person’s age. To return the corresponding age as an integer, we therefore combine or encapsulate the formula within the INT function. In Excel, the YEARFRAC function can be used to determine age using the full formula shown below:
=INT(YEARFRAC(Birth_Date,Specific_Date))
Let’s say we want to find the student’s age in years and we have the current date in cell E2. In that situation, we may use the formula below to get the age:
=INT(YEARFRAC(B2,$E$2))
Age in years can be calculated using the YEARFRAC function and the TODAY function using the following formula:
=INT(YEARFRAC(B2, TODAY()))
The YEARFRAC function only provides the most recent or current age in years when used in combination with the TODAY function.
-
Using the ROUNDDOWN Function:
The ROUNDDOWN function can also be used to determine age in Excel, despite its infrequent use. The ROUNDDOWN formula’s syntax for calculating age in Excel is as follows:
=ROUNDDOWN((Specific_Date – Birth_Date)/365.25,0)
The ROUNDDOWN function typically aids in reducing the number of decimal places. The formula has been modified by us such that it now determines age in years. For a leap year, which occurs every four years and has 366 days, we use 365.25 in the formula. To ignore the decimal places in the age, we use 0 as the ROUNDDOWN function’s final parameter.
The ROUNDDOWN formula is a reasonable practice for figuring out age, although it is not advised due to its flaws. In the event that a child hasn’t yet experienced a leap year and we use this formula to divide by 365.25 to determine age, the result will be incorrect.
In most circumstances, dividing by the typical number of days in a year is likewise acceptable, thus we can divide by 365 days rather than 365.25. This scenario, however, also has certain problems and occasionally yields inaccurate results. Consider someone whose date of birth is February 29 and the current date is February 28. If we divide that number by 365, the age the formula returns will be one day older. So, in order to determine the date in this instance, we must divide by 365.25. As a result, neither of these two methods is ideal. Excel’s DATEDIF function should always be used to determine someone’s age.
Let’s say we want to find the student’s age in years and we have the current date in cell E2. In that situation, we may use the formula below to get the age:
=ROUNDDOWN(($E$2-B2)/365.25,0)
Age in years can be calculated using the YEARFRAC function and the TODAY function using the following formula:
=ROUNDDOWN(((TODAY()-B2)/365.25,0)
-
Using the TODAY Function:
The TODAY function can also be used to calculate age in Excel, which aids in calculating age to a certain extent because age is often computed by deducting birthdate from the current date. The TODAY method, like the ROUNDDOWN formula, isn’t ideal for figuring out someone’s age in Excel.
Let’s say we want to find the student’s age from today in Excel in years and we have the today’s date in cell E2. In that situation, we may use the formula below to get the age:
=(TODAY()-B2)/365
Depending on certain circumstances, we may occasionally need to divide by 365.25 rather than 365. The first component of this formula (TODAY()-B2) typically computes the discrepancy between the present date and the birthdate. To determine the number of years, divide the difference by 365 using the second portion of the formula. (i.e., age in years).
Unfortunately, exactly like the YEARFRAC function, the TODAY formula in this case gives the data in decimals. To display the age in entire years or the closest integer value, the TODAY formula is included within the INT function. Thus, the following is the TOTAL formula to determine age in years:
=INT((TODAY()-B2)/365)
3. Determining Age in Years, Months, and Days altogether:
We must apply three separate DATEDIF functions at once and integrate them in a formula to determine the person’s precise age in complete years, months, and days. The three DATEDIF functions will be as follows if the person’s birthdate is in cell B2:
- To determine the total number of years: =DATEDIF(B2,TODAY(),”Y”)
- To determine the number of remaining months: =DATEDIF(B2,TODAY(),”YM”)
- To determine the number of remaining days: =DATEDIF(B2,TODAY(),”MD”)
Now, using the ‘&’ operator, we join all of these DATEDIF functions as follows:
=DATEDIF(B2,TODAY(),”Y”)&DATEDIF(B2,TODAY(),”YM”)&DATEDIF(B2,TODAY(),”MD”)
Despite being provided to us as a single string, the age in years, months, and dates is meaningless. We use the comma to separate each unit and decide what each value represents in order to make the findings (or age) useful or clear. Thus, the following is the formula:
=DATEDIF(B2,TODAY(),”Y”) & ” Years, ” & DATEDIF(B2,TODAY(),”YM”) & ” Months, ” & DATEDIF(B2,TODAY(),”MD”) & ” Days”
The illustration above demonstrates that the age findings are, in comparison to the prior one, more significant. It does, however, also show a few zero values. By merging our DATEDIF method within the three separate IF statements to check and remove zeros, we can further enhance it. The following is the final Excel age formula to get a person’s current age in years, months, and days:
=IF(DATEDIF(B2, TODAY(),”Y”)=0,””,DATEDIF(B2, TODAY(),”Y”)&” Years, “)& IF(DATEDIF(B2, TODAY(),”YM”)=0,””,DATEDIF(B2, TODAY(),”YM”)&” Months, “)& IF(DATEDIF(B2, TODAY(),”MD”)=0,””,DATEDIF(B2, TODAY(),”MD”)&” Days”)
Only non-zero values for the person’s age are visible in the above image. The formula, however, only determines the person’s current age.
4. Calculating Age on any Specific/Particular Date:
We just covered the syntax of the formulas above that instruct us on how to determine a person’s age as of a given date. For instance, the DATEDIF method below determines a person’s age on a particular date:
=DATEDIF(Birth_Date,Specific_Date,”Y”)
If necessary, the unit argument can be modified. Normally, we can enter the cell references for both dates in the formula above, and the outcome will be displayed on the target cell.
Another common approach is to include the desired date directly in the formula, which will allow you to apply the same formula to determine the person’s age on any given date.
Let’s say we want to find the student’s age from specific date in Excel and we have the specific date in cell E2. In that situation, we may use the formula below to get the age:
=DATEDIF(B2,$E$2,”Y”)
In addition, we can utilize the same DATEDIF concept from before where we combined three DATEDIF functions to determine the person’s date in years, months, and days at a specific day. However, we must substitute the appropriate date for TODAY() in the second argument.
Let’s say we want to find the student’s age in years, months and days for specific date and we have the specific date in cell E2. In that situation, we may use the formula below to get the age:
=IF(DATEDIF(B3, $E$2,”Y”)=0,””,DATEDIF(B3, $E$2,”Y”)&” Years, “)& IF(DATEDIF(B3, $E$2,”YM”)=0,””,DATEDIF(B3, $E$2,”YM”)&” Months, “)& IF(DATEDIF(B3, $E$2,”MD”)=0,””,DATEDIF(B3, $E$2,”MD”)&” Days”)
Application of Calculate Age in Excel
- Employee Records: Calculate the age of employees for HR records, benefits eligibility, and retirement planning.
- Financial Planning: Determine clients’ or individuals’ ages for financial planning, such as retirement savings projections.
- Healthcare Analytics: Analyze patient data by calculating ages for healthcare planning and research purposes.
- Educational Purposes: Calculate the age of students or participants for academic planning and program eligibility.
- Demographic Analysis: Analyze demographic data by calculating the age distribution of populations for marketing or policy purposes.
- Event Planning: Determine the age of attendees for event planning, such as age-specific marketing strategies or age-restricted events.
For ready-to-use Dashboard Templates:
