Overtime Calculation for Overtime

S

SilviaG

I have a timecard spreadsheet in excel that automatically calculates how much
an employee worked on a day.
ex:
Clock in Lunch out Lunch in End of day TOTAL
8:30 am 12 pm 1 pm 6:00 p 8.50

I am trying to create a column in which the overtime will be carried onto
another column. So it will look something like this:

Clock in Lunch out Lunch in End of day Reg Hrs OT
8:30 am 12 pm 1 pm 6:00 p 8 .50

Is there a way in which it will done like this atumatically?
 
T

T. Valko

Try these...

Reg hours:

=MIN(8,((D2-C2)+(B2-A2))*24)

OT hours:

=MAX(0,((D2-C2)+(B2-A2))*24-8)
 
J

joeu2004

I am trying to create a column in which the overtime will be carried onto
another column. So it will look something like this:

Clock in Lunch out Lunch in End of day Reg Hrs OT
8:30 am 12 pm 1 pm 6:00 p 8 .50

Is there a way in which it will done like this atumatically?

Ostensibly, if the TOTAL is in column G (after "OT"), the formuals for
"Reg Hrs" and "OT" are:

Reg Hrs in E2: =max(8, G2)

OT in F2: =G2 - E2
....or =if(E2 = G2, "", G2 - E2)

Caveat: In some jurisdictions, overtime computation is much more
complex. For example, in Calif (US), it is something like the amount
of time in excess of 8 hours in a day, and the amount of time in
excess of 40 hours in a week. Additionally, any time on days not
considered part of the "normal week" is considered overtime.
Moreover, there might be different overtime rates for the different
categories of overtime. Check your local laws.


----- original posting -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Variable Pay-rate after 5pm 1
Overtime Calculation 1
Amending meeting cancels all future meetings 0
Overtime formulas 14
Rows to table 0
overtime calculation 1
Adding hours in Excel 3
OVERTIME AND DOUBLETIME 4

Top