Need help with JsonTime2Excel

I’d like to serialize what I believe to be ISO 8601 compliant date/time strings but in spite of trying a bunch of different approaches, I’m not having any luck. Attached are formula an value screen shots of a sample sheet that recreates the problem that I’m having. I believe that I should be able to use JSONTIME2EXCEL to get a serialized time value from the data I’m receiving. The Value function hasn’t helped and I’m not sure how to force the data type to string.
Any advice sincerely appreciated.

I forgot to include the config info: Running StreamSheets 2.0 in Chrome 85.0.4183.83 on Win10 Pro 64

Hi kweb,

thanks for the post!
We should be able to solve this right away. :muscle:

If I understand it correctly, the date format you receive corresponds to YYYY-MM-DDThh:mm:ss
Our JSONTIME2Excel() expects a slightly extended version which is why we have to add “.000Z”.
If you append this extension, the conversion works nicely.

In the screenshot you see this in Cells B9:B11 and the result in D11.

(In case you wonder about the datetime I receive in the payload and which is placed in B5. This is simulated in Streamsheets and therefore has this format already.
Then, in B9 I am extracting the left part in order to have a format that corresponds to your format.)


Let me know how it works on your end.
Thanks for the hint, we´ll double-check whether we have to extend our implementation here to fully support the ISO 8601 standard.

Sorry it took so long to get back to this. The python code on the remote device is supposed to be producing iso compliant output including but it seems that it doesn’t include the UTC Z offset. I took the lazy way out and append a zero offset string to the date/time stamp at the source and now serialization is working great in the spread sheet.
Thanks!

2 Likes