Calc Dates in V2.2.1

gizz · Posted: Wed Aug 29, 2007 10:06 pm Post subject: Calc Dates in V2.2.1

There seems to be a problem with the date format in this new version V2.2.1.
If I format a column for Date and then enter a number of dates into it - then save the file and close it. When I reopen it the dates have gone back 2 days. Some dates then keep going back 2 days every time I reopen the file.

This is now happening on a number of my spreadsheets. I have had to change the format to text to keep the dates correct.

Date Format is set to English - Australia

Any solutions would be appreciated.

Villeroy · Posted: Thu Aug 30, 2007 1:31 am Post subject:

Did you enter the values manually or did you paste cell values from Excel or some other program?

gizz · Posted: Thu Aug 30, 2007 3:14 am Post subject:

Excel is banned on my new PC.

No it is happening on two new spreadsheets that I have built since V2.0.0 came out. But I have only noticed it happening on the recent upgrade. The cell values were entered manually. If I enter a date as 5/8 it formats to say 5-Aug-07. Save > Close then reopen and the date will be showing 3-Aug-07.

Thanks for taking an interest.

jwr · Posted: Thu Aug 30, 2007 4:06 am Post subject:

Villeroy · Posted: Thu Aug 30, 2007 4:54 am Post subject:

I can not reproduce this behaviour with my slightly older version 2.2.0 on Ubuntu 5.10. It is the original Sun version, not a compilation made by Debian/Ubuntu or any other third party.

How I tried to reproduce the issue:
Created a blank new spreadsheet from scratch, not from template.
Formatted the first column DD/MM/YY with number format locale English(Australian).
Entered 3/8 in A1 and a zero in A2. A2 indicates the currently used zero-date 30/12/1899 (see Tools>Option>Calc>Calculation).
Saved as date_aus.ods, reloaded.
Restarted the application and loaded date_aus.ods.
The date values remain unchanged.

Your offset by +2 days indicates that something happens with the zero-date.
Make a copy of the file in question and proceed with that one, just in case ...
If Tools>Option>Calc>Calculation>Date is set to 01/01/1900 set it back to default 30/12/1899. This should substract 2 days from all constant date values. This is a per-document setting.
If the default is already set:
Put a number 2 into a cell, copy the cell, select the dates in question, call Edit>PasteSpecial and check "Numbers" with option "Substract". This will substract 2 from all constant values. Formulae will be changed to =(<old formula>)-2. Now the question is, what happens when you reopen the file?

jwr · Posted: Thu Aug 30, 2007 5:24 am Post subject:

Another possible fix is to temporarily rename your OOo user settings directory.
For those who don't know where the user settings are:
C:\Documents and Settings\**user**\Application Data\OpenOffice.org2\user
Under Unix, this is the hidden directory called .openoffice.org in your home directory.

The next time you run OOo, it will regenerate this directory using default values. This will cause all your user settings to be lost, but you can restore the values by deleting the new directory and changing the old directory back to its original name.

Regards -Hans-
_________________
Windows 2K, OOo-2.3.0, MS-IE-6.0, Firefox-1.0.4, MS Office 97,
Say NO to MS OpenXML: http://www.noooxml.org/petition

gizz · Posted: Mon Oct 01, 2007 9:17 pm Post subject:

Thanks jwr and Villeroy for your replies. The problem seemed to contain itself to only two files that were updated with new versions of OO so it must have just been this caused the problem. No other files have been affected since.

Thanks
Gizz