Page 1 of 1

[Solved] NOW() produces negative numbers

PostPosted: Sun Apr 18, 2021 9:31 am
by Hilly Bill
Well this is interesting. All the NOW() serial numbers generated yesterday and today are negative. The date format displays 9/10/1778, and when the time format is updated, it moves backwards. =NOW()*-1, =ABS(NOW)), and =TODAY() however all produce correct results. What's going on here?

It's getting weirder. I opened a new Calc file to show you what's happening, but in the new file NOW() works as expected, with positive serial numbers and correct dates and times. The new file and a couple other existing files all work fine. What happened to this one file that makes it backwards?

And weirder still. Now only some of the values are negative. The file in question now has a NOW() function in four adjacent cells:

Code: Select all   Expand viewCollapse view
CELL      FUNC.           VALUE
I6        =NOW()          -44304.09
I7        =ABS(NOW())     44304.09
I8        =NOW()*-1       -44304.09
I9        =NOW()          44304.09


If NOW() is consistently producing negative numbers, shouldn't I8 be positive and I9 negative??

I guess you could say I'm pretty confused right now.

Re: NOW() produces negative numbers

PostPosted: Sun Apr 18, 2021 10:30 am
by RoryOF
Right click on I6 and select "Format Cells" from the popup. What format is selected?

Re: NOW() produces negative numbers

PostPosted: Sun Apr 18, 2021 11:53 am
by Hilly Bill
RoryOF wrote:Right click on I6 and select "Format Cells" from the popup. What format is selected?


I6 Format: Category -> Number; Format -> -1234.12; Preview-> 44304.19; Display-> 44304.19

I closed the document and then re-opened it, and yeah, now I6 is positive. The VALUE numbers from my first post all had the same format. Also, I was not aware that the sign of a number could be changed via formatting.

The thing that's buggin' me about this is the inconsistency. Any clues?

Re: NOW() produces negative numbers

PostPosted: Sun Apr 18, 2021 3:09 pm
by John_Ha
It sounds like a corrupted file though it could be a misunderstanding as it fixed itself.

NOW() produces a date and a time as "18/04/21 14:10" which, formatted as a number, displays as 44304.5907540509. It is updated every time the spreadsheet does a change. It is 44,304 days since the Calc default date start of 30 Dec 1899.

Your date of "9/10/1778" is equal to -44276 which, if you display only two year digits, appears as 9/10/78 which may mislead you into thinking it is 9/10/1978 or 9/10/2078. It is 44,276 days before 30 Dec 1899.

Copy the sheet to a new sheet and delete the old sheet. That may fix it.

Hilly Bill wrote:I was not aware that the sign of a number could be changed via formatting.

It cannot be.
 Edit: To be more precise:

Formatting cannot change the value of a number in the spreadsheet which is used in calculations.

Formatting can be used to display a hyphen character in front of a number's digits which a user may incorrectly interpret as a minus sign. The underlying number used in calculations is still correct. 

Re: NOW() produces negative numbers

PostPosted: Sun Apr 18, 2021 3:28 pm
by Villeroy
I think the number which was displayed with a minus sign, was a positive number actually, kind of "lying by number format".

Re: NOW() produces negative numbers

PostPosted: Sun Apr 18, 2021 10:35 pm
by Hilly Bill
I just played around with a range of over 2k cells with a few variations of =NOW(), displayed the entire date/time number, updated numerous times, and couldn't get it to misbehave like it was doing yesterday.

So thanks for your comments, John_Ha. You're right, it was confusing when the issue first started happening, because the format included YY. The misbehavior continued in just a few cells through several updates and formula changes. A search of the Calc forum for NOW found nothing.

I'm going with a partly corrupted file that fixed itself when it was closed and re-opened. And hoping it doesn't happen again!

Re: NOW() produces negative numbers

PostPosted: Sun Apr 18, 2021 11:44 pm
by Lupp
Hilly Bill wrote:... and couldn't get it to misbehave like it was doing yesterday.

You surely changed the format - and it's easy to get a lying format again.
Frankly: Formats are made for lying.
FunnyLying.ods
(13.08 KiB) Downloaded 29 times


The inconsistency you complain about is a fact. How spreadsheets work nowadays was mostly defined by those who weren't much interested in quality and clear concepts, but in selling licenses. Free software is expected to be compatible. It must therefore implement te same misbehaviour.

Hilly Bill wrote:I'm going with a partly corrupted file that fixed itself when it was closed and re-opened. And hoping it doesn't happen again!
I don't think your document is actually corrupted.

To get an more detailed idea of the issue:
A complete number format code (without colours an other toys of the kind) may consist of up to four semicolon-separated parts. I wouldn't talk about the fourth part at the moment. The other three parts apply to
(1) all numbers if no second part exist, positive numbers only otherwise, and in additoion to 0 if no third part is present.
(2) negative numbers.
(3) only zero.

(A) The really confusing thing is that the number itself is taken including its sign for the first position, but a prefixed sign, if present, isn't treated as a mathematical operator, but like any arbitrary character.
(B) But for the second part the thing that gets formatted is the absolute amount of the result in the cell.
And ...

Basically it's simple: Follow the path of maximum confusion, and you may be right.
Once again. All that nonsense wasn't invented by or for OpenOffice.

(A) should be "implemented" on the request of extremely lazy users, trying to avoid the need of typing two doublequotes if constant text should be included with formatting.
(B) is most likely due to the generally low level of mathematical education. In fact it's again a bit demanding now and then to distinguish values from their amounts - specifically when talking about them.

Conclusions:
Never include mathematical symbols as literals in number formats.
Always designate literals in format strings with the help of doublequotes.

Re: NOW() produces negative numbers

PostPosted: Mon Apr 19, 2021 12:24 am
by John_Ha
John_Ha wrote:
Hilly Bill wrote:I was not aware that the sign of a number could be changed via formatting.

It cannot be.

Lupp

Thanks for your example which suggests my "It cannot be" comment needs clarifying to "Formatting cannot change the value used in calculations but formatting can be used to display a hyphen character in front of the number's digits which a user may interpret as a minus sign." I have edited the post.

It had never occurred to me that a user might set up a format which included a preceding "hyphen character" so the displayed number is preceded by the hyphen character which then looks like, but is not, a minus sign. It took me ages to work out how you had got B5 to display the number PI as positive and B6 to display the same number PI as negative. A fraudster could wreak havoc with cell formatting so as to falsify the data and it would difficult to spot.

I get so frustrated when posters post screen images or, in this case, the cell formulae when the actual file is needed otherwise it is guesswork. It would be excellent if we could force users to post a file with their question - it would save so much wasted effort trying to guess all the obscure things that might have gone wrong. There are so many different ways to get different gaps between lines in Writer that you need the actual file to work out which has been used.

John_Ha wrote:It sounds like a corrupted file ...

I stick by my claim it is (almost) a corrupted file, albeit one which AOO didn't corrupt but which the user corrupted by using a bad format definition :crazy:

Clipboard01.gif