Excel-如何转换UTC日期时间 [英] Excel - How to convert UTC date time
问题描述
我想在Excel中将此字符串用作日期时间
I would like to use this string as a date time in excel
2016-04-06T18:05:32.6550717+03:00
如何转换?
推荐答案
日期/时间中有两件事引起了问题.第一个是T
,它表示时间的开始. Excel使用空格.第二个是+
右侧的所有内容.
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 +
.
如果我们摆脱了这些限制,那么excel会将其视为日期/时间.因此,使用它可以删除不需要的信息:
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"," ")
编辑
正如@ForwardEd所指出的那样,这只会带来UTC时间.
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:
您将得到:
这篇关于Excel-如何转换UTC日期时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!