|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)|
|Night Shift (7)||01/01/19||N||6PM||3AM||-14||14|
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.
Ps thank to someone on here for the initial formulas