Created attachment 57039 [details]
what's shown on my screen
The date of today() shown on my screen is off-by-one, see the attachment. Doing something in the spreadsheet to cause recalculation or just retype the content of the fields don't seem to have any effect.
If I copy the cells and paste them to notepad, they show up right.
format as date format as numbers
“=now()” 02/14/12 10:43 AM 40951.45
“=today()” 02/14/12 40951.00
“02/13/12” 02/13/12 40950.00
The value is not always wrong. It just changes sometime while I keep the file open (not necessary overnight, sometimes middle of the day, but I can't tell exactly when). Usually exiting Calc and restarting it once or twice will get the correct value back.
Options/LibreOffice Calc/Calculate/Date is set to "01/01/1900 (StarCalc 1.0)"
Operating System: Windows XP
Eastern Standard Time
Thanks for bugreport
In 3.5.3 on Fedora 64 bit I can not reproduce. Bug still exist?
Yes, it still exists.
Build ID: 235ab8a-3802056-4a8fed3-2d66ea8-e241b80
Again, from PortableApps.com, on Windows XP
I opened the spreadsheet on Friday, it was fine as of noon today, but when I checked it a couple minutes ago (it is 5pm here now), the =today() field 'rolled back' to yesterday.
Thanks for additional testing. As I can understand, this bug appears randomly or on specific situation. In case it depends on time, please verify if it depends of time of day. May be it depends of time by GMT.
Please also attach ods with used formulas, may be it will help in reproducing this bug.
And pressing F9 to force re-calculate the whole sheet doesn't work ? AFAIK the 'today()' is only today when it re-calculates.
The code that implements today() - which gets turned into 'ocGetActDate' is here:
which looks sensible; it's hard to see how it could behave like that :-) you're sure your system time is not acting strangely too ?
Eike - some funkiness in the date formatting logic ? :-) or a localtime date format vs. a UTC 'today()' function ?
Created attachment 61948 [details]
The date shows as off-by-one when this file is saved.
As far as I can tell, it can happen anytime during the day. I didn't really systematically try it though. Just sometimes I noticed that the date is already off-by-one in the morning, sometimes it is late in the afternoon.
Pressing F9 doesn't help. Retyping the formula in the same cell or entering the formula in a new cell after it becomes off-by-one doesn't help either. Both cells will show the wrong date.
Re comment#5. My system looks normal as far as I can tell. And as I mentioned, "RightClick/Copy then paste into notepad" shows the right date in notepad, so I think the 'date' is right on my system.
Thanks for attachment
Please, verify on another computer if possible. Try also to move LibreOffice user profile in another place and verify if it helps.
I renamed PortableApps\LibreOfficePortable\Data\settings\user, and restarted LibreOffice. Still happening.
I will see if I can find another computer to try (but that would be the same as you trying on yours since the other computer won't be sharing anything with the computer I saw the problem on).
What strikes me is the odd setting
"Options/LibreOffice Calc/Calculate/Date is set to "01/01/1900 (StarCalc 1.0)""
That might be related, didn't check yet, though the difference should be 2 days not 1.
Any reason to have that date instead of the standard 12/30/1899?
Note that if you change it while in an already existing document all fixed dates will display with a difference, so that isn't advisable.
The document I originally encountered this problem was created in StarOffice (don't remember the version and may have gone through several versions of StarOffice), then I moved to LibreOffice so I had to set that the make the dates right. However, the screenshot and the small example was created in LibreOffice, long after I changed the setting (and restarted LibreOffice many times)
To me, the odd thing is that it is not the date doesn't go forward, it is that the date rolls back one day to yesterday sometime during the day.
Also, I don't know if this is a 'underlying-value' or 'display' problem. As you see in the screenshot, the date-formatted fields all display a day earlier (today() showed 02/13 on 02/14 while the field I manually typed in 02/13/12 showed 02/12/12).
And I tried other things this morning, in a spreadsheet that has the problem showing up (meaning today() shows up as 05/24): day(1) shows 2, day(now()) shows 25, and day(today()) also shows 25.
What code is specific to the problem I am seeing?
Is there some debugging feature I can turn on (in the prebuild release) to trace what's going on?
Can we get this out of UNCONFIRMED status? Is it confirmed as NEW? I don't want to mark as NEW with so many devs involved.
Thanks all :)
Have you tried changing the cell format? I noticed that cells B1 and B2 have 'number' and B3 has 'date'.
A similar 'wrong date showing' error was reported last month on the Dutch user mailing list. It turned out that changing the cell format solved the problem. (And changing it back yo the original format didn't reintroduce the problem, so it may have been something with conversion from older versions to the current. It wasn't reported as a bug as the user was happy with the solution.)
Could you try changing the cell format? It would help pinpointing the cause. I can't reproduce your problem on my machine, so your help is very much appreciated.
(In reply to comment #15)
> Have you tried changing the cell format? I noticed that cells B1 and B2 have
I will try to reproduce (haven't used it in a while. I was using it for time accounting so having the date flip at me at unexpected times makes me nervous) and see if changing the cell format helps.
> user mailing list. It turned out that changing the cell format solved the
> problem. (And changing it back yo the original format didn't reintroduce the
What I did before was closing the file and reopen it. Usually doing this once or twice would get back the right date. However, if I then left the file open for some time (usually a day or two), then the problem would show up again. And it was not that the date shown didn't go forward, rather, it went forward and then backward.
> problem, so it may have been something with conversion from older versions
> to the current. It wasn't reported as a bug as the user was happy with the
The test file I attached at comment#7 was created with the then current version (my original file was created using an older version of StarOffice)
(In reply to comment #16)
> (In reply to comment #15)
> > Have you tried changing the cell format? I noticed that cells B1 and B2 have
> I will try to reproduce (haven't used it in a while. I was using it for time
> accounting so having the date flip at me at unexpected times makes me
> nervous) and see if changing the cell format helps.
I look forwqard to your results.
By the way, what time zone are you in?
Bug 44286 (now fixed) described a possibly similar problem that was related to a bug in the timezone calculation.
> By the way, what time zone are you in?
> Bug 44286 (now fixed) described a possibly similar problem that was related
> to a bug in the timezone calculation.
Sounds like BZ:44286 just shows up when one types in the date, not some time later?
(In reply to comment #18)
> Sounds like BZ:44286 just shows up when one types in the date, not some time
It affects the value during display time, so may be well the cause for this today() case even if no odd historical timezones are involved.
Would be good if you could verify and/or dis/approve with the 4.0.2 release or upcoming 3.6.6 daily builds.
> Could you try changing the cell format? It would help pinpointing the cause.
> I can't reproduce your problem on my machine, so your help is very much
Reproduced and changing format didn't help.
The date looked correct 01:12 this afternoon, but rolled back to yesterday when I checked at 05:39. I changed the format to Currency, Scientific, Time, then back to Date, it was still showing 04/08.
Created attachment 77703 [details]
I will try to reproduce the problem on a machine with the timezone set to Eastern.
If that succeeds, the bug can finally be confirmed and I can have a look at the cause of the problem.
What is the latest LibreOffice version with which you have reproduced the problem?
(In reply to comment #22)
> What is the latest LibreOffice version with which you have reproduced the
Build ID: 235ab8a-3802056-4a8fed3-2d66ea8-e241b80
Don't know if it matters: it is the portable version from portableapps.com.
I left my test spreadsheet open yesterday with the wrong date shown and just (@06:22) did something to cause it to recalculate, the field updated, but is now showing 04/09/2013 (today is 04/10/2013).
> LibreOffice 126.96.36.199
> Build ID: 235ab8a-3802056-4a8fed3-2d66ea8-e241b80
> Don't know if it matters: it is the portable version from portableapps.com.
As Eike pointed out in comment 19, the problem has probably been fixed with version 4.0.2 and 3.6.6 (currently RC2, not yet released).
Your description of the behaviour fits the bug that Eike fixed quite well.
On http://www.libreoffice.org/download you will find a link to the portable version 4.0.2
I look forward to your findings with version 4.0.2
> As Eike pointed out in comment 19, the problem has probably been fixed with
> version 4.0.2 and 3.6.6 (currently RC2, not yet released).
Sorry, I thought that bug was different since it was for historical dates and it happened when people just typed the dates in (not afterwards).
> I look forward to your findings with version 4.0.2
ok, I will give it a try.
(In reply to comment #25)
> I look forward to your findings with version 4.0.2
Upgraded to Version 188.8.131.52 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3), had the file open for 3 days and the date is still correct.
(In reply to comment #27)
> Upgraded to Version 184.108.40.206 (Build ID:
> 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3), had the file open for 3 days and
> the date is still correct.
Well, that is good news :-)
The fix for bug 44286 fixed this one too.
I set thuis bug as solved and as a dulpicate of bug 44286.
Thank you for your help.
*** This bug has been marked as a duplicate of bug 44286 ***