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

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

Suggestions...

I think you should stick to your original table layout as that will
definitely be easier to work with. For example, your headers:
A B C D E F
Item ItemOut ItemIn Duration Overlap =CurrentOverlap

where each column (A to E) is a local defined name matching the field
name, and defined as column-absolute, row-relative. For example, A1 is
named "'Sheet1'!Item" and its RefersTo is "=$A1" while the active cell
is in Row1. B1 is named "'Sheet1'!ItemOut" and its RefersTo is "=$B1"
while the active cell is in Row1. And so on through "Overlap". (**don't
include the double quotes in Name or RefersTo)
//

Also, I assume that Item/ItemOut are empty unless an item is logged
out. Thus, logged out items will have ItemIn empty until they're
returned, and Duration will have the meter running to show how long the
item has been out. This can be done with a cell formula until you log
the item back in:

Make all defined names local by prefixing the name with the sheet name
wrapped in single quotes, and then the exclamation character, as
follows.
Name: "'Sheet1'!TimeOut"

RefersTo:
=IF(AND(ItemIn<"",ItemOut<""),ItemIn-ItemOut,NOW()-ItemOut)

We will use this formula in the Duration column. Note that this formula
will only recalc when the sheet recalcs as a result of changes or
activation, and so you may need to occasionally use the F9 key to force
a recalc.
//

Also, I assume your definition of 'overlap' refers to 2 or more items
being out at the same time. If so then you contradict yourself later
when you state that if a third item goes out that the overlap hours
stay the same. I can understand why you say you're confused. That said,
for now I will proceed on the premise that we will consider TimeOut
overlaps as being the cummulative duration that 2 or more items are
currently logged out. You can change this however you decide it should
be later, but this will allow us to build a solution that we can start
working with in the meantime.

Cell $F$1 (named "TotalOverlap") will contain a formula that sums all
TimeOut overlaps as the cummulative overlaps value. This will only
display a value if 2 or more items are out at the same time.
//

So now it remains to design the rest of your spreadsheet so this will
work. I propose that when items are logged out you use a keyboard
shortcut to the date&time into ItemOut, and enter the formulas we'll
use in Duration and Overlap. When the item is logged back in you use
another keyboard shortcut to hardcopy the Duration formula's resulting
value, and set ItemIn date&time. We will do this via code in a standard
module in your workbook. This will hardcopy the formula results so they
are stored as constant values, providing you historic data as to when
each item went out and was returned. It will also persist the Overlap
formula in case subsequent items are logged out while there's any
existing items still out.

I suggest using a formula for Overlap that uses an enhaced version of
the same one we use for Duration, to also monitors things while 2 or
more items are still out. So while ItemIn is empty, Duration is keeping
time as suggested with the above formula, and Overlap is keeping time
while there's more rows with ItemIn empty. This will require defining
the ItemIn column as a named range so we can get a count of the empty
cells. This should be a dynamic range so it adjusts to include only the
number of rows in the Item column as there are listed items. This
precludes that there must be no empty cells between the header and last
item in that column, and the header row is Row1. So here's what you
need to enter in the Defined Name dialog:

Name RefersTo
Item_Hdr =$A$1
Items =OFFSET(Item_Hdr,1,0,COUNTA($A:$A)-1,1)
ItemIn_Hdr =$C$1
ItemsOut =OFFSET(ItemIn_Hdr,1,0,COUNTA(Items),1)
Overlaps =IF(AND(ItemIn="",COUNTIF(ItemsOut,"")1),Duration ,"")
TotalOverlap =$F$1
CurrentOverlap =IF(COUNTIF(ItemsOut,"")1,SUM($E:$E),"")

Be sure to prefix the name with the sheet tab name so that they are
local to the sheet they're being used on.

In the Overlap column we will use this formula: "=Overlaps". What it
does is it checks to see if the item is out AND if there's other items
out at the same time. If so then it retrieves the value in Duration for
that item, else it returns an empty string if this is the only item
out.

You should format columns Duration, Overlap, and cell named
TotalOverlap as:
Category=Custom, Type="[h]:mm;@" (minus the double quotes)

