Overlapping hours more than 37 hours paid in a 24 hour period

[Jadezircon]

Hello, this is my first post. I need some help. This is a problem to be solved and it has been awhile since I had to calculate something this complex. The sales person is in a store working but the time sheets show more than 24 hours per day, up to 37 hours working and paid for. The scenario is below. Basically

Column A is the Date by day month and year
Column B is the time in by department
Column C is the time out by the department.\
There are up to 7 departments more in two columns each per department in and out shown below.
Each department pays differently and the timesheets overlap.

First I need to calculate across overlapping hours, then apply the correct rate to the overlap for reimbursement.

Your guidance would be greatly appreciated.



[TABLE="width: 927"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9"]Jane works at a Department store like JC Penny's, however there is no clock punch in or out. </SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Military Time</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 7"]When Jane works in Jewelry or Cosmetics, she is paid $16.00 per hour</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]=time plus 12 hours</SPAN>[/TD]
[TD="colspan: 7"]When Jane works in the Shoe Department she only makes $10.00 per hour</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Midnight is 0:00</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 7"]When Jane is on the floor directing people, she is paid only $8.00 per hour</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Noon is 12:00</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 6"]When Jane is a greeter at the front door she make $7.90 per hour</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>[/TD]
[TD="colspan: 9"]I need to be fair to the employee and only request the lowest paid overlapping hours back. </SPAN></SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]$16.00 In Jewelry </SPAN>[/TD]
[TD]Out of Jewelry</SPAN>[/TD]
[TD]$16.00 In Cosmetics</SPAN>[/TD]
[TD]Out of Cosmetics</SPAN>[/TD]
[TD]$10.00 In the Shoe Department</SPAN>[/TD]
[TD]Out of the Shoe Department</SPAN>[/TD]
[TD]$8.00 In Directing People</SPAN>[/TD]
[TD] Out Directing People</SPAN>[/TD]
[TD]$7.90 In Greeting People</SPAN>[/TD]
[TD]$7.90 Greeting People Out</SPAN>[/TD]
[TD]$7.80 In Janes Cosmetics</SPAN>[/TD]
[TD] Out of Janes Cosmetics</SPAN>[/TD]
[TD]$7.80 In Janes Cosmetics</SPAN>[/TD]
[/TR]
[TR]
[TD]Date</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2011</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21:30</SPAN>[/TD]
[TD="align: right"]11:20</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2011</SPAN>[/TD]
[TD="align: right"]13:00</SPAN>[/TD]
[TD="align: right"]19:00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2011</SPAN>[/TD]
[TD="align: right"]16:00</SPAN>[/TD]
[TD="align: right"]6:00</SPAN>[/TD]
[TD="align: right"]6:30</SPAN>[/TD]
[TD="align: right"]10:30</SPAN>[/TD]
[TD="align: right"]5:00</SPAN>[/TD]
[TD="align: right"]9:00</SPAN>[/TD]
[TD="align: right"]5:00</SPAN>[/TD]
[TD="align: right"]9:00</SPAN>[/TD]
[TD="align: right"]12:00</SPAN>[/TD]
[TD="align: right"]14:00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13:00</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1/4/2011</SPAN>[/TD]
[TD="align: right"]16:00</SPAN>[/TD]
[TD="align: right"]6:00</SPAN>[/TD]
[TD="align: right"]6:30</SPAN>[/TD]
[TD="align: right"]10:30</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5:00</SPAN>[/TD]
[TD="align: right"]9:00</SPAN>[/TD]
[TD="align: right"]12:00</SPAN>[/TD]
[TD="align: right"]14:00</SPAN>[/TD]
[TD="align: right"]21:30</SPAN>[/TD]
[TD="align: right"]11:20</SPAN>[/TD]
[TD="align: right"]13:00</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2011</SPAN>[/TD]
[TD="align: right"]16:00</SPAN>[/TD]
[TD="align: right"]6:00</SPAN>[/TD]
[TD="align: right"]6:30</SPAN>[/TD]
[TD="align: right"]10:30</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5:00</SPAN>[/TD]
[TD="align: right"]9:00</SPAN>[/TD]
[TD="align: right"]12:00</SPAN>[/TD]
[TD="align: right"]14:00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13:00</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2011</SPAN>[/TD]
[TD="align: right"]16:00</SPAN>[/TD]
[TD="align: right"]6:00</SPAN>[/TD]
[TD="align: right"]6:30</SPAN>[/TD]
[TD="align: right"]10:30</SPAN>[/TD]
[TD="align: right"]14:00</SPAN>[/TD]
[TD="align: right"]16:00</SPAN>[/TD]
[TD="align: right"]5:00</SPAN>[/TD]
[TD="align: right"]9:00</SPAN>[/TD]
[TD="align: right"]12:00</SPAN>[/TD]
[TD="align: right"]14:00</SPAN>[/TD]
[TD="align: right"]21:30</SPAN>[/TD]
[TD="align: right"]11:20</SPAN>[/TD]
[TD="align: right"]13:00</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2011</SPAN>[/TD]
[TD="align: right"]16:00</SPAN>[/TD]
[TD="align: right"]6:00</SPAN>[/TD]
[TD="align: right"]6:30</SPAN>[/TD]
[TD="align: right"]8:30</SPAN>[/TD]
[TD="align: right"]14:00</SPAN>[/TD]
[TD="align: right"]16:00</SPAN>[/TD]
[TD="align: right"]5:00</SPAN>[/TD]
[TD="align: right"]9:00</SPAN>[/TD]
[TD="align: right"]12:00</SPAN>[/TD]
[TD="align: right"]14:00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13:00</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2011</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5:00</SPAN>[/TD]
[TD="align: right"]9:00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21:30</SPAN>[/TD]
[TD="align: right"]11:20</SPAN>[/TD]
[TD="align: right"]13:00</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2011</SPAN>[/TD]
[TD="align: right"]12:00</SPAN>[/TD]
[TD="align: right"]18:00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5:00</SPAN>[/TD]
[TD="align: right"]9:00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=2><COL><COL><COL></COLGROUP>[/TABLE]

 

[Jadezircon]

Test