A better YEARFRAC()

[chrisw76]

Hello!

Recently I was using the YEARFRAC() function again for the first time in a while and again noticed that it doesn't quite work as expected with the actual / actual basis (majority of my use cases). I'm not sure of the underlying logic to it, but it just doesn't seem to handle leap years very well or it is doing it to a much higher level of precision than required. That is, is may be taking into account the century / 400 not being a leap year. Whatever the case I'd expect that a formula like:

=YEARFRAC( DATE(2020, 1, 1),  YEARFRAC( DATE(2023, 12, 31) ) + 1, 1)

I'd expect it to return 4 but it returns 3.998357964 instead.

So, this is the formula I came up with to give me what I want. What it does calculate the beginning year fraction, the ending year fraction and any whole years in the middle and add them together. Here it is:

=LET(
    _sd, MIN(<cell_1>, <cell_2>),
    _ed, MAX(<cell_1>, <cell_2>),
    _yr1Days, DATE(YEAR(_sd), 12, 31) - DATE(YEAR(_sd), 1, 1) + 1,
    _yr2Days, DATE(YEAR(_ed), 12, 31) - DATE(YEAR(_ed), 1, 1) + 1,
    _midYrs, YEAR(_ed) - YEAR(_sd) - 1,
    _yr1Frac, (DATE(YEAR(_sd), 12, 31) - _sd + 1) / _yr1Days,
    _yr2Frac, (_ed - DATE(YEAR(_ed), 1, 1) + 1) / _yr2Days,
    IF(V25 > V26, -1, 1) * (_yr1Frac + _midYrs + _yr2Frac)
)

It probably could be a bit less verbose, but it's my style . Are there any mistakes / optimisations I missed?

Cheers, Chris W.

 

[RoryA]

=YEARFRAC( DATE(2020, 1, 1), YEARFRAC( DATE(2023, 12, 31) ) + 1, 1)

Did you mean:

=YEARFRAC( DATE(2020, 1, 1),  DATE(2023, 12, 31) + 1, 1)

 

[chrisw76]

Did you mean:

=YEARFRAC( DATE(2020, 1, 1),  DATE(2023, 12, 31) + 1, 1)

Yes I definitely - oops! Typing too quick again.

Cheers, Chris W.

 

[chrisw76]

And while I'm re-reading it the last line of my formula still had the cell references in it. It should be:

=LET(
    _sd, MIN(<cell_1>, <cell_2>),
    _ed, MAX(<cell_1>, <cell_2>),
    _yr1Days, DATE(YEAR(_sd), 12, 31) - DATE(YEAR(_sd), 1, 1) + 1,
    _yr2Days, DATE(YEAR(_ed), 12, 31) - DATE(YEAR(_ed), 1, 1) + 1,
    _midYrs, YEAR(_ed) - YEAR(_sd) - 1,
    _yr1Frac, (DATE(YEAR(_sd), 12, 31) - _sd + 1) / _yr1Days,
    _yr2Frac, (_ed - DATE(YEAR(_ed), 1, 1) + 1) / _yr2Days,
    IF(<cell_2> > <cell_1>, -1, 1) * (_yr1Frac + _midYrs + _yr2Frac)
)

I'll read my preview more carefully in future.

 

[MARK858]

That is, is may be taking into account the century / 400 not being a leap year

Why would 400 not be a leap year (other than when the Gregorian calendar started)?
The rules AFAIK for a leap year are...

The year must be evenly divisible by 4;

If the year can also be evenly divided by 100, it is not a leap year;
unless...
The year is also evenly divisible by 400. Then it is a leap year.

If it is divisible by 4000 it is not a leap year

 

[chrisw76]

Why would 400 not be a leap year (other than when the Gregorian calendar started)?
The rules AFAIK for a leap year are...

The year must be evenly divisible by 4;

If the year can also be evenly divided by 100, it is not a leap year;
unless...
The year is also evenly divisible by 400. Then it is a leap year.

If it is divisible by 4000 it is not a leap year

Yes you're right: if a century year is dividable by 400 then it is a leap year otherwise it isn't. What I was getting at here is that I'd expect this function to return a 4 but it doesn't with one possible reason the way a year is calculated.

I haven't been able to determine exactly what the YEARFRAC() function is doing and wasn't able to find published logic on-line for how it actually calculates to explain it.

Cheers, Chris W.

 

[MARK858]

Someone online has claimed the below

"If one uses Excel's YEARFRAC(date1,date2,1) function then Excel will use the basis as per the second parameter. This does not matter if date1 and date2 are in the same year, but it does matter if date1 is a leap year (e.g. 2020) and date2 is a non-leap year (e.g. 2021)"

Still looking to see if I can see anything official