Based on system is there overlapping downtime

Hello,

Thanks in advance for your help.
System Overlapping Downtime.xlsx (11.7 KB)

For my use case I would like to determine if the same system is down at the same time. As you can see I have three systems Earth, Wind and Fire. As per the highlighted lines Fire system is down on rows 4 and 7 and Earth system is down on lines 5 and 8.

The time is not important but the overlap of the same date is important.

Any help would be greatly appreciated.

Kind regards,

Sam

Hello @sammeredith
Are you just trying to identify LOGICAL if a system downtime row is overlapped with any other…?

Same time than…? the same system category? other system category?

For me, it’s no clear in your proposed output table as “overlap” is a one to many relation; then your tested 1 criteria [TRUE] is: “downtime system is overlapping” AND “downtime steps an existing downtime (?)” (is it there a time relation?); besides a clarification on whether you are crossing system category (inter-system) or not (intra-category)

Your proposed output doesn’t fit a criteria in any of these categories. Lets focused on intra-system Earth:

  • Row6 marked as 1 overlaps stepping on previous Row0 and Row3
  • Row3 marked as 1 but is the period starting the earliest (01 APR) and doesn’t step over
  • Row0 marked as 0 but overlaps stepping on previous Row3

Fire:

  • Row5 and Row2 overlaps (1 overlap) but counts 2

Now assume your criteria is inter-system:

  • Row4 (Fire) marked as 0 but overlaps stepping on Row0 (Earth) and Row3 (Earth)
  • Row0 is marked as 0
  • Row3 is marked as 1
    Then you have 2 overlaps but counting 1

Could you clarify this challenge? by the way, these are all the 6 existing overlaps in your data:

intra-system overlaps:

inter-system overlaps:

BR

Hello,

Thanks for your response and apologies for the delay in mine.

I would like to identify intra-system overlaps. Thank you.

Kind regards,

Sam

Hello @sammeredith
I am sharing with you the full workflow I worked out that day. I hope this covers your request. The workflow identifies unique overlaps (Downtime ID) with their respective starting and ending times. Then, you can further extend your analysis…

  • Upper Branch: intra-system overlaps (4)
  • Lower Branch: inter-system overlaps (2)

Comments or feedback would be great.
BR

Thank you @gonhaddock. I will take a look and feedback to you.

Kind regards,

Sam