Formula to calculate overtime hours worked per week per employee

[HafNah1951]

Hello

I need help to build an excel formula for the bellow scenario

Column A has week number

Column B has employee name

Column C represent whether a particular day in a week is a public holiday

Column D represents overtime hours worked daily

In column E I need to formula to calculate total overtime hours eligible per employee per week.

Note eligibility of overtime is when the employee has exceeded 45 hours worked in a week.

Also note on public holiday, employee gets the full overtime hours worked without having to consider the statutory 45 hours of work in a week.

So these two conditions need to be considered in the formula.

Thank you in advance.

 

[Hany ali]

where's the excel file ?!!!!!!!!!!!!!!!!!

 

[HafNah1951]

Hello see attached,
Column N and O requires formula
N6 should give 6.5 as result since hours worked should be fully considered on public holidays
N7 should give 6.5+3.5 as result. The 3.5 is obtained by taking into consideration that in a week there was one public holiday and 4 normal days. The employee should have worked (9*4)=36 but has worked ((9*3)+12.5)=39.5.
N8 should give only 0.5 as result as the overtime relates to another employee.
Please help to edit formula in column N and insert a formula in column O which should generate the total overtime hours per employee per week which the employee will be paid.