The code:
Sub LogItemsOut() 'Shortcut=Ctrl+o
Dim c As Range
For Each c In Selection.Rows
Cells(c.Row, Range("ItemOut").Column).Value = Now()
Cells(c.Row, Range("Duration").Column).Formula = "=TimeOut"
Cells(c.Row, Range("Overlap").Column).Formula = "=Overlaps"
Next
End Sub

Sub LogitemsIn() 'Shortcut=Ctrl+i
Dim c As Range
For Each c In Selection.Rows
Cells(c.Row, Range("Duration").Column).Value = _
Cells(c.Row, Range("Duration").Column).Value
Cells(c.Row, Range("ItemIn").Column).Value = Now()
Next
End Sub

Both procs support multiple row selection so that you can process
groups of items with a single keyboard shortcut.

The LogItemsOut() proc sets up ItemOut date&time and your formulas for
Duration and Overlap, so there's no copying or FillDown required.

The LogItemsIn() proc sets the Duration formula results to constant
values, and sets ItemIn date&time. If there is still 2 or more items
out then the commulative overlap total will persist to display, and
thus be calculated in TotalOverlap ($F$1).

<Summary
- We have a list of local defined names (13) for ranges and formulas.
- We use the formula "=TimeOut" in the ItemIn column.
- We use the formula "=Overlaps" in the Overlap column. This formula
persists so the CurrentOverlap formula includes it when subsequent
items are logged out.
- We enter ItemOut using a keyboard shortcut "Ctrl+o" to run the
"LogItemOut" proc.
- We enter ItemIn using the keyboard shortcut "Ctrl+i" to run the
"LogItemIn" proc.
- The only input required by you is the entry for Item.
- You can log out/in multiple items per keyboard shortcut.
- The TotalOverlaps cell displays the sum of current overlap for 2 or
more items being out at the same time.
</Summary

If you set up a single sheet workbook as a template then you can save
files for given periods and/or have a collection of period sheets in a
single workbook, depending on how you load it.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 19, 1:56*am, GS wrote:
Frankly am really getting confused. Any help from the experts would be
highly appreciated

Suggestions...

I think you should stick to your original table layout as that will
definitely be easier to work with. For example, your headers:
* A * * *B * * * * C * * * *D * * * * *E * * * * F
* Item * ItemOut * ItemIn * Duration * Overlap * =CurrentOverlap

where each column (A to E) is a local defined name matching the field
name, and defined as column-absolute, row-relative. For example, A1 is
named "'Sheet1'!Item" and its RefersTo is "=$A1" while the active cell
is in Row1. B1 is named "'Sheet1'!ItemOut" and its RefersTo is "=$B1"
while the active cell is in Row1. And so on through "Overlap". (**don't
include the double quotes in Name or RefersTo)
//

Also, I assume that Item/ItemOut are empty unless an item is logged
out. Thus, logged out items will have ItemIn empty until they're
returned, and Duration will have the meter running to show how long the
item has been out. This can be done with a cell formula until you log
the item back in:

Make all defined names local by prefixing the name with the sheet name
wrapped in single quotes, and then the exclamation character, as
follows.
Name: "'Sheet1'!TimeOut"

RefersTo:
* =IF(AND(ItemIn<"",ItemOut<""),ItemIn-ItemOut,NOW()-ItemOut)

We will use this formula in the Duration column. Note that this formula
will only recalc when the sheet recalcs as a result of changes or
activation, and so you may need to occasionally use the F9 key to force
a recalc.
//

Also, I assume your definition of 'overlap' refers to 2 or more items
being out at the same time. If so then you contradict yourself later
when you state that if a third item goes out that the overlap hours
stay the same. I can understand why you say you're confused. That said,
for now I will proceed on the premise that we will consider TimeOut
overlaps as being the cummulative duration that 2 or more items are
currently logged out. You can change this however you decide it should
be later, but this will allow us to build a solution that we can start
working with in the meantime.

Cell $F$1 (named "TotalOverlap") will contain a formula that sums all
TimeOut overlaps as the cummulative overlaps value. This will only
display a value if 2 or more items are out at the same time.
//

