在Google Sheets中将Unix Epoch时间转换为日期

Question

我有一张表格，其中一列是Unix纪元时间（以秒为单位）：1500598288

我该如何将它们转换为正常的日期？

- Mike Bjorge

---

表格会显示哪个时区的日期？用户是谁会影响到吗？ - stracktracer

Answer 1

截至2023年，GSheet引入了一个新的函数，EPOCHTODATE：

=EPOCHTODATE(1500598288)

要将整列数字转换，只需使用ARRAYFORMULA（或BYROW，如果你倾向于使用LAMBDA）：

=ARRAYFORMULA(EPOCHTODATE(A:A))

 

---以下答案仍然有效，但截至2023年已过时---

最简单的方法，不需要任何JS编程，是通过一个公式来实现，将秒数除以每天的86400秒，并加上1970年1月1日。例如，以下公式可以得到2017年7月21日：

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

要将整列数字转换，只需使用ARRAYFORMULA：

=ARRAYFORMULA(A:A / 86400 + DATE(1970, 1, 1))

- ttarchala

---

此外，Unix时间是UTC（协调世界时）。考虑到您所在的时区，例如，我在旧金山，它应该是： - Jiayang

2017年，作为强大用户的聚集地，谷歌似乎认为没有人想在电子表格中将时间戳转换为易读的日期格式。也许是我生活在错误的星球上。 - Sridhar Sarnobat

不错！！而且反过来是：=(B71-date(1970,1,1))*86400 - Menelaos

@Shaardool，是的，带有数据的单元格！ - Menelaos

这里需要注意的是，如果你想将Unix时间戳转换为自己所在的时区，你应该加上 time(1, 0, 0) 来到达UTC+1，或者减去 time(1, 0, 0) 来到达UTC-1，以此类推。 - Joeytje50

Answer 2

使用javascript的日期函数(Date function)创建自定义函数：

工具 > 脚本编辑器... >

function FROM_UNIX_EPOCH(epoch_in_secs) {
  return new Date(epoch_in_secs * 1000);  // Convert to milliseconds
}

接着在一个新列中，您可以输入=FROM_UNIX_EPOCH(A1)

- Mike Bjorge

---

请注意，现在您需要使用@customfunction JSDoc注释才能使您的脚本在表格中可用。来源：https://developers.google.com/apps-script/guides/sheets/functions - Spencer

Answer 3

在谷歌表格单元格中将Epoch时间戳转换为日期时间（GMT调整），请添加以下内容：

=A1/86400+DATE(1970,1,1)+time(5,30,0)

- UserBSS1

---

简单而且有效。谢谢！ - Casey L

Answer 4

现在距离原帖已经过去了3年，你可以进行以下操作：

工具 > 脚本编辑器... >

function millisToDate(timeInMillis){
    var yourDateFromMillis = new Date(timeInMillis);
    return yourDateFromMillis;
}

并在单元格中使用你的新函数...

=millisToDate(此处为单元格)

- adaao mascarelli

---

我经常使用上述函数。我在这里添加了一些额外的项目。 :)您还可以设置为本地时间。例如：function millisToDate(timeInMillis){ var yourDateFromMillis = new Date(timeInMillis).toLocaleString('en-US', { timeZone: 'Asia/Kolkata' }); return yourDateFromMillis; }时区在此处。 https://en.wikipedia.org/wiki/List_of_tz_database_time_zones - Jason Allshorn

这个答案在Google表格中有效，而上面的FROM_UNIX_EPOCH()函数则无效。 - KevinDeus

这对我不起作用。如果我使用：1665578823 / Wed Oct 12 2022 12:47:03 GMT+0000表格中的值为20/01/1970。 - decodebytes

Answer 5

在已经接受的答案基础上，如果您希望能够比较日期，可以使用FLOOR()函数。

=FLOOR(1500598288/86400)+date(1970,1,1)

否则，一个单元格中的“7/21/2017”可能与另一个单元格中的“7/21/2017”不相等。

- emmby

Answer 6

在其他精彩答案的基础上，这是我用来从纪元时间戳获取完整日期时间的方法。已经在Google Sheets中测试。

=QUOTIENT(A1, 86400) + date(1970, 1, 1) + time(quotient(MOD(A1, 86400), 3600), quotient(mod(A1, 3600), 60), mod(A1, 60))

一些例子：

时期	日期时间
0	1970年01月01日 0点0分0秒
61	1970年01月01日 0点1分1秒
3599	1970年01月01日 0点59分59秒
86403	1970年01月02日 0点0分3秒
2678402 (=86400*31+2)	1970年02月01日 0点0分2秒
31536008 (=86400*365+8)	1971年01月01日 0点0分8秒

- Liu Zehua

Answer 7

在 AppScript 中粘贴以下内容：

function EpocToDate(epoch) {
  //var epoch = 1451606400000; 12/31/2015 19:00:00
  return (Utilities.formatDate(new Date (epoch),'America/New_York','MM/dd/yyyy HH:mm:ss'));
}

在 Google Sheets 中使用方法：

=EpocToDate(1451606400000)

如果您需要从另一个单元格引用，可以这样做：

=EpocToDate(VALUE(C2))

- sukumar yadagiri

Answer 8

ttarchala 给出的公式是不正确的。这里是一个更详细的答案。

我建议不要替换以秒为单位的时期时间列中的数据。您可能需要它进行排序等操作。此外，将公式应用于整个列将会很困难。相反，最初在其旁边插入一个新列。

单击最顶部时期单元格旁边的新单元格。假设最顶部的时期单元格是 A1，则将此公式输入到新单元格的公式框中。

=A1/8640000+date(1970,1,1)

日期时间应该显示在您的新单元格中。默认情况下，它只会显示日期而不是时间。要同时显示时间，您需要更改单元格格式。选择您的新日期单元格后，单击123格式下拉菜单并选择所需的单元格格式。

要将此公式应用于列中的所有单元格，请单击日期/时间单元格底部右侧的小黑色方块（手柄），并将其拖动到该列的底部。一旦您释放拖动，列应该填充有相邻时期数据单元格的日期时间转换。当您单独选择每个单元格时，您可以看到时期数据单元格索引在公式中发生了变化。

- markhorrocks

---

您能详细说明一下我的答案哪里不正确吗？ - ttarchala

此外，使用=ARRAYFORMULA()函数可以轻松地将我的答案应用于整个列。 - ttarchala

很抱歉，您的答案是不正确的——OP要求将时间转换为“秒”，因此应该将时间除以每天的86,400秒。为什么您告诉OP要除以8,640,000？ - ttarchala