In this article, we will learn about how to use the YEARFRAC function in Excel.
YEARFRAC function calculates the fraction of year between the start_date & end_date. A period of six month is half a year which is 0.5 years. So to view the results in the year format use the YEARFRAC function.
The YEARFRAC function is a built-in financial function which returns the fraction of year between the start_date & end_date.
Start_date : A valid date which represents the start date
end_date : A valid date which represents the end date
basis : [optional] Day count basis. Default is 0.
|Basis||Day count basis|
|0||US (NASD) 30 / 360|
|1||actual / actual|
|2||actual / 360|
|3||actual / 365|
|4||European 30 / 360|
Let’s understand this function using it in an example.
Here we have values to calculate the YEARFRAC value between the two given dates.
Use the formula:
A2 : start date given as cell reference.
B2 : end date given as cell reference.
B5 : 0 (default) specifies Day count basis method choose US (NASD) 30 / 360 .[optional]
Here all the arguments to the function is given as cell reference.
Note: Dates in the formula must be entered using cell reference or DATE function. Excel date format creates #VALUE! error while computing results. Use the cell reference or DATE function in excel while taking date as input to the function
Now Press Enter to get the YEARFRAC value
The result specifies the year gap between the two dates.
Now copy the formula to other cells using the Ctrl + D shortcut key.
As you can see here, the function works fine. Now see the below notes to remember some observational mistakes
Hope you understood how to use YEARFRAC function and referring cell in Excel. Explore more articles on Excel function here. Please feel free to state your query or feedback for the above article.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.