SOLVED

Date/hour format

Copper Contributor

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. 

4 Replies

@TP927490e

 

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

Special Timestamp.png

 

Also, you need this custom format to be applied to it:

m/d/yyyy h:mm.000

 

Hope that helps

best response confirmed by TP927490e (Copper Contributor)
Solution

@TP927490e 

as 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).

 

Thank you @Haytham Amairah !

Thank you for your time.
1 best response

Accepted Solutions
best response confirmed by TP927490e (Copper Contributor)
Solution

@TP927490e 

as 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).

 

View solution in original post