Check Sheet Logo
Check Sheet

YEARFRAC

The YEARFRAC function calculates the fraction of a year between two dates. This is useful for calculating the duration of time periods that are less than a year. The function takes into account leap years and can use different day count conventions to calculate the fraction of a year.

Usage

Use the YEARFRAC formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:

=YEARFRAC(start_date, end_date, [day_count_convention])
Parameters:
  1. start_date (required):
    The start date of the period.
  2. end_date (required):
    The end date of the period.
  3. day_count_convention (optional):
    An optional parameter that specifies the day count convention to use when calculating the fraction of a year. If omitted, the default value of 0 is used, which corresponds to the US (NASD) 30/360 day count convention. Other possible values are 1 (actual/actual), 2 (actual/360), 3 (actual/365), 4 (European 30/360), and 5 (actual/365L).

Examples

Here are a few example use cases that explain how to use the YEARFRAC formula in Google Sheets.

Calculating the age of a person

The YEARFRAC function can be used to calculate the age of a person using their birthdate and the current date.

Calculating the length of a loan

The YEARFRAC function can be used to calculate the length of a loan in years, given the start and end dates of the loan.

Calculating the duration of an investment

The YEARFRAC function can be used to calculate the duration of an investment in years, given the start and end dates of the investment.

Common Mistakes

YEARFRAC not working? Here are some common mistakes people make when using the YEARFRAC Google Sheets Formula:

Incorrect date format

Users may input the dates in an incorrect format or with incorrect separators, leading to an error in the formula. Dates should be entered in the format YYYY-MM-DD.

Incorrect arguments

Users may input the arguments in the wrong order or use incorrect arguments, leading to an error in the formula. The start_date and end_date arguments must be valid dates, and the day_count_convention argument should be an integer between 0 and 4.

Incorrect date range

Users may input a date range that is invalid, leading to an error in the formula. The start_date must be earlier than the end_date.

Related Formulas

The following functions are similar to YEARFRAC or are often used with it in a formula:

  • DATEDIF

    The DATEDIF function in Google Sheets calculates the difference between two dates using a specified unit of measurement. This function is commonly used to calculate the length of time between two dates, such as the number of days, months, or years. The function is particularly useful when working with financial or project planning data.

  • YEAR

    The YEAR formula in Google Sheets returns the year of a specified date. This function is useful when you need to extract the year from a date for analysis or calculations. The YEAR formula takes one argument, which is the date from which to extract the year. The output is a four-digit number representing the year.

  • MONTH

    The MONTH formula is used to extract the month from a given date, returning it as a number between 1 and 12. This function is commonly used to summarize and analyze data that includes dates.

  • DAY

    The DAY function is used to extract the day of the month from a given date. It takes in a single argument, a date in a valid format, and returns the day of the month as a number between 1 and 31. This function can be useful for tasks such as sorting or filtering data based on the day of the month.

  • EOMONTH

    The EOMONTH function returns the date of the last day of the month that is the indicated number of months before or after the start date. This function is commonly used to calculate due dates, expiration dates, and other dates relative to a specified start date.

Learn More

You can learn more about the YEARFRAC Google Sheets function on Google Support.