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
DATEfunction or as results of other formulas or functions. For example, useDATE(2018,5,23)for the 23rd day of May 2018. Problems can occur if dates are entered as text. - The
YEARFRACfunction 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_dateorend_dateare not valid dates,YEARFRACreturns the#VALUE!error value. - If
basis< 0 or ifbasis> 4,YEARFRACreturns 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
DATEfunction or the result of other formulas or functions to enter dates in theYEARFRACfunction. This will prevent any issues that may arise from entering dates as text. - Be aware that the
YEARFRACfunction may return an incorrect result when using the US (NASD) 30/360 basis, whichstart_dateis 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 yourstart_dateandend_dateto make sure they are valid dates. - If you get the
#NUM!error value, check yourbasisto make sure it is between 0 and 4 (inclusive).