Calculating Downtime with Excel

[Duffanator 1]

Hey all, I am trending the times when a conveyor starts and stops in Citect and then exporting this data out to excel so the managers can see what times they shut off and turned on. I was trying to figure out how to automaticly calculate the downtime right from the spreadsheet but I can't figure out a way to do it nicely. Does anyone have any ideas? I am calculating downtime right in the PLC, so it's not really a big deal but I think it would be another nice feature to export right to a spreadsheet templete. 2009_05_07_Landing_Conveyor.xls

[PLCMentor.com 60]

Looking at your spreadsheet, it seems that you are logging on a regular interval to the excel sheet - not just when an event occurs. If you were just logging the actual events, then you could just add a column that would determine the time difference between each consecutive record. That would give you up time and down time as I understand your sheet. Alternatively, I think there is a way to create a separate sheet in your workbook that eliminates all the 0 records after which you could achieve the same thing. Do I understand your log properly? Can you create an alternate trigger that would just save the down and up events? Russell

[Duffanator 1]

Yes, the interval changes depending on how I have the trend spaned out (1 hour span, 4 hour span etc...) I did it this way because they wanted a visual representation when the conveyor was shut down and when it was started up. That spreadsheet is the result of exporting out of Citect into a .CSV file and then importing that into Excel. My goal was to have a templete that I could give to people so that when they are looking at the graph they can take how ever many days worth of info and import it into the templete and it would spit out that data. Does that make sense?

[BobLfoot 301]

If you insert another sheet the following macro will reduce the data eliminating non-zero datums. Sub CompressData() 'Declare Variables Dim SourceRow As Integer Dim TargetRow As Integer Dim SourceCol As Integer Dim TargetCol As Integer 'Initialize the Pointers SourceRow = 1 TargetRow = 1 SourceCol = 1 TargetCol = 1 'Move the Header Row Sheet2.Cells(TargetRow, TargetCol).Value = Sheet1.Cells(SourceRow, SourceCol).Value Sheet2.Cells(TargetRow, TargetCol + 1).Value = Sheet1.Cells(SourceRow, SourceCol + 1).Value Sheet2.Cells(TargetRow, TargetCol + 2).Value = Sheet1.Cells(SourceRow, SourceCol + 2).Value Sheet2.Cells(TargetRow, TargetCol + 3).Value = Sheet1.Cells(SourceRow, SourceCol + 3).Value 'Increment the Row Pointers SourceRow = SourceRow + 1 TargetRow = TargetRow + 1 'Loop Thru the Data Do While Sheet1.Cells(SourceRow, SourceCol).Value <> "" 'Check for Non Zero Data If Sheet1.Cells(SourceRow, SourceCol + 2).Value <> 0 Or Sheet1.Cells(SourceRow, SourceCol + 3).Value <> 0 Then 'Move the Header Row Sheet2.Cells(TargetRow, TargetCol).Value = Sheet1.Cells(SourceRow, SourceCol).Value Sheet2.Cells(TargetRow, TargetCol + 1).Value = Sheet1.Cells(SourceRow, SourceCol + 1).Value Sheet2.Cells(TargetRow, TargetCol + 2).Value = Sheet1.Cells(SourceRow, SourceCol + 2).Value Sheet2.Cells(TargetRow, TargetCol + 3).Value = Sheet1.Cells(SourceRow, SourceCol + 3).Value 'Increment the Row Pointer TargetRow = TargetRow + 1 End If 'Increment the Row Pointer SourceRow = SourceRow + 1 'Defer to Operating System While Looping DoEvents Loop End Sub THE CURIOUS THING WAS SOME DATUMS HAVE BOTH ON AND OFF DATA THIS CONFUSES ME - CAN YOU EXPLAIN? DATE TIME TT_Landing_On TT_Landing_Off 5/7/2009 5:06:48 AM 1 0 Uptime Starts Here? 5/7/2009 5:16:12 AM 0 1 Downtime Starts Here? 5/7/2009 5:19:44 AM 1 0 Uptime Starts Here? THEN EXPLAIN THE BELOW PLEASE? 5/7/2009 8:03:05 AM 1 0 5/7/2009 8:11:19 AM 0.5 1 5/7/2009 8:58:20 AM 0 1 5/7/2009 8:59:30 AM 1 0

[Duffanator 1]

