YEARFRAC Function – Google Sheets

The YEARFRAC function takes two dates and returns the years between them, including fractions of a year. 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 must use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.

Function help dialogue for the YEARFRAC function

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.

Video Tutorial

Related Functions

DATE – Takes separate year, month, and day values and returns them as a date

DATEDIF – Calculates the difference between two days in years, days, and months

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.”

YEARFRAC Function Examples

Example 1 – Different Count Conventions

The function has five possible values for the day_count_convention. Their outputs are slightly different, as shown in the examples below and the linked Sheet.

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

When using YEARFRAC in Google Sheets in a general situation, such as calculating someone’s age, the actual/actual method is the clearest choice. This method uses the actual days elapsed divided by the actual days in a year.

Example 2 – Calculating Age with YEARFRAC

When combined with the TODAY function, we can calculate age.

Using YEARFAC and TODAY to calculate age in Google Sheets
Calculating Age

=YEARFRAC(A2,TODAY(),1)

The result will update every subsequent day you open the spreadsheet as the value of TODAY will change.

Example 3 – Calculating Age in Whole Years

Let’s add to our last example by converting the result of the formula to an integer. This is done using the INT function.

Calculating age with INT and YEARFRAC
Calculating Age and Rounding Down

=INT(YEARFRAC(A2,TODAY(),1))

Adding the INT function allows us to provide age as a whole number.

Example 4 – YEARFRAC in Finance

We used the actual/actual year convention setting of 1 in examples 2 and 3 because it counts the actual days. However, interest calculations often use a 360-day year, with each month having 30 days. Let’s use the 0 year day_count_convention for that.

The YEARFRAC function being used to calculate interest
Calculating Interest

=YEARFRAC(D2,E2,0)*C2*A2

We calculated the interest incurred on this loan with only one formula using the principal amount, rate, and two dates.

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