The YEARFRAC function takes two dates and returns the number of years, including fractions of a year, between them. You can optionally specify different methods of counting days. Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.
start_date– The date for the calculation to start on.
end_date– The date for the calculation to end on.
day_count_convention– Optional indication of day count method to use.
- 0 [or blank] – US (NASD) 30/360
- 1 – actual/actual Uses the actual number of days.
- 2 – actual/360 Uses the actual number of days but with a 360 day year.
- 3 – actual/365 Uses the actual number of days but assumes a 365 day year. Doesn’t recognize leap year.
- 4 – European 30/360 Similar to 0 but uses European standards instead of US.
DATE – Takes separate year, month, and day values and returns them as a date
DATEVALUE – Returns the serial value of a date
DAY – Returns the value the day from a given date
DAYS360 – Calculates interest using the 360 method
#NUM – An input is a number but is not a valid date. This could happen if you used the 35th day of November.
#VALUE! – The input is something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.
Live examples in Sheets
Go to this spreadsheet for the examples of the YEARFRAC function shown above that you can study and use anywhere you would like.