Overtime Calculation for Overtime

  • Thread starter Thread starter SilviaG
  • Start date Start date
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?
 
Try these...

Reg hours:

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

OT hours:

=MAX(0,((D2-C2)+(B2-A2))*24-8)
 
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?
Click to expand...

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 -----
 
Back
Top