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