How to use the YEARFRAC function

What is the YEARFRAC function?

The YEARFRAC function returns the fraction of the year based on the number of whole days between a start date and an end date. This is made possible because of how Excel handle dates as integers.

In Excel, date values are stored as integer numbers where each integer represents the number of days since a reference date (usually January 1, 1900). This allows Excel to perform mathematical operations on date values such as calculating the number of days between two dates. The integer representation of dates makes it easy to manipulate and calculate with date information within Excel formulas and functions.

1. Introduction

What is an Excel date?

An Excel date is a serial number that Excel recognizes and can be filtered, sorted and used in other date calculations.

Excel dates are actually serial numbers formatted as dates, 1/1/1900 is 1 and 2/2/2018 is 43133. There are 43132 days between 2/2/2018 and 1/1/1900.

You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.

What is a year?

A period of 365 days (366 days in leap years) based on the revolution of the Earth around the Sun. It represents one complete cycle of seasons - spring, summer, autumn, winter. Calendar years are numbered and divided into 12 months.

What is a leap year?

A leap year is a 366 day year occurring every 4 years to maintain calendar accuracy. A year containing an extra day, February 29th, that occurs once every 4 years.
The leap year was added to the Gregorian calendar to keep it aligned with the astronomical year. It occurs in years divisible by 4, except centurial years not divisible by 400.

What is a fraction?

A fraction has a numerator and a denominator:

• Numerator - The top number in a fraction.
• Denominator - The bottom number.

For example:

6/7

• The numerator is 6, meaning 6 parts.
• The denominator is 7, meaning the whole was split into 7 equal parts.

2. Syntax

YEARFRAC(start_date, end_date, [basis])

start_date	Required.
end_date	Required.
[basis]	Optional. Day count basis. 0 - Default value. US (NASD) 30/360 1 - Actual/actual 2 - Actual/360 3 - Actual/365 4 - European 30/360

3. Example 1

Theses examples demonstrate how to calculate the fraction of a year based on two specific dates located in cell range B3:C9. The [basis] argument is 0 (zero) to 4  in this example.

Formula in cell F3:

Arguments:

• Start date: 1/1/2028 (cell B3)
• End date: 5/31/2028 (cell C3)
• Basis: 0 (cell D3, which represents the US (NASD) 30/360 method)

Result: 0.416666667 (5 months out of 12 in a year) 5/12 = 0.416666667

Formula in cell F4:

Arguments:

• Start date: 1/1/2028 (cell B4)
• End date: 5/31/2028 (cell C4)
• Basis: 1 (cell D4, which represents the Actual/actual method)

Result: 0.412568306 (accounts for leap year in 2028)

Formula in cell F5:

Arguments:

Start date: 1/1/2028 (cell B5)
End date: 5/31/2028 (cell C5)
Basis: 2 (cell D5, which represents the Actual/360 method)

Result: 0.419444444 (151 days / 360 days)

Formula in cell F6:

Arguments:

Start date: 1/1/2028 (cell B6)
End date: 5/31/2028 (cell C6)
Basis: 3 (cell D6, which represents the Actual/365 method)

Result: 0.413698630 (151 days / 365 days)

Formula in cell F7:

Arguments:

Start date: 1/1/2028 (cell B7)
End date: 5/31/2028 (cell C7)
Basis: 4 (cell D7, which represents the European 30/360 method)

Result: 0.413888889 (149 days / 360 days)

Formula in cell F8:

Arguments:

Start date: 4/1/2028 (cell B8)
End date: 5/1/2028 (cell C8)
Basis: 0 (cell D8, which represents the US (NASD) 30/360 method)

Result: 0.083333333 (1 month out of 12 in a year)

Formula in cell F9:

Arguments:

Start date: 4/1/2028 (cell B9)
End date: 5/1/2028 (cell C9)
Basis: 3 (cell D9, which represents the Actual/365 method)

Result: 0.082191781 (30 days / 365 days)

