Using the Excel YEARFRAC Function
SummaryThe YEARFRAC function in Excel is used to calculate the fractional years between two given dates. The function returns the answer in decimal form. The YEARFRAC function is useful when trying to calculate age when you know the date of birth or a portion of annual benefits.
Syntax=YEARFRAC (start_date, end_date, [basis])
Start_date is a required field. It is advisable to enter the date using the DATE function or use the output from another formula or function.
End_date is a required field. Similar to start_date, it is advisable to enter the date using the DATE function or use the output from another formula or function.
Basis is an option field and is used to specify the type of day count that should be used. Options for basis include:
Usage NotesYEARFRAC Errors
Excel stores dates as serial numbers and valid dates must fall between January 1, 1900 and December 31, 9999. If start_date or end_date are not valid the function will return the #VALUE! error.
The basis argument must be either blank or one of the values specified in the above table. If basis is not a valid entry the function will return the #NUM! error.
Values Truncated to Integers
All arguments in the YEARFRAC function are truncated to integers.
Incorrect Result Using US/NASD 30/360
The function may return an incorrect result if start_date is the last day of February when using the US/NASD 30/360 basis.