SOLVED
Home

Date/hour format

%3CLINGO-SUB%20id%3D%22lingo-sub-739981%22%20slang%3D%22en-US%22%3EDate%2Fhour%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739981%22%20slang%3D%22en-US%22%3E%3CP%3EI%20imported%20a%20csv.%20The%20date%2Fhour%20format%20is%20quite%20special%3A%26nbsp%3B2017-07-07T17%3A42%3A42.931Z%2C055%3C%2FP%3E%3CP%3EWhat%20would%20be%20the%20custom%20format%20for%20this%3F%3C%2FP%3E%3CP%3EThanks%20for%20your%20support.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-739981%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740157%22%20slang%3D%22en-US%22%3ERe%3A%20Date%2Fhour%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371708%22%20target%3D%22_blank%22%3E%40TP927490e%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20convert%20this%20timestamp%20to%20a%20proper%20date%2Ftime%20so%20that%20Excel%20can%20read%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20suggest%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DREPLACE(SUBSTITUTE(A2%2C%22T%22%2C%22%20%22)%2CSEARCH(%22z%22%2CA2)%2CLEN(A2)-SEARCH(%22z%22%2CA2)%2B1%2C%22%22)%2B0%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122376i8B07C3D07B485F2B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Special%20Timestamp.png%22%20title%3D%22Special%20Timestamp.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20you%20need%20this%20custom%20format%20to%20be%20applied%20to%20it%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3Em%2Fd%2Fyyyy%20h%3Amm.000%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740397%22%20slang%3D%22en-US%22%3ERe%3A%20Date%2Fhour%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371708%22%20target%3D%22_blank%22%3E%40TP927490e%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eas%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DDATEVALUE(MID(A2%2C1%2C10))%2BTIMEVALUE(MID(A2%2C12%2C12))%3C%2FPRE%3E%0A%3CP%3Eassuming%20the%20timestamp%20is%20always%20in%20same%20format.%20Another%20assumption%20we%20use%20date%2Ftime%20for%20UTC%2B0%20time%20zone%20as%20it%20is%20in%20timestamp.%20If%20conversion%20to%20local%20time%20zone%20is%20required%20that%20will%20be%20more%20complicated.%3C%2FP%3E%0A%3CP%3Ehave%20no%20idea%20what%20the%20number%20after%20the%20comma%20means%20(055%20in%20this%20sample).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769771%22%20slang%3D%22en-US%22%3ERe%3A%20Date%2Fhour%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769771%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769784%22%20slang%3D%22en-US%22%3ERe%3A%20Date%2Fhour%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769784%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20time.%3C%2FLINGO-BODY%3E
TP927490e
New 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

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.
Related Conversations