# Formula in excel

#### Shazzi1005

##### New Member
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

##### Active Member
I think this is what you want. I copied down the formula a few more rows to show results for different data.

EFG
4628
5639.5
67717
7000

</tbody>
Sheet2

Worksheet Formulas
CellFormula
G4=IF(F4+E4>8,8+(F4+E4-8)*1.5,F4+E4)
G5=IF(F5+E5>8,8+(F5+E5-8)*1.5,F5+E5)
G6=IF(F6+E6>8,8+(F6+E6-8)*1.5,F6+E6)
G7=IF(F7+E7>8,8+(F7+E7-8)*1.5,F7+E7)

</tbody>

<tbody>
</tbody>

#### Shazzi1005

##### New Member
Great thank you the formula works except it still won’t multiply the remainder by 1.5 so I’d need it to automatically times it by 1.5 ??? Any ideas

I think this is what you want. I copied down the formula a few more rows to show results for different data.

EFG
4628
5639.5
67717
7000

</tbody>
Sheet2

Worksheet Formulas
CellFormula
G4=IF(F4+E4>8,8+(F4+E4-8)*1.5,F4+E4)
G5=IF(F5+E5>8,8+(F5+E5-8)*1.5,F5+E5)
G6=IF(F6+E6>8,8+(F6+E6-8)*1.5,F6+E6)
G7=IF(F7+E7>8,8+(F7+E7-8)*1.5,F7+E7)

</tbody>

<tbody>
</tbody>

#### Shazzi1005

##### New Member
Maybe I didn’t write it properly
For example Start time is in cell E4 finish time is in F4 they calculate into cell G4 so in the H4 cell that’s where I need it to multiply by 1.5. But even using this formula it doesn’t multiply. TIA

##### Active Member
Ah! So E4 are time of day and not elapsed hours. Plase be aware you'll have an issue if the shift goes over midnight.

I'll convert the elapsed hours to decimal hours then show the hours to pay based on hours up to 8 then 1.5 times any hours over 8.

EFGH
3Start TimeEnd TimeElapsed Decimal HoursPay Hours
49:0017:0088
58:3019:301112.5
69:3018:3099.5
70000

</tbody>
Sheet2

Worksheet Formulas
CellFormula
G4=(F4-E4)*24
H4
=IF(G4>8,((G4-8)*1.5)+8,G4)

</tbody>

<tbody>
</tbody>

#### Shazzi1005

##### New Member
Thank you for your help. It was elapsed hours but it still won’t multiply I’ve handed it over to my manager to see if he can work it out. It was trying to multiply the 8.5 hours when I only wanted the .5 to multiply because after 8 hours it’s time and a half.
Thanks for responding

##### Active Member
Maybe this is what you want? If not then please give sample numbers of input and expected results.

EFGH
3Start TimeEnd TimeCap at 8Remainder x 1.5
49:0017:0080
58:3019:3084.5
69:3017:007.50
710:0016:0060

</tbody>
Sheet2

Worksheet Formulas
CellFormula
G4=IF(((F4-E4)*24)>8,8,(F4-E4)*24)
H4
=IF(((F4-E4)*24)>8,((((F4-E4)*24)-8)*1.5),0)

</tbody>

<tbody>
</tbody>

#### Shazzi1005

##### New Member
You are a genius thank you so much that’s exactly what I needed but now I’ll throw another one at you. Using your example where you have the H5 and it shows 4.5 hours I would need that to cap at 2 hours And the last 2.5 hours would go into double time under I5

Do you know how to do that? Thanks heaps

##### Active Member
See if these work for you:

EFGHI
3Start TimeEnd TimeCap at 8 8 to 10 x 1.5Over10 x 2
49:0018:0081.50
58:3019:30832
67:0021:00838
710:0016:00600

</tbody>
Sheet2

Worksheet Formulas
CellFormula
G4=IF(((F4-E4)*24)>8,8,(F4-E4)*24)
H4=IF(((F4-E4)*24) > 8,MIN(((F4-E4)*24),10)-8)*1.5
I4
=IF(((F4-E4)*24) > 10,((((F4-E4)*24)-10)*2),0)

</tbody>

<tbody>
</tbody>

#### Shazzi1005

##### New Member
Great thank you. Is there a way to cap it like you did with cell G4. Using your example so in the H5 cell where it’s 8 hours normal and 3 hours 1.5 then 2 at 2 hours. I need the 1.5 cell to stop at 2hours then calculate the remainder in double time