Shift totals according to a standard Time Clock

[KLprogressive]

My goal is to differentiate a standard timecard's hours into three shift totals.


My Shifts are 7:00 am to 3:00 pm, 3:00 pm to 11:00 pm and 11:00 pm to 7:00 am. (7-3, 3-11, 11-7)


I want to be able to show my "Check in/Check out, Check in/Check out" in four columns to the left and then on the right have it show my shift totals for the amount of time worked. The thing is, time can't overlap in the shift totals


Example: If someone works from 6:50 am to 11:10 pm (6:50 am/3:00 pm [lunch break] 3:30 pm/11:10pm)


Since the person came in 10 minutes before shift1(7-3) that time would go to shift three(11-7). then this person worked 8.00 hours in shift1. started again at shift2 at 3:30 pm and work until 11:10 pm. so that would be 7.50 hours in shift2 and the remaining 10 minutes would fall into shift3.

I need to know, how much time was spent working in each shift. (preferably in a format which reads x.xx)

Shift Totals

Check In Check Out Check In Check Out "TotalHrs" 7/3 3/11 11/7
6:50 AM 3:00 PM 3:30 PM 11:10 PM 15.83 ? ? ?



Please Help me with the proper formulas and formatting, I've been staring at this problem so long I'm "solution blind".

Thank you,

Kyle

 

[KLprogressive]

I have come to a solution on laying my formula's out correctly using if statements. Now the major thing that i'm having problems with is the switch over from P.M time to A.M time. It's throwing off my formulas and I don't understand how to overcome it.

So for instance.
for my pm shift staff, who work mainly from 3/11 and sometimes over from 11/7.
my formula to calculate the 11/7 shift (which is pm to am) is


=IF(SUM(D34:G34)=0,0,IF(G34<=0.958333,IF(E34>$K$2,(E34-0.958333),0+IF(F34<=$K$2,($L$2-G34)+($L$2+$K$2),(($L$2+F34)+($L$2-G34))*24))))

D34=check in 1, E34=check out 1
F34=check in 2, G34= check out 2
0.958333 is excel format for 11:00 pm
$k$2 = a cell which contains the time formatted 11:00 pm
$L$2 = a cell which contains the time formatted 12:00 am

adding or subtracting time that's technically from the previous day (from am to pm) seems to be screwing me up.

Any Idea's?