Skip to main content

YEARFRAC

Definition of YEARFRAC

Returns the number of years, including fractional years, between two dates using a specified day count convention.

Sample Usage

YEARFRAC(DATE(1969,7,16),DATE(1969,7,24),1)

YEARFRAC(A2,A3)

Syntax

YEARFRAC(start_date, end_date, [day_count_convention])
  • start_date - The start date to consider in the calculation. Must be a reference to a cell containing a date, a function returning a date type, or a number.
  • end_date - The end date to consider in the calculation. Must be a reference to a cell containing a date, a function returning a date type, or a number.
  • day_count_convention - [ OPTIONAL - 0 by default ] - An indicator of what day count method to use.
    • 0 indicates US (NASD) 30/360 - This assumes 30 day months and 360 day years as per the National Association of Securities Dealers standard, and performs specific adjustments to entered dates which fall at the end of months.
    • 1 indicates Actual/Actual - This calculates based upon the actual number of days between the specified dates, and the actual number of days in the intervening years. Used for US Treasury Bonds and Bills, but also the most relevant for non-financial use.
    • 2 indicates Actual/360 - This calculates based on the actual number of days between the speficied dates, but assumes a 360 day year.
    • 3 indicates Actual/365 - This calculates based on the actual number of days between the specified dates, but assumes a 365 day year.
    • 4 indicates European 30/360 - Similar to 0, this calculates based on a 30 day month and 360 day year, but adjusts end-of-month dates according to European financial conventions.

 

Notes

  • This function is mostly used in a financial setting, for calculation involving fixed-income securities. Because the most common calculations performed use the NASD standard calendar, this is the default behavior. However, for use in non-financial settings, option 1, Actual/Actual, is most likely the correct choice.
  • Ensure that the inputs to the function are either references to cells containing dates, functions which return date objects such as DATEDATEVALUE or TO_DATE, or date serial numbers of the type returned by the N function.
  • YEARFRAC does not autoconvert number formats. Therefore, YEARFRAC(10/10/2000,10/10/2001) is interpreted as YEARFRAC(0.005,0.00499750124938), the quotients of 10 divided by 10 divided by 2000 and 2001, respectively.

See Also

TO_DATE: Converts a provided number to a date.

N: Returns the argument provided as a number.

DAYS360: Returns the difference between two days based on the 360 day year used in some financial interest calculations.

DATEVALUE: Converts a provided date string in a known format to a date value.

DATE: Converts a provided year, month, and day into a date.

In order to use the YEARFRAC formula, start with your edited Excellentable:

Then type in the YEARFRAC Formula in the area you would like to display the outcome:

By adding the value you would like to calculate the YEARFRAC formula for, Excellentable will generate the outcome:

 

A
B
C
D
1
State
Population
Total area in mi2
Land area in mi2
Water area in mi2
House seat(s)
2
Alabama
4833722
52,420 (135,767)
50,645 (131,170)
3
Alaska
735132
665,384 (1,723,337)
570,641 (1,477,950)
4
Arizona
6626624
113,990 (295,233)
113,594 (294,207)
5
Arkansas
2959373
53,179 (137,733)
52,035 (134,770)
6
California
38332521
163,695 (423,968)
155,779 (403,466)
7
Colorado
5268367
104,094 (269,602)
103,642 (268,432)
8
Connecticut
3596080
5,543 (14,356)
4,842 (12,541)
9
Delaware
925749
2,489 (6,446)
1,949 (5,048)
10
Florida
19552860
65,758 (170,312)
53,625 (138,888)
11
Georgia
9992167
59,425 (153,910)
57,513 (148,958)
12
Hawaii
1404054
10,932 (28,314)
6,423 (16,635)
13
Idaho
1612136
83,569 (216,443)
82,643 (214,044)
14
15
E
F
G
1
Date
2
1,775 (4,597)
7
7/5/2008
3
94,743 (245,383)
1
7/6/2009
4
396 (1,026)
9
7/7/2010
5
1,143 (2,960)
4
7/8/2011
6
7,916 (20,502)
53
7/9/2012
7
452 (1,171)
7
7/10/2013
8
701 (1,816)
5
7/11/2014
9
540 (1,399)
1
7/12/2001
10
12,133 (31,424)
27
7/13/2002
11
1,912 (4,950)
14
7/14/2003
12
4,509 (11,678)
2
7/15/2004
13
926 (2,398)
2
7/16/2005
14
15
YEARFRAC:
5.016438356

 

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.