**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:

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

The `YEARFRAC`

function has the following arguments:

`start_date`

(Required): A date that represents the start date.`end_date`

(Required): A date that means the end date.`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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year Worked | Result |

2 | 01/01/2023 | 06/30/2023 | `=YEARFRAC(A2,B2)` | 0.495890411 |

3 | 02/01/2023 | 07/31/2023 | `=YEARFRAC(A3,B3)` | 0.495890411 |

4 | 03/01/2023 | 08/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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 03/31/2023 | `=YEARFRAC(A2,B2)` | 0.246575342 |

3 | 04/01/2023 | 06/30/2023 | `=YEARFRAC(A3,B3)` | 0.246575342 |

4 | 07/01/2023 | 09/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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 04/30/2023 | `=YEARFRAC(A2,B2)` | 0.328767123 |

3 | 05/01/2023 | 08/31/2023 | `=YEARFRAC(A3,B3)` | 0.328767123 |

4 | 09/01/2023 | 12/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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 05/31/2023 | `=YEARFRAC(A2,B2)` | 0.410958904 |

3 | 06/01/2023 | 11/30/2023 | `=YEARFRAC(A3,B3)` | 0.410958904 |

4 | 12/01/2023 | 12/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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 06/30/2023 | `=YEARFRAC(A2,B2)` | 0.495890411 |

3 | 07/01/2023 | 12/31/2023 | `=YEARFRAC(A3,B3)` | 0.495890411 |

4 | 01/01/2024 | 06/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:**

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year Worked | Result | Eligible for Bonus |

2 | 01/01/2023 | 06/30/2023 | `=YEARFRAC(A2,B2)` | 0.495890411 | `=IF(D2>=0.5,"Yes","No")` |

3 | 02/01/2023 | 07/31/2023 | `=YEARFRAC(A3,B3)` | 0.495890411 | `=IF(D3>=0.5,"Yes","No")` |

4 | 03/01/2023 | 12/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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 04/30/2023 | `=YEARFRAC(A2,B2)` | 0.328767123 |

3 | 05/01/2023 | 08/31/2023 | `=YEARFRAC(A3,B3)` | 0.328767123 |

4 | 09/01/2023 | 12/31/2023 | `=YEARFRAC(A4,B4)` | 0.328767123 |

5 | Total: | `=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:**

A | B | C | D | |
---|---|---|---|---|

1 | Project | End Date | Fraction of Year | Result |

2 | Project 1 | 04/30/2023 | `=YEARFRAC(DATE(2023,1,1),B2)` | 0.328767123 |

3 | Project 2 | 08/31/2023 | `=YEARFRAC(DATE(2023,1,1),B3)` | 0.663013699 |

4 | Project 3 | 12/31/2023 | `=YEARFRAC(DATE(2023,1,1),B4)` | 1.000000000 |

5 | Project 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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 04/30/2023 | `=YEARFRAC(A2,B2)` | 0.328767123 |

3 | 05/01/2023 | 08/31/2023 | `=YEARFRAC(A3,B3)` | 0.328767123 |

4 | 09/01/2023 | 12/31/2023 | `=YEARFRAC(A4,B4)` | 0.328767123 |

5 | Average: | `=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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 04/30/2023 | `=YEARFRAC(A2,B2)` | 0.328767123 |

3 | 05/01/2023 | 08/31/2023 | `=YEARFRAC(A3,B3)` | 0.328767123 |

4 | 09/01/2023 | 12/31/2023 | `=YEARFRAC(A4,B4)` | 0.328767123 |

5 | Longest 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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 04/30/2023 | `=YEARFRAC(A2,B2)` | 0.328767123 |

3 | 05/01/2023 | 08/31/2023 | `=YEARFRAC(A3,B3)` | 0.328767123 |

4 | 09/01/2023 | 12/31/2023 | `=YEARFRAC(A4,B4)` | 0.328767123 |

5 | Shortest 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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 06/30/2023 | `=YEARFRAC(A2,B2)` | 0.495890411 |

3 | 07/01/2023 | 12/31/2023 | `=YEARFRAC(A3,B3)` | 0.495890411 |

4 | 01/01/2024 | 06/30/2024 | `=YEARFRAC(A4,B4)` | 0.495890411 |

5 | Projects > 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:**

A | B | C | D | |
---|---|---|---|---|

1 | Start Date | End Date | Fraction of Year | Result |

2 | 01/01/2023 | 06/30/2023 | `=ROUND(YEARFRAC(A2,B2),2)` | 0.50 |

3 | 07/01/2023 | 12/31/2023 | `=ROUND(YEARFRAC(A3,B3),2)` | 0.50 |

4 | 01/01/2024 | 06/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**

- 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. - 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. - Remember that Excel stores date as sequential serial numbers for calculations. By default, January 1, 1900, is serial number 1.
- If you get the
`#VALUE!`

error value, check your`start_date`

and`end_date`

to make sure they are valid dates. - If you get the
`#NUM!`

error value, check your`basis`

to make sure it is between 0 and 4 (inclusive).