Profile

tamaranth: me, in the sun (Default)
tamaranth

September 2017

S M T W T F S
     12
34 56 789
101112131415 16
1718 1920212223
24252627282930

Custom Text

Jan. 4th, 2007

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.

screenshot )
cute news item.

Now look at the Related BBC Links on the right of the page.
Further to my earlier post about eldritch date conversion in Google Docs'n'Spreadsheets, I am creating some test spreadsheets with various apps. (See under cut.)

So, where is the setting in Microsoft Excel 2003 which says "Yes, I do believe that a date before 1st Jan 1900 can be formatted as a date?"

Grrrrrrrrrrrrrrrrrrrrrrrrrrrrrr.
'History is Bunk,' sez Excel )

(no subject)

Jan. 4th, 2007 05:00 pm
tamaranth: me, in the sun (Ariel)
I have created the same simple 9-cell spreadsheet in Excel, OpenOffice and Google. (Basically, it tested what happened in date and numeric formats if I typed "07/04/1701", "04/07/1701", "31/12/1899" and "01/01/1900".)

I have saved / exported each spreadsheet to each of the other formats (except that Excel is too snobbish to export to OpenOffice).

I conclude that, individually, they each have problems. (Excel doesn't believe in pre-1900; Google doesn't believe in Europe; OpenOffice thinks that everyone will like it if it tries really really hard.)

But put any two together, and @#$£%!WTF chaos ensues.

Have sent OpenOffice-saved-as-Excel file to collaborator. If we want it online, I suspect I shall have to type out the calculated values by hand.

And it's true: there was No New Year's Eve in 1899. Unless, y'know, you were running OpenOffice.
Tags:

Expand Cut Tags

No cut tags

Style Credit