如何在Excel中将Unix纪元时间戳转换为可读的日期/时间？

Question

我有一些包含Java应用程序的Unix纪元时间戳的Excel文档。 我想查看它们的翻译，并在Excel中表示它们为人类可读日期。

例如，以下长整型：1362161251894 应该被转换为可读的东西，如：01 Mar 2013 11:07:31,894 我假设我可以为此创建一个公式，但我不确定如何操作。谢谢！

- Bryce

Answer 1

是的，您可以创建一个公式来为您完成此操作。Java和Unix / Linux从1970年1月1日开始计算毫秒数，而Microsoft Excel在Windows上从1900年1月1日开始计算，Mac OS X上从1904年1月1日开始计算。您只需要执行以下操作进行转换：

对于Windows上的GMT时间

=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))

在Mac OS X上的GMT时间。

=((x/1000)/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))

对于 Windows 上的本地时间（将 t 替换为您当前与 GMT 的偏移量）

=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))

在 Mac OS X 上获取本地时间（将 t 替换为您与 GMT 的当前偏移量）

=(((x/1000)-(t*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1904"))

---

在您的特定情况下，看起来您处于山区时间（GMT偏移量为7）。因此，如果我将您给出的值1362161251894粘贴到新的Excel电子表格中的单元格A1中，然后粘贴以下公式，则会得到结果41333.46356，如果我告诉Excel格式化为日期（在单元格上按ctrl + 1），则是：2/28/13 11:07 AM。

=(((A1/1000)-(7*3600))/86400)+(DATEVALUE("1-1-1970") - DATEVALUE("1-1-1900"))

- 11101101b

---

在Excel for Mac 2011版本中，如果我不加上"- DATEVALUE("1-1-1904")"这一段，它就能正常运行。 - rymo

我阅读得太快了，没有意识到这是毫秒级别的。如果你的时间戳是以秒为单位，请确保在x后面删除/1000。 - David Jones

奇怪。为什么1485190688881会转换成1/22而不是1/23？这是基于五小时的偏移量。 - KDP

@rymo，这个公式不需要考虑Excel的宿主系统如何开始计算日期。Excel将DATEVALUE表示为整数天和小数时间，因此在Mac上，DATEVALUE（“1-1-1904”）会从结果中减去1天，这不是OP想要的（据我所知）。 - mysteryegg

我注意到和@KDP一样的问题--我的时间是正确的，但日期却比应该的早了一天（即报告6/21，而实际上应该是6/22）。这是什么原因，我该如何“修复”它？谢谢！ - theforestecologist

Answer 2

这个问题似乎已经在这里得到了回答,

=(A1/86400)+25569

- Freiheit

---

如果你要将时间戳从毫秒转换为秒，这个方法就可以使用。如果想要正确地处理毫秒级时间戳，应该进行以下操作：=(A1/86400000)+25569，并且要记住这不考虑时区。 - 11101101b

Answer 3

时间点以毫秒为单位给出(1523060201838)。 用户希望转换为纽约时区(GMT -04:00或DST -14400)。

Excel的A1单元格中有13位数的时间点数值。

((A1/1000)-14400)/86400 + 25569

转换为4/6/18 8:16 PM（在您将单元格格式设置为日期之后）。

- brennan4u

---

这种方法在Excel 2016中每次都有效。+1。谢谢，伙计！[另外，提醒其他人：将“14400”替换为“3600 * x”，其中x是您与GMT的偏移量。在此示例中，EST是GMT-4，“3600 * 4”是“14400”。] - theforestecologist

Answer 4

尝试

=(A2/86400)+DATE(1970,1,1)+TIME(5,30,0)

这将有助于本地时间，例如印度是GMT + 5:30。

- Arunav dutta gupta

Answer 5

我发现最容易记忆和阅读的公式如下：

将Unix/Epoch时间戳转换为Excel日期（假设时间戳在A2中）：

=(A2/86400)+DATE(1970,1,1)

这将生成GMT Excel时间值。然后，您需要设置单元格的格式为其中的日期格式之一，或创建自定义格式。我倾向于使用以下任一格式：

dd-mmm-yy                --> gives 05-Oct-18
dd-mmm-yyyy hh:mm:ss     --> gives 05-Oct-2018 09:45:12

将日期转换为Epoch时间戳（假设日期在A2中）：

=(A2-DATE(1970,1,1))*86400

我发现这个页面非常有用：Extendoffice excel-timestamp-to-date 注意：如果你需要从GMT（以纽约为例）调整到本地时间，请在结尾处添加差值。以下是-5小时的偏移量示例。

=(A2/86400)+DATE(1970,1,1)+(-5/24)

- Traveler_3994

Answer 6

Excel将天数表示为整数值，将时间表示为小数值。因此，如果UNIX提供的纪元时间是自1970年1月1日以来的毫秒数，那么我们希望将其除以一年中的毫秒数，并加上Excel对1970年1月1日的表示，以提供可读的UTC时间。如果您的值在单元格A1中，则Excel需要：

=A1/86400/1000+DATEVALUE("1-1-1970")

请注意，如果您的纪元时间是以秒为单位而不是毫秒，则可以省略/1000。要转换为本地时间，其中“t”是您的本地UTC偏移量（如果您有负的UTC偏移量，请记得使用负值），则可以添加/减去UTC偏移量：

=A1/86400/1000+DATEVALUE("1-1-1970")+t/24

请注意，您的UTC偏移量可能会因您所在地区是否观察夏令时而有所不同，这使得在单个电子表格中显示全年本地时间成为一种不完整的解决方案。

- mysteryegg