CSV Output field formatting

Can we get a better date format than the current one used in the CSV export.

Excel doesn’t really know what 2019-02-07T09:33:50Z is, and I’m not even sure why there is a T and Z in there.

Can we get it to be just 2019-02-07 09:33:50 (or some other agreed format that is recognised by other apps?)

1 Like

Although not ideal, there is a workaround you can use for now:

2 Likes

Ahhh thanks for that.

Dare I say it? Another example of ‘technical implementation’ over ‘user friendliness’?

Cheers for the links.

Another option is to first open the CSV file in your text editor of choice and run a grep find/replace to replace the T with a , and remove the Z on all lines:

replace:
^([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])T([0-9][0-9]:[0-9][0-9]:[0-9][0-9])Z$

with: \1,\2

You could also do this directly on the command-line if you are familiar with Unix tools (I’m not familiar enough to come up with the right incantation without a bit of work!). It’s an extra step, but doesn’t take very long, especially after the first time.

1 Like

Yup could do that.

Again - tech vs ‘average joe’ user … this isn’t really a good solution.

But thanks for the suggestion.

1 Like

Late to the thread, but I was researching this for myself. I had to figure out a way of converting the ISO8601 date/time value to something Apple Numbers can understand. I thought I would share my solution in case someone else is looking for it. I added a column to the table with this formula on every cell:

IFERROR(DATEVALUE(SUBSTITUTE(SUBSTITUTE($created,"Z",""),"T"," "))+TIMEVALUE(SUBSTITUTE(SUBSTITUTE($created,"Z",""),"T"," ")),"")

I’m sure there is a more efficient way to do it, but I hope this helps someone regardless.

3 Likes