Excel⽤函数把时间戳格式和⽇期格式相互转换
X+8*3600当前时区的时间(秒),北京时间+8⼩时
“⼯欲善其事,必先利其器。”——孔⼦《论语-卫灵公》
⼀、时间戳格式和⽇期格式相互转换
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)/86400转换单位为天,⼀天为86400秒
(X+8*3600)/86400+70*365加上1900到1970这七⼗年
(X+8*3600)/86400+70*365+19加上闰年多出来的天数19天unix时间戳转换日期格式
细⼼的话你会发现,1900年到1970年共是17个闰年,考虑到Excel将1900-1-1当作1,那么公式最后应该加18才对,为什么要加19呢?这是Excel中的⼀个bug——1900年也被当作闰年,因此应当再多加⼀天。
另外要注意,再Excel的⼯具->选项->重新计算中,有个1904年⽇期系统,如果勾选这个选项,上⾯的公式应当将70改为66,即 = (X+8*3600)/86400+66*365+19

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。