Search code examples
dategoogle-sheetsmath

Is there a built-in Google Sheets formula to calculate the **exact number** of fractional years between two dates?

I need to calculate the exact number of years between two dates in Google Sheets.

The most obvious methods (below) don't return the exact number of years.

  • number of days between the start and end divided 365, 354, or 364.25
  • The YEARFRAC function

To get the exact number of years you have to add 3 sub parts:

  1. The fraction of a year the start date covers of the start year (start date to end of year)
  2. The whole number of full years between the start and end date
  3. The fraction of a year the end date covers of the end year (first day of year to end date)

This is because the start date and/or end date could be a leap year which changes what each day in the year is as a fraction of the year.

  • 2020-02-16 is a leap year and the divisor is 365
  • 2021-02-16 is not a leap year and the divisor is 364

So both dates are a different fraction of the year they are.

I will explain with an example. Let's say:

  • start date = 2020-02-16
  • end date = 2022-05-12

The various formulas above give these results:

  • number of days / 365 = 2.23561643835616
  • number of days / 364 = 2.24175824175824
  • number of days / 364.25 = 2.24021962937543
  • YEARFRAC with 0 for day_count_convention = 2.23888888888889
  • YEARFRAC with 1 for day_count_convention = 2.23322104947975
  • YEARFRAC with 2 for day_count_convention = 2.26666666666667
  • YEARFRAC with 3 for day_count_convention = 2.23561643835616
  • YEARFRAC with 4 for day_count_convention = 2.23888888888889

But, if we break it down, we see those results are not right. Example:

  • The start year (2020) is a leap year with 365 days. From the start date (2020-02-16) to the end of that year (2020-12-31) there are 319 days. So that is 0.873972602739726 of a year.
  • There is exactly 1 whole year between the start and end date (all of 2021)
  • The end year (2022) is not a leap year and has 364 days. From the start of that year (2022-01-01) to the end date (2022-05-12) there are 131 days. So that is 0.35989010989011 of a year.

If you add all 3 parts, you get 2.23386271262984.

If we shifted the start/end dates down exactly by one year, we'll see that the exact number of years between them would be different than above:

  • start date = 2021-02-16
  • end date = 2023-05-12

Then:

  • number of days / 365 = 2.23287671232877
  • number of days / 364 = 2.23901098901099
  • number of days / 364.25 = 2.23747426218257
  • YEARFRAC with 0 for day_count_convention = 2.23888888888889 (no change)
  • YEARFRAC with 1 for day_count_convention = 2.23287671232877
  • YEARFRAC with 2 for day_count_convention = 2.26388888888889
  • YEARFRAC with 3 for day_count_convention = 2.23287671232877
  • YEARFRAC with 4 for day_count_convention = 2.23888888888889 (no change)
  • exact number:
    • start year fraction = 318 / 364 = 0.873626373626374
    • whole years = 1
    • end year fraction = 131 / 364 = 0.35989010989011
    • total = 2.23351648351648

So, I am wondering, is there a built-in Google Sheets formula I can use to give me this exact number with a start/end date input? I know I can create named formulas and all that but I'm looking for a built-in, already available formula.

Solution

  • no, there is not a built in formula to get this answer