Excel的日期并不特殊

  • 发布时间:2017年4月10日 11:07
  • 作者:杨仕航
  • 分类标签: Excel
  • 阅读(6710)
  • 评论(1)

日期处理对于新手来说,简直无从下手,而且经常出错。(这里的日期包含日期和时间)

主要是不知道日期是什么类型,不知道如何处理该类型。这里对Excel的日期以及相关公式总结归纳。


1、日期的本质

在Excel中,数据类型很明显可以看出有数值、字符串、布尔值(True和False)等。

而日期看上去是一种独立的类型。实际上,日期本质还是数值。这点很重要,贯穿整个日期类型。

记住!1天就是数字1。其他时间换算单位都是根据这个计算。

1)1天有24个小时,那么1小时 = 1/24

2)1小时有60分钟,那么1分钟 = 1/24/60

3)1分钟有60秒,那么1秒 = 1/24/60/60


打开Excel,选中1个单元格。按快捷键 Ctrl + 分号键,再回车,可以得到当前日期。

接着,再右键该单元格,选择“设置单元格格式”,修改格式为常规。可以得到如下结果:

不要惊讶,正如上面所说的,日期本质是数值。日期只是数值的一种格式而已。

那么,这个42832怎么和 2017/4/7 对应?


这里可以做个简单实验。

在任意单元格输入数字1,再将其单元格格式设置为“短日期”。

正常情况可以得到日期:1900/1/1。

也可能得到1904/1/2,这个是被设置为1904日期系统。在选项设置中可以看到。

建议非特殊情况,不要设置1904的日期系统。该设置会导致一些问题。

下面均以1900/1/1为准,给大家讲解。


2、日期差计算

那么,数字2则对应1900/1/2。以1为1天的单位计算。

所以,42832对应2017/4/7。既然如此,日期与日期之间可以相减计算,得到日期差。

从上图可以看出,2017/4/10和2017/4/1相差9天。

不要问,日期和日期相加得到什么,这个没有意义。


但,日期可以加减天数,得到一个新的日期。例如计算前5天和后5天分别是几月几号。


这里需要插入一个知识点。刚刚计算使用的日期都是写在单元格中。

若我需要直接在公式中写入日期,直接写=2017/4/10 - 5 吗?

这个不会得到你想要的结果。斜线将被Excel认为是除法计算。有些电脑日期是 2017-4-10也不行,会被认为减法计算。

这里有3种方式:

1)写成日期的本质:数字,再参与计算。该方法操作麻烦,而且公式可读性差。不推荐!

2)使用字符串。例如 = "2017/4/10" - 5。因为有运算,Excel会自动将字符串转成合适的类型。推荐!

3)使用Date公式组合日期。例如 = Date(2017, 4, 10) - 5。该方法中规中矩,看情况使用。


继续日期差的话题。我们直接对两个日期相减计算,得到天数差。假如天数很多,可以进一步计算得到月份差、年份差。

例如,计算月份差,则用天数除以30再取整即可。

不能整除的,用求余函数获取剩余天数。


但这个计算精确度不高。因为我们1个月有时是30天,有时是31天。尤其是2月份最为特殊。

若你要求精确度很高的话,可以用Excel一个隐藏公式Datedif。

例如,计算月份差:

计算剩余天数:


Datedif格式是 =Datedif(小的日期, 大的日期, 计算日期差参数)

前两个参数很明显。最后个参数是固定参数,分别如下:

y   计算年份差

m   计算月份差

d   计算天数差

ym  计算年份差之后零几个月

md  计算月份差之后零几天

选择哪个参数计算哪种结果。


3、常用的日期公式

日期公式不多也常用。


1)当前日期和时间

例如,获取当前日期有两个now和today。now获取的日期包含当前时间,而today只有日期不包含时间。


2)获取日期中的数据

日期公式还有一组公式:获取日期中的数据。例如,我要获取日期中的年份:

当然还有几月、几号、几点、几分、几秒。分别是month、day、hour、minute、second。


3)月份叠加

月份叠加通常在合同或者什么期限常遇到。

在一个日期上,加上6个月、12个月等。这个需要用edate公式:

第1个参数是日期,第2个参数是要叠加的月份,该月份可为负数。

实际应用,通常还需要减去1天。


4)月份最后一天

月份最后一天有些别扭,因为很少在实际应用中单独使用。该公式名为eomonth。

举一些栗子。通过一个日期计算该日期所在月份的天数。

先用eomonth得到当前月份的最后一天日期。第1个参数为日期,第2个参数为叠加月份。这里第2个参数为0,表示获取当前月份的最后1天。

从该日期可看出,4月有30天。我们可用day公式获取天数:

该公式更多应用需要大家自己探索。至少要知道有该公式的存在,使用时才会想起它。


4、日期格式化

日期格式化是一个比较完整的知识模版。

一开始接触可能会有排斥心理,因为格式化需要记住不少参数。

死记硬背不是我的风格,有技巧帮助大家学习。


格式化有两种实现:单元格格式和text公式。这两种都是同个原理。此处我只采用text公式,方便讲解。

简单说一下text公式。text公式有两个参数 =text(需要格式化的内容, 格式化参数),例如:

将2017/4/10,格式化为2017-04-10。你可以格式为2017年4月10号,格式化参数为"yyyy年m月d号"。

这里的ymd有各种的含义。和日期相关的格式化基本参数有6个,记忆技巧来了:

y,对应year。year是年的意思;

m,对应month,月份的意思;

d,对应day,天的意思;

h,对应hour,小时的意思;

m,对应minute,分钟的意思。这个和月份一样,会冲突。一般Excel会根据整个格式化参数进行判断。

s,对应second,秒的意思。


因为年份有4个数字,所以使用时,用4个y。有时我们只需要年份后两位,使用2个y即可。

其他参数也有类似使用方式。例如月份有1位和2位。若我要统一显示两位的月份,不足两位用0补上。那么写2个m表示即可。例如:


日期格式化差不多是这些,记住这6个格式化参数即可。

我再拓展讲两种特殊情况。


1)8位日期简写的数字转成日期格式

8位日期简写的数字,例如 20170410。该数值并非是真正的日期。无法参与日期公式等计算。

可以用text公式转换:

因为不是日期,所以不能使用日期的格式化参数。直接用0占位即可。


2)从日期得知星期几

这种也是经常碰到的需求。可用text公式直接获得:

是的,今天是星期一。上周五,7号那天就开始写稿,今天才完稿。

看我写博文这么认真,给个赞呗 [坏笑脸]

上一篇:VSTO保存配置的方法和建议

下一篇:Python读写XML文档(lxml方式)

相关专题: Excel公式学习   

评论列表

效率墨鱼君

效率墨鱼君

👍🏆写得好详细 ,受益匪浅

2019-06-05 23:04 回复

新的评论

清空