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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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>
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top