Calculate overlaps in time and date data in Excel

[Nerina]

Hi all
I am new to the group and an intermediate excel user. I really need help. I have searched through this forum and found a similar question, but the answer does not work on my data.

I have Plant Downtime data, where time is recorded against a machine for stop and start again, as well as the date of the stop
The easier part of the question is, how do I calculate the overlapped time (to remove double counting of standing time), for a specific data. The overlap could be partial or full, and the start times are not necessarily the same.

I do not know how to upload my data, but it comprise
Column A - Object (Machine no)
Column B - Date of event
Column C - Time breakdown commence
Column D - Time breakdown stops

The more difficult one would be, how do I tell the program to only look at one specific object when calculating overlaps in time (i.e only overlaps when that specific machine has downtime recorded against it)

(I know this sounds impossible, but sometimes 2 artisans are working on the same machine, and both submit electronic job cards in which they indicate a stoppage time that overlaps - sometimes not 100%).

[chandy123987]

upload result format

[Nerina]

Hi Chandy,
do not understand your reply? Did you upload a document with a solution? I did upload an excel file with some of my data, but I am struggling with the website.
Regards
Nerina

[chandy123987]

what is your output? update in your sample Excel file.

[Kaper]

Hi Nerina,

Unfortunately, this is not especially easy problem in excel, and I can hardly imagine (If I understood the requirement correctly) it can be effectively solved with formulas.

I prepared (not optimized, but works reasonably fast) macro to do that.
Original times are copied as reference to columns E and F
then a list of machines is created in column G and for each machine the time off/on is adjusted to eliminate potential overlapping downtimes.
then sum of downtimes for each machine is listed in column H
finally the same procedure of ovelapping time adjustment is executed on all events listed in columns A:D despite the machine.

So for your sample data: total down time (as listed) was
128:34:00
after corrections for overlapping down times for each machine it was
117:54
but after overlapping between machines has been eliminated only
115:56:00
of "genuine" downtime has been noted.

See the code:

Please Login or Register  to view this content.

[Nerina]

Hi Kaper

Thank you so much for assisting. I will have to take time to work through it - wish I understood Macros. I really appreciate you taking the time to do this for me!

Regards,
Nerina

[FlameRetired]

... Unfortunately, this is not especially easy problem in excel, and I can hardly imagine ... it can be effectively solved with formulas....

Thank you Kaper! I began to think I had lost my mind.

But then again ....