Google Docs'n'Spreadsheets Problem

[tamaranth]

I'm having a problem with date conversion in a spreadsheet that was created in OpenOffice, saved as Excel format and then uploaded to Google Docs&Spreadsheets, for the purpose of collaboration.

As you will see from the screenprint under the cut, the dates in it are all quite a long time ago.

In Google Docs&Spreadsheets, they are showing up as 3 days later than the dates that were entered. E.g. 10th April 1701 instead of 7th April 1701.

When the spreadsheet is saved as Excel and the columns are formatted as Date, the correct dates show up.

I'm scanning their help system and trying to join the support group -- but if anyone can help / suggest anything that would be great.



Click on image to see those dates in detail ...

Comments

[dougs.livejournal.com]

My suspicion is that the two systems are making different provision for leap days in 1900, 1800 and 1700 -- see, for example, the description of the way Excel handles dates on this page (http://en.wikipedia.org/wiki/Epoch_(reference_date)#Trivia).

[drplokta]

Yes, and also see this. Lotus 1-2-3 assumed all years divisible by four were leap years, which works between 1901 and 2099, and anything that wants to be fully Lotus-compatible must perforce do the same.

However, that would only account for two days, not three -- extra non-existent leap days in 1800 and 1900.

[tamaranth.livejournal.com]

That's a really interesting link. I think I might have to design an Experiment!

I'm sure I'm not the only person having issues with historical timelines in spreadsheets. I wonder if there's a simple, functional alternative? (Please don't say 'text-only' -- we're using it to fiddle with dates and timings, so there are formulae!)

[tamaranth.livejournal.com]

thank you for this -- that Wikipedia page is most useful! (Though surely it won't be thinking about 1700, given that dates start in 1701?) (On the other hand, it might be making false allowances for 2000, not a leap year ...)

[autopope.livejournal.com]

Practical query: is the original OpenOffice spreadsheet available? Because Google Docs and Spreadsheets should be able to read it, I hope? (I know Google docs can cope with OASIS OpenDoc files so I'm assuming their spreadsheet app can do likewise ...)

[tamaranth.livejournal.com]

It's a while since I created the original spreadsheet (last year!) but I have a feeling I couldn't get it to upload in OO format. Mind you, a month is a long time in software ... I'll give it another try in various formats, and see if I can work out some pattern.