E5: =IF(WEEKDAY($A5,2)<6,IF($E5=TIME(11,30,0),$E5-TIME(9,30,0),0),0)
F5: =IF(WEEKDAY($A5,2)<6,0,$E5)

What happens if they stay say an extra hour every day, how does that get
factored in?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tia" wrote in message
...
Hello,
Im working on a workbook that calculates total working hours on
monthly basis for each employee each employee should work 09:30 on
daily baisis
i am trying to find a formula that gives me the total of Over time
hours
During Weekdays: 1st 2 OVT hours will not be considered as an OVT but
if u stayed more than 2 hours on the same day u will add to ur
balance the 2 first hours as well.
During weekends and holidays : all attendance hours are considered as
OVT
my sheet is this way
A5=Date
C5=In
D5=Out
E5=Total hours
what i need is
Weekend OVT Hours F5
Weekdays OVT hours=g5

Waiting for your reply
Tia