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
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies