After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 166413 - String "4/2005" interpreted differently in Gnumeric and Excel
String "4/2005" interpreted differently in Gnumeric and Excel
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: Analytics
1.4.x
Other All
: Normal minor
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2005-02-06 02:01 UTC by Wayne Hammond
Modified: 2006-04-11 17:35 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
Excel spreadsheet for calculating holidays (16.00 KB, application/vnd.ms-excel)
2005-02-07 03:42 UTC, Wayne Hammond
Details

Description Wayne Hammond 2005-02-06 02:01:05 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
Comment 1 Andreas J. Guelzow 2005-02-07 02:14:46 UTC
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.
Comment 2 Wayne Hammond 2005-02-07 03:42:03 UTC
Created attachment 37085 [details]
Excel spreadsheet for calculating holidays
Comment 3 Andreas J. Guelzow 2005-02-07 05:54:22 UTC
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.
Comment 4 Morten Welinder 2005-02-07 15:18:54 UTC
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).
Comment 5 Wayne Hammond 2005-02-07 23:07:27 UTC
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.
Comment 6 Morten Welinder 2005-02-08 00:05:54 UTC
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?
Comment 7 Andreas J. Guelzow 2005-02-08 01:50:49 UTC
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
Comment 8 Andreas J. Guelzow 2005-02-08 02:02:56 UTC
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. :-)
Comment 9 Andreas J. Guelzow 2005-02-08 02:07:39 UTC
And http://www.assa.org.au/edm.html seems to give a nice algorithm to calulate
the date.
Comment 10 Morten Welinder 2006-04-11 17:35:26 UTC
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.