YEARFRAC Function – Google Sheets

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.

This function can be used to calculate someone’s age. An alternative to YEARFAC is DATEDIF which can return the difference between two days in years, months, or days.

Remember that dates are 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.

Syntax

=YEARFRAC(start_date,end_date,[day_count_convention])

  • start_date – The date for the calculation to start.
  • end_date – The date for the calculation to end.
  • day_count_convention – Optional day count method.
    • 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 number of days but assumes a 365-day year. This option doesn’t recognize leap year.
    • 4 – European 30/360 Similar to 0 but uses European standards instead of US.

Related Functions

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 of the day from a given date

DAYS360 – Calculates interest using the 360 method

Errors

#NUM – An input is a number but is not a valid date.

#VALUE! – The input is something that doesn’t convert to a number such as “The other day” or “Yester-yester-day.”

Examples

Examples of the YEARFRAC function with different inputs and outputs
Examples of YEARFRAC

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.

Leave a Comment