Time Card

[Lorrie]

I am having a problem producing a time card for our
company that can assume 7:30 reg hours a day. The first
1/2 hr of overtime is paid at straight time and the
remainder is paid at overtime rates. Some people,
however, frequently work 15 minutes over so I need the
formula to fit that scenario. If you can't help me with
this can you at least tell me where I might find the
information that would help me figure it out on my own.

sample:

M3=7:30 (reg hrs per day)
I J K L
Total REGULAR EXTRA HRS EXTRA HRS
hours HOURS (ST RATE) (OT RATE)
8:00 7:30 :30 0:00
10:00 7:30 :30 2:00
4:00 4:00 :30 0:00
The formula above produces the
wrong results.If J11 is not greater than or equal to
7:30, which it is not (4:00) it should be "-". But it's
not. Here, I need to produce the number of hours over
8 starting with the "I" column figure. I'm concerned if
some works 7:45.then I have that formula wrong as well,
because the results there should then be :15.

 

[Guest]

Assuming the first data set is in row three, and you want the standard pay to be in cell M3

=J3*(reg pay rate

If you want the over time amount in N3, you can use

=K3*(reg pay rate)+L3*(overtime pay rate

The J3, K3 and L3 are references which refer to the cells which contain the number of hours. You can also use formulas with references to calaculate the split of hours, if you haven't already

Good Luck
Mark Graesse
(e-mail address removed)


----- Lorrie wrote: ----

I am having a problem producing a time card for our
company that can assume 7:30 reg hours a day. The first
1/2 hr of overtime is paid at straight time and the
remainder is paid at overtime rates. Some people,
however, frequently work 15 minutes over so I need the
formula to fit that scenario. If you can't help me with
this can you at least tell me where I might find the
information that would help me figure it out on my own.

sample

M3=7:30 (reg hrs per day
I J K
Total REGULAR EXTRA HRS EXTRA HRS
hours HOURS (ST RATE) (OT RATE
8:00 7:30 :30 0:0
10:00 7:30 :30 2:0
4:00 4:00 :30 0:0
The formula above produces the
wrong results.If J11 is not greater than or equal to
7:30, which it is not (4:00) it should be "-". But it's
not. Here, I need to produce the number of hours over
8 starting with the "I" column figure. I'm concerned if
some works 7:45.then I have that formula wrong as well,
because the results there should then be :15.