'Excel - How to convert UTC date time
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?
Solution 1:[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:
Solution 2:[2]
Given a UTC dateTime e.g. : 2017-08-22T14:26:30.000+1000
=(DATEVALUE(MID(A1,1,10)) +TIMEVALUE( MID(A1,12,12)))+TIME(MID(A1,25,4)/100,0,0)
Solution 3:[3]
This worked to convert
from :Brisbane time - 2022-04-24T11:04:33+10:00
to :UTC time - 2022-04-24 01:04:33
=TEXT((LEFT(A1,10)&" "&MID(A1,12,8))-10/24,"YYYY-MM-DD HH:mm:ss")
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | jimjim |
| Solution 3 | RayFitz |