So now it remains to design the rest of your spreadsheet so this will
work. I propose that when items are logged out you use a keyboard
shortcut to the date&time into ItemOut, and enter the formulas we'll
use in Duration and Overlap. When the item is logged back in you use
another keyboard shortcut to hardcopy the Duration formula's resulting
value, and set ItemIn date&time. We will do this via code in a standard
module in your workbook. This will hardcopy the formula results so they
are stored as constant values, providing you historic data as to when
each item went out and was returned. It will also persist the Overlap
formula in case subsequent items are logged out while there's any
existing items still out.

I suggest using a formula for Overlap that uses an enhaced version of
the same one we use for Duration, to also monitors things while 2 or
more items are still out. So while ItemIn is empty, Duration is keeping
time as suggested with the above formula, and Overlap is keeping time
while there's more rows with ItemIn empty. This will require defining
the ItemIn column as a named range so we can get a count of the empty
cells. This should be a dynamic range so it adjusts to include only the
number of rows in the Item column as there are listed items. This
precludes that there must be no empty cells between the header and last
item in that column, and the header row is Row1. So here's what you
need to enter in the Defined Name dialog:

Name * * * * * RefersTo
Item_Hdr * * * =$A$1
Items * * * * *=OFFSET(Item_Hdr,1,0,COUNTA($A:$A)-1,1)
ItemIn_Hdr * * =$C$1
ItemsOut * * * =OFFSET(ItemIn_Hdr,1,0,COUNTA(Items),1)
Overlaps * * * =IF(AND(ItemIn="",COUNTIF(ItemsOut,"")1),Duration ,"")
TotalOverlap * =$F$1
CurrentOverlap =IF(COUNTIF(ItemsOut,"")1,SUM($E:$E),"")

Be sure to prefix the name with the sheet tab name so that they are
local to the sheet they're being used on.

In the Overlap column we will use this formula: "=Overlaps". What it
does is it checks to see if the item is out AND if there's other items
out at the same time. If so then it retrieves the value in Duration for
that item, else it returns an empty string if this is the only item
out.

You should format columns Duration, Overlap, and cell named
TotalOverlap as:
* Category=Custom, Type="[h]:mm;@" (minus the double quotes)

The code:
Sub LogItemsOut() 'Shortcut=Ctrl+o
* Dim c As Range
* For Each c In Selection.Rows
* * Cells(c.Row, Range("ItemOut").Column).Value = Now()
* * Cells(c.Row, Range("Duration").Column).Formula = "=TimeOut"
* * Cells(c.Row, Range("Overlap").Column).Formula = "=Overlaps"
* Next
End Sub

Sub LogitemsIn() 'Shortcut=Ctrl+i
* Dim c As Range
* For Each c In Selection.Rows
* * Cells(c.Row, Range("Duration").Column).Value = _
* * * *Cells(c.Row, Range("Duration").Column).Value
* * Cells(c.Row, Range("ItemIn").Column).Value = Now()
* Next
End Sub

Both procs support multiple row selection so that you can process
groups of items with a single keyboard shortcut.

The LogItemsOut() proc sets up ItemOut date&time and your formulas for
Duration and Overlap, so there's no copying or FillDown required.

The LogItemsIn() proc sets the Duration formula results to constant
values, and sets ItemIn date&time. If there is still 2 or more items
out then the commulative overlap total will persist to display, and
thus be calculated in TotalOverlap ($F$1).

<Summary
- We have a list of local defined names (13) for ranges and formulas.
- We use the formula "=TimeOut" in the ItemIn column.
- We use the formula "=Overlaps" in the Overlap column. This formula
persists so the CurrentOverlap formula includes it when subsequent
items are logged out.
- We enter ItemOut using a keyboard shortcut "Ctrl+o" to run the
"LogItemOut" proc.
- We enter ItemIn using the keyboard shortcut "Ctrl+i" to run the
"LogItemIn" proc.
- The only input required by you is the entry for Item.
- You can log out/in multiple items per keyboard shortcut.
- The TotalOverlaps cell displays the sum of current overlap for 2 or
more items being out at the same time.
</Summary

If you set up a single sheet workbook as a template then you can save
files for given periods and/or have a collection of period sheets in a
single workbook, depending on how you load it.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks a lot for taking the trouble. Shall check it out and revert
back ASAP

On Jun 19, 1:56*am, GS wrote:
Frankly am really getting confused. Any help from the experts would be
highly appreciated

Suggestions...

