Formula in excel

[Shazzi1005]

I think I need to use an IF formula but can’t work it out.

I have F4+E4 to give me the G4. So i have a few different hours so I need the formula so that if G4 is equal to or greater then 8 then the remainder hours calculate to multiply by 1.5

Can anyone help please?

 

[Toadstool]

Yes, that's what it's doing.

e.g. row 5 is 08:30 to 19:30 so eleven hours.
The 8 goes into the "Cap at 8" column at standard rate of x 1.
The next 2 of the 11 hours (hours 9 and 10) are at 1.5 rate so I multiply by 1.5 and those 2 hours become the "8 to 10 x 1.5" value of 3.
The last hour of those 11 is at double rate so the "Over10 x 2" becomes a value of 2.

 

[Shazzi1005]

Ok so but I need 1.5 times cell to cap at 2 as with overtime they get 2 hours at 1.5 and all other hours are double after that

 

[Toadstool]

So you don't want it to do the multiplication, just show the number of hours which will need the multiplication applied?

OK, this should do it:

	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Cap at 8[/TD]
[TD="align: center"] 8 to 10 for 1.5[/TD]
[TD="align: center"]Over 10 for 2[/TD]

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

[TD="align: center"]6[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]19:30[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"]21:00[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]10:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

</tbody>

Shazzi1005​

[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] "]G5[/TH]
[TD="align: left"]=IF(((F5-E5)*24)>8,8,(F5-E5)*24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=IF(((F5-E5)*24) > 8,MIN(((F5-E5)*24),10)-8,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5
[/TH]
[TD="align: left"]=IF(((F5-E5)*24) > 10,((((F5-E5)*24)-10)),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]