Overtime Calculations

[Michele]

I have figured out how to calculate regular hours and overtime hours on a
daily basis but now I need to take those calculations and determine if an
employee has worked over 37.5 hours in a week (2.5 hours would go to regular
time) and anything over 40 would go to overtime. Our spreadsheet allows each
employee to show each day's clock in and clock out times and then calculates
the regular hours and any overtime hours. I use the following formula for
the regular hours
(IF((($C14-$B14)+($E14-$D14)+($G14-$F14))*24>8,8,(($C14-$B14)+($E14-$D14)+($G14-$F14))*24)
and then the following formula for overtime hours
(IF((($C14-$B14)+($E14-$D14)+($G14-$F14))*24>8,((($C14-$B14+$E14-$D14+$G14-$F14)*24)-8),0)
where B is the in time, C is the out time, D is the in time, E is the out
time, F is the in time and G is the out time.

 

[Elkar]

See if this is something you can work with:

Regular Hours for the week
=MIN(SUM(IF(MOD(COLUMN(Week1),2)=1,Week1,0),-IF(MOD(COLUMN(Week1),2)=0,Week1,0))*24,40)

Overtime Hours for the week
=MAX(SUM(IF(MOD(COLUMN(Week1),2)=1,Week1,0),-IF(MOD(COLUMN(Week1),2)=0,Week1,0))*24-40,0)

Note that these are Array Formulas and must be entered with CTRL-SHIFT-ENTER
instead of just Enter. If done properly, the formula should be enclosed in {
}.

For this example, I created a Named Range of "Week1" which is actually
$B$14:$G$18 representing a 5 day workweek. You can adjust this as needed, or
just insert the range instead of the Name.

HTH,
Elkar