Excel用函数把时间戳格式和日期格式相互转换

“工欲善其事，必先利其器。”——孔子《论语-卫灵公》

一、时间戳格式和日期格式相互转换

        1、时间戳转换成正常日期的公式：

            B2=(A2+8*3600)/86400+70*365+19

            Excel对应的B2表格中应该输入如下的公式，并且转换成时间格式"yyyy-mm-dd hh:mm:ss"

            或者 "yyyy/mm/dd hh:mm:ss" 或者"yyyy-m-d hh:mm:ss"：

            =TEXT((A2+8*3600)/86400+70*365+19,"yyyy-mm-dd hh:mm:ss")

            其中A2表示时间戳数值所在的表格，B2就是所需呈现日期格式的表格，双击B2表格右下角鼠标为“黑十字”，可以填充满

        2、正常日期转换为时间戳格式的公式：

            C2=(B2-70*365-19)*86400-8*3600

            其中，B2为正常日期时间的表格，C2为所需时间戳格式的表格

           C2表格中输入：=(B2-70*365-19)*86400-8*3600，然后Enter回车，想充满表格可以双击C2表格右下角鼠标“黑十字”

二、什么是时间戳

        Unix时间戳（Unix  timestamp），或称Unix时间（Unix  time）、POSIX时间（POSIX  time），是一种时间表达方式，定义为从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数。Unix时间戳不仅被使用在Unix系统、类Unix系统中，也在许多其他操作系统中被采用。

三、Excel中时间戳转换公式及原理

        这个公式的原理：Excel的日期实际上是序列值，它以1900-1-1为1，每过一天序列值加1,。而Unix时间戳是从1970-1-1 00:00:00 UTC开始到现在经过的秒数。用X表示时间戳，可得到换算公式：

X+8*3600	当前时区的时间（秒），北京时间+8小时
(X+8*3600)/86400	转换单位为天，一天为86400秒
(X+8*3600)/86400+70*365	加上1900到1970这七十年
(X+8*3600)/86400+70*365+19	加上闰年多出来的天数19天

         

       

       

       

 

        细心的话你会发现，1900年到1970年共是17个闰年，考虑到Excel将1900-1-1当作1，那么公式最后应该加18才对，为什么要加19呢？这是Excel中的一个bug——1900年也被当作闰年，因此应当再多加一天。

        另外要注意，再Excel的工具->选项->重新计算中，有个1904年日期系统，如果勾选这个选项，上面的公式应当将70改为66，即 =(X+8*3600)/86400+66*365+19

 

 

参考原文：https://www.kafan.cn/A/jv41elo7vr.html

                  https://www.cnblogs.com/xueluozhangxin/p/5868225.html