Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
On Jun 20, 11:19*pm, "Charabeuh" wrote:
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" 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.- Hide quoted text - - Show quoted text - 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
On Jun 21, 12:44*am, GS wrote:
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 athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
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/kgnrng...lap%284%29.xls "shriil" a écrit dans le message de groupe de discussion : ... On Jun 20, 11:19 pm, "Charabeuh" wrote: 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" 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.- Hide quoted text - - Show quoted text - 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
On Jun 22, 1:11*am, "Charabeuh" wrote:
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/kgnrng...lap%284%29.xls "shriil" a écrit dans le message de groupe de discussion : ... On Jun 20, 11:19 pm, "Charabeuh" wrote: 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" a écrit dans le message de groupe de discussion : ... On Jun 20, 1:06 pm, "" 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.- Hide quoted text - - Show quoted text - 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- Hide quoted text - - Show quoted text - 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..? |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
shriil presented the following explanation :
On Jun 21, 12:44*am, GS wrote: 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 athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - 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 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 at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation of Overlap Outage Hours
On Jun 22, 9:06*pm, GS wrote:
shriil presented the following explanation : On Jun 21, 12:44 am, GS wrote: 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 athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - 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 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 - 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. .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Man hours calculation | Excel Worksheet Functions | |||
Excel Automatically locking sheets after power outage | Excel Discussion (Misc queries) | |||
HOURS CALCULATION | Excel Discussion (Misc queries) | |||
Average outage time? | Excel Worksheet Functions | |||
A function that separates hours worked in work shifts that overlap | Excel Programming |