overtime during weekdays and weekend ( more specified)

[Tia]

Hi
I was calculating the ovt hours for my company as follows :
A5=DATE
C5= IN
D5=OUT
E5=TOTAL HOURS
F= REQUIRED HOURS (9:30)
G5= OVT DURING WEEKDAYS =IF(WEEKDAY($A5,2)<6,IF($E5>=TIME(9,30,0),$E5-
TIME(9,30,0),0),0)
H5=OVT DURING WEEKEND =IF(WEEKDAY($A6,2)<6,0,$E6)
THE RULES HAVE CHANGED
I AM LOOKING FOR A FORMULA THAT GIVES ME TOTAL OVERTIME HOURS PER DAY
BASED ON THE FOLLOWING
TOTAL OVT DURING WEEKDAYS FROM MONDAY TILL FRIDAY = TOTAL OVT HOURS
MINUS 2 HOURS
TOTAL OVT HOURS DURING SATURDAYS IS TOTAL WORKING HOURS MINUS 1 HOUR
TOTAL OVT HOURS ON SUNDAY IS TOTAL WORKING HOURS WITHOUT ANY DEDUCTION

PLEASE HELP

 

[Bob Phillips]

One formula for both

=MAX(E5-F5-TIME(2-(WEEKDAY($A5,2)>5)-(WEEKDAY($A5,2)=7),0,0),0)

--
HTH

Bob

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

 

[Tia]

One formula for both

=MAX(E5-F5-TIME(2-(WEEKDAY($A5,2)>5)-(WEEKDAY($A5,2)=7),0,0),0)

--
HTH

Bob

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







- Show quoted text -

Thank you it is working just fine