《Excel中日期时间函数使用辨析》-工学论文，计算机论文-论文范文参考-科学狗论文网

网站首页词典首页

标题

Excel中日期时间函数使用辨析

范文

摘要：日期时间函数在Excel中广泛使用，在使用过程中，对应于一些函数的基本知识要充分了解，函数的参数，取值要注意辨析，才能保证结果正确。

关键词：Excel;函数;辨析

中图分类号：TP3 文献标识码：A

文章编号：1009-3044（2020）27-0196-02

开放科学（资源服务）标识码（OSID）：

1 Excel的日期、时间函数的基本知识

1.1 Excel日期系统

Excel提供了两种日期系统，一种是1900日期系统，系统的起始日期是1900年1月1日，应用在Windows操作系统中;另一种是1904日期系统，起始日期为1904年1月1日，使用在Ma-cintosh中，本文主要以1900日期系统为例。

1.2日期、时间与序列号

一般来说，Excel中有3种数据类型，分别为文本、数字与日期和时间类型。实际上，日期和时间是特殊的数字。从直观上，我们可以看到在默认状态下，数字与日期和时间自动右对齐，而文本自动左对齐;从本质上看，日期和时间是与数字有着一一对应关系的，即系统的起始日期1900年1月1日对应于数字“1”，从这个日期开始，以“天”为单位，24小时为1天，因而得到如下的对应关系：1天=“1”整天;1小时= 1/24=0.0416667;1分钟=1/24*60 - 1/1440=0.000694;1秒=1/24*60*60=1/86400

0.00001157;1毫秒=1/24*60*60*1000=1/86400000，Excel精确到的最小单位就是毫秒。如果把实际中不存在的1900年1月0日，记为坐标原点，1900年1月1日的序列号即为1，因而，如图1所示，我们也可以看到2020年4月8日对应的就是43929。

1.3常用日期和时间函数

（1）NOWO返回当前Windows系统设定的日期和时间，显示方式为正常的日期格式，如当前日期为4月18日，当使用NOW（）函数后，则显示为当前日期和当前时间，并且也不会实时更新，除非重新计算工作表。如果要显示当前日期对应的序列号，则需将单元格格式设置为“常规”，或使用“=text （NOWO，”0”）”，则显示函数在Excel中的常规格式，为整型数。

（2）TODAYO返回当前Windows系统设定的日期，显示方式为正常的日期格式，并且也不会实时更新，除非重新计算工作表。如果要显示当前日期对应的序列号，则需将单元格格式设置为“常规”，或使用“=text（TODAYO，”0”）”。因为TODAYO函数自动获取系统时间，因而在某些场合可以参与倒计时公式=截止日期-TODAYO。

（3） DATE（year，month，day）返回指定日期的序列号。在DATE函数中所有参数必须为数值型数据，year值为1900-9999之间，month正常值为1-12，day正常值为1-31，但date有自动更正功能，当月>12或日>31时，自动更正为下一年或下一月;同理当月<1或日<1时，自动更正为上一年或上一月。如图2所示。

（4）Time（hour，minute，second）返回指定时间的序列号，返回值为小数，值在0-0.99999999之间表示从0：00：00到23：59：59。Hour（提取小时数的时间），返回值为0-23。Minute（提取分钟数的时间），返回值为0-59。Second（提取秒数的时间），返回值为0-59。

（5）Year（提取年份的日期），返回值在1900-9999之间。注意，待提取年份的日期为标准格式日期，或通过其他日期时间函数计算得到的日期值。Month（提取月份的日期），返回值为1-12。Day（提取天数的日期），返回值为1-31。

2 Excel日期函数应用中的问题

由于在日常计算中，涉及小数位数取舍问题，因而对于日期及时间计算很容易出错，需多加注意。

2.1 使用常规方式解决周年、周岁问题

例如：某年NCRE考题中，就曾出现“年龄需要按周岁计算，满1年才计1岁”类似题目，就需要注意公式使用情况，如果仅使用公式=（今天日期一出生日期）/365，就可能出错，因为每一年不一定都是365天，即使使用了INT（）函数，F2=INT《TO-DAYO-[@出生日期]）/365），也是一个大致的数值，一般情况下比较接近。

2.2 使用隐含函数，精确计算工龄、周岁问题

DATEDIF（起始日期，截止日期，间隔类型）函数是隐含的函数，不仅没有函数帮助，而且也不出现在公式记忆式录入列表中，DATEDIF中间隔类型比较常用的有：“y”代表年差，“m”代表月差，“d”代表日差;还有3中不常用的间隔类型，忽略年月日中的一部分计算日期之差，分别为“md”“ym”“yd”。在计算年龄，尤其是周岁问题，这种要求精确数据时，使用DATEDIF就非常方便了。在计算单元格中直接输入公式= DATEDIF（[@出生日期，TODAYO，”y”）就可以啦。

2.3 停车场收费问题

某停车场根据记录数据，利用出场日期、时间与进场日期、时间的关系，计算“停放时间”列，单元格格式为时间类型的“XX时XX分”，而后再計算费用更改前后效益差计算费用更改前后的标准如下：固定的是超过15分钟的按每15分钟计费，其中小型车1.5元，中型车2.0元，大型车2.5元;更改的是以前“不足15分钟按15分钟收费”，现在是“不足15分钟的部分不收费”标准。

题目给出之后，考虑的思路就是日期和时间都是数值型数据可以直接参与运算，给出公式：J2=H2-F2+12-G2，这样就得到停放时间。

在计算现行收费标准中“不足15分钟按15分钟收费”，直接使用J2值，转化为分钟数，然后再除15，利用ROUNDUP函数向上舍人，K2=ROUNDUP（J2*24*60/15，0）*E2得到结果。

同理在拟实施标准中“不足15分钟不收费”，直接使用J2值，转化为分钟数，然后再除15，利用ROUNDDOWN函数向下舍人，得到结果。

如果，采用另一种计算方法如下：使用公式K2=ROUNDUP《HOUR（J2）*60+MINUTE（J2）/15，0）*E2，看似结果与上面计算一样

但继续浏览到下面发现问题了在第544和545行，利用该公式计算得到的结果：

而依据原来的公式K2=ROUNDUP（J2*24*60/15，O）*E2，得到结果如下：

问题出现的原因在哪里？原来我们计算停车时间虽然按照时间格式得到是**时**分，但还是存在**秒的，如果单单应用HOURO和MINUTEO函数提取了小时和分钟数，相当于舍弃了“秒”值，因而会收费减少。

日期、时间函数在Excel函数中应用非常灵活，但由于在Excel中应用非常广泛，因而对于日期、时间函数的使用必须注意辨析，防止出现各式错误答案。

参考文献：

[1]宋翔.Excel公式与函数大辞典[Ml.北京：人民邮电出版社，2010.

[2]教育部考试中心.全国计算机等级考试二级教程MS Office高级应用[M].北京：高等教育出版社，2018.

【通联编辑：代影】

作者简介：胡杰华（1972-），女，安徽绩溪人，副教授，硕士，主要研究方向为计算机技术与保密安全。

随便看

科学优质学术资源、百科知识分享平台，免费提供知识科普、生活经验分享、中外学术论文、各类范文、学术文献、教学资料、学术期刊、会议、报纸、杂志、工具书等各类资源检索、在线阅读和软件app下载服务。