logo标签列表

在Excel中将时间戳转换为日期

excel-formula
70

我有一个非常大的 Excel 电子表格，其中包含时间戳列。有没有人知道如何将其转换为日期？是否有可以使用的函数？我尝试了格式化单元格日期，但无效。我的文件大小为 91,568 KB。如果有更简单的方法，那就太好了。我愿意听取建议。

先行致谢 :)

附言： 我不会任何编程语言

1
那是什么类型的时间戳？毫秒级别的吗？ 互联网上有很多答案。你试过其中一些了吗？ http://www.bajb.net/2010/05/excel-timestamp-to-date/ - NeplatnyUdaj
时间戳看起来像这样1234817823。当我使用这个网站(http://www.onlineconversion.com/unix_time.htm)时，它显示Mon, 16 Feb 2009 20:57:03 GMT。我的问题是如何创建一个可以应用于100万条记录的函数。 - user10165
1
然后看一下我发布的链接。我没有Excel，但我在Libreoffice Calc中尝试过，输出结果是： 02/16/09 08:57 PM - NeplatnyUdaj
13个回答
109
3
这个公式的意思是将一个时间戳（单位为秒）转换成日期格式。具体做法是将时间戳除以60再除以60再除以24，然后加上1970年1月1日这一日期。请注意，这个公式只适用于时间戳以秒为单位的情况。 - nischayn22
如果您要根据自己的时区显示时间值，应该添加时间偏移量 +TIME(1,0,0)。 - Horsing
=INT(((COLUMN_ID/60)/60)/24)+DATE(1970;1;1) 适用于Microsoft Excel在线版本 - Kostas Minaidis
INT会四舍五入到最近的一天，这可能不是OP想要的。 - MikeF
39

时间戳是自纪元时间（1970年1月1日）以来的经过时间，因此我们基本上必须将这个时间转换为天数，并添加纪元时间，以获得任何类似于Excel电子表格软件的有效格式。

  • From a timestamp in milliseconds (ex: 1488380243994)

    use this formula:

    =A1/1000/86400+25569

    with this formater:

    yyyy-mm-dd hh:mm:ss.000

  • From a timestamp in seconds (ex: 1488380243)

    use this formula:

    =A1/86400+25569

    with this formater:

    yyyy-mm-dd hh:mm:ss

其中A1是您的列标识符。给定自定义格式化程序，可以在显示的数据中不失精度，但您当然可以使用任何其他符合您需求的日期/时间格式。

很棒的答案！您能解释一下为什么要特别加上 25569 吗？我知道结果是正确的，但我不明白为什么需要这样做。 - GigiSan
1
Excel从1900年开始计算日期。25569是距离Unix开始的1970年1月1日的天数。 - MikeF
26

以下公式适用于我的MS Excel：

=TEXT(CELL_VALUE/24/60/60/1000 + 25569,"YYYY-MM-DD HH:MM")

CELL_VALUE是以毫秒为单位的时间戳。

这里是“TEXT函数”的说明。

2
这对我也起作用，以下是另一个示例，如果需要时区更正（示例为UTC-5）： =TEXT((D3/60/60/1000 + 5)/24 + 25569,"YYYY-MM-DD HH:MM") - Jono
22

如果在Libre Office中遇到错误509，您可以将DATE()函数中的,替换为;

=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970;1;1)
6
这可能取决于您操作系统中的语言环境设置。 - lorenz
10
如果你的文件非常大，尝试使用以下公式： =A1 / 86400 + 25569
需要将A1替换为所需内容。 比=((（列ID_HERE/60）/60）/24)+DATE(1970,1,1)更快速，因为需要的计算次数较少。
这是一种更优雅的解决方案，同时保留了时间戳的“时间”部分（而不仅仅是日期）。 - Patrick
7
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)

假设 A1 是您的时间戳所在的单元格，请不要忘记调整以考虑您所在的时区（如果您在东部标准时间，即5）。

5
这个DATE无法在所有版本的Excel中使用。
=CELL_ID/(60 * 60 * 24) + "1/1/1970"

采用保存的赌注会更加可靠。
引号必要，以防止Excel计算该术语。

5

请注意纪元时间的数字位数。通常为十位数（例如1534936923），然后使用：

=(A1 / 86400) + 25569

如果要将13位数字的epoch时间（1534936923000）调整到人类可读的时间，请使用以下公式：

=(LEFT(A1,LEN(A1)-3) / 86400) + 25569

为避免
###################################

日期或时间如果是负数或太大，将显示为######。
更多信息请参见https://www.epochconverter.com/。
1
@NeplatnyUdaj的答案是正确的，但需要考虑到Excel要求函数名称使用设定语言，例如我的情况是德语。因此你需要使用"DATUM"而不是"DATE"：
=(((COLUMN_ID_HERE/60)/60)/24)+DATUM(1970,1,1)
0

将AD滴答转换为日期时间格式：=A1/864000000000 - 109205

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接