Error in subtracting times

[BadBoy]

Good morning all,

Perhaps you can help with a question;

When subtracting times using the time format, and the answer is a negative
time, the answer shows like this ##########

Cell A1 20:00 - Cell A2 50:00 = Cell A3 #######

Is the any way to fix this?

Thank you in advance.

 

[Jacob Skaria]

What do you mean by -ve time.?

Try the below
=ABS(A1-A2)
and format to [h]:mm

="-" & ABS(A1-A2)
to display as decimal hours -30:00 as -1.25

If this post helps click Yes

 

[David Biddulph]

Good afternoon.
In general, Excel will not display negative dates or times. One option is
Tools/ Options/ Calculation/ 1904 date system, but beware of difficulties if
you try to transfer data to and fro between the 1900 (default) and 1904 date
systems.
Another option, if you don't mind the result being text rather than a
number, is
=IF(A3>A2,"-"&TEXT(A3-A2,"[h]:mm"),TEXT(A2-A3,"[h]:mm"))