Excel YEARFRAC function
If you want to follow along with this tutorial, please download the example spreadsheet.
Description
The YEAR function is used to calculate the fractional year in a decimal format between given dates.
Syntax and arguments
Formula syntax
| YEARFRAC(start_date, end_date, [basis]) |
Arguments
|
| Basis | Day count basis |
| 0 or omitted | 30/360 (USA convention) |
| 1 | Actual/Actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | 30/360 (European convention) |
Return Value
The YEAR function returns a decimal number.
Remarks
1. When using the US (NASD) 30/360 basis, and the start_date is the last day in February, the YEARFRAC function may return an incorrect result.
2. All arguments will be truncated to integer.
Errors
1. #VALUE! error appears when the argument start_date or end_date are invalid dates.
2. #NUM! error appears when the basis is out of range 0-4.
Usage and Examples
Example 1 Basic usage
To get the fractional year based on the start date in cell B3 and end date in cell C3, please use below formula:
=YEARFRAC(B3,C3)
Press Enter key to get the result.
In this example, the [basis] are omitted, you can use the type of basis as you need.
Example 2 Combine DATE function
Sometimes, you may want to directly type the dates as the argument start_date and end_date in the formula. For preventing errors, you can use the DATE function in the formula.
For instance, the start date is 12/1/2019, the end date is 1/3/2020, please use below formula:
=YEARFRAC(DATE(2019,1,12),DATE(2020,3,1))
Press Enter key to get the result.
In this example, the [basis] are omitted, you can use the type of basis as you need.
Relative Functions:
Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.
Excel DAYS360 Function
DAYS360 function can help you to return the number of days between two dates based on a 360-day year, which is considered to have 30 days in each month.
Excel WEEKNUM Function
The WEEKNUM in Excel returns the week number of the given date in a year, which starts counting weeks from January 1.
Excel WORKDAY;Function
The WORKDAY is used to add a specified number of workdays to a started date and returns the future or past date in a serial number format.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.