Other Excel functions can find the number of days between two dates, but they are limited to returning a value in years, months, days, or a combination of the three. YEARFRAC, on the other hand, returns the difference between the two dates in decimal form automatically, such as 1.65 years, so that the result can be used directly in other calculations. These calculations could include values such as an employee’s length of service or the percentage to be paid for yearly programs that are terminated early such as health benefits.
YEARFRAC Function Syntax and Arguments
A function’s syntax refers to the layout of the function and includes the function’s name, brackets, and arguments. The syntax for the YEARFRAC function is: Start_date (required) is the first date variable; this argument can be a cell reference to the location of the data in the worksheet or the actual start date in serial number format. End_date (required) is the second date variable. The same argument requirements apply as those defined for the Start_date. Basis (optional) is a value ranging from zero to four that tells Excel which day count method to use with the function.
0 or omitted — 30 days per month/360 day per year (U.S. NASD)1 — Actual number of days per month/Actual number of days per year2 — Actual number of days per month/360 days per year3 — Actual number of days per month/365 days per year4 — 30 days per month/360 days per year (European)
Of the options for the basis argument, a value of 1 gives the most accurate for counting days per month and days per year. The different combinations of days per month and days per year for the Basis argument of the YEARFRAC function are available because businesses in various fields, such as share trading, economics, and finance, have different requirements for their accounting systems.
YEARFRAC Function Example
As can be seen in the image above, this example will use the YEARFRAC function in cell E3 to find the length of time between two dates — March 9, 2012, and November 1, 2013. In this example, you’ll use cell references to the location of the start and end dates since they are usually easier to work with than entering serial date numbers. You can also take the optional step of reducing the number of decimal places in the answer from nine to two using the ROUND function. Begin by entering data into cells D1 to E2, as seen in the above image. Cell E3 is where the formula will go. The Date function syntax is as follows for this example:
Entering the YEARFRAC Function
In this example, you’ll enter the YEARFRAC function into cell E3 to calculate the time between the two dates in cells E1 and E2.
Nesting the ROUND and YEARFRAC Functions
To make the function result easier to work with, you can round the value in cell E3 to two decimal places by nesting the ROUND and YEARFRAC functions. To do this, type ROUND after the equal (=) sign, and ,2 in front of the last parentheses. The resulting formula is: The answer rounds to 1.65.