Yeah, I know it's kinda weird. This also has to do with the spanning of the trend. When the on signal (basicly a contactor that pulls in to start the conveyor) goes high I log it as TT_Landing_On with a value of 1, when it goes low I log it as TT_Landing_Off and it has a value of 2. This is so that they can see on the trend what time it was started and what time it was stopped as spikes on the trend graph. It works great but when you span the graph out farther than a few hours Citect "edits" the data and instead of always getting values of 1 for on and 2 for off you can get .5 and 1 or other values depending on what Citect decides to do with it. The examples where it's both on and off at the same time are examples of when production starts and stops the conveyor very close to eachother, again, if I would span the trend to 15 minutes or something you would see a gap in the data but since it's spaned to 8 hours it seems to be at the same time. Everything is vary variable and depends greatly on how the trend is spaned with it is converted to the .CSV file. Thanks for the Macro Bob, I will try that and see if I can figure something out from there.

[BobLfoot 301]

If I have time tonight I'll also play with the condensed data and see if I can generate uptime and downtime sums with another Macro.

[Duffanator 1]

Cool, thanks I appreciate it.

[BobLfoot 301]

This macro appears to do the trick. For a starter point. It does not take into account if the date changes or "rolls" SO it will need debug and beef up. But not tonight by me. Sheet1 is your original data. Sheet2 is compressed data. Sheet3 is calcualted up and down times. Sub CalcTimes() 'Declare Variables Dim SourceRow As Integer Dim TargetRow As Integer Dim SourceCol As Integer Dim TargetCol As Integer Dim ScratcH 'Initialize the Pointers SourceRow = 1 TargetRow = 1 SourceCol = 1 TargetCol = 1 'Move the Header Row Sheet3.Cells(TargetRow, TargetCol).Value = Sheet2.Cells(SourceRow, SourceCol).Value Sheet3.Cells(TargetRow, TargetCol + 1).Value = Sheet2.Cells(SourceRow, SourceCol + 1).Value Sheet3.Cells(TargetRow, TargetCol + 2).Value = Sheet2.Cells(SourceRow, SourceCol + 2).Value Sheet3.Cells(TargetRow, TargetCol + 3).Value = Sheet2.Cells(SourceRow, SourceCol + 3).Value 'Increment the Row Pointers SourceRow = SourceRow + 1 TargetRow = TargetRow + 2 'Loop Thru the Data Do While Sheet2.Cells(TargetRow, SourceCol).Value <> "" If Sheet2.Cells(SourceRow, SourceCol + 2).Value <> 0 Then If Sheet2.Cells(SourceRow, SourceCol + 3).Value = 0 Then Sheet3.Cells(SourceRow, SourceCol + 2).NumberFormat = "h:mm:ss" Sheet3.Cells(SourceRow, SourceCol + 2).Value = Sheet2.Cells(TargetRow, TargetCol + 1).Value - Sheet2.Cells(SourceRow, SourceCol + 1).Value Else Sheet3.Cells(SourceRow, SourceCol + 2).Value = "" End If Else Sheet3.Cells(SourceRow, SourceCol + 2).Value = "" End If If Sheet2.Cells(SourceRow, SourceCol + 3).Value <> 0 Then If Sheet2.Cells(TargetRow, SourceCol + 2).Value <> 0 Or Sheet2.Cells(TargetRow, SourceCol + 3).Value <> 0 Then Sheet3.Cells(SourceRow, SourceCol + 3).NumberFormat = "h:mm:ss" Sheet3.Cells(SourceRow, SourceCol + 3).Value = Sheet2.Cells(TargetRow, TargetCol + 1).Value - Sheet2.Cells(SourceRow, SourceCol + 1).Value Else Sheet3.Cells(SourceRow, SourceCol + 3).Value = "" End If Else Sheet3.Cells(SourceRow, SourceCol + 3).Value = "" End If SourceRow = SourceRow + 1 TargetRow = TargetRow + 1 DoEvents Loop End Sub

[Duffanator 1]

Thanks Bob, that works pretty well. I've never done any coding in Excel before, it's pretty interesting I didn't even know you could do that stuff. I appreciate your respose and your time. Thank you!

[BobLfoot 301]

Your Welcome Duff. And yes you can do quite a bit with VBA Code and Excel macros. I even wrote 75% of a PLC Program with Excel one time.

[edda 1]

VBA is powerful even when it comes to PLC programming, excel does the boring job for me... I did a excelsheet that creates the symbolics, puts up all motor/valves blocks (as a STL Source, S7) and all the tags to scada/hmi (for the motors/valves) from some simple inputs... So I can focus on the heavy stuf (seqvenses, regulations and other functions)... Reduce the developing time and minimize the copy/paste errors...