Formula in excel

Shazzi1005

New Member
Joined
Sep 2, 2019
Messages
7
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

Active Member
Joined
Mar 5, 2018
Messages
452
Office Version
2016
Platform
Windows
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Shazzi1005

New Member
Joined
Sep 2, 2019
Messages
7
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Shazzi1005

New Member
Joined
Sep 2, 2019
Messages
7
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
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
452
Office Version
2016
Platform
Windows
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

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

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Shazzi1005

New Member
Joined
Sep 2, 2019
Messages
7
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
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
452
Office Version
2016
Platform
Windows
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Shazzi1005

New Member
Joined
Sep 2, 2019
Messages
7
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
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
452
Office Version
2016
Platform
Windows
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Shazzi1005

New Member
Joined
Sep 2, 2019
Messages
7
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
 

Forum statistics

Threads
1,085,429
Messages
5,383,629
Members
401,843
Latest member
stevensmith1

Some videos you may like

This Week's Hot Topics

Top