I need 19:00 - 7:00 to display 12:00

[Mally]

My sheet shows tool down times for a works project

A1 = 19:00 (in time format)
B1 = 7:00 (in time format)

C1 = =B1-A1 which displays #############
How do i minus time after 00:00 0:00

If i take 19:00(B1) from 19:01(C1) it gives me the correct answer of 0:01,
its just with numbers after 00:00.

[JE McGimpsey]

The WinXL default 1900 date system can't show negative times (times are
stored as fractional days, so 19:00 = 0.791666667). The (Mac default)
1904 date system (Tools/Options/Calculation, check the 1904 date system
checkbox) can, but the result would be -12:00

One solution would be to add 1 if the number to be subtracted is larger
than the number to be subtracted from. One can do that using XL's
implicit conversion of a boolean (TRUE/FALSE) value to 1/0 in math
operations:

=(B1<A1)+B1-A1

or, one could use the more obscure MOD() solution:

=MOD(B1-A1,1)



In article <DABF6F55-E58A-4C8B-800D-14058BAF73BB@microsoft.com>,
"Mally" <Mally@discussions.microsoft.com> wrote:

> My sheet shows tool down times for a works project
>
> A1 = 19:00 (in time format)
> B1 = 7:00 (in time format)
>
> C1 = =B1-A1 which displays #############
> How do i minus time after 00:00 0:00
>
> If i take 19:00(B1) from 19:01(C1) it gives me the correct answer of 0:01,
> its just with numbers after 00:00.

[Rutgers_Excels]

I think the easiest way to do this would be to take the absolute value of the answer. The absolute value of a negative number or a positive number will always return positive. ie |-10|=10 and |10|=10.

Try this....


=ABS(B1-A1)