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
Highlighted
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
Highlighted

@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

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

 

Highlighted

Thank you @Haytham Amairah !

Highlighted
Thank you for your time.
Related Conversations
Is it possible for Edge to play HEVC (H265) MKV files ?
HotCakeX in Discussions on
2 Replies
Date format in Stream
Dave Pyett in Microsoft Stream Forum on
2 Replies
Totaling using two sets of data
dazedandconfused in Excel on
2 Replies
Invoice date
Opti-IT in Microsoft Invoicing on
1 Replies