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

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" wrote in message
...
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

On Jun 24, 11:22*am, "Bob Phillips" wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -


Thank you it is working just fine