YEARFRAC Function in Microsoft Excel

Part 1: Introduction

Definition

The `YEARFRAC` function in Microsoft Excel calculates the fraction of the year represented by the number of whole days between two dates (the `start_date` and the `end_date`). This function can be helpful in various business scenarios, such as identifying the proportion of an entire year’s benefits or obligations to assign to a specific term.

Purpose

The primary purpose of the `YEARFRAC` the function is to calculate the fraction of a year between two dates. This can be particularly useful in financial calculations where you need to calculate the exact duration between two dates in terms of years.

Syntax & Arguments

The syntax for the `YEARFRAC` the function is as follows:

syntax
`YEARFRAC(start_date, end_date, [basis]) `

The `YEARFRAC` function has the following arguments:

1. `start_date` (Required): A date that represents the start date.
2. `end_date` (Required): A date that means the end date.
3. `basis` (Optional): The type of day count basis to use.

The `basis` can have the following values:

• 0 or omitted: US (NASD) 30/360
• 1: Actual/actual
• 2: Actual/360
• 3: Actual/365
• 4: European 30/360

Return Value

The `YEARFRAC` the function returns the fraction of the year between the `start_date` and the `end_date`.

Remarks

• Dates should be entered using the `DATE` function or as results of other formulas or functions. For example, use `DATE(2018,5,23)` for the 23rd day of May 2018. Problems can occur if dates are entered as text.
• The `YEARFRAC` function may return an incorrect result when using the US (NASD) 30/360 basis, the last day in February.
• Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2018, is serial number 43101 because it is 43,101 days after January 1, 1900.
• All arguments are truncated to integers.
• If `start_date` or `end_date` are not valid dates, `YEARFRAC` returns the `#VALUE!` error value.
• If `basis` < 0 or if `basis` > 4, `YEARFRAC` returns the `#NUM!` error value.

Part 2: Examples

Example 1

Purpose of Example: Calculate the fraction of the year that an employee has worked.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of Year WorkedResult
201/01/202306/30/2023`=YEARFRAC(A2,B2)`0.495890411
302/01/202307/31/2023`=YEARFRAC(A3,B3)`0.495890411
403/01/202308/31/2023`=YEARFRAC(A4,B4)`0.495890411

Explanation: In this example, we are calculating the fraction of the year that an employee has worked based on their start and end dates. The `YEARFRAC` function is used to calculate this fraction.

Example 2

Purpose of Example: Calculate the fraction of the year between the start and end of a financial quarter.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202303/31/2023`=YEARFRAC(A2,B2)`0.246575342
304/01/202306/30/2023`=YEARFRAC(A3,B3)`0.246575342
407/01/202309/30/2023`=YEARFRAC(A4,B4)`0.246575342

Explanation: In this example, we calculate the fraction of the year each financial quarter represents. The `YEARFRAC` function is used to calculate this fraction.

Example 3

Purpose of Example: Calculate the fraction of the year between the start and end of a project.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202304/30/2023`=YEARFRAC(A2,B2)`0.328767123
305/01/202308/31/2023`=YEARFRAC(A3,B3)`0.328767123
409/01/202312/31/2023`=YEARFRAC(A4,B4)`0.328767123

Explanation: In this example, we calculate the fraction of the year a project lasts based on its start and end dates. The `YEARFRAC` function is used to calculate this fraction.

Example 4

Purpose of Example: Calculate the fraction of the year between two payment dates.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202305/31/2023`=YEARFRAC(A2,B2)`0.410958904
306/01/202311/30/2023`=YEARFRAC(A3,B3)`0.410958904
412/01/202312/31/2023`=YEARFRAC(A4,B4)`0.082191781

Explanation: In this example, we calculate the year’s fraction between two payment dates. The `YEARFRAC` function is used to calculate this fraction.

Example 5

Purpose of Example: Calculate the fraction of the year between the start and end of a lease.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202306/30/2023`=YEARFRAC(A2,B2)`0.495890411
307/01/202312/31/2023`=YEARFRAC(A3,B3)`0.495890411
401/01/202406/30/2024`=YEARFRAC(A4,B4)`0.495890411

Explanation: In this example, we are calculating the fraction of the year that a lease lasts based on its start and end dates. The `YEARFRAC` function is used to calculate this fraction.

Example 6

Purpose of Example: Calculate the fraction of the year that an employee has worked, and use the `IF` function to determine if they are eligible for a year-end bonus.

Data Tables and Formulas:

ABCDE
1Start DateEnd DateFraction of Year WorkedResultEligible for Bonus
201/01/202306/30/2023`=YEARFRAC(A2,B2)`0.495890411`=IF(D2>=0.5,"Yes","No")`
302/01/202307/31/2023`=YEARFRAC(A3,B3)`0.495890411`=IF(D3>=0.5,"Yes","No")`
403/01/202312/31/2023`=YEARFRAC(A4,B4)`0.747945205`=IF(D4>=0.5,"Yes","No")`

Explanation: In this example, we are calculating the fraction of the year that an employee has worked based on their start and end dates. The `YEARFRAC` function is used to calculate this fraction. We then use the `IF` function to determine if the employee is eligible for a year-end bonus. The employee is suitable for the prize if the fraction of the year worked is greater than or equal to 0.5.

Example 7

