formula help

[Bradley]

Hi! there,
I have a time sheet format for employees and
I just don't know how to create the formula for overtime.
I only want to input finished time in column C.
I don't have much experience in formula function.
The timesheet example is below and please help me to
solve.
Thanks.

A B C D E
1 30-01-04 08:00 18:00 1:00
2 31-01-04 08:00 12:00
3 01-02-04 08:00 14:00 5:00
4 07-02-04 08:00 17:00 8:00
5 08-02-04 08:00 12:00 4:00

Column (A) is Date
(B) is started time
(C) is finished time
(D) is overtime hour for normal day and Saturday
(E) is overtime hour for Sunday (different OT rate)
Normal working hour is 08:00 to 17:00
On Saturday 08:00 to 12:00
Off on Sunday.
Lunch time is 12:00 to 13:00
What I want is
formula for normal OT hours in column D and
formula for Sunday & Public holiday OT hours in column E

For column D
(1) count OT hour after 17:00 if the date is Mon to Fri
(2) blank cell if the date is Sunday or no OT or 12:00 on
Saturday or Public holiday
(3) count OT hour after 13:00 if the date is Saturday

For column E
(1) count OT hour from started time "B" to finished
time "C" if the date is Sunday & Public holiday
(2) deduct 1 hour for lunch if finished time is more than
12:00
(3) do not deduct 1 hour if finished time is 12:00
(4) blank cell if the date is Monday~Saturday

Thanks again for your time.
Bradley

 

[JE McGimpsey]

Take a look here for some ideas on handling overtime:

Overtime hours in timesheets
http://cpearson.com/excel/overtime.htm

 

[Guest]

Hi!
McGimpsey,

I had already found the web site
http://cpearson.com/excel/overtime.htm before
and it couldn't help me much.
I just want to know how to calculate
4 conditions in a cell.
Anyway thanks.

Bradley