overtime computation

[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

 

[Peo Sjoblom]

In E

=(D2-C2)*24-1.5

in F

=MAX(0,E2-8)

 

[jv]

Thanks Peo.

Do you have any comments on my # 3 enquiry.


jv

 

[Bob Phillips]

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)

 

[jv]

Bob,

Thank you for helping me out.


jv

-----Original Message-----
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)




.