Excel中转换Unix时间的方法

最近在处理的一个案件，需要统计用户上传的图片数量和网站帖子数量。拿到服务器硬盘，镜像后将MySQL数据库及网站代码全部复制到了分析机。网站使用的“织梦CMS”内容管理系统，使用WAMP（Apache+Mysql+php）将网站充搭建起来。在浏览器中输入“http://127.0.0.1/phpmyadmin”查看对应数据库，将“dede_archives”表导了出来，这个表里面有所有帖子信息，包括帖子名，上传时间、发布时间，附件位置等等，我接下来大部分工作就在这个表上进行。

“dede_archives”表中所有时间都形如“1400890656”，初步判断是Unix时间格式，百度“Unix时间转换”，将此值转换得“2014-5-24 0:17:36”，结合其他因素判断，果然没错。由于数据量比较大，一个一个转换肯定不行，经过考虑，准备用Excel批量转换。经过一番折腾，找到了转换方法。

假设A1单元格为Unix时间值，我们要在B1单元格显示可视化时间，则只需要在B1单元格中输入“=A1/86400+25569”即可，然后将单元格格式设置为时间格式，如果嫌麻烦，也可以直接在B1单元格中输入“=TEXT(A1/86400+25569,"yyyy-m-d h:mm:ss")”，这样B1单元格便会以“2014-5-24 0:17:36”的格式显示。前面的操作并没有考虑时区，如果原Unix时间为UTC时间，而我们要转换成北京时间（东8时区），则B1单元格中只需输入“=TEXT(A1/86400+25569+8/24,"yyyy-m-d h:mm:ss"”,同理，如果要转换成西7时区，B1单元格输入的内容为“=TEXT(A1/86400+25569-7/24,"yyyy-m-d h:mm:ss"”。

下面来解释一原理。Unix时间格式记录的是距离1970年1月1日0:00:00流逝的秒数，86400=606024，正好是一天的秒数，A1/86400即为Unix时间值代表的距离1970年1月1日0:00:00流逝的天数。25569又是什么东西呢？25569=70*365+19，是从1900年1月1日到1970年1月1日的天数，+19是因为1990年到1970年之间闰年数目。那么，1900年1月1日0:00:00又是怎么来的？这个，是我试出来的。当我们在Excel表格中输入一个时间，然后将单元格设置成常规类型，就会发现单元格内容变成了一个数值，所以说Excel表格中记录时间的方式类似Unix时间格式，当我在单元格中输入“0”，然后装换成日期格式时，显示的是“1900年1月1日 0:00:00”。TEXT(数值,"yyyy-m-d h:mm:ss")函数的意思是将“数值”按照“年-月-日 时:分:秒”的时间格式显示。

现在来分析一下“A1/86400+25569”，假设A1值为1400890656，

则A1/86400表示1400890656距离1970年1月1日0:00:00的天数 ①

25569代表1970年1月1日0:00:00距离1900年1月1日 0:00:00的天数 ②

由①、②得A1/86400+25569表示1400890656距离1900年1月1日 0:00:00的天数，正好就是Excel记录日期的格式。

下面是操作方法，是不是很简单？

本文2015年5月6日发布于本博客