Error in subtracting times

  • Thread starter Thread starter BadBoy
  • Start date Start date
B

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.
 
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
 
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"))
 
Back
Top