Hi

I have a database in excel where I keep a track of equipment outage
hours by inputting the Date & Time of Outage (O/C Date, O/C Time) and
again the Date & Time when the equipment comes back into service (I/C
Date, I/C Time). Subsequently after subtraction of the O/C Date+ Time
from the I/C Date+ Time, I get the Outage Hours. Sample Data is as
below:


EQUIPMENT O/C DATE TIME I/C DATE TIME OUTAGE HRS
MILL_GRP_1D 28/05/2010 11:21 08/06/2010 14:35 267:14
MILL_GRP_1C 31/05/2010 12:00 12/06/2010 17:00 293:00
MILL_GRP_1B 06/06/2010 10:46 07/06/2010 13:10 26:24
MILL_GRP_1D 06/06/2010 20:30 06/06/2010 23:00 02:30
MILL_GRP_1A 07/06/2010 12:03 12/06/2010 18:00 125:57
MILL_GRP_1C 09/06/2010 14:15 11/06/2010 9:21 43:06
MILL_GRP_1B 12/06/2010 15:17 13/06/2010 6:03 14:46
MILL_GRP_1E 14/06/2010 10:10 16/06/2010 18:00 55:50
MILL_GRP_1B 15/06/2010 13:13 16/06/2010 14:30 25:17


My requirement is to calculate the Overlap Outage Hours when two or
more equipment are under outage .
I have been racking my brains for trying to find out a solution to the
above problem as there are quite a few variables :


a) First I need to find out what is the overlap between two
equipment,
secondly if a third equipment falls under the same overlap, the
Overlap hours remain the same
b) Outage hours of an equipment can fall under three divisions: "No
overlap", "Common Overlap", New Overlap". Finally I have to calculate
the Cumulative Overlap Hours

One solution could be, if from the original table, I could arrange
all the O/CDate Times & I/C DateTimes , horizontally in an ascending
order and the Equipment in a vertical column, then mark "X: under the
date-time columns if the particular equipment remains out w.r.t. the
date-time column... a sample arrangement as given below:

28/05/2010 11:21 31/05/2010 12:00 06/06/2010 10:46 06/06/2010 20:30
MILL_GRP_1D X X X X
MILL_GRP_1C X X X
MILL_GRP_1B X X
MILL_GRP_1D X


From the above, I shall capture the date-time for the first "two or
more X" and again the date-time where the No. of "X" becomes 1. (This
actually would be the date-time left to the column under which X
becomes 1) The Difference of these two figures should give the overlap
hours.


Frankly am really getting confused. Any help from the experts would be
highly appreciated


Thanks


SNL