Formula To Show Negative Hours On Timesheet

[SoulfulNeo]

Hi.

I've made a timesheet for myself at work. The data can be viewed by anyone so there isn't any personal information.

At the top of the spreadsheet, you'll see I have this:

Hours Worked: 144:00 ? Pay: ?1,454.40 ? Contract Difference: 00:00

If I force my hours to be under my contract, it's supposed to have a "-" before the hours to indicate I am under hours. If I am over hours, it'll simply give me the hours without the "-". I used to have the "-" working when the hours were negative, but this has since stopped working. Here is my formula if anyone can help?

="Hours Worked: "&TEXT(SUM(F12,F22,F32,F42),"[hh]:mm")&" ? Pay: "&TEXT(SUM(G12,G22,G32,G42),"?#,###.00") &" ? Contract Difference: "&IF(SUM(F12,F22,F32,F42)<SUM(H12,H22,H32,H42),"-","")&TEXT(ABS(SUM(F12,F22,F32,F42)-SUM(B12,B22,B32,B42)),"[hh]:mm")

Please find spreadsheet attached and thanks in advance for any help.

[Pete_UK]

The items in column H are text values, and so this part of your formula:

SUM(H12,H22,H32,H42)

will result in zero. Thus, the comparison with the values in column F will always be false, and so you won't get to see the "-".

Perhaps you should think about changing to the 1904-date system, where negative times are allowed.

Hope this helps.

Pete