• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to calculate overtime hours worked per week per employee


New Member

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.
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.


  • Overtime Calculation.xlsx
    18.4 KB · Views: 5