Time formula (difference of predicted and actual time)

[deb]

I have a time formula in my spreedsheet. It is to calculate the difference
in a predicted time with the actual time. Time is entered as text eg. 14:00.
My formula is
=TEXT(A4-H4,"H:MM") OR
=TEXT(predicted time - actual time, "H:MM")

The formula works great if the actual time is prior to or equal to the
predicted time. However I cannot get a result for the time if the actual
time is later than the predicted time. I need the result to be -h,mm. If
you can help me accomplish this I would really appriceate it. Thank you

 

[Bob Phillips]

=IF(H4>A4,"-","")&TEXT(MOD(A4-H4,1),"H:MM")

 

[muddan madhu]

check it here

http://www.j-walk.com/ss/excel/usertips/tip051.htm

 

[David Biddulph]

If the answer should be -3 hours, your formula gives -21 hours, Bob.

Perhaps try
=IF(H4>A4,"-","")&TEXT(ABS(A4-H4),"H:MM")

 

[deb]

Thanks David, this works great for the late times but now it does not show
early times properly do you know what to do so that it will work either way.
Thanks again

 

[deb]

Thank you. Very simple solution

check it here

http://www.j-walk.com/ss/excel/usertips/tip051.htm

 

[deb]

I am so sorry my mistake. I'm not sure what I did wrong, but I reworked it
and the formula you provided worked perfect. Thank you so much