Calculating Cycle Time with Off Hours

[harryandthepeapods]

Hello, I have a problem.

I'm trying to calculate cycle time for a particular stage at my work. I have time stamps that are in the date time format. I can subtract them easily to find out how long it took. The problem is that I need to account for off hours. The department that does these tasks works in shifts from 8am till 8pm. I only want that time in a given day to count towards their cycle time.

So if a task started on 12/28/2012 14:29 and was completed at 12/31/2012 12:31 I don't want it to return 2.91... I want it to return something like 1.729... Thanks!

 

[Matt Rogers]

Hi,
a proposal:

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='6' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='119,25pt'><col width='119,25pt'><col width='119,25pt'><col width='119,25pt'><col width='72pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' >start</td><td align='middle' >end</td><td align='middle' >total hrs</td><td align='middle' >real time hrs</td><td align='middle' >?</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' >12/28/12 14:29</td><td align='middle' >12/31/12 12:31</td><td align='middle' >70:02</td><td align='middle' >34:02</td><td align='middle' >1.418055556</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' style='font-size:9px; ' >MM/DD/YY h:mm</td><td align='middle' style='font-size:9px; ' >MM/DD/YY h:mm</td><td align='middle' style='font-size:9px; ' >[h]:mm</td><td align='middle' style='font-size:9px; ' >[h]:mm</td><td align='middle' style='font-size:9px; ' >General</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' > </td><td align='middle' > </td><td align='middle' > </td><td align='middle' > </td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' >work time start</td><td align='middle' >work time end</td><td align='middle' >hrs</td><td align='middle' >daily work time</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' >8</td><td align='middle' >20</td><td align='middle' >12</td><td align='middle' >0.5</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' style='font-size:9px; ' >General</td><td align='middle' style='font-size:9px; ' >General</td><td align='middle' style='font-size:9px; ' >General</td><td align='middle' style='font-size:9px; ' >General</td><td align='right' > </td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td><Span style='color:#222222'>=B2-A2</Span></td></tr><tr><td>D2</td><td><Span style='color:#222222'>=</Span><Span style='color:#0000DD'>(B6/24)</Span><Span style='color:#222222'>-</Span><Span style='color:#0000DD'>(A2-INT</Span><Span style='color:#222222'>(A2)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>+</Span><Span style='color:#0000DD'>(ROUNDDOWN</Span><Span style='color:#222222'>(B2,0)</Span><Span style='color:#0000DD'>-ROUNDUP</Span><Span style='color:#222222'>(A2,0)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>*D6+</Span><Span style='color:#0000DD'>(B2-INT</Span><Span style='color:#222222'>(B2)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>-8/24</Span></td></tr><tr><td>E2</td><td><Span style='color:#222222'>=D2</Span></td></tr><tr><td>C6</td><td><Span style='color:#222222'>=B6-A6</Span></td></tr><tr><td>D6</td><td><Span style='color:#222222'>=C6/24</Span></td></tr></table>

 

[Matt Rogers]

Correction:

Formula D2:
=(B6/24)-(A2-INT(A2))+(ROUNDDOWN(B2,0)-ROUNDUP(A2,0))*D6+(B2-INT(B2))-A6/24

 

[harryandthepeapods]

Thank you so much Matt! This equation is brilliant!