IF formula

[Nunya1]

I am doing timesheets and the following is the layout

E F G H I J
Start Break Finish Normal Hours Time & Half Double Time
08:30 1:00 19:30 8 3 2

Under the H cell I use this formula =IF(((G5-F5-E5)*24)>8,8,(G5-F5-E5)*24) to calculate the hours and to stop them at 8

Under the I cell I have this formula =IF(((G5-F5-E5)*24)>8,((((G5-F5-E5)*24)-8)*1.5)) to calculate the remainder of the hours at time and a half (X1.5)

Under the J Cell I have this formula =IF(((G5-F5-E5)*24)>8,((((G5-F5-E5)*24)-8)*1.5)>2)*2 to calculate the remainder as double time

What I need is the I cell (time and a half) to work similar to the H cell so I need it to take the remainder of the hours worked which in this example is 2 hours multiply it by 1.5 (which works in the formula I have used) now I need it to stop at 2 and then I need the rest of the hours which would be 1 to multiply in the J cell to double time (x2)

Can anyone help please

TIA

 

[petertenthije]

Re: IF formula help

Do I understand you correctly?

You want all hours worked up to 8 hours to be counted as 1.
All hours worked between 8 and 10 counted x1.5. So up to two hours can be counted x1.5
All hours worked over 10 counted x2.

If so:

Column I (x1.5).
The number 2 marked red can be changed to increase/decrease the amount of hours for which x1.5 applies.
=IF(((G5-F5-E5)*24)>8,(MIN((((G5-F5-E5)*24)-8),2)*1.5))

Column J (x2.0).
The number 10 marked red can be changed to modify the hour at which x2.0 starts.
=IF(((G5-F5-E5)*24)>10,((G5-F5-E5)*24)-10)*2

 

[Toadstool]

Re: IF formula help

Hi Nunya1,

I've just answered an identical question for Shazzi1005 but without the break hour(s).
https://www.mrexcel.com/forum/excel-questions/1108760-formula-excel-post5336244.html#post5336244

