Excel - negative time display

[asrisk]

If a formula results in a negative time (e.g. -3:14), excel converts it to "####", and helpfully provides a tool tip telling you it has done this.

Is there any way to disable this behaviour?

The odd thing is that, if I chart a range containing "valid" (positive) times and "invalid" negative ones (###) they are charted correctly, with the negative ones below the line. What's the problem with displaying them in the worksheet?

-------
I am not responsible for any "Sponsored Links" which may appear in my messages.

 

[TonyJollans]

Hi asrisk,

The behaviour is different if you change to the 1904 Date System (under Tools > Options > Calculation Tab) and negative times can be displayed.

Be aware, though, that negative dates may not appear as you expect so use with caution.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[asrisk]

Thanks for that Tony, the option works as you say, but has the unfortunate side-effect of catapulting all the dates on my worksheet 4 years and one day into the future! Oh well, I'll chalk it up as another XL quirk...

Cheers,
Andy.



-------
I am not responsible for any "Sponsored Links" which may appear in my messages.

 

[TonyJollans]

Oh, yes, it will do that as well

I have no idea why the one (which is really for Macs) shows negative times and the other doesn't. There really isn't a consistent system for properly representing dates and times before the baseline any particular system is using - and VBA does it slightly differently again.

Putting it down to quirks is about all you can do.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.