Excel time sheet trouble after midnight

I have a problem with my time sheet not giving me the proper value after midnight.. here is the code im using... =TEXT((E4-B4)+(I4-F4),"h:mm")

Originally Posted by Portuga

Try the ABS formula. It will give you the absolute difference between 2 times. (Format result cell as hh:mm)

=ABS(A1-A2)

Or

the MOD formula

=MOD(A1-A2,1)

I don't believe ABS is appropriate. If you have start time of 22:00 and end time of 06:00 then ABS would give a result of 16:00 rather than 8:00.

MOD is good though, i.e.

=MOD(E4-B4,1)+MOD(I4-F4,1)

or

=E4-B4+(B4>E4)+I4-F4+(F4>I4)

in either case format result cell as h:mm [that's a better approach than using the TEXT function which gives a text string result]