I think you should stick to your original table layout as that will
definitely be easier to work with. For example, your headers:
* A * * *B * * * * C * * * *D * * * * *E * * * * F
* Item * ItemOut * ItemIn * Duration * Overlap * =CurrentOverlap

where each column (A to E) is a local defined name matching the field
name, and defined as column-absolute, row-relative. For example, A1 is
named "'Sheet1'!Item" and its RefersTo is "=$A1" while the active cell
is in Row1. B1 is named "'Sheet1'!ItemOut" and its RefersTo is "=$B1"
while the active cell is in Row1. And so on through "Overlap". (**don't
include the double quotes in Name or RefersTo)
//

Also, I assume that Item/ItemOut are empty unless an item is logged
out. Thus, logged out items will have ItemIn empty until they're
returned, and Duration will have the meter running to show how long the
item has been out. This can be done with a cell formula until you log
the item back in:

Make all defined names local by prefixing the name with the sheet name
wrapped in single quotes, and then the exclamation character, as
follows.
Name: "'Sheet1'!TimeOut"

RefersTo:
* =IF(AND(ItemIn<"",ItemOut<""),ItemIn-ItemOut,NOW()-ItemOut)

We will use this formula in the Duration column. Note that this formula
will only recalc when the sheet recalcs as a result of changes or
activation, and so you may need to occasionally use the F9 key to force
a recalc.
//

Also, I assume your definition of 'overlap' refers to 2 or more items
being out at the same time. If so then you contradict yourself later
when you state that if a third item goes out that the overlap hours
stay the same. I can understand why you say you're confused. That said,
for now I will proceed on the premise that we will consider TimeOut
overlaps as being the cummulative duration that 2 or more items are
currently logged out. You can change this however you decide it should
be later, but this will allow us to build a solution that we can start
working with in the meantime.

Cell $F$1 (named "TotalOverlap") will contain a formula that sums all
TimeOut overlaps as the cummulative overlaps value. This will only
display a value if 2 or more items are out at the same time.
//

So now it remains to design the rest of your spreadsheet so this will
work. I propose that when items are logged out you use a keyboard
shortcut to the date&time into ItemOut, and enter the formulas we'll
use in Duration and Overlap. When the item is logged back in you use
another keyboard shortcut to hardcopy the Duration formula's resulting
value, and set ItemIn date&time. We will do this via code in a standard
module in your workbook. This will hardcopy the formula results so they
are stored as constant values, providing you historic data as to when
each item went out and was returned. It will also persist the Overlap
formula in case subsequent items are logged out while there's any
existing items still out.

I suggest using a formula for Overlap that uses an enhaced version of
the same one we use for Duration, to also monitors things while 2 or
more items are still out. So while ItemIn is empty, Duration is keeping
time as suggested with the above formula, and Overlap is keeping time
while there's more rows with ItemIn empty. This will require defining
the ItemIn column as a named range so we can get a count of the empty
cells. This should be a dynamic range so it adjusts to include only the
number of rows in the Item column as there are listed items. This
precludes that there must be no empty cells between the header and last
item in that column, and the header row is Row1. So here's what you
need to enter in the Defined Name dialog:

Name * * * * * RefersTo
Item_Hdr * * * =$A$1
Items * * * * *=OFFSET(Item_Hdr,1,0,COUNTA($A:$A)-1,1)
ItemIn_Hdr * * =$C$1
ItemsOut * * * =OFFSET(ItemIn_Hdr,1,0,COUNTA(Items),1)
Overlaps * * * =IF(AND(ItemIn="",COUNTIF(ItemsOut,"")1),Duration ,"")
TotalOverlap * =$F$1
CurrentOverlap =IF(COUNTIF(ItemsOut,"")1,SUM($E:$E),"")

Be sure to prefix the name with the sheet tab name so that they are
local to the sheet they're being used on.

In the Overlap column we will use this formula: "=Overlaps". What it
does is it checks to see if the item is out AND if there's other items
out at the same time. If so then it retrieves the value in Duration for
that item, else it returns an empty string if this is the only item
out.

You should format columns Duration, Overlap, and cell named
TotalOverlap as:
* Category=Custom, Type="[h]:mm;@" (minus the double quotes)

