If you manage a human resources database in Microsoft Excel, you often need to calculate an employee’s exact age based on their birth date. Simply subtracting their birth year from the current year is highly inaccurate, as it completely ignores the month and day. If you attempt to subtract the two exact dates, Excel will output the total number of days between them (e.g., 14,230 days), requiring you to manually divide by 365.25 to account for leap years. To instantly and accurately calculate the exact fractional age in years between two dates, you must use the YEARFRAC function.
How the YEARFRAC Function Works
The YEARFRAC (Year Fraction) function calculates the proportion of a whole year that exists between two specific dates. It outputs a decimal number. If the output is 38.5, the person is exactly 38 and a half years old.
The syntax requires two specific dates: =YEARFRAC(start_date, end_date, [basis])
- start_date: The older date (e.g., a birth date in cell A2).
- end_date: The more recent date (e.g., today’s date in cell B2).
- [basis]: (Optional but highly recommended). This tells Excel which specific day-count system to use. For precise human ages, always use the number 1 (which instructs Excel to use the actual, real-world calendar, accounting perfectly for leap years).
How to Calculate Exact Age
Imagine cell A2 contains an employee’s birth date: 04/15/1985.
Instead of hardcoding today’s date into cell B2, you can nest the TODAY() function directly into the formula so the employee’s age automatically updates every time you open the spreadsheet.
Click into cell C2 and type:
=YEARFRAC(A2, TODAY(), 1)Excel will calculate the exact number of days between April 15, 1985, and whatever today’s actual date is, divide it by the exact number of calendar days in those specific years, and output a highly precise decimal, such as 39.421.
How to Truncate the Decimal
While 39.421 is mathematically perfect, human resources departments usually only care about the integer (the person is simply 39 years old until their 40th birthday). You do not want to use the standard ROUND function, because if the output is 39.8, Excel will round it up to 40, falsely stating the employee has had their birthday.
Instead, wrap your formula in the INT (Integer) function. This forcefully strips away all decimals without rounding up.
=INT(YEARFRAC(A2, TODAY(), 1))This combined formula is the absolute gold standard for age calculations in Microsoft Excel, guaranteeing a clean, accurate whole number that automatically updates every day.