how do I write a formula that will figure weekly overtime?

[jasperPcuccumber]

I am setting up a payroll worksheet. I need a cell to show total hours, one
for regular time and one for over time. What formatting or formula can I use
to figure over time and straight time based on 40 overtime rule? (anything
over 40 hours in a week is overtime)

 

[Paul B]

Jasper, have a look here and see if this will help

http://www.cpearson.com/excel/overtime.htm

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[CLR]

Total hours in A1
Rate per Hour in B1
In C1 put this formula......copy down if desired..........

=IF(A1<=40,A1*B1,(40*B1)+((A1-40)*B1*1.5))

Vaya con Dios,
Chuck, CABGx3

 

[jasperPcuccumber]

I need to break down the hours only into straight time and ot. If A1 is 32
hours total time then C1 should show "0" over time hours. If A1 is 42 hours
total then C1 should show"2" overtime hours. I can't figure out how to
create this formula.

 

[CLR]

Total hours in A1
In B1 put =MIN(A1,40)..........this is Straight time hours
In C1 put =IF(A1>40,A1-40,0)...........this is OT hours

Vaya con Dios,
Chuck, CABGx3

 

[jasperPcuccumber]

CLR, you are truly a God among humankind.
One more and I will stop bugging you.

Trying to enter times so they will total hours for the day, forgot about
grave shift. Punch in at 23:00 (11pm) in cell a1 punch out at 2:00 (2am) in
cell b1, in at 2:30am in cell c1 out at 6:00am in cell d1.

Conditional Format formula? If so what is it?

 

[Peo Sjoblom]

This will work over midnight

=MOD(D1-A1-(C1-B1),1)

 

[jasperPcuccumber]

This did not work. I tried it as conditional format and formula in E1. It
gave me a result of 0 or neg hours. Also, sometimes only "in" time is before
midnight, sometimes "in" and "out" are before midnight then after lunch break
"in" and "out" after midnight, ie; 19:00 to 23:00, then 23:30 to 4:00. or
23:00 to 2:00 then 2:30 to 6:30. Is there a (if <24 but >18 then = that, but
if >0 but <18 = this)? Or something like that.

 

[jasperPcuccumber]

Ok another one Chuck.
We have some employees who work swing and grave. They punch in before
midnight and sometimes out for lunch before midnight sometimes after. They
may or may not finish the shift after midnight. I need a conditional format
formula that will differentiate between the two.
Can you help????

 

[CLR]

This TimeKeeping thing is not really my forte, but here's my shot.........
First off, I would not just use times, but instead use Date-times, such as
"4/10/05 4:00:00AM"........This would seem to solve the problems associated
with going past the 24 hour mark.........then assuming your start time was
in A1 and your finish time in B1, a Conditional Format formula would be,
=DAY($B$1)>DAY($A$1)

hth
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

I replied before, but with the troubles this afternoon, it appears it didnt
get through......here 'tiz again........

This TimeKeeping thing is not really my forte, but here's my shot.........
First off, I would not just use times, but instead use Date-times, such as
"4/10/05 4:00:00AM"........This would seem to solve the problems associated
with going past the 24 hour mark.........then assuming your start time was
in A1 and your finish time in B1, a Conditional Format formula would be,
=DAY($B$1)>DAY($A$1)

hth
Vaya con Dios,
Chuck, CABGx3

 

[jasperPcuccumber]

Hi Chuck,
I tried this but only get ##########. Maybe more clarification. Try it
once, maybe I am just entering it wrong.

 

[jasperPcuccumber]

Thanks Peo,
This formula works but I think I need a conditional format. I am trying
to set up a spreadsheet that looks like a time card but will add the hours
automatically. The same card may be used for any shift and they do not
always punch at the same time. In other words a1 may be 23:00 for one person
and 6:00 for another person. One punch may look like; 22:00 in 23:45 out
00:15 in 4:00 out. The next day the same persons punch could look like 6:00
in 10:15 out 10:45 in 14:15 out.
Or any other combination in beginning before or after midnight and out
ending before or after midnight. Or any combination of any kind. Any idea???

 

[CLR]

I dunno, it works for me on my XL2k...........notice there is a space
between the date and the time...........make sure that's in there, and the
Conditional Format formula is entered into the window after highlighting B1
and doing Format > ConditionalFormat > select "Formula is" in the left
window and typing =DAY($B$1)>DAY($A$1) into the right window, then >
Format button and selecting a
format, such as "Patterns tab > RED

This will make B1 RED background if the date in B1 is one day or more
greater tan the date in A1.........bringing to your attention that the
midnight hour has passed.........is this what you were looking for?

Vaya con Dios,
Chuck, CABGx3

 

[jasperPcuccumber]

OK, Chuck,
This is getting really complicated. I used the formula again and it
worked, I must have entered it wrong before. Now there is another problem.
When I try to total all the daily totals they come up wrong. Also, the
formula's to separate straight time and overtime quit working.
When daily totals add up to 45:51 the result shown is 21:51. If I format
the cell using custom format [h]:mm:ss the result is correct 45:51:00. I
don't want the seconds to show but cannot figure out a way not to.
This is driving me nuts. Do you have an e-mail I could send this
worksheet to so you could look at it, or do you have an answer for me????
HELP!!!!!!!

 

[CLR]

Ok, I'll be glad to look at it, but I'm getting ready for bed now and I have
to get up in the morning and take one of the cats to the Vet and then go to
work, so it will be tomorrow evening before I can look at it.......If that
timing is ok, send away to
croberts(at)tampabay(dot)rr(dot)com..........otherwise, maybe someone will
jump in here and help.........

Vaya con Dios,
Chuck, CABGx3

OK, Chuck,
This is getting really complicated. I used the formula again and it
worked, I must have entered it wrong before. Now there is another problem.
When I try to total all the daily totals they come up wrong. Also, the
formula's to separate straight time and overtime quit working.
When daily totals add up to 45:51 the result shown is 21:51. If I format
the cell using custom format [h]:mm:ss the result is correct 45:51:00. I
don't want the seconds to show but cannot figure out a way not to.
This is driving me nuts. Do you have an e-mail I could send this
worksheet to so you could look at it, or do you have an answer for me????
HELP!!!!!!!

I dunno, it works for me on my XL2k...........notice there is a space
between the date and the time...........make sure that's in there, and the
Conditional Format formula is entered into the window after highlighting B1
and doing Format > ConditionalFormat > select "Formula is" in the left
window and typing =DAY($B$1)>DAY($A$1) into the right window, then >
Format button and selecting a
format, such as "Patterns tab > RED

This will make B1 RED background if the date in B1 is one day or more
greater tan the date in A1.........bringing to your attention that the
midnight hour has passed.........is this what you were looking for?

Vaya con Dios,
Chuck, CABGx3



it
didnt such
as time
was would
be, If
A1 A1
is 42 out
how show
total