I am trying to put together a "time sheet" worksheet.

Column A is start time formatted in hh:mm
Column B is end time formatted in hh:mm
Column C has a formula Bx-Cx and is formatted as hh:mm

Now for the problem:

If the person works exactly 4 hours I want to make "comp" time for that day
0
If the person works more than 4 hours I want to calculate the "comp" time as
hours_worked-4
If the person works less than 4 hours I want to calculate the negative
"comp" time as 4-hours_worked

I'm running into a problem with the calculations

for example:

Column A1 = 8:00 AM
Column B1 = 2:00 PM
Column C1 = 6:00 (Formula is B1-A1)
Column D1 = C1-4

I "expect" D1 to show 4 instead it shows 3.78.

Column D is formatted as a number with two decimal places. If I try any of
the time formats I get an invalid cell (all # signs)