Find Fractions of a Year Between Dates With Excel's 'YEARFRAC'

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on March 6, 2021
A sand timer and a calendar sitting on a desk.

Utamaru Kido / Getty Images

The YEARFRAC function calculates the fraction of a year represented by the number of days between two dates (start_date and end_date).

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.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Microsoft 365.

Excel showing the YEARFRAC function syntax

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: 

=YEARFRAC(Start_date,End_date,Basis)

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

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.

Excel showing YEARFRAC example data

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.

Use the DATE function to input the start and end date arguments to prevent possible problems that can occur if Excel interprets the dates as text data.

The Date function syntax is as follows for this example: 

E1 — =DATE(2012,3,9)
 E2 — =DATE(2013,11,1)

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.

Excel spreadsheet showing the YEARFRAC Function Dialog Box

  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.

    Using the DATE function to input the start and end date arguments prevents possible problems that can occur if the dates are interpreted as text data.

  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.

  13. Your worksheet might display more or fewer decimal points depending on your settings.

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: 

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

The answer rounds to 1.65.

Was this page helpful?