Purpose of Example: Calculate the total fraction of the year for multiple projects using the `SUM` function.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202304/30/2023`=YEARFRAC(A2,B2)`0.328767123
305/01/202308/31/2023`=YEARFRAC(A3,B3)`0.328767123
409/01/202312/31/2023`=YEARFRAC(A4,B4)`0.328767123
5Total:`=SUM(D2:D4)`

Explanation: In this example, we calculate the fraction of the year each project lasts based on its start and end dates. The `YEARFRAC` function is used to calculate this fraction. We then use the `SUM` function to calculate the total fraction of the year for all projects.

Example 8

Purpose of Example: Use the `VLOOKUP` function to find the end date for a project and calculate the fraction of the year.

Data Tables and Formulas:

ABCD
1ProjectEnd DateFraction of YearResult
2Project 104/30/2023`=YEARFRAC(DATE(2023,1,1),B2)`0.328767123
3Project 208/31/2023`=YEARFRAC(DATE(2023,1,1),B3)`0.663013699
4Project 312/31/2023`=YEARFRAC(DATE(2023,1,1),B4)`1.000000000
5Project 2:`=YEARFRAC(DATE(2023,1,1),VLOOKUP("Project 2",A2:B4,2,FALSE))`

Explanation: In this example, we are using the `VLOOKUP` function to find the end date for a specific project. We then use the `YEARFRAC` function to calculate the fraction of the year for that project.

Example 9

Purpose of Example: Use the `AVERAGE` function to calculate the average fraction of the year for multiple projects.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202304/30/2023`=YEARFRAC(A2,B2)`0.328767123
305/01/202308/31/2023`=YEARFRAC(A3,B3)`0.328767123
409/01/202312/31/2023`=YEARFRAC(A4,B4)`0.328767123
5Average:`=AVERAGE(D2:D4)`

Explanation: In this example, we calculate the fraction of the year each project lasts based on its start and end dates. The `YEARFRAC` function is used to calculate this fraction. We then use the `AVERAGE` function to calculate the average fraction of the year for all projects.

Example 10

Purpose of Example: Use the `MAX` function to find the project with the most extended duration in terms of the fraction of the year.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202304/30/2023`=YEARFRAC(A2,B2)`0.328767123
305/01/202308/31/2023`=YEARFRAC(A3,B3)`0.328767123
409/01/202312/31/2023`=YEARFRAC(A4,B4)`0.328767123
5Longest Duration:`=MAX(D2:D4)`

Explanation: In this example, we calculate the fraction of the year each project lasts based on its start and end dates. The `YEARFRAC` function is used to calculate this fraction. We then use the `MAX` function to find the project with the most extended duration in terms of the fraction of the year.

Example 11

Purpose of Example: Use the `MIN` function to find the project with the shortest duration in terms of the fraction of the year.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202304/30/2023`=YEARFRAC(A2,B2)`0.328767123
305/01/202308/31/2023`=YEARFRAC(A3,B3)`0.328767123
409/01/202312/31/2023`=YEARFRAC(A4,B4)`0.328767123
5Shortest Duration:`=MIN(D2:D4)`

Explanation: In this example, we calculate the fraction of the year each project lasts based on its start and end dates. The `YEARFRAC` function is used to calculate this fraction. We then use the `MIN` function to find the project with the shortest duration in terms of the fraction of the year.

Example 12

Purpose of Example: Use the `COUNTIF` function to count the number of projects that last more than half a year.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202306/30/2023`=YEARFRAC(A2,B2)`0.495890411
307/01/202312/31/2023`=YEARFRAC(A3,B3)`0.495890411
401/01/202406/30/2024`=YEARFRAC(A4,B4)`0.495890411
5Projects > 0.5 Year:`=COUNTIF(D2:D4,">0.5")`

Explanation: In this example, we calculate the fraction of the year each project lasts based on its start and end dates. The `YEARFRAC` function is used to calculate this fraction. We then use the `COUNTIF` function to count the number of projects that last more than half a year.

Example 13

Purpose of Example: Use the `ROUND` function to round the fraction of the year to two decimal places.

Data Tables and Formulas:

ABCD
1Start DateEnd DateFraction of YearResult
201/01/202306/30/2023`=ROUND(YEARFRAC(A2,B2),2)`0.50
307/01/202312/31/2023`=ROUND(YEARFRAC(A3,B3),2)`0.50
401/01/202406/30/2024`=ROUND(YEARFRAC(A4,B4),2)`0.50

Explanation: In this example, we calculate the fraction of the year each project lasts based on its start and end dates. The `YEARFRAC` function is used to calculate this fraction. We then use the `ROUND` function to round the fraction of the year to two decimal places.

Part 3: Tips and Tricks

1. Always use the `DATE` function or the result of other formulas or functions to enter dates in the `YEARFRAC` function. This will prevent any issues that may arise from entering dates as text.
2. Be aware that the `YEARFRAC` function may return an incorrect result when using the US (NASD) 30/360 basis, which `start_date` is the last day in February.
3. Remember that Excel stores date as sequential serial numbers for calculations. By default, January 1, 1900, is serial number 1.
4. If you get the `#VALUE!` error value, check your `start_date` and `end_date` to make sure they are valid dates.
5. If you get the `#NUM!` error value, check your `basis` to make sure it is between 0 and 4 (inclusive).