When retrieving Internal Log Data from a controller or creating a Data Log file with the data logging feature of MSView, the data gets saved to a .csv file which can be opened using a spreadsheet software program like Excel. The format of the Date and Time is in ISO 8601 format which is an international convention for exchanging date and time information. The logged data will also be in Coordinated Universal Time (UTC) formerly known as Greenwich Mean Time (GMT). The following steps explain how to adjust the data from the ISO 8601 UTC time format to Excel local time format.
1) Create a new Column B for Date and Column C for Time (A single Date/Time format can also be used with a single column)
2) In Cells B2 & B3 enter “= DATEVALUE (MID(A2,1,10)) + TIMEVALUE(MID(A2,12,8)) + ([X # of Hours]/24)”
- [X # of Hours] is the time difference from the UTC/ Greenwich meridian. East of UTC is positive, West of UTC is negative
- Example Eastern Standard Time (EST) (US) = -5 hours during winter compared to UTC: “= DATEVALUE (MID(A2,1,10)) + TIMEVALUE(MID(A2,12,8)) + (-5/24)”
- Explanation: Since every day= 24 hours, 1 hour is 1/24.
- This will show up as a number with a decimal. Left of the decimal is the Date (counted in days), Right of the decimal is the Time (counted as a fraction of 1 day)
3) Change the format to a date/time format. Date format for B2 and Time format for B3.
4) Copy & Paste new cell(s) to remaining cells in column(s).
5) Note: UTC/GMT is never adjusted forwards or backwards for daylight savings time (DST) so part of the data may need to be adjusted. For example, DST started at 2AM on March 13th for 2016. In the example for Eastern Standard Time (EST) Winter we would adjust to EST Summer by using -4 hours starting on March 13 at 2AM and adjusting the data accordingly in the formula following that date. “= DATEVALUE (MID(A2,1,10)) + TIMEVALUE(MID(A2,12,8)) + (-4/24)”
Example of re-formatted data and adjustment to EST Winter Daylight Savings Time:
time (UTC) Date Time
2016-03-11T21:10:14.000+00:00 March 11, 2016 4:10:14 PM
Adjusted for Daylight savings after March 13, 2016; 2AM:
time (UTC) Date Time
2016-03-13T07:10:14.000+00:00 March 13, 2016 3:10:14 AM