Home

Trying to create an "if statement" in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-759677%22%20slang%3D%22en-US%22%3ETrying%20to%20create%20an%20%22if%20statement%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759677%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20never%20had%20the%20need%20to%20create%20an%20If%20statement%20until%20now.%26nbsp%3B%20I%20am%20trying%20to%20calculate%20the%20percentage%20of%20time%20that%20should%20be%20added%20to%20hours%20worked.%26nbsp%3B%20For%20example%20if%20an%20employee%20works%20for%203%20hours%20on%20one%20specific%20job%20and%20then%20works%206%20hours%20on%20another%20job%2C%20how%20much%20of%20the%20overtime%20hour%20should%20be%20f%3Dcredited%20to%20each%20job.%26nbsp%3B%20I%20have%20a%20formula%20that%20does%20the%20calculation%20form%20me%2C%20but%20I%20have%20to%20adjust%20the%20amount%20for%20every%20instance.%26nbsp%3B%20Id%20like%20the%20if%20statement%20to%20be%20like%3A%20if%20a1%26gt%3B8%20then%20divide%20the%20total%20hours%20worked%20by%20the%20hours%20worked%20on%20the%20specific%20job%20to%20give%20me%20a%20fraction%20that%20I%20could%20then%20add%20to%20the%20specific%20numbers..%20Please%20email%20me%20at%20gsardinha%40snet.net%20if%20someone%20could%20at%20least%20steer%20me%20in%20the%20right%20direction.%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-759677%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759955%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20create%20an%20%22if%20statement%22%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378005%22%20target%3D%22_blank%22%3E%40Carol423%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20suppose%20the%20total%20amount%20of%20time%20worked%20is%20in%20cell%20A1%2C%20and%20the%20amount%20of%20time%20worked%20on%20job%20XYZ%20is%20in%20B1.%20Furthermore%2C%20time%20is%20being%20entered%20as%20decimal%20hours%20(e.g.%206.5)%20rather%20than%20clock%20time%20(6%3A30).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20suggest%20using%20MAX%20instead%20of%20IF%20when%20you%20figure%20out%20your%20overtime%20and%20pro-rating.%20Once%20you%20wrap%20your%20head%20around%20it%2C%20MAX%20makes%20the%20calculations%20simpler.%3C%2FP%3E%0A%3CP%3EThe%20number%20of%20hours%20of%20overtime%20is%20%3DMAX(0%2C%20A1-8)%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20assign%20overtime%20pro-rata%20to%20project%20XYZ%20you%20might%20use%20%3DMAX(0%2C%20A1-8)*(B1%2FA1)%3C%2FP%3E%0A%3CP%3EIf%20you%20use%20time%20and%20a%20half%20for%20overtime%2C%20the%20number%20of%20equivalent%20straight%20time%20hours%20is%3A%3C%2FP%3E%0A%3CP%3E%3DB1%2B0.5*MAX(0%2C%20A1-8)*(B1%2FA1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20still%20need%20help%2C%20I%20suggest%20that%20you%20post%20a%20workbook%20in%20this%20thread%20using%20the%20Browse...%20button%20at%20the%20bottom%20of%20the%20Comment%20box.%20That%20way%20people%20can%20give%20suggestions%20specific%20to%20your%20worksheet%20layout.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Carol423
Occasional Visitor

I've never had the need to create an If statement until now.  I am trying to calculate the percentage of time that should be added to hours worked.  For example if an employee works for 3 hours on one specific job and then works 6 hours on another job, how much of the overtime hour should be f=credited to each job.  I have a formula that does the calculation form me, but I have to adjust the amount for every instance.  Id like the if statement to be like: if a1>8 then divide the total hours worked by the hours worked on the specific job to give me a fraction that I could then add to the specific numbers.. Please email me at gsardinha@snet.net if someone could at least steer me in the right direction.  Thanks!

1 Reply

@Carol423 

Let's suppose the total amount of time worked is in cell A1, and the amount of time worked on job XYZ is in B1. Furthermore, time is being entered as decimal hours (e.g. 6.5) rather than clock time (6:30).

 

I suggest using MAX instead of IF when you figure out your overtime and pro-rating. Once you wrap your head around it, MAX makes the calculations simpler.

The number of hours of overtime is =MAX(0, A1-8)

If you want to assign overtime pro-rata to project XYZ you might use =MAX(0, A1-8)*(B1/A1)

If you use time and a half for overtime, the number of equivalent straight time hours is:

=B1+0.5*MAX(0, A1-8)*(B1/A1)

 

If you still need help, I suggest that you post a workbook in this thread using the Browse... button at the bottom of the Comment box. That way people can give suggestions specific to your worksheet layout.

 

 

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies