If you wish to perform calculations on the time and dates recorded in Citect SCADA log files, this can be accomplished by opening the files in Microsoft Excel.
Open the file in Excel by selecting settings in the Text Import wizard such that the date and time fields are NOT split into separate cells. Once opened in Excel, select the column containing the date and time and format the cells in the column with a custom format of yyyy-mm-dd hh:mm:ss.000. This will allow Excel to correctly parse the date/time, and store the value in days, as the number of days (and fractions of a day) since 1900-01-01 00:00:00.000 (this date/time has a value of 1).
Calculations between date/time values can be simply performed. However, it needs to be remembered that, since Excel treats times as days, if the difference in milliseconds between two date/times is necessary, the result of the difference calculation needs to be multiplied by (24*60*60*1000).
Parsing the UTC offset
Using the UTC offset in Excel is a little more complicated, as Excel will not accept negative times. Therefore format the UTC offset cell/s as a text string, and write a formula to convert it into a fraction of a day. For example, if the UTC offset is held in cell B4, the formula is:
=LEFT(B4,1)&TIME(MID(B4,2,2),RIGHT(B4,2),0)
This will convert the UTC offset into a correctly signed numerical value expressed as a fraction of one day.
For example:
This UTC offset value can be subtracted from the local time to derive the UTC time.
UTC offset could be used with merge logs from two different time zones. In this case you may wish to make both logs based on UTC time. For example:
11:00:00.000 +01:00 is 10:00:00.000 in UTC (minus 1 hour)
09:01:00.000 -01:00 is 10:01:00.000 in UTC (plus 1 hour)
See Also
Published June 2018