关于Excel中时间日期的纪元时间和规则的不为人知的秘密

日期时间对于我们现实的生活意味着什么？它是我们生活中点滴美好记忆，常常回味，还是痛苦过去，不堪回首，还是我们的身体被被毒药氧气一点点的被氧化的见证者，这些似乎是答案，却有不太准确，不过我可以负责任的告诉你，它在Excel中的的确确就是数字，只是用特殊的格式呈现而已。那它和数字之间的关系是遵守什么规则呢？他们又是怎么互相转化的呢？带着这些疑问，开始我们今天的内容，不过在讲这些问题的之前，需要了解一下Excel的纪元时间是什么？它出现什么BUG却又不加以修复呢？

纪元时间

日期元年其实就是软件支持最早的日期，这个也并非微软的专利，linux系统的纪元时间就是1970年，因为受当时硬件的限制，而系统的时间戳又是以秒为单位存储的，换算下来也就够存68.1年，根据最早出现计算机的年代就规定了1970年1月1日00:00:00 为Linux的计算时间，而Excel则是以天为单位，同样的存储空间，它能存储更长时间日期，为了和当时比较火的Lotus 1-2-3 电子表格软件相互兼容，就将纪元时间定为1900年1月0日对应数字0，而在MAC版的软件中却使用1904年1月0日为纪元时间，即对应数字0。

为什么会采用两个日期系统呢？因为Lotus 1-2-3在编程的时候将1900年设定成闰年，事实上并不是，也导致现在的软件中依然存在1900年2月29日这个现实中并不存在的日期，也是因为历史的遗留问题导致1900年3月之前的返回返回星期天的有错误，而Mac版正式发布的比较晚，为了规避这个错误就采用1904年时间系统，直到2011版或2016版的Excel为了兼容window版的文件，默认也使用1900的日期系统，如果你想使用1904的时间系统，具体操作如下图：

MAC版Excel使用1904日期系统操作步骤

window系统的切换方法：【文件】->【选项】->高级选项卡下，你勾选使用1904日期系统（Y）（2016版）；

windows版Excel使用1904日期系统操作步

说完了纪元时间的事，该聊聊日期，时间和数字之间的关系了，它们又遵循着什么规则来互相转化呢？

规则

我们了解到Excel是用整数存储正常日期并以天为单位，虽然可以延长存储的日期长度，但也并不是无限的，它支持的最高日期为9999年12月31日，看着日期是不是有点眼熟啊，这不是一万年吗？它换成数字是多少天呢？结果：2958465，也就是说Excel支持0-2958465的数字转化成日期，超出了这个范围再以日期格式显示就返回##，不论怎么拉宽单元格，会一直显示#。

间的规则比日期规则稍微复杂那么一丢丢，因为时间部分都是小数表示，1时：1/24,1分：1/(24*60),1秒：1/(24*60*60)=1/86400；那么问题来了，我们如果计算时间的时候，需要精确小数点后多少位就可以？

我们根据上面的公式可以算出1秒=1/86400=1.15741E-05，公式变形一下1秒=1.157e-05*86400=1.157*0.864,从调整后的公式我们简单推理出，小数精度保留6小数就足以满足 时间秒的需求了，保留位越多就会成为计算时的负担，上面的公式就变为1秒=1.2e-05=0.000012,示意图如下：

通过秒的转化公式确认计算需要保留的精

在Excel中除了这些计算的规则外，如果想让字符串设置日期格式转化成日期还需满足这些规则：

1）数字串的连接符必须为“-”或“/”,默认显示格式为系统日期显示格式，通常为2019/7/12；

注：小数点“.”并不是合法的日期连接符，因为它会和正常带小数的数字引起混乱，这也是我们再输入日期时应该注意的地方。

2）在输入日期格式，需要注意几个规则：年份，可输入1位，2位或4位，输入1位和2位数字时会自动增加前缀2000或20形成完整的年份数字，四位数字的有效区间为1900-9999不在范围的内的都不能识别为日期格式；月份：1-12的数字，日期：根据月份和年是不是闰年规则，输入日期必须为小日期，否则也不能识别为日期格式；如果我们省略年份的输入，只输入月份和日期，则会识别为系统当前年份加输入的月份和日，比如：09/03则会识别为2019/9/3;

3)时间方面，连接符为“:”(英文),想识别为时间格式需输入遵守时间的数字规则：小时：分：秒；

时间格式对数字要求需要符合时间的规则，比如小时为0-23 分和秒则：0-59，除此之外，Excel还支持一种情况的发生，如果其中一个时或分或秒出现一样，都是可以在日期基数为1900年1月0日基础上进行计算转化成正常日期时间格式；比如输入：8:25:358,回车后：8:30:58（默认为1900年1月0日且不显示）;输入60:34:30回车转化为1900/1/2 12:34:32;如果出现2个或以上异常数字，则不能识别为日期。

日期时间自动转换效果图

聊完日期的“潜”规则后，我们最后来编写一下小数转换时间的公式吧

小数转时间公式

小数转时间有两种思路，一种截取整数法：

1.先将小数与24相乘，截取整数部分，

2.取的上一步乘积的小数部分与60相乘，截取整数部分，

3.再将上一步乘积的小数部分与60相乘，用round函数四舍五入取整得出秒数；

截取整数法的公式详解图

一种取余法：

1.可以同上，

2.与86400相乘，乘积取余3600（求除不到1小时的总秒数），然后除60，取整求除分钟数

3.与86400相乘，乘积取余60，用round函数四舍五入取整就可以求出秒数。

两种方法需要用的函数有：mod(取余函数),round（四舍五入取整函数），TRUNC(取整函数）

取余法公式详解图

这两种求解的方法中，值的注意的地方，公式的trunc函数并不能使用int函数代替，因为我们在计算时间的时候，我们有可能会得到负数，用int的取整负数会导致其绝对值变大，这样得出来的时间上就有出现错误。Excel中不是提供转化方法了吗？为什么我们还要自己制作公式呢？因为我们在日常使用中，比如计算加班时间，累计加班时长或其他涉及到时间计算的地方，我们就可以用N函数来讲时间数列转化成数字，计算完成后，在用用公式你逆转成时间就可以，即便我们没有其他的任何日期相关的函数，我们依然可以玩转的时间日期，是不是很酷呢！

今天的文章我们先写到这里的，希望能从阅读中有所收获，如果你遇到办公或电脑上的问题，可以给我留言或私信我，我看到后，会第一时间回复你！在工作和学习的路上，你并不孤单，我们可以结伴而行！