Automaticaly calculate over time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have a question I’m creating a weekly schedule in excel. I was wondering if there is a way to create a Rule that if there is more than 40 hours worked that it will automatically multiply the hours over 40 by 1.5

Thanks for your help!
 
With your hours in A1

=MIN(A1,40)+MAX(0,A1-40)*1.5

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Aaron said:
Hello

I have a question I'm creating a weekly schedule in excel. I was wondering if
Click to expand...
there is a way to create a Rule that if there is more than 40 hours worked that
it will automatically multiply the hours over 40 by 1.5
 
Assuming hours worked is in col A, A2 down

Try something like this:

Put in B2: =IF(A2>40,A2*1.5,A2)
Copy B2 down col B

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
---------------------------------------------
Aaron said:
Hello

I have a question I'm creating a weekly schedule in excel. I was wondering
Click to expand...
if there is a way to create a Rule that if there is more than 40 hours
worked that it will automatically multiply the hours over 40 by 1.5
 
Correction, apologies:

Try instead in B2: =IF(A2>40,(A2-40)*1.5,"")
Copy down col B

Col B will calculate & return the overtime, if any,
for the hours worked in col A

Think this would be closer to what you're after
 
Hey guys thanks two both of you for your help I used both of your sugestions and got my formula to work. In my spread sheat R= hours worked S = Rate of pay. so i used the following formula to calculate rate of pay for hours both over 40 and under 40.
=IF(R9>=40,((R9-40)*S9*0.5+R9*S9),(R9*S9)

Thanks again for your guys help, I used both of your ideas to get me to my soluation
 
Aaron, you're welcome !

Thanks for your feedback.

Great to know it helped get you to your solution.

--
Rgds
Max xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------
Aaron said:
Hey guys thanks two both of you for your help I used both of your
Click to expand...
sugestions and got my formula to work. In my spread sheat R= hours worked S
= Rate of pay. so i used the following formula to calculate rate of pay for
hours both over 40 and under 40..
=IF(R9>=40,((R9-40)*S9*0.5+R9*S9),(R9*S9))

Thanks again for your guys help, I used both of your ideas to get me to my
Click to expand...
soluation
 
I don't know if this answers your question, but assume that you are entering
hours in cell A1 for example, and you would like hours times 1.5 in cell B1.
In cell B1 enter =A1*1.5

Now, I have a question for you: how do I accumulate hours and minutes in a cell
and allow for more than 23 hours and 59 minutes without it rolling over back to
0 hours and mintes. Is there a particular cell format that I should use?

Chris
 
Back
Top