5 Replies Latest reply on May 18, 2017 7:54 PM by Nick Warchol

    Count Occurrences of Consecutive Days

    Nick Warchol

      Hi All,

       

      Trying to count the occurrences of consecutive days with no delays.  The data is structured in a way that a day has two records (inbound and outbound) and those records can either have a delay ("Yes") or not have a delay ("No").  If a delay happened for either the inbound or outbound record for a day, that day is considered a delay day.  So a non-delay day would be a day in which both records were "No".  From there we're trying to identify how many occurrences of consecutive non-delay days there are.

       

      One requirement that we're trying to meet is that Saturday, Sunday and Monday count as one day:

       

           a.) If Saturday was a delay day, but Sunday and/or Monday were not, all three together would count as one delay day

           b.) If Saturday, Sunday and Monday were all non-delay days that would only count as one non-delay day

       

      I've attached a workbook with the calcs we currently have, but we just haven't been able to get the running sum of non-delay days to reset after a delay day and then count the overall instances of those consecutive non-delay days.

       

      Ideally we'd like to be able to present the output as a single number of consecutive non-delay days (for this data set: 5 )

       

      Any help would be greatly appreciated.

       

      Thanks.
      Nick