Calculate Overtime with multiple criteria

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
221
Office Version
  1. 2016
Platform
  1. Windows
I am trying to make a timesheet that calculates overtime with the following criteria...

1. Overtime is after 8hrs on any given day.
2. Overtime is after 40hrs/week...consideration to the after 8hr criteria to ensure not duplicating overtime.
3. The after 40hrs/week overtime criteria changes if there is a Stat holiday...if there is one stat holiday in the week overtime is calculatd after 32hrs/week,
if there is two stat holiday in the week overtime is calculatd after 24hrs/week etc. Basically for every stat holiday during the week the overtime threshhold changes by 8hrs.

Spreadsheet is set-up with days of the week across the columns, employee names down the rows.
Daily total hours for each employee are entered in the appropriate cells and the formulas need to do all the overtime calculations.
I was planning on using text "Stat" in the cells were a stat holiday occured.

I have managed to calculate all the criteria except the "Stat" holiday criteria without circular references but I am now stumped and have not found a solution on any posts.
 

Excel 2010
ABCDEFG
7WeeksWeek 1Week 2Total
8NameStartingAmountAmountAmount
9Joe17-Feb-19$695.00$690.00$1,385.00
10Fred17-Feb-19$1,762.50$1,702.50$3,465.00
11
Summary_Time
Cell Formulas
RangeFormula
E9=SUMIFS(Time!O:O,Time!A:A,A9,Time!C:C,"="&C9)
E10=SUMIFS(Time!O:O,Time!A:A,A10,Time!C:C,"="&C10)
F9=SUMIFS(Time!O:O,Time!A:A,A9,Time!C:C,"="&C9+7)
F10=SUMIFS(Time!O:O,Time!A:A,A10,Time!C:C,"="&C10+7)
G9=E9+F9
G10=E10+F10
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top