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