Day (A) | Date (B) | Job (C) | Site Name(D) | Start Time (E) | Break (F) | Finish Time (G) | Hours (H) | Normal (I) | 1.5 Time (J) | 2.0 Time (K) |

Monday (2) | 01/01/19 | X | 6:30AM | :30 | 3:30PM | 8.5 | 8 | .5 | 0 | |

(3) | 01/01/19 | Y | 4:30PM | 08:30PM | 4 | 4 | ||||

(4) | 01/01/19 | Z | 10:30AM | 10:30PM | 12 | 8 | 2 | 2 | ||

(5) | 01/01/19 | |||||||||

(6) | 01/01/19 | |||||||||

Night Shift (7) | 01/01/19 | N | 6PM | 3AM | -14 | 14 | ||||

Daily Total | 01/01/19 | 8 | 2 | 14.5 |

<tbody>

</tbody>

Currently this is how my time sheet is set up.Night **** is highlighted red because at the moment it does not work in with any of the formulas I have in place.The normal hours need to cap at 8 the 1.5 time need to cap at 2 and the 2.0 time shows the remainder of the hours

So I will start with the first issue.

The first row is showing the correct hours. the formulas I have in the rows are:

- =IF(E2<>"",(G2-E2-F2)*24,"") - Cell H for total hours - Which is fine
- =IF(((G2-F2-E2)*24)>8,8,(G2-F2-E2)*24) - Cell I for normal hours
- =IF(((G2-F2-E2)*24) > 8,MIN(((G2-F2-E2)*24),10)-8,"") - Cell J for 1.5 time
- =IF(((G2-F2-E2)*24) > 10,((((G2-F2-E2)*24)-10)*1),"") - Cell K for 2.0 Time
- Except for row 7 which is night shift & Daily Total
- The Daily total formulas are;
- =IF(AND(B8<>B9,B8<>""),MIN(SUMIFS(H:H,B:B,B8),8),"") - Normal Cell I
- =IF(AND(ISNUMBER(I8),SUMIFS(H:H,B:B,B8)>8),MIN(SUMIFS(H:H,B:B,B8)-8,2),"") - 1.5 time Cell J
- =IF(ISNUMBER(J8),MAX(0,(SUMIFS(H:H,B:B,B8)-10)*1),"") - 2.0 Time cell k
- daily totals are currently reflecting what I need them to (excluding the night shift)

The third row should all go into 2.0 time

So I need to adjust the formulas so that they recognise that on the first row the 8 hours in normal has been reached and so forth 2 hours 1.5 time has been reached

I also need the night shift 6pm - 3am to work in with these formulas and calculate in with the daily total currently it comes up as a negative and then takes the hours off my total the night shift row hours need to be a positive and all of those hours should go into 2.0 time - What I do know is to get a negative to a positive it is =I7*-1 but I can't get it to work in with the formulas to recognise it. - I am not even sure if this is doubale?

Really appreciate any assistance with this.

Thanks,

Ps thank to someone on here for the initial formulas