This issue is when the person clocks their last punch after midnight which is on the next day. I use the Route Start to make sure the MIN formula is not pulling those after midnight punches from the previous day's route(with a little buffer in case the route starts early). After some searching I cant find a way to have excel continue looking into the next day for the last punch. At most the next days punches could be up to 5:00 AM. A better method might be to continue looking for the next punch until the gap is more than 2 hours.
The table is A to D and the sum fields are E to I. Shown is what the formulas are currently producing. What cell H2 should show is 0:12:17 and cell I2 should be 2:27:37
G- {=MIN(IF(($A$2:$A$4=E2)*($B$2:$B$4=F2)*($C$2:C$4>(D2-TIME(2,0,0))),$C$2:$C$4))}
H- {=MAX(IF($A$2:$A$4=E2,IF($B$2:$B$4=F2,$C$2:$C$4)))}
I- =H2-G2
A B C D E F G H I Route Date Punch Route Start Route Date Start Time End Time Cycle Time Loc1 2/1/2016 21:44:40 21:00:00 Loc1 2/1/2016 21:44:40 23:32:13 1:47:33 Loc1 2/1/2016 23:32:13 21:00:00 Loc1 2/2/2016 0:09:45 21:00:00 Loc1 2/2/2016 0:12:01 21:00:00 Loc1 2/2/2016 0:12:17 21:00:00
Thanks in advance!
Bookmarks