您在这里:首 页 > IT技巧
【原创】DATE函数的应用

在处理日期相关的计算时,我们经常会遇到这样的问题:未来的某一天是否存在(例如:2011229日)?50天以后是几月几号?这个月一共有多少天?

这些问题都需要我们对一个未知的日期进行判断,最直接的方法是将这个日期组合出来再进行分析,而组合出日期所需要的三个基本元素分别是“年”、“月”、“日”,DATE函数就能够将三者结合在一起,生成一个日期格式的值,供我们使用。


函数语法

DATE(年,月,日)

  “年”参数:

     “年”参数可以为一到四位数字,同时Microsoft Excel将根据不同的日期系统来解释“年”参数。在Windows系统中安装Microsoft Excel将默认使用1900日期系统。在1900日期系统中,将1900年作为日期的开始,如果“年”是01899(包含)之间的数字,Microsoft Excel会将该值加上1900以后作为日期的“年”。例如DATE111,8,30)其结果为2011830日,其年份为1900111的和;如果“年”是19009999(包含)之间的数值,则Microsoft Excel将直接使用这个数值作为“年”。例如DATE2011,8,30)其结果同样为2011830日;如果“年”小于0或者大于等于10000,则Microsoft Excel将返回错误值#NUM!。由于这个原因,导致我们无法使用DATE函数得到1900年到9999年以外的日期。

  “月”参数:

     “月”参数是一个代表月份的数字,但该数字不仅可以大于12,甚至可以为0或负数,当该数字为大于12的数字,将按照12进制的方式累加到年份上。例如:DATE2010,20,30)将返回2011830日;当该数字为负数时,则同样按照12进制的方式减少年份,并将减少的年份换算成月份与该负数相加最终得到一个112之间的月份。例如DATE2012,-4,30)将返回2011830日;如果“月”是一个112之间数字则直接将该数字作为月份生成日期。

  “日”参数:

     “日”参数是一个代表在月份中第几天的数字。当“日”大于该月的最大天数时,则会从下个月的第一天开始累加并同时累加月份。例如:DATE2011,7,61)将返回2011830日;当“日”小于0时,则会从上个月的最后一天减掉对应的天数并同时减少月份。例如:DATE2011,9,-1)将返回2011830日;当“日”为0时,则会得到上个月最后一天的日期。例如:DATE2011,9,0)将返回2011831日。

 

0115 19001904日期系统:

相对于上文阐述的1900日期系统,还有较为常见的1904日期系统,在这个日期系统中,是将1904年作为日期的开始,如果“年”是41899(包含)之间的数字,Microsoft Excel会将该值加上1900以后作为日期的“年”;如果“年”是19049999(包含)之间的数值,则Microsoft Excel将直接使用这个数值作为“年”;如果“年”小于4或者大于等于10000,或者位于19001903(含)之间,则Microsoft Excel将返回错误值#NUM!。

这样做是为了回避1900年的闰年错误,在1900日期系统的Microsoft Excel1900年为闰年,实际上1900年应该为平年。

 

应用实例

  计算食品的过期时间

  如下图所示,B列和C列分别为采购日期和保质天数,通过这两列数据,在D列中计算该食品的过期时间。

公式:

D2 =DATE(YEAR(B2),MONTH(B2),DAY(B2)+C2)

公式说明:

YEAR(B2):提取B2单元格内日期中的“年”作为过期日期的“年”。

MONTH(B2):提取B2单元格内日期中的“月”作为过期日期的“月”。

DAY(B2)+C2:提取B2单元格内日期中的“日”,并将这个日期加上保质天数作为过期日期的“日”。

DATE(YEAR(B2),MONTH(B2),DAY(B2)+C2):使用DATE函数将上述过期日期中的“年”,“月”,“日”组合为一个日期,根据DATE函数中“日”参数的累加特性,最终得到正确日期。

 

  判断年份是否为闰年

  如下图所示,A列列出了一些年份,通过使用函数计算的方式在B列计算该年份是平年还是闰年。

公式:

B2 =IF(DAY(DATE(A2,2,29))=29,"闰年","平年")

公式说明:

DATE(A2,2,29):将A列的年份作为年份,229日作为日期,如果该年份为闰年则会存在229日,否则,如果该年份为平年,则没有229日这个日期,根据函数语法中“日”参数的特性,将得到该年份的31日。

