overtime computation

  • Thread starter Thread starter jv
  • Start date Start date
J

jv

I have this spreadsheet wherein i input all the time in
and out of personnel in a daily basis. All sheets have
the same format, and each sheet was named after its date.
example, 01-Oct(sheet1), 02-Oct(sheet2) and so on.

I need to have a computation that will automatically
calculate the overtime in a day and come up with total OT
for each personnel in a given month.

A B C D E F
# Name In Out Ttl Hr. OT
1. Smith 8:00 5:30 8 -
2. Johnson 8:00 7:30 10 2
3. Frank 15:00 00:30 8 -

Question:
1 - in column "E" i need a formula that will automatically
deduct 1.5hrs from its total ( C3-D3 - 1.5 )
2 - a formula for column "F" that will reflect the OT
based on the result of column "E".
3 - collect datas from all the sheets and reflect the
total OT hrs.in a separate sheet.

A B C D
# Name Ttl Hr. Total OT
1. Smith
2. Johnson
3. Frank

Any help to simplify my work is appreciated.

Thanks in advance.

jv
 
JV,

the formula for time worked would be, in E2,
=(D2-C2)*24+(D2<C2)*24-1.5
but make sure that the end time is actual time, e.g. 17:30, not 5:30

the formula for OT is, in F2,
=MAX(0,E2-8)

To get a summary on anther sheet, use

=SUM('01-Oct:31-Oct'!F2)

--

HTH

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