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
4	Start Time	End Time	Cap at 8	8 to 10 for 1.5	Over 10 for 2
5	9:00	18:00	8	1	0
6	8:30	19:30	8	2	1
7	7:00	21:00	8	2	4
8	10:00	16:00	6	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Shazzi1005​

Worksheet Formulas Cell Formula G5 =IF(((F5-E5)*24)>8,8,(F5-E5)*24) H5 =IF(((F5-E5)*24) > 8,MIN(((F5-E5)*24),10)-8,0) I5 =IF(((F5-E5)*24) > 10,((((F5-E5)*24)-10)),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>