Each formula calculates the fraction of a year between the start and end dates using different basis methods, which affect how days and years are counted. The results vary slightly due to these different calculation methods.

4. Example 2

Bond valuation with accrued interest: A 5-year bond with a face value of $100,000 and a 4% annual coupon rate (paid semi-annually) was issued on January 15, 2024. If an investor purchases this bond on April 30, 2024, what is the accrued interest?

Given in the question:

• 5-year bond with $100,000 face value
• 4% annual coupon rate, paid semi-annually.
• Issued on January 15, 2024
• Purchased on April 30, 2024

Formula in cell C7:

The formula returns $583.33 which is the accrued interest between 1/15/2024 and 4/30/2024 based in a 5-year bond with a face value of $100,000.

Let's break down the formula calculation:

1. Determine the semi-annual coupon payment.
   Semi-annual coupon rate = 4% / 2 = 2%
   Semi-annual coupon payment = $100,000 × 2% = $2,000
2. Calculate the fraction of the coupon period that has passed using YEARFRAC
   First coupon date: July 15, 2024 (6 months after issuance)
   YEARFRAC(January 15, 2024, April 30, 2024, 0) = 0.2877698 (using actual/actual basis)
3. Calculate the accrued interest.
   Accrued interest = Semi-annual coupon payment × Fraction of period passed
   Accrued interest = $2,000 × 0.2877698 = $575.54

This calculation demonstrates how the YEARFRAC function is useful in determining the precise fraction of time for which interest has accrued. The function accounts for the exact number of days between the last coupon date (or issuance date in this case) and the purchase date, considering factors like leap years.

5. Example 3

Project cost allocation with varying rates: A consulting project runs from March 1, 2024, to November 30, 2024. The consultant charges $100/hour for the first third of the project duration and $150/hour for the remaining two thirds? Assuming a 40-hour work week, what is the total cost of the project?

Formula in cell C7:

The formula returns the total cost of the consulting project which is $207,229.63

1. Calculate the total project duration using YEARFRAC (this remains the same)
   Project start: March 1, 2024 Project end: November 30, 2024 Total duration = YEARFRAC("3/1/2024", "11/30/2024", 1) = 0.7472 years (approximately)
2. Calculate the number of working hours for each portion of the project
   Total working days = 0.75 years * 52 weeks/year * 5 working days/week = 195 working days Hours for first third = (195 days / 3) * 8 hours/day = 518.07 hours Hours for remaining two-thirds = 195 days * 8 hours/day - 520 hours = 1,036.148 hours
3. Calculate the cost for each portion of the project
   First third (at $100/hour): 518.07 * $100/hour = $51,807.41 Remaining two-thirds (at $150/hour): 1,040 hours * $150/hour = $155,422.22
4. Calculate the total project cost

Total cost = First third cost + Remaining two-thirds cost Total cost = $51,807.41 + $155,422.22 = $207,229.63 The total cost of the consulting project is $207,229.63

6. YEARFRAC function tips

Use the DATE function to create a date if you enter the date in the function.

Example:

The YEARFRAC function requires Excel dates to work properly, the DATE function allows you to create Excel dates by specifying the year, month, and day.

7. Function not working

If using the US (NASD) 30/360 basis which is argument 0 (zero), and the start_date argument is the last day in February the YEARFRAC function may return an incorrect result.

The YEARFRAC function may return an incorrect result if dates are entered as text values.

YEARFRAC returns

• the #VALUE! error value if start_date or end_date arguments are not valid dates.
• the #NUM! error value if basis < 0 or if basis > 4

7.1 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

7.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue..

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference B3 converted to hard-coded value using the F9 key. The YEARFRAC function requires valid Excel dates which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

7.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

Back to top

'YEARFRAC' function examples

The following article has a formula that contains the YEARFRAC function.

Functions in 'Date and Time' category

The YEARFRAC function function is one of 22 functions in the 'Date and Time' category.

Excel function categories

Excel categories

Leave a Comment