Calculating Overlaps

[shriil]

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_1B 06/06/2010 10:46 07/06/2010 13:10 26:24
MILL_GRP_1C 31/05/2010 12:00 12/06/2010 17:00 293:00
MILL_GRP_1D 28/05/2010 11:21 08/06/2010 14:35 267:14

Another requirement is to calculate the Overlap Outage Hours when two
or more equipment are out.
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". So I need to know the outage
hours under each.

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

Thanks

SNL

 

[Herbert Seidenberg]

Excel 2007 Tables
Gantt Chart
Calculate overlap.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/06_19_10.xlsm
pdf preview:
http://www.mediafire.com/file/tl5eynlm1yw/06_19_10.pdf

 

[shriil]

Excel 2007 Tables
Gantt Chart
Calculate overlap.http://c0718892.cdn.cloudfiles.rackspacecloud.com/06_19_10.xlsm
pdf preview:http://www.mediafire.com/file/tl5eynlm1yw/06_19_10.pdf

Thanks for the help

 

[Michael.Tarnowski]

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_1B     06/06/2010 10:46        07/06/2010 13:10        26:24
MILL_GRP_1C     31/05/2010 12:00        12/06/2010 17:00        293:00
MILL_GRP_1D     28/05/2010 11:21        08/06/2010 14:35        267:14

Another requirement is to calculate the Overlap Outage Hours when two
or more equipment are out.
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". So I need to know the outage
hours under each.

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

Thanks

SNL

Hi
have a look at chandoo.org

http://chandoo.org/wp/2010/07/07/days-overlapped-excel-formula/

HTH, cheers, have a nice day
Michael