I am looking to perform a calculation to ascertain an employees overtime hours per week. There are a number of factors that I need to take into consideration when calculating:

1. The number of hours per day is 8, and the number of hours per week is 40
2. Overtime only kicks in if the hours number of hours for the day is greater than 8 AND the number of hours in a week is greater than 40
3. Consideration is required for the employee taking a Personal Holiday, a Sick Day or if there is a Public Holiday. If any or some of these are the case, then the total number of hours in the week should be reduced by a day (8 hours) for each day that the employee is out. So if for example there is a Bank holiday and they have taken a days holiday, the total number of hours in the week would be 24, and so any hours over 8 per day and 24 per week should be calculated and displayed in the Overtime hours

The calculation should be dynamic and is based of the column entitled "Week Number".

Please see the screenshot of the worksheet I am using:

Table updated with Data below, now showing a week with overtime, and also saturday work: (humbelest apologies for the stat that this looks, but I am still learning in this space...)

I am looking to perform a calculation to ascertain an employees overtime hours per week. There are a number of factors that I need to take into consideration when calculating:

1. The number of hours per day is 8, and the number of hours per week is 40
2. Overtime only kicks in if the hours number of hours for the day is greater than 8 AND the number of hours in a week is greater than 40
3. Consideration is required for the employee taking a Personal Holiday, a Sick Day or if there is a Public Holiday. If any or some of these are the case, then the total number of hours in the week should be reduced by a day (8 hours) for each day that the employee is out. So if for example there is a Bank holiday and they have taken a days holiday, the total number of hours in the week would be 24, and so any hours over 8 per day and 24 per week should be calculated and displayed in the Overtime hours

The calculation should be dynamic and is based of the column entitled "Week Number".

Please see the screenshot of the worksheet I am using:

Table updated with Data below, now showing a week with overtime, and also saturday work: (humbelest apologies for the stat that this looks, but I am still learning in this space...)

• excel
• excel-formula
• dynamic

• 2 Please post sample data ( tablesgenerator/markdown_tables#google_vignette ) not an image. Make the data reproducible to your problem. Your problem includes bank/personal holidays, your image doesn't show how. Please also have expected results with given data. Right now we don't know how you want the result or how the data looks like in different scenarios – P.b Commented Mar 2 at 18:25
• Are Sat&Sun always non-working? Based on your explanation there is no overtime in your sample data; is this correct? what is the Week number? does it restart in Feb (how do you handle the partial week at the end/start of the year)? – tinazmu Commented Mar 2 at 22:45
• Saturday and sunday are can be worked, as per the updated data, and so should be taken into consideration. Sheet has now been updated ton include overtime. The Week Number relates to the actual week of the year, and continues to 52/53. Partial Week at start and end are usually non working weeks. Thank you for your help. – John Hendrick Commented Mar 3 at 0:15
• Which part is your desired output? – Harun24hr Commented Mar 3 at 2:21
• Formulas should now be showing The desired output is to show the correct Overtime amount. – John Hendrick Commented Mar 3 at 15:45

2 Answers 2

Paste this in a new sheet:

I have resolved my query. What I was wanting to do was to calculate the overtime based on the Total Number of hours worked in a week, the standard hours being 40 per week and 8 per day. Overtime is only paid once both thresholds are breached. Thank you all for your help, it was veery much appreciated. Especially Black cat, as the reminder about SUM(FILTER()) function was key.

Kind Regards, John