Times that are negative?

[Elegidos]

Hi everyone,

I've bumped into a problem which I can't resolve. It's about subtracting time from time.

Example:

1) A1 with 1:41:03 (this isn't time, it's how long something takes to do)

2) B1 with 2:00:00 (this is 2 oclock in the moring, this is real clock time

3) C1 with 1:00:00 (this is 1 oclock in the moring, this is real clock time

4) Cell F1 has =B1 - A1 which equals 0:18:57 (this is real clock time)

Heres the problem

5) Cell F2 has =C1 - A1 which equals ########

(Dates and times that are negative or too large display as ########, this is the information that appears when mouse hovers the cell F2)

Any help on how to subtract time when Time1 < Time2?


Thanks

[DonkeyOte]

On 1900 date system you can't display negative time as a time value.

You "can" either

a) switch to 1904 date system --- but don't would be my advice

b) convert times to decimal hours by * result by 24 and display as Number/General (ie -0.5 for -00:30)

c) display as a text string, ie use ABS to conduct the calc such that it is positive and prefix with "-" if C1 < A1

[zbor]

nothing... : )

[oldchippy]

Hi,

I'm not sure whether zbor was going to say something, but how about

=MOD(C1-A1,1)

[DonkeyOte]

OC, I'm guessing as answer isn't actually specified, but I don't think that would work here unfortunately... ie I suspect that given:

eg

A1: 02:00
C1: 01:00

OP wants the difference to display as -01:00 rather than showing 23:00 (ie per MOD)

The remainder below is based on that rather large assumption

If you want to display difference as -1 hour giving impression of time you would I believe need something along the lines of :

=REPT("-",C1<A1)&TEXT(ABS(C1-A1),"hh:mm")
(this would be option c as outlined earlier)

Alternatively if you need the value as number for latter calcs I see no other real option that to utilise decimal hours

=24*(C1-A1)
formatted as General - will show as -1

Alternatively you could multiply by 1440

=1440*(C1-A1)
and apply a 00\:00 custom format

the last would give impression of time value though underlying value is of course not a time value (ie -60)

[zbor]

Hi,

I'm not sure whether zbor was going to say something, but how about

=MOD(C1-A1,1)

Actually, I write same that solution. However, I realise it doesn't match to users issue so I deltete it before I lead him to the wrong path

[DonkeyOte]

Actually guys, in hindsight I think you're both on the money and I am the one barking up the wrong true... the OP states C1 is time and A1 is duration... thereby implying result should itself be a true time value (ie 23:00) and not a duration value as my posts imply.