Excel YEARFRAC Function

 The YEARFRAC function, as its name suggests, 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., this 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 06

The YEARFRAC Function Syntax and Arguments

Excel YEARFRAC Function
Excel YEARFRAC Function. © Ted French

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.

  1. 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)

Notes:

  • 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
  • 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
  • More information on the Basis argument is given at the end of the tutorial
02
of 06

Example Using Excel's YEARFRAC Function

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.

The 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.

03
of 06

Entering the Tutorial Data

Note: 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.

 Cell - Data
 D1 - Start:
 D2 - Finish:
 D3 - Length of time:
 D4 - Rounded Answer:
 E1 - =DATE(2012,3,9)
 E2 - =DATE(2013,11,1)
  1. Enter the following data into cells D1 to E2. Cells E3 and E4 are the location for the formulas used in the example
04
of 06

Entering the YEARFRAC Function

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 list
  4. Click on YEARFRAC in the list to bring up the function's dialog box
  5. In the dialog box, click on the Start_date line
  6. Click on cell E1 in the worksheet to enter the cell reference into the dialog box
  7. Click on the End_date line in the dialog box
  8. Click on cell E2 in the worksheet to enter the cell reference into the dialog box
  9. Click on the Basis line in the dialog box
  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 and close the dialog box
  12. The value 1.647058824 should appear in cell E3 which is the length of time in years between the two dates.
05
of 06

Nesting the ROUND and YEARFRAC Functions

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 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.

06
of 06

Basis Argument Information

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.

By standardizing the number of days per month, companies can make month to month comparisons that would not normally be possible given that number of days per month might range from 28 to 31 in a year.

For companies, these comparisons might be for profits, expenses, or in the case of the financial field, the amount of interest earned on investments. Similarly, standardizing the number of days per year allows for yearly comparison of data. Additional details for the

U.S. (NASD - National Association of Securities Dealers) method:

  • If the Start_date is the last day of a month, it becomes equal to the 30th day of the same month.
  • If the End_date is the last day of a month and the Start_date is earlier than the 30th day of a month, the End_date becomes equal to the 1st day of the next month; otherwise, the End_date becomes equal to the 30th day of the same month

European method:

  • Start_dates and End_dates that occur on the 31st day of the month become equal to the 30th day of the same month.
Format
mla apa chicago
Your Citation
French, Ted. "Excel YEARFRAC Function." ThoughtCo, Jan. 19, 2018, thoughtco.com/excel-yearfrac-function-3123766. French, Ted. (2018, January 19). Excel YEARFRAC Function. Retrieved from https://www.thoughtco.com/excel-yearfrac-function-3123766 French, Ted. "Excel YEARFRAC Function." ThoughtCo. https://www.thoughtco.com/excel-yearfrac-function-3123766 (accessed January 20, 2018).