Negative dates or times are displayed as ####

[John Sheakley]

Negative dates or times are displayed as ####. The cell retains the
negative value. How can I display negative time as "h:mm AM/PM" in red or
with a negative sign? The cell should retain the negative value.

 

[Dave Peterson]

If you change the base date to 1904, then you'll see negative times.

Tools|options|calculation tab|check the 1904 date system.

But this'll change existing dates to about 4 years into the future.

You can adjust/fix the dates by putting 1462 in an empty cell.
Edit|copy it.
Select the cells with the "bad" dates.
Edit|Paste special|click subtract

 

[Bob Phillips]

That is because negative time is an anachronism.

If it is caused by time straddling midnight, just add 1 to the result to get
the true hours elapsed.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chrissy]

You cant do it. The cell can contain only one value and
if that is a negative number you cannot have it display
a different number.

There are some options but why do you want it to keep the
negative number?

I can get it to show the date you want but it will be a positive
number and do other things to get it to be used as a negative
number if you reference it from other cells.


Chrissy.

 

[pezza72]

Negative hours and the a reason for needing them

Chrissy,

One particular need to show a negative hour is for calculating Time Sheets, if a person works flexi hours. It is possible that someobody may work less hours than there contracted hours, therefore needing to display a negative figure.

I had this issue and solved it by choosing the 1904 option as described in a previous post.

HTH

Andy