YEARFRAC

Function Reference: YEARFRAC

Syntax
=YEARFRAC(start_date,end_date,[basis])
Purpose / Description
Returns a decimal value representing the fraction of the year elapsed between two date exclusive of the start date
Arguments
Name Type Description
start_date number A constant, reference or nested expression that returns a serial date number representing the starting date for the calculation.  Any decimal value is ignores.
end_date number A constant, reference or nested expression that returns a serial date number representing the ending date for the calculation.  Any decimal value is ignored.
[basis] number An optional constant, reference or nested expression that indicates the basis on which the calculation will be performed.  Defaults to 0, if omitted.
NASD 30/360 months have equal weight in the year, (1/12) except that February is fractionally smaller, and March is fractionally larger
1 Actual/Actual the fraction of a year is the number of days divided by 365 or 366 depending upon whether or not it is a leap year
2 Actual/360 number of days elapsed / 360
3 Actual/365 number of days elapsed / 365
4 European 30/360 months have equal weight in the year (1/12), except that February is fractionally smaller, and March is fractionally larger
Return Values
Type Specific Value Description
number   A decimal value representing the portion of a year elapsed between start_date and end_date, exclusive of start_date.  If the dates are more than a year apart, the value will include an integer portion representing fully completed years and a decimal portion representing the part completed year.
error #VALUE! If any of the arguments are not numbers and cannot be coerced to numbers.
error #NUM! If either start_date or end_date is outside the range allowed
1900 date system: 0 to 2,958,465
1904 date system: 0 to 2,957,003
error   Any errors referenced by arguments are returned due to propagation of error values.
Notes / Comments / Advice / Warnings
Note: If end_date < start_date, the result is still a positive fraction of full or part years between the dates.
Comment: To calculate inclusively of start_date and end_date, subtract 1 from the earlier of start_date and end_date.
Advice: YEARFRAC represents a safer way of apportioning cost and revenue between periods rather than using approximations from complex date calculations.
Examples: Open / download sample workbook: YEARFRAC.xlsx
 
Tags: