Jul 05 2019 08:51 AM
I imported a csv. The date/hour format is quite special: 2017-07-07T17:42:42.931Z,055
What would be the custom format for this?
Thanks for your support.
Jul 05 2019 10:11 AM
Hi,
You need to convert this timestamp to a proper date/time so that Excel can read it.
So I suggest this formula:
=REPLACE(SUBSTITUTE(A2,"T"," "),SEARCH("z",A2),LEN(A2)-SEARCH("z",A2)+1,"")+0
Also, you need this custom format to be applied to it:
m/d/yyyy h:mm.000
Hope that helps
Jul 05 2019 01:43 PM
Solutionas variant that could be
=DATEVALUE(MID(A2,1,10))+TIMEVALUE(MID(A2,12,12))
assuming the timestamp is always in same format. Another assumption we use date/time for UTC+0 time zone as it is in timestamp. If conversion to local time zone is required that will be more complicated.
have no idea what the number after the comma means (055 in this sample).
Jul 05 2019 01:43 PM
Solutionas variant that could be
=DATEVALUE(MID(A2,1,10))+TIMEVALUE(MID(A2,12,12))
assuming the timestamp is always in same format. Another assumption we use date/time for UTC+0 time zone as it is in timestamp. If conversion to local time zone is required that will be more complicated.
have no idea what the number after the comma means (055 in this sample).