 • 544K Members
• 3,749 Online
• 649K Conversations

Highlighted

# Trying to create an "if statement" in excel

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!

Highlighted

# Re: Trying to create an "if statement" in excel

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