Excel -如何转换UTC日期时间

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

我想在excel中使用这个字符串作为日期时间

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

How can it be converted?

如何转换?

2 个解决方案

#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 +.

在日期/时间里有两件事是有争议的。第一个是表示时间开始的T。Excel的使用空间。第二个是+右边的所有项。

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

如果我们去掉这些，excel就会将其视为日期/时间。所以用这个来去除不需要的信息:

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

EDIT

编辑

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

正如@ forwarding指出的那样，这只带来UTC时间。

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:

得到:

#2

Given a UTC dateTime e.g. : 2017-08-22T14:26:30.000+1000

给定一个UTC日期时间，例如:2017-08-22t14:30.000 +1000

=(DATEVALUE(MID(A1,1,10)) +TIMEVALUE( MID(A1,12,12)))+TIME(MID(A1,25,4)/100,0,0)

#1