Negative time value hh:mm:ss

J

Jelinek

i have two columns of data both time formats when i subtract one from the
other it displays ###### if it is a minus value is there away to get round
this i would like to view the diffrence even if it is a minus amount.

Thanks

Simon
 
C

Chip Pearson

You can do this by setting the date system to the 1904 reference.
Go to the Tools menu, choose Options, then the Calculation tab.
Check "1904 date system". Note, though, that this will cause all
existing dates to be 4 years off.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
N

Niek Otten

Or:

=IF(A1-B1<0,"-","")&TEXT(ABS(A1-B1),"hh:mm")

If you want to calculate with it, use just =A1-B1. It will not show the way you want, but further calculations are correct.
 
G

Gary''s Student

For negative dates/times pull-down:

Tools > Options > Calculation and check the 1904 date system
 
T

Toppers

Try 1904 type date format:

Tools/Options/Calculation tab/(bottom left corner)

Note that changing to the 1904 date system will cause all your
existing dates to be off by 4 years and a day

OR

F1: <end time>
F2: <start time>


As Excel shows negative times as ##### take largest from smallest and prefix
by negative sign.

=IF(F1<F2,TEXT(F2-F1,"-" & "hh:mm"),F1-F2)
 
T

Tom Ogilvy

You could go into tools=>options, calculation tab and change to the 1904 date
system (the default for the mac). This supports negative dates, but your
dates would be off by about 4 years.

or you could use a formula like

=if(A1>b1,"-"&Text(a1-b1,"hh:mm"),Text(b1-a1,"hh:mm"))
 
Top