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

 

[mrshl9898]

Try:

H2 = =IF(OR(E2="",G2=""),"",IF(G2>E2,IF(E2<>"",(G2-E2-F2)*24,""),((1-E2)+G2-F2)*24))
I2 = =IF(H2="","",IF(H2>8,8,H2))
J2 = =IFERROR(IF(H2-I2>2,2,H2-I2),"")
K2 = =IFERROR(IF(SUM(I2:J2)<h2,h2-sum(i2:j2),""),"")< html=""></h2,h2-sum(i2:j2),""),"")<>

 

[Nunya1]

Thanks Heaps that works for the night shift part

It still shows all the hours at normal and doesn't cap the normal hours at 8 then cap the 1.5 time at 2 then move the rest into the 2.0 time it does it correctly for the daily total but not for each row and I dont actually know if it is possible. However the problem is the guys see their timesheets and stress out because they see row 1-3 calculating at normal and not giving them the overtime if that makes sense even though it does say it at the bottom in the daily total.

They get paid 8 hours normal 2 hours x1.5 and the rest is double time.

 

[Nunya1]

Using that formula in K2 it now doesnt show any 2.0 time hours?

 

[mrshl9898]

Sorry, I didn't paste it in properly.

K2 = =IFERROR(IF(SUM(I2:J2)<h2,h2-sum(i2:j2),""),"")<h4,h4-sum(i4:j4),""),"")

<h4,h4-sum(i4:j4),""),"")

It's capping J at 2 for me... Can you paste in the results you get with the new formulas?

EDIT, still not pasting in, edit the below with the less than symbol

=IFERROR(IF(SUM(I2:J2) is less than H2,H2-SUM(I2:J2),""),"")</h4,h4-sum(i4:j4),""),"")
</h2,h2-sum(i2:j2),""),"")<h4,h4-sum(i4:j4),""),"")

 

[Nunya1]

Yeah it is still not working normal time and 1.5 time is but the 2.0 time is showing nothing.

Using yours I think it needed the H column
so i did this and it worked =IFERROR(IF(H2-J2-I2>2,2,H2-J2-I2),"")

Really appreciate it - Do you know how to get for example row 3 to recognise that row 2 has already reached its 8 hours normal and .5 of its time an a half so that row 2 hours wont show in normal but 1.5hours would go into 1.5 time and the rest would go to 2.0 time and so forth?

 

[mrshl9898]

Ahh, didn't realise you wanted to see a 0.

You'd need an identifier to show that the jobs are related, guessing this isn't date as 4 overlaps the time in 2 and 3, if you have an employee name or number then you could do something with SUMIF maybe...

I'd put in another table to get the total hours per person, then use the current formulas to get their breakdown into regular, 1.5 and 2.


So, add a column, employee name. As an example say it's in L.

Then list the unique names in N2 down.

O2 = =SUMIF(L:L,N2,H:H)
P2 = =IF(O2>8,8,O2)
Q2 = =MIN(O2-P2,2)
R2 = =O2-P2-Q2

 

[Peter_SSs]

See if these, copied down, do what you want.

Excel Workbook
	E	F	G	H	I	J	K
1	Start Time (E)	Break (F)	Finish Time (G)	Hours (H)	Normal (I)	1.5 Time (J)	2.0 Time (K)
2	6:30 AM	0:30	3:30 PM	8.5	8	0.5	0
3	4:30 PM		8:30 PM	4	4	0	0
4	10:30 AM		10:30 PM	12	8	2	2
5
6
7	6:00 PM		3:00 AM	9	8	1	0
8
Split times

 

[Nunya1]

Great thankyou.

What I need though and I don't know if it is possible as per your example row 3 I would need from the 4 hours - 1.5hours to go directly into 1.5time cell, and the the 2.5 hours to go straight into the 2 time cell.

so using your example this is how it would look (see below). The daily total row I still need to show as it does but i need it to recognise when the first 8 hours has been reached for normal, then when 2 hours has been reached for 1.5 and the rest into 2.0 time hopefully this makes sense

	E	F	G	H	I	J	K
Break (F)	Hours (H)

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 94px;"><col style="width: 109px;"><col style="width: 74px;"><col style="width: 75px;"><col style="width: 87px;"><col style="width: 90px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: right"]Start Time (E)[/TD]

[TD="align: right"]Finish Time (G)[/TD]

[TD="align: right"]Normal (I)[/TD]
[TD="align: right"]1.5 Time (J)[/TD]
[TD="align: right"]2.0 Time (K)[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]6:30 AM[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]4:30 PM[/TD]

[TD="align: right"]8:30 PM[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]2.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]10:30 AM[/TD]

[TD="align: right"]10:30 PM[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]6:00 PM[/TD]

[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

	E	F	G	H	I	J	K
Break (F)	Hours (H)

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 94px;"><col style="width: 109px;"><col style="width: 74px;"><col style="width: 75px;"><col style="width: 87px;"><col style="width: 90px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: right"]Start Time (E)[/TD]

[TD="align: right"]Finish Time (G)[/TD]

[TD="align: right"]Normal (I)[/TD]
[TD="align: right"]1.5 Time (J)[/TD]
[TD="align: right"]2.0 Time (K)[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]6:30 AM[/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"]3:30 PM[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]4:30 PM[/TD]

[TD="align: right"]8:30 PM[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]10:30 AM[/TD]

[TD="align: right"]10:30 PM[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]6:00 PM[/TD]

[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

</tbody>

</body>

 

[Peter_SSs]

What I need though and I don't know if it is possible as per your example row 3 I would need from the 4 hours - 1.5hours to go directly into 1.5time cell, and the the 2.5 hours to go straight into the 2 time cell.

so using your example this is how it would look (see below). The daily total row I still need to show as it does but i need it to recognise when the first 8 hours has been reached for normal, then when 2 hours has been reached for 1.5 and the rest into 2.0 time hopefully this makes sense

Ah, I didn't understand that very well, did I?

See if this is closer to the mark.

Excel Workbook
	E	F	G	H	I	J	K
1	Start Time (E)	Break (F)	Finish Time (G)	Hours (H)	Normal (I)	1.5 Time (J)	2.0 Time (K)
2	6:30 AM	0:30	3:30 PM	8.5	8	0.5	0
3	4:30 PM		8:30 PM	4	0	1.5	2.5
4	10:30 AM		10:30 PM	12	0	0	12
5
6
7	6:00 PM		3:00 AM	9	0	0	9
8
Split times (2)