Hello,

Your suggestion is rigth and came to me too but after having sent my
response. Il will give something like that:
http://www.mediafire.com/file/kgnrng...lap%284%29.xls



"shriil" a écrit dans le message de groupe de
discussion :
...
On Jun 20, 11:19 pm, "Charabeuh" wrote:
Hello,

Thanks for your reply concerning the infection.

For the Cumulative Overlap calculating, I have tried a new method.

The idea is:

1) I will consider all the periods of one minute from the beginning
(date:hour) of the oldest equipment outage to the end (date:hour) of the
last equipment outage.

2) I will build a table to count for each period of one minute how many
equipment outages have this period of one minute in their interval of
outage.

3) if the result is greater or equal to 2, that will mean that there is
an
overlap of at least two outages.

4) The number of one minute periods that have at least one overlap will
be
the duration (in minutes) of overlap ?

If this is correct, in VBA it will be a double loop on the periods and on
the equipment outages. I have not tested this method but it will
certainly
last very long. Another way in VBA is perhaps to use a collection with
the
periods of one minute and to compute only the periods of outage.

My english is bad. I could have not really understood what you are
looking
for and what I wrote is perhaps not clear to you. I apologized.

The link to the file
Overlap(2).xlshttp://www.mediafire.com/file/ijmzinwyjej/Overlap(2).xls

Hope it will give you some tracks to solve your problem.

Charabeuh

"shriil" a écrit dans le message de groupe de
discussion :
...



On Jun 20, 1:06 pm, "Charabeuh" wrote:
Just another
link:http://www.mediafire.com/file/yz5namhy2yj/Overlap.xls

"shriil" a crit dans le message de groupe de
discussion :
...

The website to the link you posted is infected.- Hide quoted text -

- Show quoted text -

Thanks. Downloaded it. But how to calculate the Cumulative Overlap ? I
need to calculate the same when two or more equiopment are out. The
moment no. of out equipment becomes 1, the overlap hours stop counting
and then may restart if another equipment goes out.- Hide quoted text -

- Show quoted text -

Thanks for your help and advice. As you rightly said, I need to
calculate the overlap only and only if the no. of equipment under
outage is =2. As per the overlap.xls file that you have sent earlier,
you have done a fantastic work by offsetting the millgroups and
respective outage periods depending upon the out date time and in date
time. What I had in mind, is that if by the offsetting I can arrange
all OUT and IN dates in ascending order, column-wise and in the row
side the equipment will be arranged. Now for each equipment, the cell
which falls under each date shall be evaluated on the basis of whether
that particular date (in the column head) is within the outage period
of the concerned equipment. If it falls under the period, then the
cell is checked with a "X" or anything for that matter. Similarly the
next equipment or the next row is also checked against the column-head
date vis-a-vis its outage period and if the date is within the said
period, another "X" is checked for the particular cell.

After this exercise, we check the count of "X" column-wise, and the
moment the count is =2, the particular column-head date (under which
the count becomes =2) is stored in another cell. Similarly we capture
the column-head date when the count again becomes <2. (Actually, for
the column where the count becomes less than 2, we need to take the
preceding column -head date). This 2nd date is again stored and the
difference between the 2nd date and the first date will give the
Overlap hours. Subsequently when the "X" count again becomes =2, the
column-head date under which the count beomes =2 is stored and the
sequence starts. Finally the calculated Overlap hours of multiple
instances are added to get the Cumulative Overlap Hours.

I hope I have been able to explain my thoughts and requirement.
Anyway, thanks again for the help you are giving