Formula in excel

S

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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
I think this is what you want. I copied down the formula a few more rows to show results for different data.

EFG

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet2​

[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] "]G4[/TH]
[TD="align: left"]=IF(F4+E4>8,8+(F4+E4-8)*1.5,F4+E4)[/TD]
[/TR]
[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>8,8+(F5+E5-8)*1.5,F5+E5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=IF(F6+E6>8,8+(F6+E6-8)*1.5,F6+E6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]=IF(F7+E7>8,8+(F7+E7-8)*1.5,F7+E7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
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


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

EFG

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet2​

[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] "]G4[/TH]
[TD="align: left"]=IF(F4+E4>8,8+(F4+E4-8)*1.5,F4+E4)[/TD]
[/TR]
[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>8,8+(F5+E5-8)*1.5,F5+E5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=IF(F6+E6>8,8+(F6+E6-8)*1.5,F6+E6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]=IF(F7+E7>8,8+(F7+E7-8)*1.5,F7+E7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Elapsed Decimal Hours[/TD]
[TD="align: center"]Pay Hours[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]19:30[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]9:30[/TD]
[TD="align: center"]18:30[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9.5[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

</tbody>
Sheet2​

[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] "]G4[/TH]
[TD="align: left"]=(F4-E4)*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4
[/TH]
[TD="align: left"]=IF(G4>8,((G4-8)*1.5)+8,G4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Cap at 8[/TD]
[TD="align: center"]Remainder x 1.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]19:30[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]4.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]9:30[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]7.5[/TD]
[TD="align: center"]0[/TD]

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

</tbody>
Sheet2​

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/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 x 1.5[/TD]
[TD="align: center"]Over10 x 2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]1.5[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]8:30[/TD]
[TD="align: center"]19:30[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"]21:00[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]7[/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>
Sheet2​

[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] "]G4[/TH]
[TD="align: left"]=IF(((F4-E4)*24)>8,8,(F4-E4)*24)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=IF(((F4-E4)*24) > 8,MIN(((F4-E4)*24),10)-8)*1.5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4
[/TH]
[TD="align: left"]=IF(((F4-E4)*24) > 10,((((F4-E4)*24)-10)*2),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
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
You must log in or register to reply here.

Similar threads

J
  • Question Question
Nested Formula Issues
Replies
6
Views
232
MARK858
MARK858
E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
920
Fluff
Fluff
Jyggalag
  • Solved
Help me expand my formula please :(
2 3 4
Replies
33
Views
863
Jyggalag
Jyggalag
R
  • Solved
Hours & minutes calculation
Replies
2
Views
164
Ron99
R
S
  • Question Question
Turn around times based on days and hours worked
Replies
2
Views
86
smllchng5
S

Forum statistics

Threads
1,223,707
Messages
6,174,000
Members
452,542
Latest member
Bricklin

Share this page

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