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 <gesan...@netscape.net> 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

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" <(E-Mail Removed)> a écrit dans le message de groupe de
discussion :
26e3e3e4-26e3-45b2-aac1-dd6766ef44a2...oglegroups.com...
> 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" <Ple...@FeedBack.fr> 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" <sanjib.lah...@gmail.com> a écrit dans le message de groupe de
> discussion :
> 26e3e3e4-26e3-45b2-aac1-dd6766ef4...@u20g2000pru.googlegroups.com...
>
>
>
> > 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.

On Jun 19, 1:56*am, GS <gesan...@netscape.net> 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

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.

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


"shriil" <(E-Mail Removed)> a écrit dans le message de groupe de
discussion :
e6d4d860-c4b0-4198-8318-29d390121003...oglegroups.com...
>
> The website to the link you posted is infected.

On Jun 20, 12:23*pm, "Charabeuh" <Ple...@FeedBack.fr> 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.

On Jun 20, 1:06*pm, "Charabeuh" <Ple...@FeedBack.fr> wrote:
> Just another link:http://www.mediafire.com/file/yz5namhy2yj/Overlap.xls
>
> "shriil" <sanjib.lah...@gmail.com> a crit dans le message de groupe de
> discussion :
> e6d4d860-c4b0-4198-8318-29d390121...@34g2000prs.googlegroups.com...
>
>
>
>
>
> > 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.