The YEARFRAC is a Date & Time function that calculates and returns the fraction of the year between two days. The function calculates the fraction by the number of whole days in given day count basis. In this guide, we’re going to show you how to use the YEARFRAC function and also go over some tips and error handling methods.
- All Excel versions
YEARFRAC Function’s Syntax
|start_date||A date value that represents prior date.|
|end_date||A date value that represents later date.|
|[basis]||Optional. The type of day count basis.|
Day Count basis
|Basis||Day count basis|
|0 or omitted||US (NASD) 30/360 – All months are 30 days and the year is 360 days by US convention.|
|1||Actual/actual – Calculates by real-time day counts including leap years.|
|2||Actual/360 – A year is 360 days.|
|3||Actual/365 – A year is 365 days.|
|4||European 30/360 – All months are 30 days and the year is 360 days by European convention.|
YEARFRAC Function Example
Calculating age with YEARFRAC Function
Calculating the percent of completed year
If you provide the previous year’s last day as start_date, you can find the percent of calculated year with the YEARFRAC function. DATE and YEAR functions can help to calculate previous year’s last day. You can find detailed information in How to calculate the percent of completed year.
- Excel stores dates as serial numbers. Excel assumes the date 1/1/1900 as 1 and increases this number by 1 for each day. For example; 12/31/2018 is equal to 43465.
- Because the calculation is based on whole days, Excel ignores the time part of start_date and end_date
- If [basis] has decimal digits, the number will be truncated.
- If [basis] is not between 0 and 4, the YEARFRAC function returns #NUM! error.
- If either start_date or end_date is not a valid date.