Calculate total hours if time ranges overlap

Good Afternoon,

I am trying to determine the total hours of downtime accumulated when there are 2 or more machines down during the same time period. To do this, I need to be able to determine if at any point there are 2 or more pieces of equipment down at the same time, and if this is true, how many hours were overlapped.

The attached spreadsheet shows how the data is presented. Any help would be much appreciated.

Kind Regards,

JL

Hi,

Try see attached file..

Downtime Reports1.xlsx

Here is the modified spreadsheet and a word doc explaining the logic

Hi MikeNDavis,

Thank-you for your quick response to my thread and apologies that it has taken me so long to reply. The spreadsheet you worked on for me re: overlapping downtime hours is working great and I'm very grateful for your help.

The only issue I am currently having is when there are 4 or more pieces of equipment down over the same time period, the overlapped time is calculated more than once. In the attached spreadsheet I have highlighted where this occurs. I have been trying to figure out how this can be fixed but so far I'm not having any luck. I believe it may be able to be fixed by adjusting the Earliest Usable STD formula but other than that I haven't got a clue. If you get a chance would you be able to have a look and give me some advice as to how to solve this?

Thanks and kind regards,

JL_Stewart

Pl see attached file with formula for number of overlaps and total duration.

Thank-you to everyone who has helped me with my Downtime Report so far. I greatly appreciate your help and believe me, I wouldn't have got this far without you!

The overlapped downtime is working great in most situations, apart from when the order of the data does not allow the formulas to calculate the correct answer. For an example, please refer to rows 65 and 158 of the attached spreadsheet. You will notice that there is another overlapped time to be calculated (in the case of row 65 it needs to calculate the overlap with row 63), but because the "end date" of the entry above is smaller than the "start date" of the current entry, the formula does not go looking for any other entries which may overlap. It also gets tricky because I don't want the formula to double up the overlapped time with other entries.

Another example is in rows 76 - 84. The total downtime for all of these entries should equal 24 hours, but I cannot get the overlapped time to calculate for all entries.

There are currently two "Overlap" formulas in the attached spreadsheet. Both are exactly the same apart from the "Earliest Useable STD" time which they refer to. I have left both there to allow me to determine which option works the best. I have also gone through the full spreadsheet and highlighted all correct overlaps in green and the incorrect ones in red.

I am now pretty much stumped as to how to solve these last few issues. If anyone has any suggestions or if you think I should attack this a completely different way, I would really appreciate your feedback.

Kindest Regards,
JL

I have the same problem. Have this problem been solved? thanks