Help To Cap formulas to recognize hours reached and negative hours

[Nunya1]

[TABLE="class: grid, width: 1200"]
<tbody>[TR]
[TD]Day (A)[/TD]
[TD]Date (B)[/TD]
[TD]Job (C)[/TD]
[TD]Site Name(D)[/TD]
[TD]Start Time (E)[/TD]
[TD]Break (F)[/TD]
[TD]Finish Time (G)[/TD]
[TD]Hours (H)[/TD]
[TD]Normal (I)[/TD]
[TD]1.5 Time (J)[/TD]
[TD]2.0 Time (K)[/TD]
[/TR]
[TR]
[TD]Monday (2)[/TD]
[TD]01/01/19[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]6:30AM[/TD]
[TD]:30[/TD]
[TD]3:30PM[/TD]
[TD]8.5[/TD]
[TD]8[/TD]
[TD].5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD](3)[/TD]
[TD]01/01/19[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]4:30PM[/TD]
[TD][/TD]
[TD]08:30PM[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](4)[/TD]
[TD]01/01/19[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD]10:30AM[/TD]
[TD][/TD]
[TD]10:30PM[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD](5)[/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](6)[/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Night Shift (7)[/TD]
[TD]01/01/19[/TD]
[TD]N[/TD]
[TD]
[/TD]
[TD]6PM[/TD]
[TD]
[/TD]
[TD]3AM[/TD]
[TD]-14[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Daily Total[/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]14.5[/TD]
[/TR]
</tbody>[/TABLE]

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 second row is showing the correct hours BUT 1.5 of those should go to 1.5 time and the other 2.5 should go into 2.0 time
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

 

[Nunya1]

Thank you I realised what was going wrong and have fixed it. What I am hoping will be my last issue with them is that the night shift calculates straight into double time hours which is great and what i need but in the daily total hours if the cell I has not reached its 8 it is making up the night shift hours and turning them into normal hours which is wrong night shift will always be doublt time

For example the guys might work 7-10am and then 9pm until 2am

That whole night shift should calculate into the daily totals as time and a half but because of the formula I have in there it is trying to get me 8 normal hours first so anything that goes into row16 is double time

The daily totals I have already will be referring to cell B which is date


Daily totals sit in Row 17

Cell I - Normal =IF(AND(B17<>B18,B17<>""),MIN(SUMIFS(H:H,B:B,B17),8),"")

Cell J - 1.5 Time =IF(AND(ISNUMBER(I17),SUMIFS(H:H,B:B,B17)>8),MIN(SUMIFS(H:H,B:B,B17)-8,2),"")

Cell K - 2.0 Time =IF(ISNUMBER(J17),MAX(0,(SUMIFS(H:H,B:B,B17)-10)*1),"")


So I need to tell it that if there is hours in Row 16 they need to automatically go into double time in row 17 (Daily Totals)


Hope this makes sense thanks for your help

 

[Peter_SSs]

... night shift will always be doublt time

That whole night shift should calculate into the daily totals as time and a half

This sounds contradictory to me. One staes night shift should be double time, the other states that night shift should be time & a half.

See if this is any closer, night shift goes into double time.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	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)
2	Monday (2)	1/01/2019	X		6:30 AM	0:30	3:30 PM	8.5	8	0.5	0
3	-3	1/01/2019	Y		4:30 PM		8:30 PM	4	0	1.5	2.5
4	-4	1/01/2019	Z		10:30 AM		10:30 PM	12	0	0	12
5	-5	1/01/2019
6	-6	1/01/2019
7	Night Shift (7)	1/01/2019	N		6:00 PM		3:00 AM	9			9
Split times (2)