在Excel中将字符串转换为Epoch时间

Question

我正在尝试将以下格式的字符串转换为epoch时间(自1970年以来的秒数)在MS Excel 2013中： 20130817T140000Z (2013年8月17日14:00)。尝试了单元格格式，但是它无法处理T和Z或一般格式。

- Fearghal

Answer 1

这将把你的日期转换为Excel可以理解的格式。如果你的日期在A1单元格中，那么将其转换为Epoch时间。

=(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)

- user2140261

Answer 2

假设您要转换的字符串在单元格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))

定制格式是：

（保留HTML，不解释）。

dd/mm/yyyy hh:mm:ss

- Hooloovoo

---

@Fearghal 你应该意识到这并没有回答你的问题，对吧？这个代码获取的是Excel时间或者从1900年1月1日开始计算的数字，而你要求的是1970年1月1日开始计算的Epoch时间。 - user2140261

是的，但我选择了日期时间 - 我可以自己进行转换。如果您也想发布基于纪元时间的解决方案，那就太好了。 - Fearghal

这个问题的根源在于将一个字符串转换为日期时间值。这解决了那个问题。我同意它不会转换为纪元，但它提供了一个起点。 - Hooloovoo

Answer 3

试试这个：

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 是您输入的日期文本，A2 是格式化后的日期，A3 是您的日期与时代开始日期之间的秒差。

有用的 链接

更新：基于 @user2140261 的建议。

- zx8754

---

我认为这不正确，我认为DateDif在你的计算中出了差错，请参考我的答案，我手动通过减去1/1/1970（25569）来获取差异，然后乘以86400秒，而你也是按秒计算的（24 * 60 * 60 = 86400），我认为你的DateDif函数有问题，这个函数已知会破坏日期，你可以调查一下，这也是它不受Excel支持的确切原因，没有帮助。 - user2140261

实际上不是DateDif函数的问题，而是当您使用DateDif时丢弃了时间值。请尝试使用以下方法：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)))*86400 - user2140261

Answer 4

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

=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)

- Nicholas Flees

---

这非常复杂，并且仅获取Excel时间，而不是纪元时间。此外，OP要求秒级精度，而不是分钟。 - user2140261

Answer 5

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

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

这个公式使用TEXT函数将您的字符串转换为一个看起来像有效日期/时间的字符串 - 当您减去25569（1970年1月1日）时，它将强制将该字符串转换为有效的日期/时间，并且结果可以乘以一天中的秒数以获得Epoch时间。

- barry houdini