Negative time value hh:mm:ss
 

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

Negative time value hh:mm:ss
 

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


"Jelinek" wrote in message
...
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

Negative time value hh:mm:ss
 

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.

--
Kind regards,

Niek Otten

"Chip Pearson" wrote in message ...
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


"Jelinek" wrote in message ...
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

Negative time value hh:mm:ss
 

For negative dates/times pull-down:

Tools Options Calculation and check the 1904 date system
--
Gary's Student


"Jelinek" wrote:

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

Negative time value hh:mm:ss
 

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)



"Jelinek" wrote:

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

Negative time value hh:mm:ss
 

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(A1b1,"-"&Text(a1-b1,"hh:mm"),Text(b1-a1,"hh:mm"))

--
Regards,
Tom Ogilvy


"Jelinek" wrote:

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