The code:
Sub LogItemsOut() 'Shortcut=Ctrl+o
* Dim c As Range
* For Each c In Selection.Rows
* * Cells(c.Row, Range("ItemOut").Column).Value = Now()
* * Cells(c.Row, Range("Duration").Column).Formula = "=TimeOut"
* * Cells(c.Row, Range("Overlap").Column).Formula = "=Overlaps"
* Next
End Sub

Sub LogitemsIn() 'Shortcut=Ctrl+i
* Dim c As Range
* For Each c In Selection.Rows
* * Cells(c.Row, Range("Duration").Column).Value = _
* * * *Cells(c.Row, Range("Duration").Column).Value
* * Cells(c.Row, Range("ItemIn").Column).Value = Now()
* Next
End Sub

Both procs support multiple row selection so that you can process
groups of items with a single keyboard shortcut.

The LogItemsOut() proc sets up ItemOut date&time and your formulas for
Duration and Overlap, so there's no copying or FillDown required.

The LogItemsIn() proc sets the Duration formula results to constant
values, and sets ItemIn date&time. If there is still 2 or more items
out then the commulative overlap total will persist to display, and
thus be calculated in TotalOverlap ($F$1).

<Summary
- We have a list of local defined names (13) for ranges and formulas.
- We use the formula "=TimeOut" in the ItemIn column.
- We use the formula "=Overlaps" in the Overlap column. This formula
persists so the CurrentOverlap formula includes it when subsequent
items are logged out.
- We enter ItemOut using a keyboard shortcut "Ctrl+o" to run the
"LogItemOut" proc.
- We enter ItemIn using the keyboard shortcut "Ctrl+i" to run the
"LogItemIn" proc.
- The only input required by you is the entry for Item.
- You can log out/in multiple items per keyboard shortcut.
- The TotalOverlaps cell displays the sum of current overlap for 2 or
more items being out at the same time.
</Summary

If you set up a single sheet workbook as a template then you can save
files for given periods and/or have a collection of period sheets in a
single workbook, depending on how you load it.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Another thing.. the database which gets populated is not necessarily
in a sorted order as per the Item_Out Time.. It may look like

Equipment Out DateTime IN DateTime Out_Hrs
MILL_GRP_1E * * 14/06/2010 10:10 * * * *16/06/2010 18:00 * * *
*55:50
MILL_GRP_1D * * 28/05/2010 11:21 * * * *08/06/2010 14:35 * * * *267:14
MILL_GRP_1A * * 07/06/2010 12:03 * * * *12/06/2010 18:00 * * * *125:57
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_1B * * 12/06/2010 15:17 * * * *13/06/2010 6:03 14:46
MILL_GRP_1D * * 06/06/2010 20:30 * * * *06/06/2010 23:00 * * * *02:30
MILL_GRP_1C * * 09/06/2010 14:15 * * * *11/06/2010 9:21 43:06
MILL_GRP_1B * * 15/06/2010 13:13 * * * *16/06/2010 14:30 * * * *25:17


The basic data for each row is linked from other worksheets in the
workbook, where each worksheet pertains to an individual item.


Would like to know how to Sort the table on Out_date_time through a
code or a macro where it counts the rows that are being populated and
then executes the Sort.

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

On Jun 20, 8:20*am, "Charabeuh" wrote:
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- Hide quoted text -

- Show quoted text -

The website to the link you posted is infected.

Hello

Could you tell me what are the symptoms or virus you have have found.
So I would inform the webmaster.

Here is another link with the same file overlap.xls:
http://www.filedropper.com/overlap

Hope it will work.



The website to the link you posted is infected.

On Jun 20, 12:23*pm, "Charabeuh" wrote:
Hello

Could you tell me what are the symptoms or virus you have have found.
So I would inform the webmaster.

Here is another link with the same file overlap.xls:http://www.filedropper.com/overlap

Hope it will work.





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

- Show quoted text -

My antivirus is giving the following message:

"The website you are visiting is infected
Detected : Blk/Domain.A483
Infected Object is blocked.

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.

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.

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).xls
http://www.mediafire.com/file/ijmzin...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.

shriil used his keyboard to write :
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.

The formula I provided for the Overlap column does this, and the
TotalOverlap cell only calcs when 2 or more items are logged out. I
thought that's what you wanted!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc