Thread
:
Calculation of Overlap Outage Hours
View Single Post
#
4
Posted to microsoft.public.excel.programming
Charabeuh[_4_]
external usenet poster
Posts: 62
Calculation of Overlap Outage Hours
Hello,
You can upload the file Overlap.xls :
http://www.cijoint.fr/cjlink.php?fil...cija0CUXeF.xls
For each Item, a table display the duration of overlap with all others item.
Does this help you ?
"shriil" a écrit dans le message de groupe de
discussion :
...
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
Reply With Quote
Charabeuh[_4_]
View Public Profile
Find all posts by Charabeuh[_4_]