1. 新行网首页
  2. 投稿

excel更改日期格式公式（excel按身份证年龄排序）

• 投稿 • 阅读 34

踩坑千千万，一切宗旨都是为了让爹们满意。每天从天而降的线上反馈，没有一点点防备，也没有一丝顾虑，它就这样出现，在我的世界里…

这日又是平凡的、虚心接受用户反馈的一天。领导熟练地从SqlServer数据库中导出一份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系统中，也在许多其他操作系统中被广泛采用。

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

excel更改日期格式公式（excel按身份证年龄排序）

大意了，看来此法不通。

excel更改日期格式公式（excel按身份证年龄排序）

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

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

excel更改日期格式公式（excel按身份证年龄排序）

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

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

接下来，让我做个小结以备忘：

113位时间戳转日期时间（毫秒）

首先

excel更改日期格式公式（excel按身份证年龄排序）

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

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

excel更改日期格式公式（excel按身份证年龄排序）

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

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

210位时间戳转日期时间（秒）

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

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

excel更改日期格式公式（excel按身份证年龄排序）

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

3日期时间转10位时间戳

话不多说，直接上公式

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

excel更改日期格式公式（excel按身份证年龄排序）

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

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

excel更改日期格式公式（excel按身份证年龄排序）

以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”)

excel更改日期格式公式（excel按身份证年龄排序）

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

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

原创文章，作者：admin，如若转载，请注明出处：https://www.qq65hfghe5.com/tg/61849.html

赞 (0)
上一篇 2024年11月19日 20:58:48
下一篇 2024年11月19日 21:03:48

相关推荐