7 Replies Latest reply on Sep 6, 2012 3:19 AM by Andy Piper

    Evaluate service record dates & flag multiple same-reason visits within a specified timeframe

    Andy Piper

      I'm hoping the good folks on the forum can help me out with this one.

       

      We maintain a list of vehicles and their maintenance/service records. For each vehicle (Vehicle), we track the reason why a vehicle was taken to a shop (Reason), the date a vehicle was brought into a local shop for service (Checked In), the date it left the shop for service (Checked Out), and what shop did the work (Service Location).

       

      I'd like to be able to easily determine those vehicles that were taken back to a shop within 30 days for the same reason. For example, in the table below, Vehicle A001 was brought to a shop 4 times for reason X1. I would like to be able to determine if the Checked In date of second trip to the shop falls within a specified Service Window of the Checked Out date the vehicle left the shop the first time for that particular reason; and so on to compare the third trip Checked In to second trip Checked Out, etc.

       

      Attached is a simple packaged workbook that tries to replicate what I'm working with. I was thinking of a DateDiff function, but I haven't had much luck yet.

       

      Thanks in advance for your guidance & assistance,

       

      Regards,


      Andy

       

       

      VehicleReasonChecked InChecked OutService Location
      A001X11/1/20111/5/2011ATLANTA 1


      1/14/20111/21/2011ATLANTA 1

      2/15/20112/19/2011ATLANTA 5
      10/2/201110/14/2011ATLANTA 1
      A002Y15/25/20116/10/2011ATLANTA 2
      7/1/20117/10/2011ATLANTA 1
      Z12/3/20112/4/2011ATLANTA 4
      5/12/20115/15/2011ATLANTA 5
      A003X14/12/20115/20/2011ATLANTA 4
      Y17/20/20118/11/2011ATLANTA 5
      B001X111/5/201111/15/2011ATLANTA 2
      12/3/201112/11/2011ATLANTA 4
      B002Y14/4/20114/8/2011ATLANTA 1
      Z14/10/20114/15/2011ATLANTA 4
      C001Y111/15/201111/17/2011ATLANTA 6
      11/22/201112/13/2011ATLANTA 6
      Z19/30/201110/5/2011ATLANTA 1
      10/27/201110/29/2011ATLANTA 1
      C002X13/19/20113/22/2011ATLANTA 3
      4/30/20115/3/2011ATLANTA 3