Power Quality - .csv download issue number 2

Sense Labs
1

I finally had my first detection of a dip via Sense Labs, so I fairly had a chance to check out the CSV produced. It turns out the date field is exported in a format that is fairly unintelligible to Excel, example here: “2020-08-15T00:11:14.000Z”

Two issues:

  1. If someone knows the custom format in Excel that parses this correctly, let me know. I tried for a long while and couldn’t make it parsable as a datetime.
  2. Even after that, the date is in UTC, not local time, unlike Sense’s standard export. Given Excel’s rather poor timezone capabilities, it’s painful to create a general formula that converts it to local date/time.

I’m lucky because R/readr/strftime is able to parse that using format = “%Y-%m-%dT%H:%M:%OSZ”.

2

This was my basic excel formula to make it usable. It just moves it into a single field in a format that excel can read, but easy enough to break into two cells if you want a date column and a time column, but your dates may be off when you correct for UTC offset. Just be sure to adjust the Column B format to show both date and time to your liking. Otherwise by default, it is just going to show the date.

Excel date DATE function is just looking for Year, Month, Day. TIME is looking for Hour, Minute, Second) and it converts it all into Excel decimal formats.
The MID function just pulls date from a specific spot in a data field. Luckily Sense was nice enough to always use 2 digit format for teh date and time. Otherwise it would be slightly tricier as the function would have to count / or " to know where to look for each chunk of date.

The second “Time” function is doing the time zone correct (just subtracting X hours from the time. So you can adjust the 4 to whatever offset you need.
You could in theory do this in the first time function as part of the first MID call, but I like it broken out so I can see it easier.

The formula refers to A2, assuming you inserted a new column after A and are starting with the first row of data, not headers.

=DATE(
LEFT(A2,4),
MID(A2,6,2),
MID(A2,9,2)
)
+
TIME(
MID(A2,12,2),
MID(A2,15,2),
MID(A2,18,2)
)
-TIME(4,0,0)

Or if R1C1 is your thing

=DATE(
LEFT(RC[-1],4),
MID(RC[-1],6,2),
MID(RC[-1],9,2)
)
+
TIME(
MID(RC[-1],12,2),
MID(RC[-1],15,2),
MID(RC[-1],18,2)
)
-TIME(4,0,0)
1 Like
3

Thanks @ben, for the Excel fix ! That will be helpful for many users, I’m sure.

In the end, I’m hoping that Sense makes the export here like the data export in Trends, where the datetime is in local time for each user, so it’s more of a no-brainer when using in spreadsheets, instead of putting the onus for time zone and DST conversions on the end user.

4

Thanks for raising this @kevin1. That date format is not very friendly. We’ll look into it with our next batch of fixes.

5 Likes