*------- Original Message --------* *Subject: * [users] Date issue on open office.org calc *Date: * Wed, 11/Jul/2007 10:20:54 PM +0550 *From: * Harold Fuchs <[EMAIL PROTECTED]> *To: * [email protected] *CC: * Jonathon Coombes <[EMAIL PROTECTED]>
> Lasantha Marian wrote: >> This problem has been there since the beginning of OOo 2.2.x (starting >> with RCs') and I have brought the attention of the developers also to >> the same by this bug report >> (http://www.openoffice.org/issues/show_bug.cgi?id=75118). Nothing >> positive has happened yet. OOo 2.0.x series is immune to this problem. >> >> The circumstances under which I found the problem were different to how >> you may how found the problem. But I too have done various test and can >> confirm that this problem occurs under following conditions. >> >> Any sheet having date cells imported from databases (MSSQL or >> PostgreSQL) or pasted from result sets given by such programs, can have >> the problem. When the cell pointer is focussed at the cell (having such >> dates) the date rolls back/forward by one date. >> >> Jonathan, your point I too have observed and I found that this problem >> and the behavior you mentioned are mutually exclusive. >> >> I think attention of OOo developers should be immediately paid to this >> as date cells are almost unavoidable in Spreadsheets and accurate >> representation of dates are essential under any circumstance. >> >> Lasantha. >> >> Jonathon Coombes wrote: >> >>> <div class="moz-text-flowed" style="font-family: -moz-fixed"> >>> On 05/07/2007, at 12:35 PM, Gary Dale wrote: >>> >>> >>>> Guan Kiet Yeo wrote: >>>> >>>>> Dear Sir, >>>>> >>>>> I'm using version 2.2.1 of OpenOffice.org Calc. My >>>>> problem is the date after I save and then re-open the >>>>> file, the date that I have key in the worksheet will >>>>> totally run out. It will change to 2days earlier. >>>>> >>>>> For example, I key in 07/01/07, it will show >>>>> 06/29/07. Please help me to solve it out. >>>>> >>>>> >>>>> >>>>> Thanks & Best Regards, >>>>> >>>>> Jet Yeo >>>>> +63 920 583 9051 >>>>> +6012-260 2606 >>>>> >>>>> >>>>> >>>> I think that no one has replied because they don't understand what >>>> the problem is that you are having. When you key in a date, it >>>> displays properly unless there is something else involved. Can you >>>> explain your problem more completely? >>>> - does this happen in any worksheet, even a new, blank one? >>>> - does it happen in any cell formatted as a date? >>>> - does it happen immediately or do you have to close the worksheet >>>> and re-open it? >>>> >>> Have a look under the Tools -> Options -> OpenOffice.org Calc -> >>> Calculate area and you will see a section labeled 'Date'. My guess is >>> that you had entered your dates and then this area was changed from >>> default to one of the other two values either in your application or >>> by using a different install of OpenOffice.org to your original. Just >>> note that if you enter a date and change these Date settings, the >>> dates will change. You have to reenter existing dates to get them >>> accurate, but once changed there will be no issue if you keep it >>> consistent. Also note that it is dangerous to use the non-default >>> values here if you intend to share these or use them with other >>> versions set for default in the future. >>> >>> Regards >>> Jonathon >>> >>> -------------------------- >>> Jonathon Coombes >>> OOo Knowledgebase:- http://mindmeld.cybersite.com.au >>> http://www.cybersite.com.au >>> http://www.training4linux.com >>> </div> >>> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [EMAIL PROTECTED] >> For additional commands, e-mail: [EMAIL PROTECTED] >> >> >> > Could this be connected to the fact that Excel wrongly believes that > 1900 was a leap year? > Dates are stored as the number of dates since some "epoc" date, often > 1/1/1900. Using this we see that, using *Excel* > > 1 Jan 1900 is stored as 0 (zero days *after* 1/1/1900) > 31 Jan 1900 ........... 30 > 1 Feb 1900 ........... 31 > 28 Feb 1900 ........... 58 > > At this point things start going awry. Excel believes there is a 29 > Feb 1900 and, if it saw this date, *would* store it as 59. So we get > (*wrongly*) > > 29 Feb stored as 59 > 1 Mar as 60 > 2 Mar as 61 and so on. > > Of course, because of this phantom 29 Feb, > > 59 should be 1 Mar > 60 should be 2 Mar > 61 should be 3 Mar and so on. > > Now, OpenOffice Calc *knows* this so, when it sees a .ods (Calc) file > with a date 61 it shows it as 3 Mar but when it sees a .xls (Excel) > file with the *same* value it adjusts to show it as 2 Mar. > > Given all this, it seems possible that Calc *believes* (wrongly) that > the OP's file came from Excel and therefore that it needs to adjust > any stored date after 28 Feb 1900 (=58) by subtracting 1 from it. This > would account for the observed behaviour. > > No solace for the OP unfortunately but at least a rational (?) > hypothesis. > > Just a thought ... > Harold, I too can confirm part of your rationale based on few further tests that I did yesterday, this occurs only when the file is one with .xls extension and originally generated by Excel.
