Excel Tutorial – YEARFRAC

The YEARFRAC function in Microsoft Excel is a built-in function that allows you to calculate the fraction of a year between two dates. It takes two arguments: the start date and the end date. The function returns the fraction of a year represented by the number of days between the two dates, based on a 365-day year or a 360-day year, depending on the method that you specify.

To use the YEARFRAC function in Excel, follow these steps:

1. Open a new or existing worksheet in Excel.
2. Select the cell where you want to display the result of the YEARFRAC function.
3. Type the following formula in the cell: =YEARFRAC(start_date, end_date, [basis])
4. Replace “start_date” with the cell reference or the start date, “end_date” with the cell reference or the end date, and “[basis]” with the optional argument that specifies the method to use to calculate the fraction of a year.

The “[basis]” argument is optional, and can be one of the following values:

• 0 or omitted, to calculate the fraction of a year based on a 360-day year
• 1, to calculate the fraction of a year based on a 365-day year
• 2, to calculate the fraction of a year based on a 360-day year, with the end date included in the calculation
• 3, to calculate the fraction of a year based on a 365-day year, with the end date included in the calculation

5. Press Enter to calculate the result of the YEARFRAC function.

For example, let’s say you have a list of start dates in column A and end dates in column B, and you want to calculate the fraction of a year represented by the number of days between each pair of dates and display the result in column C. You can use the YEARFRAC function as follows:

1. In cell C1, type the formula =YEARFRAC(A1, B1)
2. Press Enter to calculate the result.
3. Select cell C1, and then drag the fill handle (the small square in the bottom-right corner of the cell) down to fill the formula in the remaining cells of column C.

This will calculate the fraction of a year represented by the number of days between each pair of dates in columns A and B, based on a 360-day year, and display the result in column C.

Note: The YEARFRAC function calculates the fraction of a year based on the number of days between two dates, and does not include the start date or the end date in the calculation. If you want to include the start date or the end date in the calculation, you can use the DATEDIF function instead.