Identify overlapping events by time/date

Hi Experts,

Thanks in advance for any help.

I have 3 small sets of data. These data refer to 3 different pumps. The data contains a start date/time and an end date/time.

I need to work out, for each time that pump 3 was running ('event'), if pump 1 and/or pump 2 was running. And if possible, the time period P3 was overlapping for. I.e. if pump 1 ran for and hour at 1pm, and pump 3 ran for 20 minutes at 1:50pm, a value of 10 minutes could be returned.

Ultimately, the overall pump run times will end up in a chart to show the overlap.

If you can read between the lines, as I probably am not explaining very well, please feel free to suggest a better way I might find this info. This may be easier with VBA, but I have 0 experience.

Thanks for reading.

Originally Posted by XLent

I suspect there are some flaws in the overlapping logic, but to get things moving...and assuming XL2013

Formula:

Please Login or Register  to view this content.

edit: the above columns should be format as time, e.g. [hh]:mm:ss or [mm]:ss

There could very well be! Thank you so much. I will try this out shortly.

You're a genius. Thank you so much! Lots of time saved.

You're a genius. Thank you so much. Lots of time saved.

Originally Posted by Excel_NoneTheWiser

There could very well be! Thank you so much. I will try this out shortly.

My boss has come back to say he’s very impressed, and it was a great solution. Now I can either fairly credit you, or you could explain exactly what that formula is doing and I can get a pay rise?!

Originally Posted by XLent

I suspect there are some flaws in the overlapping logic, but to get things moving...and assuming XL2013

Formula:

Please Login or Register  to view this content.

edit: the above columns should be format as time, e.g. [hh]:mm:ss or [mm]:ss

If you have the time, could you possibly explain what the end part of the formula is doing? For my own clarity. "[<0]\0;General")+0)

I can make sense of the rest, mainly.

Many thanks.

the TEXT format is a 'shortcut' approach of removing / altering numbers within an array of values, without doing potentially expensive re-evaluations
in your case, only those rows with a genuine overlap will generate a +ve result in the array. all other rows should return a negative result to the array

so, rather than performing another test on the resulting array of values to establish which are positive etc, which could be expensive, we apply a single custom format to the array to get rid of the negatives - i.e.

[<0]\0;General

will change any value <0 to a literal 0, and keep the +ve values as general

at this point (given use of TEXT) the array is now full of numeric strings rather than true numbers, e.g. "0.5" rather than 0.5, so the outer +0 is used to coerce these back to a numeric type, prior to the outer SUM aggregating them

p.s. good luck with the raise!

Originally Posted by XLent

the TEXT format is a 'shortcut' approach of removing / altering numbers within an array of values, without doing potentially expensive re-evaluations
in your case, only those rows with a genuine overlap will generate a +ve result in the array. all other rows should return a negative result to the array

so, rather than performing another test on the resulting array of values to establish which are positive etc, which could be expensive, we apply a single custom format to the array to get rid of the negatives - i.e.

[<0]\0;General

will change any value <0 to a literal 0, and keep the +ve values as general

at this point (given use of TEXT) the array is now full of numeric strings rather than true numbers, e.g. "0.5" rather than 0.5, so the outer +0 is used to coerce these back to a numeric type, prior to the outer SUM aggregating them

p.s. good luck with the raise!

Hero. Thanks again. Now I can afford more beer! Hurrah! Top man.