Excel中时间戳转换时间

finishtime（时间戳）	北京时间
1578464227	2020/1/8 14:17

需要导数据，网上找了很多公式，但是不知道原理。这里总结并分析了，为什么这样算。

公式：

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

将时间格式转化为长时间具体到分钟

8*3600 代表加上北京时间，北京是+8。东八区。加上八小时的秒数。

86400为一天的秒数。60*60*24=86400

 

时间戳转成正常日期的公式:

C1=(A1+8*3600)/86400+70*365+19

其中A1表示当时的1249488000时间戳数值

其中C1就是所需的日期格式，C1单元格属性改成日期格式就可以了。

正常日期转为时间戳格式公式：

A1=(C1-70*365-19)*86400-8*3600

其中C1表示正常日期时间格式；

其中A1就是所需的时间戳格式，A1单元格属性日期格式转成常规格式就可以了。

 

公式原理：

Excel时间实际上是序列值，它从1900-1-1为1开始，没过一天序列值加1。

Excel时间戳实际上是秒数，它从1970-1-1 00:00:00 UTC开始。

1h = 3600s

24h = 24*3600s = 86400s

XN+8*3600                                          当前时区的时间(秒)

(XN+8*3600)/86400                           转换单位为天

(XN+8*3600)/86400+70*365            加上1900到1970这七十年

(XN+8*3600)/86400+70*365+19     闰年多出来的天数

1970到1900年共有17个闰年，因为Excel是以1900为1开始，那么需要给1970+18，但由于excel的时间bug，Excel认为1900年也为闰年，所以需要给1970+19

时间戳 时间

1970-1-1 1900-1-1

一、Excel中时间戳转换时间

XN 为时间戳单元格

1、=(XN+8*3600)/86400+70*365+19

2、转换后的单元格设置为“时间”格式

3、注意，在 Excel 的工具 -> 选项 -> 重新计算中，有个 1904 年日期系统，如果勾选这个选项，上面的公式应当将 70 改为 66

二、Excel时间转换时间戳

XN 为时间戳单元格

公式   =(XN-19-70*365)*86400-8*3600  

注意，在 Excel 的工具 -> 选项 -> 重新计算中，有个 1904 年日期系统，如果勾选这个选项，上面的公式应当将 70 改为 66