Total Time with conditions

[Tazzbjs]

I the chart below I need to find the total time from first pick-up to actual pill-in time. But here is where it gets complicated, the total time can not be more then 20 minutes past the last drop off time. Example: 1st pu was 5:01 last drom was 11:28 and pull in was 11:45. the total time to last drop was 6 hours 27 minutes (6:27) the total to pull in was 6 hours 44 minutes (6:44) a difference of only 0:17 minutes. this one would be fine. the first line in the example below has a difference 1 hour 6 minutes (1:06) and exceeds the 20 minutes allowed and the total time would be 6 hours 47 minutes (6:47). I need the formula that can give me these total times, if possible. I've been looking everywhere and cannot find one. Any assistance is greatly appreciated.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

<TABLE style="WIDTH: 432pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=576 x:str><COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 45pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl163 height=60 rowSpan=3 width=64>Run Start Time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl181 rowSpan=3 width=64>1st PU Time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl181 rowSpan=3 width=64>Last DO Time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl163 rowSpan=3 width=64>Actual Pull-in Time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl163 width=64> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl184 rowSpan=3 width=64>Total Time</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl165 width=64> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl164> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl166 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl167 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl167> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl167> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl167> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl167> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl167> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl168> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl167> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl169> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl170 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl170> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl170> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl170> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl170> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl170> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl171> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl170> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl172> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 height=20 align=right x:num="0.31597222222222221">7:35</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 align=right x:num="0.32361111111111113">7:46</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 align=right x:num="0.59236111111111112">14:13</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl175 x:num="0.63958333333333328">15:21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl176> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 height=20 align=right x:num="0.3125">7:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 align=right x:num="0.33333333333333331">8:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 align=right x:num="0.60416666666666663">14:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl179 x:num="0.61805555555555558">14:50</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl180> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 height=20 align=right x:num="0.16666666666666666">4:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 align=right x:num="0.18055555555555555">4:20</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 align=right x:num="0.47638888888888892">11:26</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl175 x:num="0.48472222222222222">11:38</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl176> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 height=20 align=right x:num="0.16666666666666666">4:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 align=right x:num="0.20902777777777778">5:01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 align=right x:num="0.4777777777777778">11:28</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl179 x:num="0.48958333333333331">11:45</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl180> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 height=20 align=right x:num="0.16666666666666666">4:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 align=right x:num="0.17222222222222225">4:08</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 align=right x:num="0.47986111111111113">11:31</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl175 x:num="0.5">12:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl176> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 height=20 align=right x:num="0.16666666666666666">4:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 align=right x:num="0.17708333333333334">4:15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 align=right x:num="0.4770833333333333">11:27</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl179 x:num="0.50486111111111109">12:07</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl180> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 height=20 align=right x:num="0.16666666666666666">4:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 align=right x:num="0.17152777777777775">4:07</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl174 align=right x:num="0.50347222222222221">12:05</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl175 x:num="0.52083333333333337">12:30</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl173> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: lime; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl176> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 height=20 align=right x:num="0.16666666666666666">4:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 align=right x:num="0.16944444444444443">4:04</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl178 align=right x:num="0.4909722222222222">11:47</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl179 x:num="0.50138888888888888">12:02</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl177> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl180> </TD></TR></TBODY></TABLE>

 

[Snakehips]

Tazzbjs,
Select your time cells > Rt click > Format Cells.. > Number Tab > Custom

In the Type: box, type hh.mm then ok
I suggest using the . rather than : as it does not require the shift key when entering.
Your total time is shown in column F.

Can you not get the value you require, directly by subtracting last drop time from pullin time? As shown below coumn E.

As you can see your 1:06 difference stated in your question should be 1:08 ??

Excel Workbook
	A	B	C	D	E	F
16	Start	1st PU	Last DoO	Act PI	D-C	Total
17	07.35	07.46	14.13	15.21	01.08	07.46
18	01.30	08.00	14.30	15.21	00.51	13.51
19	04.00	05.01	11.28	11.45	00.17	07.45
Sheet3

 

[Tazzbjs]

Tazzbjs,
Select your time cells > Rt click > Format Cells.. > Number Tab > Custom

In the Type: box, type hh.mm then ok
I suggest using the . rather than : as it does not require the shift key when entering.
Your total time is shown in column F.

