GNOME Bugzilla – Bug 166413
String "4/2005" interpreted differently in Gnumeric and Excel
Last modified: 2006-04-11 17:35:26 UTC
Calculation returns Easter Holiday using Excel, returns December 27, 1899 for 2005 in Gnueric. Other information: This is the formula: =dollar(("4/"&A1)/7+mod(19*mod(A1,19)-7,30)*14%,)*7-6
Please attach a complete sheet with your calculation. Or at least tells us what the content of A1 is. Perhaps you cold also explain the purpose of the "dollar" invocation.
Created attachment 37085 [details] Excel spreadsheet for calculating holidays
Hmm, I guess XL interprets ("4/2005")/7 as a date divided by 7 while gnumeric interprets it as a fraction divided by 7. If we change that part of teh formula to read: =dollar(("1/4/"&A1)/7+mod(19*mod(A1,19)-7,30)*14%,)*7-6 in my locale, probably dollar(("4/1/"&A1)/7+mod(19*mod(A1,19)-7,30)*14%,)*7-6 in locale C one gets the `right' answer. Of course this calculations is so horrible (using dollar as a rounding function, encoding dates as strings that will be locale dependent) that I don't think this difference is worthwhile to investigate.
I wonder if the interpretation of "4/2005" is locale dependent (or version dependent) in XL. It sure is a horrible thing. It's far safer to replace ("4/"&A1) by date(A1,4,1).
I found another formula that seems to work with Gnumeric. The corrections you suggested did not return Sunday, but many days close to Easter Sunday depending on the year. This one seems much better! =floor("5/"&day(minute(A1/38)/2+56)&"/"&A1,7)-34 I don't understand the math, but it works well.
This is an obfuscated spreadsheet exercise. Anything that pastes little strings together and then interprets the result as a date is asking for trouble. How about I add an EASTERSUNDAY(year) function?
Just for the record, in Canadian locale, your formula =floor("5/"&day(minute(A1/38)/2+56)&"/"&A1,7)-34 happens to work for 2005 but fails miserably for 2004 or 2006, since day(minute(A1/38)/2+56) evaluates to 16 and 24 respectively but is interpreted as a month... You ae much better off with: =floor(date(A1,5,day(minute(A1/38)/2+56)),7)-34
Of course it seems that this formula gives the wrong date for the year 3100 and many others later. That is if http://users.chariot.net.au/~gmarts/easter.htm is correct. :-)
And http://www.assa.org.au/edm.html seems to give a nice algorithm to calulate the date.
Fixed in the development version. The fix will be available in the next major release. Thank you for your bug report. We now parse 4/2005 as a date. It is still not a great idea -- use the date function to construct dates instead -- but it's better than parsing as fraction.