Problems with calculating hours worked

[Gusty]

Hi we have a cheapskate Time & Attendance system which produces the attached report downloaded into excel. We them manually calculate the hours worked and send on to the payroll bureau. In this example, basic hours would be 7.5 and overtime at time and hallf would be 2hours.We only pay dble time at weekends. I would like to automate but am not sure how to calc the hours accurately.

Standard hours on the dayshift are 8.00 am to 12.00 and 12.45 to 4.15pm

Any early starts before 8.00 am are paid in 15 min blocks at time and a half.
Any overtime after 4.15 is also paid in 15 min blocks at time and a half.

Help!

[Bob Phillips]

This will calculate the standard hours

=MIN(TIME(7,30,0),SUMPRODUCT(--(J14:J16)))

overtime

=ROUND((SUMPRODUCT(--(J14:J16))-K16)*96,0)/96

is all weekends at double?

[Gusty]

Hi quick response very impressive. Just to complicate things .....
Standard hours Saturday are 6.00am till 2 pm - first 4 hours at time + half remaining 4 at double time. Suinday std hours the same but all at double time. Just one question what is the significance of the 96 in your formula?

[Bob Phillips]

K16: =IF(WEEKDAY(F13,2)>=6,0,MIN(TIME(7,30,0),SUMPRODUCT(--(J14:J16))))

L16: =MIN(TIME(4,0,0)-(WEEKDAY(F13)=1)*TIME(4,0,0)+(WEEKDAY(F13,2)<6)*TIME(12,30,0),ROUND((SUMPRO DUCT(--(J14:J16))-K16)*96,0)/96)

M16: =MAX(0,ROUND((SUMPRODUCT(--(J14:J16))-K16)*96,0)/96-K16-L16)

[RonMcK]

... Just one question what is the significance of the 96 in your formula?

Since 96 is 4 * 24, I would intuit that this is getting you the 1/4 hour increments for O/T before/after regular/std hours.

HTH,

[Bob Phillips]

Correct!

[david000]

Change:
Cover midnight shift!

J14 to =MOD(H14-H13,1)
J16 to =MOD(H16-H15,1)

And hey xld ---

I belive Sumproduct is optional in those right?
regular Sum would work too.

[Bob Phillips]

No it wouldn't. The original workbook has text fields and

=SUM(J14:J16)

yields 0, and trying to corece text to numbers via

=SUM(--(J14:J16))

yields #VALUE, whereas SUMPRODUCT ....

[Bob Phillips]

And where does a midnight shift come into it?

[david000]

And where does a midnight shift come into it?

Oh, on sheet1 row 109 the dude started at 22:39:47

In I thought is would be big trouble later so, well you know...and your right about SP I changed the text to number right of the bat sorry I did it without thinking.I went stright to the format [h]:00:00 in case of >24hrs then sum worked for ma at least.

[Bob Phillips]

It does with real numbers, the poster just had an odd worksheet.

[david000]

Still, that is THE best use of that double unary operator I?ve ever seen.
Will use it!