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)

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

"jasperPcuccumber" wrote in
message ...
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)

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" wrote in
message ...
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)

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" wrote:

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" wrote in
message ...
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)

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

Vaya con Dios,
Chuck, CABGx3


"jasperPcuccumber" wrote in
message ...
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" wrote:

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" wrote in
message ...
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)

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?

"CLR" wrote:

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

Vaya con Dios,
Chuck, CABGx3


"jasperPcuccumber" wrote in
message ...
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" wrote:

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" wrote in
message ...
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)

This will work over midnight

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

--
Regards,

Peo Sjoblom


"jasperPcuccumber" wrote in
message ...
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?

"CLR" wrote:

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

Vaya con Dios,
Chuck, CABGx3


"jasperPcuccumber" wrote in
message ...
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" wrote:

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" wrote
in
message ...
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)

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" wrote:

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

Vaya con Dios,
Chuck, CABGx3


"jasperPcuccumber" wrote in
message ...
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" wrote:

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" wrote in
message ...
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)

Between which 2? The quitting time (before or after midnight) or when they
take their lunch break?

If it's the former, and you have start time in A1, lunch start in B1, lunch
end in C1, quitting time in D1, and those times do NOT include the date, the
formula

=(D1<A1)

will be true for those who quit after midnight, false for those who quit
before midnight.


On Sat, 9 Apr 2005 21:23:02 -0700, "jasperPcuccumber"
wrote:

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" wrote:

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

Vaya con Dios,
Chuck, CABGx3


"jasperPcuccumber" wrote in
message ...
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" wrote:

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" wrote
in
message ...
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)

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" wrote in
message ...
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" wrote:

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

Vaya con Dios,
Chuck, CABGx3


"jasperPcuccumber" wrote in
message ...
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" wrote:

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"
wrote in
message ...
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)