Total OVT on monthly basis
 

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
I have used this formula
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)

I have used this formula and it worked what i do really need as well
is to have in the same formula a minus in the total whenever the
employee did not reach his 09:30 hours pr day
so i can have in the total OVT pr Weekdays what he really worked as
OVT in this month


waiting for your reply