Bug 46047 - today() off-by-one
Summary: today() off-by-one
Status: CLOSED DUPLICATE of bug 44286
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: x86 (IA32) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-02-14 09:00 UTC by chc.w1005+lo1202
Modified: 2013-11-28 22:16 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
what's shown on my screen (17.51 KB, image/jpeg)
2012-02-14 09:00 UTC, chc.w1005+lo1202
Details
example ods (8.28 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-05-22 04:10 UTC, chc.w1005+lo1202
Details
screenshot (67.11 KB, image/jpeg)
2013-04-09 21:49 UTC, chc.w1005+lo1202
Details

Note You need to log in before you can comment on or make changes to this bug.
Description chc.w1005+lo1202 2012-02-14 09:00:50 UTC
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.

LibreOffice 3.4.5  
OOO340m1 (Build:502)
(From PortableApps.com)

Options/LibreOffice Calc/Calculate/Date is set to "01/01/1900 (StarCalc 1.0)"

Operating System: Windows XP
Eastern Standard Time
Comment 1 sasha.libreoffice 2012-05-18 05:42:12 UTC
Thanks for bugreport
In 3.5.3 on Fedora 64 bit I can not reproduce. Bug still exist?
Comment 2 chc.w1005+lo1202 2012-05-21 13:57:02 UTC
Yes, it still exists.

LibreOffice 3.5.3.2 
Build ID: 235ab8a-3802056-4a8fed3-2d66ea8-e241b80

Again, from PortableApps.com, on Windows XP
Comment 3 chc.w1005+lo1202 2012-05-21 14:00:16 UTC
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.
Comment 4 sasha.libreoffice 2012-05-22 03:26:55 UTC
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.
Comment 5 Michael Meeks 2012-05-22 03:47:07 UTC
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:

http://cgit.freedesktop.org/libreoffice/core/tree/sc/source/core/tool/interpr2.cxx#n110

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 ?
Comment 6 Michael Meeks 2012-05-22 03:48:43 UTC
Eike - some funkiness in the date formatting logic ? :-) or a localtime date format vs. a UTC 'today()' function ?
Comment 7 chc.w1005+lo1202 2012-05-22 04:10:23 UTC
Created attachment 61948 [details]
example ods

The date shows as off-by-one when this file is saved.
Comment 8 chc.w1005+lo1202 2012-05-22 04:16:33 UTC
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.
Comment 9 chc.w1005+lo1202 2012-05-22 04:26:56 UTC
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.
Comment 10 sasha.libreoffice 2012-05-22 04:29:06 UTC
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.
Comment 11 chc.w1005+lo1202 2012-05-23 15:14:39 UTC
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).
Comment 12 Eike Rathke 2012-05-25 03:54:17 UTC
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.
Comment 13 chc.w1005+lo1202 2012-05-25 05:00:40 UTC
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?
Comment 14 Joel Madero 2012-11-21 17:57:03 UTC
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 :)
Comment 15 Winfried Donkers 2013-04-05 15:04:48 UTC
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.
Comment 16 chc.w1005+lo1202 2013-04-05 16:22:27 UTC
(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
> solution.)

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)
Comment 17 Winfried Donkers 2013-04-08 06:00:23 UTC
(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.
Comment 18 chc.w1005+lo1202 2013-04-08 14:18:08 UTC
> By the way, what time zone are you in?

Eastern.

> 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?
Comment 19 Eike Rathke 2013-04-08 16:54:27 UTC
(In reply to comment #18)
> Sounds like BZ:44286 just shows up when one types in the date, not some time
> later?

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.
Comment 20 chc.w1005+lo1202 2013-04-09 21:46:39 UTC
> 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.

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.
Comment 21 chc.w1005+lo1202 2013-04-09 21:49:26 UTC
Created attachment 77703 [details]
screenshot
Comment 22 Winfried Donkers 2013-04-10 05:43:52 UTC
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.

@chc.w1005+lo1202:
What is the latest LibreOffice version with which you have reproduced the problem?
Comment 23 chc.w1005+lo1202 2013-04-10 12:39:47 UTC
(In reply to comment #22)
> What is the latest LibreOffice version with which you have reproduced the
> problem?

LibreOffice 3.5.3.2 
Build ID: 235ab8a-3802056-4a8fed3-2d66ea8-e241b80

Don't know if it matters: it is the portable version from portableapps.com.
Comment 24 chc.w1005+lo1202 2013-04-10 22:26:45 UTC
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).
Comment 25 Winfried Donkers 2013-04-11 06:15:00 UTC
> LibreOffice 3.5.3.2 
> 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
Comment 26 chc.w1005+lo1202 2013-04-11 10:47:00 UTC
> 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.
Comment 27 chc.w1005+lo1202 2013-04-15 22:02:34 UTC
(In reply to comment #25)

> I look forward to your findings with version 4.0.2

Upgraded to Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3), had the file open for 3 days and the date is still correct.
Comment 28 Winfried Donkers 2013-04-16 05:57:19 UTC
(In reply to comment #27)
> Upgraded to Version 4.0.2.2 (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 ***