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.
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.
YEARFRAC function Syntax
YEARFRAC(start_date, end_date, [basis])
YEARFRAC function Arguments
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 |
YEARFRAC function example
Use the YEARFRAC function to calculate a part of a whole based on a date range relative to a year. This example demonstrates how to calculate the fraction of a year based on two specific dates located in cells C3 and C4 respectively. The [basis] argument is 0 (zero) in this example meaning it uses 30 days per month and a year has 360 days.
Formula in cell B8:
The YEARFRAC function in cell B8 returns 0.4166666 or 5/12 in fractions.
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.
YEARFRAC 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
Functions in 'Date and Time' category
The YEARFRAC function function is one of 22 functions in the 'Date and Time' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form