Forum Discussion

Carol423's avatar
Carol423
Copper Contributor
Jul 17, 2019

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 on...
excel
Brad_Yundt's avatar
Brad_Yundt
MVP
Jul 18, 2019

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.

 

 

 

Resources