如何在Google Sheets中从Mongo ID提取日期？

问题描述 #

我正在处理包含以下Mongo ID的Google表格：

621624b65241f6bddcaed7ad
63f72a1256d9a7b339d80a38
638b29fa7498254fca1d4e3c 

想从中提取日期。参考相关资料后尝试了Decimal、INT和Base函数，仅Decimal函数看似可行。手动截取前8个字符后设置日期格式未生效，得到类似1645618358的时间戳数值，请问如何在Google Sheets中实现需求？

解决方案 #

核心原理 #

MongoDB ObjectID的前8位是十六进制的秒级时间戳，Google Sheets的日期系统以1970年1月1日为基准，且1天对应86400秒，只需把十六进制时间戳转成十进制秒数，再换算成Sheets可识别的日期格式即可。

一步到位公式 #

假设Mongo ID在单元格A1，直接用以下公式就能得到对应日期：

=DATE(1970,1,1)+HEX2DEC(LEFT(A1,8))/86400

如果无法使用HEX2DEC函数，可改用手动进制转换的公式：

=DATE(1970,1,1)+LEFT(A1,8)/16^8/86400

步骤拆解 #

1. 截取时间戳片段：用LEFT(A1,8)提取Mongo ID的前8个十六进制字符，比如第一个ID会得到621624b6。
2. 进制转换：HEX2DEC()把十六进制字符串转为十进制秒级时间戳，第一个ID对应结果是1645618358。
3. 转换为日期：将秒数除以86400得到相对于1970年1月1日的天数，加上基准日期后就会得到可读的日期时间。

格式调整 #

公式计算完成后，选中目标单元格，通过「格式 > 数字 > 日期时间」选择合适的显示格式（比如YYYY-MM-DD HH:MM:SS）即可。

内容的提问来源于stack exchange，提问作者Asad