Excel - How to convert UTC date time

Question

I would like to use this string as a date time in excel

2016-04-06T18:05:32.6550717+03:00

How can it be converted?

Answer 1

There are two things in the date/time that are giving issue. The first is the T that denotes the start of time. Excel uses a space. The second is the Everything to the right of the +.

If we get rid of those then excel will see it as a Date/Time. So use this to remove the unwanted information:

=--SUBSTITUTE(LEFT(G2,FIND("+",G2)-1),"T"," ")

EDIT

As was pointed out by @ForwardEd this only brings in the UTC time.

Upon further thinking since this is computer generated the format will remain the same. The following formula also considers the time zone:

=(SUBSTITUTE(LEFT(A1,27),"T"," "))+(MID(A1,28,3)/24)

Then format it like you want:

And you get:

---