DAY(DATE(A2,2,29))=29:用DAY函数取出DATE函数所生成日期中的“日”,如果为闰年,则会得到29日;否则由于日期将被转换为31日,将会得到1日。再用这个值与29进行比较,最重闰年将返回结果TRUE,否则将为FALSE

 

  计算某日期所在的月份共有多少天

  如下图所示,A列列出了一些日期,在B列计算该日期所在月份共有多少天。

公式:

B2=DAY(DATE(YEAR(A2),MONTH(A2)+1,0))

公式说明:

YEAR(A2):提取A2单元格内日期中的“年”作为新日期的“年”。

MONTH(A2)+1:提取A2单元格内日期中的“月”,并加1作为过期日期的“月”。

DATE(YEAR(A2),MONTH(A2)+1,0):利用原日期中的年份、原日期中月份与1的和、0分别作为新日期的“年”、“月”、“日”生成日期,本例中2011830日将变成201190日。在这个新日期中,由于“日”为零,根据“日”参数的特性,将会得到上个月最后一天的日期,即2011831日。

最后使用DAY函数计算这个新日期的“日”,由于这个“日”是本月的最后一天,所以结果也就是本月共包含的天数。

扩展应用

  制作食品保质期到期提醒表

在上文“应用实例”中,我们通过食品的采购日期及保质天数得到了食品的过期时间,当食品种类较多时,如何直观的列出即将到期的食品?本例我们将使用条件格式结合DATE函数制作保质期到期提醒表,目的是把未来三天将要到期的食品名称标记为红色底纹,并随着日期的变化而变化。

操作步骤:

自上而下选取A2A5单元格区域;点击“格式”菜单中的“条件格式”命令,此时将弹出“条件格式”对话框。

如图所示,在“条件格式”对话框中,将“条件1”设置为“公式”;在“公式栏”输入公式:=AND(TODAY()

点击“格式”按钮,在弹出的“单元格格式”对话框内点选“图案”选项卡并在“单元格底纹”中选择红色后点击“确定”按钮。

点击“条件格式”对话框右下角“确定”按钮,完成设置

公式说明:

DATE(YEAR(B2),MONTH(B2),DAY(B2)+C2):此处公式由DATE函数得到过期日期,解释详见上文“计算食品的过期时间”章节。

TODAY():此处公式使用了一次比较运算符(<),用来确定今天的日期是否在食品过期日期之前,如果今天的日期比食品过期日期小(即还未过期),则得到TRUE,否则为FALSE

DATE(YEAR(B2),MONTH(B2),DAY(B2)+C2)-TODAY()<=3:此处比较食品过期日期减掉今天的日期是否小于等于3天,如果小于等于三天,则得到TRUE,否则为FALSE

最后使用AND函数将上述两次比较连接起来,当且仅当两次比较结果同为TRUE的时候,最后的比较结果才是TRUE。如果结果为TRUE,说明过期日期比今天大,即还未过期,同时过期日期比今天大的天数不多于3天。而这样的食品,是我们希望标记出的食品。

0115 条件格式

当我们在设置条件格式时,如果在“条件格式”对话框内选择了公式”并在“公式栏”中输入公式,此时当该公式结果为TRUE是,目标单元格将被设置为指定的格式;否则格式不会改变。

本例以“食品过期提醒”问题创建了条件格式,这种做法可以解决诸如员工生日提醒、设备点检日期提醒等问题,甚至与未来将要学习的时间函数相结合,还可以制作“开心农场”偷菜时间的定时提醒。

 

常组合的函数

由于DATE是一个生成日期的函数,只需要我们提供“年”、“月”、“日”三个参数就能够组合出一个日期格式的数字,所以常被我们用来生成一个不确定的日期或将不符合日期格式的“假日期”转换成日期格式,因此常与以下函数组合使用:

  YEARMONTHDAY函数结合生成新日期

  计算自今天开始100天以后的日期

公式:DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+100)

  计算自今天开始3个月以后的日期

公式:DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))

  计算自今天开始5年以后的日期

公式:DATE(YEAR(TODAY())+5,MONTH(TODAY()),DAY(TODAY()))

 

  与文本函数结合将“假日期”转换为日期格式的数字

  提取18位身份证中的生日信息

公式:DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))

 

上一篇 【原创】Excel中公式调试的利器:F9 下一篇 【黎明原创】NLB群集的操作模式

网站地图 | 联系我们 | 隐私条款 | 人才招聘 |

地址:深圳市南山区侨香路4060号香年广场A座302
粤ICP备12056245 2011© 深圳黎明网络教育中心版权所有 邮政编码:518057 Tel:(0755)26550000 Email:lmedu@liming.com