Seperating overtime from regular hours

Matlock,

The formula is working correctly but you can't see that because of the custom format you have applied to you cells.

The source of the error is in the formula

=(E4-E5)*24

E4 is the start time of 05:00
E5 is the end time of 14:00

You are subtracting the wrong way around and your formula is returning -9. because you have a custom format of 0.0;[Blue]0.0 in the cell your not seing this and the cell is displaying as 9.

the formula I have you has the same custom format applied and is actually returning -55 but is being displayed because of the format as 55

To correct things change the formula in E17 to =(E5-E4)*24 and likewise for all the other cells that calculate the hours.

Use a different custom format for those cells and I suggest
0.0;[Blue](0.0)

That way negative numbers show up in parenthesis.

I have uploaded the corrected workbook here

http://rapidshare.com/files/411828453/SDI-Schedule_Creator.xlsx

What I meant by "mix them up" was that if you had used this...

=MAX(40,SUM(A1:G1))

instead of this...

=MIN(40,SUM(A1:G1))

then you would have gotten the result you posted that you got (40 instead of 45 when your SUM was 45), but if you copied/pasted Mike's formula as you said you did, then the result you posted is, simply, an impossible one... MIN and MAX work in very predictable, repeatable ways and there is no way the result you say you got is possible outcome for them. And the same would have been true for the other equation as well.