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

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)

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.

"Peo Sjoblom" wrote:

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)

All the employees may or may not punch at any time. Sometimes any punch
coulc be before midnight and any punch could be after midnight. I need
conditional format that discerns 24 hour clock, so a punch as follows would
work;

A1 21:05 in B1 23:45 out C1 00:15 in D1 4:00 out
or
A1 23:00 in B1 00:45 out C1 1:15 in D1 5:05 out
or any other combination that spans over the midnight hour.

"Myrna Larson" wrote:

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)

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

"CLR" wrote:

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)

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???

"Peo Sjoblom" wrote:

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)

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" wrote in
message ...
Hi Chuck,
I tried this but only get ##########. Maybe more clarification. Try it
once, maybe I am just entering it wrong.

"CLR" wrote:

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)

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

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" wrote in
message ...
Hi Chuck,
I tried this but only get ##########. Maybe more clarification. Try it
once, maybe I am just entering it wrong.

"CLR" wrote:

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)

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..........other wise, maybe someone will
jump in here and help.........

Vaya con Dios,
Chuck, CABGx3




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

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" wrote in
message ...
Hi Chuck,
I tried this but only get ##########. Maybe more clarification. Try
it
once, maybe I am just entering it wrong.

"CLR" wrote:

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)