从建站 到营销 全程无忧!
您当前的位置:首页 > 新闻资讯 > 企业建站常识

Excel/WPS 电子表格中时间戳转日期时间公式

发表时间:2022-12-08 19:42:18    浏览:3348 次

从MySql数据库中导出一份Excel文件发给了我,并吩咐我排查问题的原因。

面对庞大的数据表格,我快速浏览了一遍,确认首先需要处理的是client_time一列,内容是Unix时间戳格式,很明显,Excel不支持Unix时间戳。

来看什么是Unix时间戳

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

这好说,我上来就【右键-单元格格式-选时间分类-确定】走你~然后,我看到的结果是这样的:

大意了,看来此法不通。

这有何难?用站长工具转换一下不就好了!当我转换到第3条的时候,我意识到这将是个浩大的工程...

站长工具是不支持批量转换的,这几千条数据,如此搞下去,我怕不是有个大病。

不懂就问,有一波人说,可以写个程序用编程语言转换,这...肯定可行,但时间成本有点高。

继续不懂就问,很快我找到了一种更快速有效的解决方法。

接下来,让我做个小结以备忘:

1

13位时间戳转日期时间(毫秒)

首先

假设A2单元格内容为13位的时间戳,再选中B2单元格,在公式框中输入

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

按Enter键确认,此时能看到,B2单元格显示了转换后的日期时间格式2021/07/03 18:43:42.573。

接下来,选中B2单元格,下拉应用公式,整列搞定。

2

10位时间戳转日期时间(秒)

10位时间戳的转换方法与13位时间戳同理。唯一不同的是,此时的公式为

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

可能有人会问了:能不能将日期时间转换成Unix时间戳呢?既然我的标题上说的是“互换”,答案当然是能的了。

3

日期时间转10位时间戳

话不多说,直接上公式

= INT((A2-70*365-19)*86400-8*3600)

其实就是把公式反过来,日期时间转13位时间戳, 我就不赘述了。

学习是一个举一反三的过程,如果只需解一时燃眉之急,记住以上公式也够用。但如果想知其然且知其所以然,就得了解公式背后的原理了。

以10位时间戳(秒)转日期为例。公式:

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

A2+8*3600:当前时区的时间(秒)(A2+8*3600)/86400:将秒转换为天(A2+8*3600)/86400+70*365:加上1900年至1970年的70年(A2+8*3600)/86400+70*365+19:加上闰年多出来的天数19

Excel的日期实际上是序列值,它以1900-1-1=1为始,每过一天序列值加1;Unix时间戳是从1970-1-1 0:00:00开始到现在的秒数。

细心的人可能会发现,1900年至1970年共17个闰年,如果考虑到Excel将1900-1-1当作1,那么公式最后应该加18才对,为什么要加19呢?这是Excel的一个bug--把1900年也当作闰年了。

Tips:另有一点需注意,在Excel的【文件->工具->选项->重新计算】中,有个"使用1904日期系统"选项,如果勾选此选项,上面的公式应将70改为66,即:

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

到这里,完整的备注我就写完了。

外面青色的天空,阴沉又安静。我在等待一场夏日里的倾盆大雨,和一个新的开始。


 

上一篇:windows11如何快速打开 任务管理器

下一篇:电脑睡眠总是无缘无故被唤醒没办法睡眠的解决办法

 
百度搜索推广 |公司简介 |人才招聘 |付款方式 |联系方式
恩施市百捷在线网络科技有限公司 Copyright © 2012-2021 网址:www.esbaidu.com
电话:15587589530 传真:15587589530 联系人:邹经理
地址:恩施市火车站万福国际写字楼613 Email:zou@esbaidu.com 备案号:鄂ICP备16001060号-8
公安备案信息 鄂公网安备 42280102000263号 网络经济主体信息