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

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" <Jelinek@discussions.microsoft.com> wrote in message
news:C19017FD-9BE0-4A9D-93E7-2608ADE51A02@microsoft.com...
>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

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" <chip@cpearson.com> wrote in message news:%23E6XxaDTGHA.4752@TK2MSFTNGP10.phx.gbl...
> 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" <Jelinek@discussions.microsoft.com> wrote in message news:C19017FD-9BE0-4A9D-93E7-2608ADE51A02@microsoft.com...
>>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
>
>

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

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

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"))

--
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