Can you not get the value you require, directly by subtracting last drop time from pullin time? As shown below coumn E.

As you can see your 1:06 difference stated in your question should be 1:08 ??

Excel Workbook
	A	B	C	D	E	F
16	Start	1st PU	Last DoO	Act PI	D-C	Total
17	07.35	07.46	14.13	15.21	01.08	07.46
18	01.30	08.00	14.30	15.21	00.51	13.51
19	04.00	05.01	11.28	11.45	00.17	07.45
Sheet3

The problem I'm having is I need the total time from 1st pu to actual pullin upto 20 minutes past the last drop off. If it is less then 20 minutes then the total is fine. If the total exceeds the 20 minutes from last drop then it has to be adjusted to show the adjusted total. I have over 2000 lines of data to apply this to a month and I would like a formula I can use to get the info needed with out having to go line by line.

 

[Snakehips]

Tazzbjs,

Hopefully this more like what you want...

Firstly, forget changing the format of the time cells to hh.mm rather have it as hh:mm

Because we are dealing with times, the only way I can deal with the 20 minute limit is to have a cell, in this example C2 that holds the time 00:20. This is your limit over last drop time. This can be any cell anywhere you choose on your sheet that doesn't otherwise impact on your data.

Adjust the formula shown below for cell E4 as necessary to suit the reality of your data range. The reference to cell C2 or your equivalent, must be typed as $C$2 to make it an absolute reference. Then drag the formula down to the last of your data rows.



Sheet3

<table style="font-family: Verdana,Arial; font-size: 12pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 100px;"><col style="width: 88px;"><col style="width: 88px;"><col style="width: 88px;"><col style="width: 88px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(192, 192, 192);">The 20 Minute Limit ></td><td style="background-color: rgb(192, 192, 192);">
</td><td style="background-color: rgb(192, 192, 192); text-align: center;">00:20</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Start</td><td style="background-color: rgb(192, 192, 192); text-align: center;">1st PU</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Last DoO</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Act PI</td><td style="background-color: rgb(192, 192, 192); text-align: center;">Total</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: center;">07:35</td><td style="text-align: center;">07:46</td><td style="text-align: center;">14:13</td><td style="text-align: center;">15:21</td><td style="text-align: center;">06:58</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: center;">07:30</td><td style="text-align: center;">08:00</td><td style="text-align: center;">14:30</td><td style="text-align: center;">15:50</td><td style="text-align: center;">07:20</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: center;">04:00</td><td style="text-align: center;">04:20</td><td style="text-align: center;">11:26</td><td style="text-align: center;">11:38</td><td style="text-align: center;">07:38</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: center;">04:00</td><td style="text-align: center;">05:01</td><td style="text-align: center;">11:28</td><td style="text-align: center;">11:45</td><td style="text-align: center;">07:45</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>E4</td><td>=IF(D4>C4+$C$2,C4-A4+$C$2,D4-A4)</td></tr><tr><td>E5</td><td>=IF(D5>C5+$C$2,C5-A5+$C$2,D5-A5)</td></tr><tr><td>E6</td><td>=IF(D6>C6+$C$2,C6-A6+$C$2,D6-A6)</td></tr><tr><td>E7</td><td>=IF(D7>C7+$C$2,C7-A7+$C$2,D7-A7)</td></tr></tbody></table></td></tr></tbody></table>
Is that any better?

Excel tables to the web >> Excel Jeanie HTML 4

 

[Matt Rogers]

Hi.

Without C2 =

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:83.2px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>E</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">06:58</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>E4</td><td>=IF(D4>C4+"0:20",C4-A4+"0:20",D4-A4)</td></tr></tbody></table></td></tr></tbody></table>

 

[Snakehips]

Screwdriver,

Thanks for the tweak. That is preferable.

However, I'm extremely puzzled???
Any attempt I made earlier to do similar resulted in a 'formula contains error message'.

I had exactly the same just now editing my formula to add "0:20"
It failed at least 6 times and I began to doubt your amendment until I replaced the first $C$2 with "00:20". Since then the formula is aok with two lots of "0:20".

Mighty strange.!!!

 

[Tazzbjs]

Thank you both. I will try these out and let you know if it worked ok for me.