Calculating exact shift times and rates per shift.

[MECH427]

Hello,
I am trying to find a couple formulas to extract the amount of hours worked on each shift. Time is entered beginning and end of a shift and may include a portion of all 3 shifts. 1st shift is 6am - 2pm, 2nd shift is 2pm - 10pm, 3rd shift is 10pm - 6am. Example if I punch in at 5:45pm and punch out at 6:30 am my time should show .5 hour on 1st, 4.25 hours on 2nd, and 8 hours on 3rd.
I was able to get 3rd shift, but 1st and second shift is killing me.

Start time (A5) / End Time (B5) / First shift hours (C5) / Second shift hours (D5) / Third shift hours (E5)

Any help for cells C5 and D5 would be greatly appreciated!

Thank you
Cal
*******************

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1st shift[/TD]
[TD="align: center"]6:00:00 AM[/TD]
[TD="align: center"]2:00:00 PM[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2nd shift[/TD]
[TD="align: center"]2:00:00 PM[/TD]
[TD="align: center"]10:00:00 PM[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3rd shift[/TD]
[TD="align: center"]10:00:00 PM[/TD]
[TD="align: center"]6:00:00 AM[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]START
TIME[/TD]
[TD="align: center"]END
TIME[/TD]
[TD="align: center"]1st Shift[/TD]
[TD="align: center"]2nd Shift[/TD]
[TD="align: center"]3rd Shift[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]5:56:00 PM[/TD]
[TD="align: center"]6:15:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]0.25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]4.07[/TD]
[TD="align: center"]8.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]4:00:00 PM[/TD]
[TD="align: center"]4:00:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: center"]6.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]12:00:00 PM[/TD]
[TD="align: center"]12:15:00 AM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: center"]2.25[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]5:40:00 AM[/TD]
[TD="align: center"]5:00:00 PM[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"][/TD]
[TD="align: center"]0.33[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=MOD(B5-A5,1)*24-(B5<A5)*(22-6)-MEDIAN(B5*24,22,6)+MEDIAN(A5*24,22,6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=MOD(B6-A6,1)*24-(B6<A6)*(22-6)-MEDIAN(B6*24,22,6)+MEDIAN(A6*24,22,6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=MOD(B7-A7,1)*24-(B7<A7)*(22-6)-MEDIAN(B7*24,22,6)+MEDIAN(A7*24,22,6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=MOD(B8-A8,1)*24-(B8<A8)*(22-6)-MEDIAN(B8*24,22,6)+MEDIAN(A8*24,22,6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1st Shift​

[/TD]
[TD]

2nd Shift​

[/TD]
[TD]

3rd Shift​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

StartTime​

[/TD]
[TD]

6:00:00 AM​

[/TD]
[TD]

2:00:00 PM​

[/TD]
[TD]

10:00:00 PM​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

EndTime​

[/TD]
[TD]

2:00:00 PM​

[/TD]
[TD]

10:00:00 PM​

[/TD]
[TD]

6:00:00 AM​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

StartTime​

[/TD]
[TD]

EndTime​

[/TD]
[TD][/TD]
[TD]

1st Shift​

[/TD]
[TD]

2nd Shift​

[/TD]
[TD]

3rd Shift​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

5:56:00 PM​

[/TD]
[TD]

6:15:00 AM​

[/TD]
[TD][/TD]
[TD]

0,25​

[/TD]
[TD]

4,07​

[/TD]
[TD]

8,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

4:00:00 PM​

[/TD]
[TD]

4:00:00 AM​

[/TD]
[TD][/TD]
[TD]

0,00​

[/TD]
[TD]

6,00​

[/TD]
[TD]

6,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

12:00:00 PM​

[/TD]
[TD]

12:15:00 AM​

[/TD]
[TD][/TD]
[TD]

2,00​

[/TD]
[TD]

8,00​

[/TD]
[TD]

2,25​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

5:40:00 AM​

[/TD]
[TD]

5:00:00 PM​

[/TD]
[TD][/TD]
[TD]

8,00​

[/TD]
[TD]

3,00​

[/TD]
[TD]

0,33​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

5:45:00 PM​

[/TD]
[TD]

6:30:00 AM​

[/TD]
[TD][/TD]
[TD]

0,50​

[/TD]
[TD]

4,25​

[/TD]
[TD]

8,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in D6 copied across and down
=MAX(0,MIN($B6+($A6>$B6),D$3+($A6>=D$3))-MAX($A6,D$2-(D$2>D$3)+($A6>=D$3)))*24

Format D6:F10 as Number with two decimal places

Hope this helps

M.

 

[MECH427]

Thank you Marcelo.

Time and date calculations really get the better of me! I appreciate you taking the time and effort to help me solve this!

Cal

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.