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.

### 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): 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.

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

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)

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

- Click on
**cell E3**— this is where the results of the function will be displayed. - Click on the
**Formulas****tab**of the**ribbon**menu. - Choose
**Date and Time**from the**ribbon**to open the function drop down. - Click on
**YEARFRAC****Formula Builder**. - Click on the
**Start_date**line. - Click on
**cell E1**in the worksheet to enter the cell reference. - Click on the
**End_date**line. - Click on
**cell E2**in the worksheet to enter the cell reference. - Click on the
**Basis**line. - 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 - Click
**OK**to complete the function. - The value
**1.647058824**should appear in**cell E3**which is the length of time in years between the two dates.

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