Calculation of Overlap Outage Hours

S

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_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
 
G

GS

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

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
 
S

shriil

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

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
Click to expand...

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

shriil

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

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
Click to expand...

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.
 
S

shriil

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.






- Show quoted text -
Click to expand...

My antivirus is giving the following message:

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

shriil

Just another link:http://www.mediafire.com/file/yz5namhy2yj/Overlap.xls

"shriil" <[email protected]> a crit dans le message de groupe de
discussion :
(e-mail address removed)...






- Show quoted text -
Click to expand...

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.
 
C

Charabeuh

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/ijmzinwyjej/Overlap(2).xls

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

Charabeuh







"shriil" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...
 
G

GS

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

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!
 
S

shriil

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" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...





- Show quoted text -
Click to expand...

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
 
S

shriil

shriil used his keyboard to write :







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 athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Yes Garry.. you are right.. actually I am also trying to depict it in
a columnar form for display (courtesy Charabeu) as well as using your
formula for getting the final Cumulative overlap hours
 
S

shriil

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/kgnrngm0wdh/Overlap(4).xls

"shriil" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...







- Show quoted text -
Click to expand...

Thanks Charabeuh! Your latest one (overlap4) is bang on target. One
thing, how do I sort all the date-times (Out & In) in ascending order
from the original table. and populate the same column-wise as you have
done .. I mean not by routine cut and paste and sort.. but by a
routine code or formula..?
 
G

GS

shriil presented the following explanation :
Yes Garry.. you are right.. actually I am also trying to depict it in
a columnar form for display (courtesy Charabeu) as well as using your
formula for getting the final Cumulative overlap hours
Click to expand...

Yeah, that might be doable. Charabeuh's solution is awesome but it will
run out of columns long before you use up the rows. That's why I
suggested sticking with your original layout. I'm sure a pivot tble
could be constructed to do what you want based on the original layout.

Note that I only accounted for the basic columns needed to implement
the solution. You could add more fields as required.

Also, if you think about my solution in the context of a
'point-of-sale' utility it might make more sense. Basically, all you
need to do is enter the item in columnA and the macro sets the rest up
for logging out, and the other macro concludes the logging in. So other
fields like name of user, pricing (if appropriate), and so on could be
added to make it a more useful tool.

Charabeuh's amazing solution strikes me as being more of an analysis
tool, which you could probably use as well. Maybe include a pivot table
if so desired, that works on my solution. Also, this sorting you want
to do on Charabeuh's table might be easier to code if you assigned
indexes to the columns/rows that you can use in the formulas instead of
the Column() function. That way, you could sort by entire row and the
columnar data will adjust automatically to the new position of the
first column data.
 
S

shriil

shriil presented the following explanation :







Yeah, that might be doable. Charabeuh's solution is awesome but it will
run out of columns long before you use up the rows. That's why I
suggested sticking with your original layout. I'm sure a pivot tble
could be constructed to do what you want based on the original layout.

Note that I only accounted for the basic columns needed to implement
the solution. You could add more fields as required.

Also, if you think about my solution in the context of a
'point-of-sale' utility it might make more sense. Basically, all you
need to do is enter the item in columnA and the macro sets the rest up
for logging out, and the other macro concludes the logging in. So other
fields like name of user, pricing (if appropriate), and so on could be
added to make it a more useful tool.

Charabeuh's amazing solution strikes me as being more of an analysis
tool, which you could probably use as well. Maybe include a pivot table
if so desired, that works on my solution. Also, this sorting you want
to do on Charabeuh's table might be easier to code if you assigned
indexes to the columns/rows that you can use in the formulas instead of
the Column() function. That way, you could sort by entire row and the
columnar data will adjust automatically to the new position of the
first column data.

--
Garry

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

- Show quoted text -
Click to expand...

Very true Garry.. as you rightly pointed out, Charabeuh's table is
more like an analysis tool.

Basically I am trying to amalgamate both yours and Charabeuh's for
the user to understand how he arrives at the Cumulative Overlap Hours
by the columnar representation of Charabeuh but derived initially
through your macro. Because for the user, the macro would be a black
box and he would be not in a position to understand whether the
calculation is allright or going awry. Here, Charabeuh's side-by-side
columnar depiction shall help him to understand and check. But yes ...
I am run out of columns. ..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating Overlaps 3
Find then highlight in yellow 6
Add Hours by Looping through Data Q 27
Find Running Total Value or % at a given Target Date or vice versa 3
Compare dates in two different ranges 1
Suming Up Time over the course of days 1
Sum value between two dates and copy to new cell 6
Seconds missing from time/date properties 2

Top