OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calc Dates in V2.2.1

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
gizz
General User
General User


Joined: 29 Jul 2004
Posts: 23
Location: Australia

PostPosted: Wed Aug 29, 2007 10:06 pm    Post subject: Calc Dates in V2.2.1 Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Aug 30, 2007 1:31 am    Post subject: Reply with quote

Did you enter the values manually or did you paste cell values from Excel or some other program?
Back to top
View user's profile Send private message
gizz
General User
General User


Joined: 29 Jul 2004
Posts: 23
Location: Australia

PostPosted: Thu Aug 30, 2007 3:14 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jwr
OOo Advocate
OOo Advocate


Joined: 06 Sep 2006
Posts: 367
Location: Germany

PostPosted: Thu Aug 30, 2007 4:06 am    Post subject: Reply with quote

gizz wrote:
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.


Check the default date format used by Calc.
Go to Tools > Options > OpenOffice.org Calc > calculate

The standard zero setting value (in my OOO 2.0.3) is 30.12.1899
The alternative 1 zero setting value (in my OOO 2.0.3) is 1.1.1900 (StarCalc 1.0)
The alternative 2 zero setting value (in my OOO 2.0.3) is 1.1.1904 (probably Excel ?)

Maybe you are using one of the alternative settings with 2 days offset.

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Aug 30, 2007 4:54 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
jwr
OOo Advocate
OOo Advocate


Joined: 06 Sep 2006
Posts: 367
Location: Germany

PostPosted: Thu Aug 30, 2007 5:24 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
gizz
General User
General User


Joined: 29 Jul 2004
Posts: 23
Location: Australia

PostPosted: Mon Oct 01, 2007 9:17 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group