formula to calculate multiple overtime rules and include double time, odd scenarios

Roboroadie

New Member
Joined
Aug 21, 2016
Messages
9
Hello all , I have been struggling for months to get this just right and can not seem to figure out how to do it, I was hoping maybe some of you had some ideas. here is the scenario: our company uses odd overtime rules for daily work they are as follows: any work done between midnight and 8 am is overtime, over 10 hours is overtime (without a 1 hr break), certain holidays are overtime (got that part figured out) sunday is overtime (got that part figured out)
here is where it gets tricky if they go into work at 11 pm on saturday and work until 3 am sunday that is two overtime rules coming together to make double time, now if they work over 10 hours without a break during that time it would become 3 overtime rules coming together, this is where it gets extremely tricky and I get lost, some of the formulas are fairly easy such as =IF(B1-A1>10,10-B1-A1,B1-A1) that does my 10 hours it may be a little off from what I actually use but you get the idea i've tried combining these and it fails, not sure where I am going wrong. I am able to provide any additional info needed to help me through this problem, one other part of the equation is we have 3 possible shifts in one day so it would be start and end 3 times across 6 columns with two more columns for a 1 hr break deduction, let me know if this can even be done in excel, Thank you in advance
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,215,374
Messages
6,124,571
Members
449,173
Latest member
Kon123

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