search

adobe / json-formula

Query language for JSON documents
http://opensource.adobe.com/json-formula/
Apache License 2.0
19 stars 8 forks source link

datedif "yd" and leap years #127

Closed Eswcvlad closed 7 months ago

Eswcvlad commented 7 months ago

In the implementation the following expression returns 8:

datedif(datetime(2000,2,29), datetime(2002,3,8), "yd")

While LibreOffice Calc and Excel in such cases return 7 (i.e. Feb 29th is treated as Mar 1st).

When I was implementing it myself, I've used a bit of a different logic, that instead of bumping the year on the original date, I bumped it on the original date with day changed to 1 (so that it is still valid) and then added original days separately. So in the end it becomes Mar 1st and also returns 7.

Not sure, if this is really important, but since it came from OpenFormula might be better to match behavior from Excel.

JohnBrinkman commented 7 months ago

Hmm. I don't know what the right answer is. The description for "yd" says years are ignored -- so you might interpret that to indicate leap years are ignored. But in Excel, leap year does impact the outcome. e.g.

=DATEDIF("1999/02/15", "2003/03/15", "yd") returns 28

and

=DATEDIF("2000/02/15", "2003/03/15", "yd") returns 29

Our implementation agrees with these results... I'm reluctant to change the implementation without understanding what the rule(s) really are?

Eswcvlad commented 7 months ago

Looks like Excel uses the year from start_date...

=DATEDIF("1997/02/15", "2000/03/15", "yd") returns 28

and

=DATEDIF("1997/02/15", "1999/03/15", "yd") also returns 28

Our implementation agrees with these results... I'm reluctant to change the implementation without understanding what the rule(s) really are?

Agreed, OpenFormula doesn't mention leap years at all for DATEDIF... LIbreOffice description also doesn't help much...

Number of whole days when subtracting years from the difference of Start date and End date.

JohnBrinkman commented 7 months ago

Looks like Excel uses the year from start_date...

Hmm. But I can get different results in Excel by changing the end date:

=DATEDIF("2000/02/29", "2000/03/8", "yd") returns 8 while =DATEDIF("2000/02/29", "2002/03/8", "yd") returns 7

Again, until/unless we can understand the exact rule, I am reluctant to change the implementation.

JohnBrinkman commented 7 months ago

Closing until we get a clear understanding of the algorithm to follow