Overtime calculations

[Tia]

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

 

[Bob Phillips]

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]

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)







- Show quoted text -

as pr our company rules if u stayed less than 2 hours as ovt each day
u wont get payed anything but u will have it as a bonus at the end of
the year so i need a formula that gives me the total of ovt after the
2 hours including the 2 hours

 

[Bob Phillips]

That I gave you.

I bet you don't get many people working over unless they know it will be
more than 2 hours.

--
---
HTH

Bob


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

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)







- Show quoted text -

as pr our company rules if u stayed less than 2 hours as ovt each day
u wont get payed anything but u will have it as a bonus at the end of
the year so i need a formula that gives me the total of ovt after the
2 hours including the 2 hours