Overtime past 8, 12 and 40 for the week

[Guest]

I'm creating a new spreadsheet that will take the total time each day worked from a separate workbook. The easy part is calculating if it's past 8 hours and past 12 hours.
The problem I'm running into is that the workweek is Sun-Sat, pay period is from the 1-15, 16-31
If I work more than 8 hours it is x1.5, 12 hours is x2. Anything more than 40 is x1.5
Now kick it to the next level, ANYTHING on the 7th consecutive day worked (Sun-Sat) is x1.5 and OVER 8 is x2
Is this going to be WAY too much work? Is there a easier way?

 

[Bob Phillips]

I think this might help.

Assuming that the daily hours for one week are in A1:A7, the hours over 12
are calculated in H1 with
H1: =SUMPRODUCT((A1:A7-12>0)*(A1:A7-12))+IF(COUNT(A1:A7)=7,A7,0)
the hours over 8 are calculated in H2 with
H2: =SUMPRODUCT((A1:A7-8>0)*(A1:A7-8))-SUMPRODUCT((A1:A7-12>0)*(A1:A7-12))#
and the regular hours are just
=SUM(A1:A7)-H1-H2


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I'm creating a new spreadsheet that will take the total time each day

worked from a separate workbook. The easy part is calculating if it's past 8
hours and past 12 hours.

The problem I'm running into is that the workweek is Sun-Sat, pay period is from the 1-15, 16-31.
If I work more than 8 hours it is x1.5, 12 hours is x2. Anything more than 40 is x1.5.
Now kick it to the next level, ANYTHING on the 7th consecutive day worked

(Sun-Sat) is x1.5 and OVER 8 is x2.

 

[markstro]

Or you can try this solution:
TIME CARD CALCULATOR
TOTAL REG OT DT
A B C D E F
1 MONDAY 8.00 8.00 0.00 FALSE FALSE
2 TUESDAY 9.00 8.00 1.00 FALSE 1.00
3 WEDNESDAY 12.00 8.00 4.00 FALSE 4.00
4 THURSDAY 14.00 8.00 4.00 2.00 6.00
5 FRIDAY 9.00 8.00 1.00 FALSE 1.00
6 SATURDAY 9.00 8.00 1.00 FALSE 1.00
7 SUNDAY 10.00 8.00 2.00 FALSE 2.00
8 71.00 56.00 13.00 2.00

FORMULA FOR COLUMN F: =IF(B1>8,B1-8)
FORMULA FOR COLUMN E: =IF(B1>12,B1-12)
FORMULA FOR COLUMN D: =F1-E1
FORMULA FOR COLUMN C: =IF(B1>8,B1-F1,B1)
TOTAL COLUMNS IN ROW 8
I hide column F, don't need to see it for everyday calculations
Look to daily calculations or totals for required computations of overtime.
Hope this helps