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.

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

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?

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

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.

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.

Cool, thanks I appreciate it.

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

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!

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.

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