Understanding YEARFRAC: YEARFRAC is an Excel computational tool delivering results in the form of the fraction of a year represented by the complete days between two dates.
Date Calculation Function YEARFRAC in Excel
How to Utilize the YEARFRAC Function in Excel
1. Description and Usage Syntax
This function returns the fractional part of a year calculated by the number of complete days between two specific dates in Excel.
Syntax:
YEARFRAC(start_date, end_date, [basis])
Where:
- Start_date: The start date for calculating the number of years, a required parameter.
- End_date: The end date for which the number of years is to be calculated, a required parameter.
- Basis: The basis for counting days, an optional parameter with the following values:
+ Basis =0 -> US(NASD) 30/360 day count basis.
+ Basis =1 -> Actual/actual day count basis.
+ Basis =2 -> Actual/360 day count basis.
+ Basis =3 -> Actual/365 day count basis.
+ Basis =4 -> EUROPEAN 30/360 day count basis.
Note:
- Excel stores dates as serial numbers for ease of calculation.
- All arguments of the function, if decimal numbers, are truncated to integers.
2. Practical Scenario Example
Utilize the YEARFRAC date calculation function. Assume you need to calculate the fractional part of a year represented by the complete days between two specific dates with the provided information in the spreadsheet image below:
Step 1: Enter the function syntax into cell E3 as illustrated in the image below:
Step 2: Press the Enter key to obtain the calculation result
Step 3: Similarly, copy the function downwards into the cells below to receive corresponding results:
If the Start_date or End_date are not valid dates, the YEARFRAC function will return an error result #VALUE! as shown below:
If the Basis falls outside the values 0 to 4, the function will yield an error value #NUM!
Thus, Mytour has completed the tutorial on using the YEARFRAC function in Excel. This is one of the commonly used date functions, providing the fractional part of a year calculated by the number of complete days between two specific dates in Excel. Hopefully, this article will be helpful for you in both study and work.