Excel's YEARFRAC Helps to Find Fractions of a Year Between Dates

Time.
Utamaru Kido / Getty Images

The YEARFRAC function can be used to find what fraction of a year is represented by the period of time between two dates. Other Excel functions for finding the number of days between two dates are limited to returning a value in either years, months, days, or a combination of the three.

To be used in subsequent calculations., the value then needs to be converted to decimal form. YEARFRAC, on the other hand, returns the difference between the two dates in decimal form automatically, such as 1.65 years, so 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.

01
of 04

YEARFRAC Function Syntax and Arguments

Screenshot of Excel showing the YEARFRAC function syntax

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:

= YEARFRAC(Start_date, End_date, Basis)

Start_date (required): 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): The second date variable. The same argument requirements apply as those defined for the Start_date.

Basis (optional): 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 year
  • 2 – Actual number of days per month/360 days per year
  • 3 – Actual number of days per month/365 days per year
  • 4 – 30 days per month/360 days per year (European)

Of the available 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 returns the #VALUE! error value if Start_date or End_date are not valid dates.
  • YEARFRAC returns the #NUM! error value if the Basis argument is less than zero or greater than four.
02
of 04

YEARFRAC Function Example

Screenshot of Excel showing YEARFRAC example data

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.

This example makes use of cell references to the location of the start and end dates since they are usually easier to work with than entering serial date numbers.

Next, the optional step of reducing the number of decimal places in the answer from nine to two using the ROUND function will be added to cell E4.

The start and end dates arguments will be entered using the DATE function to prevent possible problems that can occur if the dates are interpreted as text data.

Example Cell Data

Begin by entering the following data into cells D1 to E2. Cells E3 and E4 are the locations for the formulas that will be used in this example.

 D1 — Start
D2 — Finish
D3 — Length of time
D4 — Rounded Answer
E1 — =DATE(2012,3,9)
E2 — =DATE(2013,11,1)
03
of 04

Entering the YEARFRAC Function

Screenshot of Excel showing the Formula Builder

This section of the tutorial enters the YEARFRAC function into cell E3 and calculates the time between the two dates in decimal form.

  1. Click on cell E3 — this is where the results of the function will be displayed.
  2. Click on the Formulas tab of the ribbon menu.
  3. Choose Date and Time from the ribbon to open the function drop down.
  4. Click on YEARFRAC in the list to bring up the Formula Builder.
  5. Click on the Start_date line.
  6. Click on cell E1 in the worksheet to enter the cell reference.
  7. Click on the End_date line.
  8. Click on cell E2 in the worksheet to enter the cell reference.
  9. Click on the Basis line.
  10. Enter the number 1 on this line to use the actual number of days per month and the actual number of days per year in the calculation
  11. Click OK to complete the function.
  12. The value 1.647058824 should appear in cell E3 which is the length of time in years between the two dates.
04
of 04

Nesting the ROUND and YEARFRAC Functions

Screenshot of Excel showing the ROUND function nested

To make the function result easier to work with, the value in cell E3 can be rounded to two decimal places using the ROUND function in the cell of YEARFRAC is to nest the YEARFRAC function inside the ROUND function in cell E3. The resulting formula would be:

= ROUND(YEARFRAC(E1,E2,1),2) 

The answer would be -1.65.