Excel中如何将长数字转为年月日时分秒？

Question

在Excel中处理时间戳时，常遇到将长数字（如16位或13位毫秒级时间戳）转换为可读的年月日时分秒格式的问题。例如，如何正确解析类似“1713340800000”这样的时间戳，并将其准确转换为“2024-04-17 00:00:00”的日期时间格式？许多用户因忽略Excel默认以“天”为单位的时间系统，直接使用公式导致结果偏差。常见的疑问包括：是否需除以86400000？是否应结合DATE(1970,1,1)进行偏移计算？以及如何处理时区差异（如UTC转北京时间）？此外，TEXT函数与单元格格式化设置的应用也常引发困惑。掌握正确的转换逻辑对数据分析至关重要。

Answer 1

一、理解时间戳的本质与Excel时间系统的差异

时间戳（Timestamp）通常指自1970年1月1日 00:00:00 UTC以来经过的毫秒数或秒数。在IT系统中，尤其是Web API、日志系统和数据库中，常用13位毫秒级时间戳（如1713340800000），也有部分系统使用10位秒级时间戳。

而Excel内部采用“自1900年1月1日起经过的天数”作为日期存储机制。例如，Excel中日期“2024-01-01”对应的序列值为45326，表示从1900-01-01起经过了45326天。

因此，将Unix时间戳转换为Excel可识别的时间，必须解决两个核心问题：

1. 单位换算：毫秒 → 秒 → 天
2. 时间起点偏移：1970-01-01 UTC vs Excel的1900-01-01

若忽略这些差异，直接使用原始时间戳参与计算，会导致结果严重偏差。

二、基础转换公式推导过程

以13位毫秒时间戳为例，如：1713340800000

该时间戳对应UTC时间：2024-04-17 00:00:00

要将其转换为Excel时间，需执行以下步骤：

• 步骤1： 将毫秒转为天数：除以 86400000（即 24×60×60×1000）
• 步骤2： 计算1970年1月1日在Excel中的序列号：使用 DATE(1970,1,1) = 25569
• 步骤3： 最终公式为：
  = (A1 / 86400000) + DATE(1970,1,1)

其中 A1 存储原始时间戳。此公式将毫秒时间戳转换为Excel标准日期时间格式。

三、处理时区问题：从UTC到北京时间（东八区）

由于Unix时间戳基于UTC，而中国标准时间为UTC+8，因此需要额外加上8小时。

Excel中每小时相当于 1/24 天，故8小时为 8/24 = 1/3 天。

修正后的公式为：

= (A1 / 86400000) + DATE(1970,1,1) + 8/24

这样即可将UTC时间戳自动转换为北京时间。

示例验证：

时间戳	UTC时间	北京时间	Excel公式结果
1713340800000	2024-04-17 00:00:00	2024-04-17 08:00:00	2024-04-17 08:00:00
1609430400000	2021-01-01 00:00:00	2021-01-01 08:00:00	2021-01-01 08:00:00
1577808000000	2020-01-01 00:00:00	2020-01-01 08:00:00	2020-01-01 08:00:00
1483200000000	2017-01-01 00:00:00	2017-01-01 08:00:00	2017-01-01 08:00:00
1420070400000	2015-01-01 00:00:00	2015-01-01 08:00:00	2015-01-01 08:00:00
1356998400000	2013-01-01 00:00:00	2013-01-01 08:00:00	2013-01-01 08:00:00
1293840000000	2011-01-01 00:00:00	2011-01-01 08:00:00	2011-01-01 08:00:00
1262304000000	2010-01-01 00:00:00	2010-01-01 08:00:00	2010-01-01 08:00:00
1230768000000	2009-01-01 00:00:00	2009-01-01 08:00:00	2009-01-01 08:00:00
1199145600000	2008-01-01 00:00:00	2008-01-01 08:00:00	2008-01-01 08:00:00

四、TEXT函数与单元格格式化策略对比

在显示时间时，有两种主要方式控制输出格式：

1. 单元格格式设置：右键单元格 → 设置单元格格式 → 自定义 → 输入 "yyyy-mm-dd hh:mm:ss"
2. TEXT函数显式格式化：使用公式 =TEXT(B1,"yyyy-mm-dd hh:mm:ss") 直接生成文本字符串

两者区别在于：

• 格式化仅改变显示，不影响实际数值，可用于后续计算
• TEXT函数输出为文本，不可用于时间运算，但便于导出或拼接字符串

推荐做法：先用公式计算出标准时间值，再通过格式化控制展示样式。

五、自动化封装：创建通用转换模板

为提升效率，可构建如下命名公式（名称管理器中定义）：

// 名称：UnixToDateTime
// 引用位置：
=(Sheet1!$A1/86400000)+DATE(1970,1,1)+8/24
    

然后在B1输入：=UnixToDateTime，即可批量转换。

也可编写VBA函数增强灵活性：

Function ConvertTimestamp(ts As Double) As Date
    ConvertTimestamp = (ts / 86400000) + DateSerial(1970, 1, 1) + TimeValue("8:00:00")
End Function
    

六、流程图：时间戳转换逻辑总览

graph TD
            A[原始时间戳] --> B{是否为毫秒?}
            B -- 是 --> C[除以86400000转为天数]
            B -- 否 --> D[除以86400转为天数]
            C --> E[加上DATE(1970,1,1)]
            D --> E
            E --> F{是否需转时区?}
            F -- 是 --> G[加时区偏移(如+8/24)]
            F -- 否 --> H[输出UTC时间]
            G --> I[输出本地时间]
            I --> J[设置单元格格式或使用TEXT函数]
            H --> J
            J --> K[完成可读时间展示]