The same answer applies but with the break subtracted, as you've identified. I've added a total hours worked column for clarity.

	E	F	G	H	I	J	K	L

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Break[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Cap at 8[/TD]
[TD="align: center"] 8 to 10 x 1.5[/TD]
[TD="align: center"]Over10 x 2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hours Worked[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]19:30[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]21:00[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]10:00[/TD]
[TD="align: center"]2:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

</tbody>

Nunya1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=IF(((G4-F4-E4)*24)>8,8,(G4-F4-E4)*24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=IF(((G4-F4-E4)*24) > 8,MIN(((G4-F4-E4)*24),10)-8)*1.5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=IF(((G4-F4-E4)*24) > 10,((((G4-F4-E4)*24)-10)*2),0)
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L4
[/TH]
[TD="align: left"]=((G4-F4-E4)*24)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nunya1]

Re: IF formula help

Ta I just flicked over to the other post and it answered my question.

 

[Nunya1]

Re: IF formula help

One more thing - So below is what our timesheets look like. Now the guys can do more then 1 job a day so if for the first job (row 4) they work the 8 hours then the second line (Row 5) they worked 4 hours then that row should go into the overtime hours so how can I get it to recognise that if rows 4-6 total 8 all together under normal then the first 1.33 hours is time and a half and the rest of the hours go in at double time. Each one is rows 4-6 and also why is my formula showing false until I enter data in times?

I am using the formulas written in this thread except in time and half I am using this formula =IF(((G4-F4-E4)*24)>8,(MIN((((G4-F4-E4)*24)-8),1.333333)*1.5))






<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 132px"><col width="364"><col width="197"><col width="145"><col width="113"><col width="113"><col width="113"><col width="113"><col width="128"><col width="116"><col width="146"><col width="112"><col width="106"><col width="98"><col width="97"><col width="83"><col width="71"><col width="72"><col width="63"><col width="63"><col width="63"><col width="63"><col width="63"><col width="63"><col width="63"><col width="63"><col width="55"><col width="55"><col width="55"></colgroup><tbody>[TR]
[TD="align: center"]Day[/TD]
[TD="align: center"]Site Name[/TD]
[TD="align: center"]Job No[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Break[/TD]
[TD="align: center"]Finish Time[/TD]
[TD="align: center"]Normal [/TD]
[TD="align: center"]1.5 Time[/TD]
[TD="align: center"]2.0 Time[/TD]
[TD="align: center"]On Call[/TD]
[TD="align: center"]TOIL Taken[/TD]
[TD="align: center"]Public Holiday[/TD]
[TD="align: center"]Sick Leave[/TD]
[TD="align: center"]Annual Leave[/TD]
[TD="align: center"]$70.00[/TD]
[TD="align: center"]$130.00[/TD]
[TD="align: center"]$195.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1-Jan-19[/TD]
[TD="align: center"]07:00am[/TD]
[TD][/TD]
[TD="align: center"]09:00pm[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: center"]8.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]09:00am[/TD]
[TD][/TD]
[TD="align: center"]12:00pm[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]12:00pm[/TD]
[TD="align: center"]01:00[/TD]
[TD="align: center"]04:00pm[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Wednesday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Toadstool]

Re: IF formula help

Hi Nunya1,

So this needs a different approach and a change to the structure of the sheet.

The Normal, OT1 and OT2 hours can only be calculated after the last entry for that day, so I need the date repeated for each row with times and I need a new column "Hours" so I can total up the hours for a day and then calculate.

Cell formulae H2, I2, J2 and K2 should be copied down as far as the last row which may have times entered.

The formulae search the whole column (i.e. H:H and D:D) but if you've other data in lower rows then you should limit the range to those which have times entered (e.g. $H$2:$H$100 and $D$2:$D$100).

I am showing the hours for OT1 and OT2. If you want me to actually calculate those hours as multiplied by 1.5 and 2 then let me know.

	A	B	C	D	E	F	G	H	I	J	K
Monday
Tuesday
Wednesday
Thurday

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Site Name[/TD]
[TD="align: center"]Job No[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Break[/TD]
[TD="align: center"]Finish Time[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"]Normal[/TD]
[TD="align: center"]OT 1[/TD]
[TD="align: center"]OT 2[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]21:00[/TD]
[TD="align: center"]14.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]1.33[/TD]
[TD="align: center"]10.67[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]02-Jan-19[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]7.50[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]02-Jan-19[/TD]
[TD="align: center"]7:30[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]16:30[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]1.33[/TD]
[TD="align: center"]6.17[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]03-Jan-19[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]16:30[/TD]
[TD="align: center"]7.50[/TD]
[TD="align: center"]7.50[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]04-Jan-19[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]7.00[/TD]
[TD="align: center"]7.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Nunya1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(E2<>"",(G2-E2-F2)*24,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IF(AND(D2<>D3,D2<>""),MIN(SUMIFS(H:H,D:D,D2),8),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER(I2),SUMIFS(H:H,D:D,D2)>8),MIN(SUMIFS(H:H,D:D,D2)-8,1.33333),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=IF(ISNUMBER(J2),SUMIFS(H:H,D:D,D2)-9.333333,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nunya1]

Re: IF formula help

And how do I get the J2 to multiply by 1.5 to get a total value of 2 and the same with K2 I need it to multiply by 2 to get a total?

The formulas you gave me work appreciated that but when I try to *1.5 or *2 it just brings it up as (Value)

 

[Toadstool]

Re: IF formula help

Here you go...

	A	B	C	D	E	F	G	H	I	J	K
Monday
Tuesday
Wednesday
Thurday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Site Name[/TD]
[TD="align: center"]Job No[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Break[/TD]
[TD="align: center"]Finish Time[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"]Normal[/TD]
[TD="align: center"]OT 1[/TD]
[TD="align: center"]OT 2[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]21:00[/TD]
[TD="align: center"]14.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: center"]21.33[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]02-Jan-19[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]7.50[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]02-Jan-19[/TD]
[TD="align: center"]7:30[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]16:30[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: center"]12.33[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]03-Jan-19[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]16:30[/TD]
[TD="align: center"]7.50[/TD]
[TD="align: center"]7.50[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]04-Jan-19[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]7.00[/TD]
[TD="align: center"]7.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Nunya1 (3)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(E2<>"",(G2-E2-F2)*24,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IF(AND(D2<>D3,D2<>""),MIN(SUMIFS(H:H,D:D,D2),8),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER(I2),SUMIFS(H:H,D:D,D2)>8),MIN(SUMIFS(H:H,D:D,D2)-8,1.33333)*1.5,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2
[/TH]
[TD="align: left"]=IF(ISNUMBER(J2),(SUMIFS(H:H,D:D,D2)-9.333333)*2,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nunya1]

Re: IF formula help

K still wont calculate at double time with that formula the J one works but K won'?

Appreciate your help BTW

 

[Toadstool]

Re: IF formula help

It's working for me (as per the example).

1st January has three entries for 14,3 and 3 hours so total = 20.
Normal is 8 hours at flat rate x 1 = 8, so that leaves 12 hours to pay.
OT 1 is capped at 1.33333 hours and paid at 1.5 x so 1.5 x 1.33333 = 1.99999995 which Excel rounds to 2,so that leaves 20-8-1.33333 hours=10.66667
OT 2 is twice the remaining hours so 10.66667 x 2 = 21.33334 which Excel rounds to 21.33

2nd January had two entries 7.50 + 8 = 15.5 hours
Normal is 8 hours so leaving 7.5 hours
OT 1 takes 1.33333 of those 7.5 hours and x 2=2, so leaving 6.16667 hours
OT 2 takes the remaining hours and applies double rate so 6.16667 x 2 = 12.33334 which Excel rounds to 12.33

If those aren't the results you're seeing then please copy and paste the cells with your results?