在Excel中将字符串转换为一个历元时间

I am trying to convert string in following format: 20130817T140000Z(17th Aug 2013 at 14:00) to epoch time (seconds since 1970) in an MS Excel 2013.

我正在尝试将以下格式的字符串转换为:20130817T140000Z(2013年8月17日14:00)到epoch time (seconds since 1970) in an MS Excel 2013。

Tried cell formatting, but it doesn't work with T and Z or the format in general.

尝试了单元格格式，但它不能与T和Z或一般格式一起工作。

5 个解决方案

#1

This will convert your date into somethign Excel will understand, If you have your date in Cell A1, Then convert that into Epoch Time

这将把你的日期转换成某个ign Excel会理解的，如果你在A1单元格中有你的日期，然后将其转换为历元时间

=(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) + TIME(MID(A1,10,2),MID(A1,12,2),MID(A1,14,2))-25569)*86400)

#2

Try this:

试试这个:

A1=20130817T140000Z
A2=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))
A3=(DATEDIF("01/01/1970 00:00:00",A2,"D")+TIME(MID(A1,10,2),MID(A1,12,2),MID(A1,14,2)))*24*60*60

A1 is your input date as text, A2 is a formatted date, and A3 is a seconds difference between your date and epoch start date.

A1是你的输入日期作为文本，A2是格式化的日期，A3是你的日期和历元起始日期之间的秒差。

Useful link

有用的链接

Update: based on @user2140261 suggestions.

更新:基于@user2140261的建议。

#3

Assuming the string to convert is in cell B1 you can use the following with a custom format on the cell to get the date/time.

假设要转换的字符串在单元B1中，您可以在单元格上使用以下的自定义格式来获取日期/时间。

=DATE(MID(B1,1,4),MID(B1,5,2),MID(B1,7,2))+TIME(MID(B1,10,2),MID(B1,12,2),MID(B1,14,2))

The custom format is:

自定义格式是:

dd/mm/yyyy hh:mm:ss

#4

This will get you the date with accuracy to the minute. You can follow the pattern to get seconds if necessary.

这将使你的日期精确到分钟。如果需要，您可以按照该模式获取秒。

=DATE(VALUE(LEFT(A1,4)),VALUE(RIGHT(LEFT(A1,6),2)),VALUE(RIGHT(LEFT(A1,8),2)))+VALUE(RIGHT(LEFT(A1,11),2))/24+VALUE(RIGHT(LEFT(A1,13),2))/(24*60)

#5

You could use this version to convert your string to Epoch time

您可以使用此版本将您的字符串转换为Epoch时间。

=(TEXT(LEFT(A1,8)&MID(A1,10,6),"0000-00-00 00\:00\:00")-25569)*86400

=(文本(左(A1,8)中期(A1 10 6),“0000-00-00 00 \:00 \:00”)-25569 * 86400

This uses TEXT function to convert your string into a string that looks like a valid date/time - when you subtract 25569 (1/1/1970) that will co-erce that string to a valid date/time and the result can be multiplied by the number of seconds in a day to get Epoch time

它使用文本函数将字符串转换为一个看起来像有效日期/时间的字符串—当您减去25569(1/1/1970)时，该字符串将与一个有效的日期/时间重合，结果可以乘以一天中的秒数来获得纪元时间

#1