第2章 日期、时间函数与公式应用
通过日期与时间函数，可以在公式中轻松地分析和处理日期值和时间值。本章介绍Excel 2007日期与时间函数的功能和实际应用。
2.1 日期系统
知识点讲解
ExceI实际是支持两种日期系统的，即1900日期系统和1904日期系统。1900日期系统将1900年1月1日作为首个日期，而1904日期系统则为1904年1月1号。
1.1900日期系统
在1900日期系统中，所支持的第一天是1900年1月1日。当输入某一日期后，ExceI会将该日期转换为表示从1900年1月1日起已逝去的天数的序列号。例如，输入“1998年7月5日”，则ExceI会将该日期转换为序列号“35981”。
默认情况下，Microsoft ExceI for Windows使用1900日期系统。1900日期系统能够在ExceI与其他适合于在MS-DOS或Microsoft Windows系统下运行的电子表格程序（如Lotus 1-2-3）之间实现更好的兼容性。
2.1904日期系统
在1904日期系统中，所支持的第一天是1904年1月1日。当输入某一日期后，ExceI会将该日期转换为表示从1904年1月1日起已逝去的天数的序列号。例如，输入“1998年7月5日”，则ExceI会将该日期转换为序列号“34519”。
默认情况下，Microsoft ExceI for the Macintosh使用1904日期系统，这是由于早期的Macintosh计算机的设计，不支持1904年1月1日之前的日期。如果切换到1900日期系统，则ExceI for the Macintosh可支持早至1900年1月1日的日期。
在Windows系统下，用户可以改变日期系统，若改为1904年日期系统后，可与Macintosh系统兼容，但Macintosh系统无法修改日期系统。
动手练
下面练习在Windows系统下改变ExceI 2007的日期系统，操作步骤如下。
1 单击“Office” 按钮，在弹出的“Office”菜单中单击“Excel选项”按钮，打开“Excel选项”对话框。
2 在左侧单击“高级”选项卡，然后在右侧的“计算此工作簿时”栏中选中“使用1904日期系统”复选框，如图2-1所示。
★ 图2-1
3 单击“确定”按钮即可。
提示
在一般情况下，最好不要改变默认的日期系统。因为当你同时使用两种不同日期系统的工作簿时，可能会遇到输出结果不一致的情况。
2.2 日期函数
在数据表的处理过程中，日期函数是相当重要的函数工具。而ExceI在这方面也提供了相当丰富的函数供用户使用。
知识点讲解
使用日期函数，用户可以在工作表中方便快速地处理大量的日期值，下面介绍几种常用的日期函数。
1.DATE函数
用途：
返回代表特定日期的序列号。
提示
如果在输入函数前，单元格格式为“日期”，则结果将为日期格式。
语法：
DATE(year,month,day)
参数：
year，为1～4位数字。ExceI根据所使用的日期系统来理解year参数。默认情况下，ExceI for Windows使用1900日期系统，而ExceI for Macintosh则使用1904日期系统。
month，代表每年中月份的数字。如果所输入的月份大于12，则进位计算（进年）。例如，DATE(2008,14,2)返回代表2009年2月2日的序列号。
day，代表在该月份中第几天的数字。如果day参数大于该月份的最大天数，则进位计算（进月）。例如，DATE(2008,1,35)返回代表2008年2月4日的序列号。
ExceI可将日期存储为可用于计算的序列号，这样用户就可以使用它进行计算。如果工作簿使用的是1900日期系统，则ExceI会将1900年1月1日保存为序列号“1”。同理，会将2006年1月1日保存为序列号“38718”，因为该日期距离1900年1月1日有38718天。
函数DATE在年、月、日为变量的公式中非常有用。
实例：
如果采用1900日期系统，则输入公式“=DATE(2008,4,25)”，返回39563，表示从1900年起至2008年4月25日，逝去的天数是39563，如图2-2所示。
★ 图2-2
注意
在本例中输入公式“=DATE(2008, 4,25)”后，单元格内返回值默认是“2008-4-25”，此时还需要将该单元格的格式重新设置为“常规”，才能显示出“39563”。
2.DATEVALUE函数
用途：
返回date_text参数所表示的日期的序列号。
函数DATEVALUE的主要功能是将以文本表示的日期转换成一个序列号。
语法：
DATEVALUE(date_text)
参数：
其中，date_text代表以ExceI日期格式表示的日期的文本。例如，“2008-1-30”或“30-Jan-08”就是带引号的文本，它用于代表日期。在使用ExceI for Windows中的默认日期系统时，date_text参数表示的必须是1900年1月1日到9999年12月31日之间的一个日期；而在使用ExceI for Macintosh中的默认日期系统时，date_text参数表示的必须是1904年1月1日到9999年12月31日之间的一个日期。如果date_text超出上述范围，则函数DATEVALUE返回错误值#VALUE！。
如果省略date_text中的年份部分，则函数DATEVALUE使用计算机系统内部时钟的当前年份。
实例：
公式“=DATEVALUE("2005/1/1")”返回38353，也就是日期格式2005/1/1的序列号表示；“=DATEVALUE("1-1")”返回38718，二者之差恰好是一年365天，如图2-3所示。
★ 图2-3
3.DAY函数
用途：
返回以序列号表示的某日期的天数，用整数1～31表示。
语法：
DAY(serial_number)
参数：
seriaI_number是要查找天数的日期，它有多种输入方式：带引号的文本串（如"1998/01/30"）、序列号（如1900日期系统的“35825”），以及其他公式或函数的结果（如DATEVALUE("1998/1/30")）。
可以使用DATE函数来输入日期，或者将日期作为其他公式或函数的结果输入。例如，可使用函数DATE(2008,5,23)输入日期2008年5月23日。如果日期以文本的形式输入，则会出现问题。
实例：
公式“=DAY("2006/1/1")”返回1，“=DAY(38718)”返回1，“=DAY(DATEVALUE("2006/1/1"))”返回1，如图2-4所示。
★ 图2-4
4.DAYS360
用途：
按照一年360天的算法（每个月30天，一年共计12个月），返回两日期间相差的天数。
语法：
DAYS360(start_date,end_
date,method)
参数：
start_date和end_date是用于计算期间天数的起止日期。如果start_date在end_date之后，则DAYS360将返回一个负数。日期可以有多种输入方式：带引号的文本串（如"1998/01/30"）、序列号（如果使用1900日期系统，则“35825”表示1998年1月30日），以及其他公式或函数的结果（如DATE-VALUE("1998/1/30")）。
method是一个逻辑值，它指定了在计算中是采用欧洲方法还是美国方法。若为FALSE或忽略，则采用美国方法（如果起始日期是一个月的31日，则等于同月的30日；如果终止日期是一个月的31日，并且起始日期早于30日，则终止日期等于下一个月的1日，否则，终止日期等于同月的30日）。若为TRUE则采用欧洲方法（无论是起始日期还是终止日期为一个月的31号，都将等于同月的30号）。
实例：
公式“=DAYS360("2000/4/25","2008/4-25")”返回2880，如图2-5所示。
★ 图2-5
5.EDATE
用途：
返回指定日期(start_date)之前或之后指定月份的日期的序列号。
语法：
EDATE(start_date,months)
参数：
start_date参数代表开始日期，它有多种输入方式：带引号的文本串（如"1998/01/30"）、序列号（如35825表示1998年1月30日），以及其他公式或函数的结果（如DATE-VALUE("1998/1/30")）。
months为在start_date之前或之后的月份数，未来日期用正数表示，过去日期用负数表示。
实例：
公式“=EDATE("2008/4/25"，2)”返回39624，即2008年6月25日，“=EDATE("2008/4/25"，-3)”返回39472，即2008年1月25日，如图2-6所示。
★ 图2-6
6.YEAR函数
用途：
返回某日期的年份，其结果为1900～9999之间的一个表示年份的整数。
语法：
YEAR(serial_number)
参数：
seriaI_number是一个日期值，其中包含要查找的年份。
实例：
公式“=YEAR("2000/5/1")返回2000”，“=YEAR("2008/6/1")”返回2008，“=YEAR(38718)”返回2006，如图2-7所示。
★ 图2-7
7.MONTH函数
用途：
返回日期中的月份。月份是介于1(一月)和12(十二月)之间的整数。
语法：
MONTH(serial_number)
参数：
seriaI_number表示一个日期值，其中包含着要查找的月份。日期有多种输入方式：带引号的文本串（如"2006/1/1"）、序列号（如表示2006年1月1日的38718），以 及 其 他 公 式 或 函 数 的 结 果（如DATEVALUE("1998/1/30")）等。
实例：
公式“=MONTH("2006/1/1")”返回1，“=MONTH(38718)”返回1，“=M ONTH(DATEVALUE("2000/6/1"))”返回6，如图2-8所示。
★ 图2-8
8.WEEKDAY函数
用途：
返回某日期的星期数。在默认情况下，它的值为1（星期天）到7（星期六）之间的一个整数。
语法：
WEEKDAY(serial_number,return_
type)
参数：
seriaI_number是要返回日期数的日期，它有多种输入方式：带引号的文本串(如"2001/02/26")、序列号(如35825表示1998年1月30日)，以及其他公式或函数的结果(如DATEVALUE("2000/1/30"))。
return_type为确定返回值类型的数字，参数值为1或省略，则返回值1～7代表星期天到星期六，参数值为2，则返回值为1～7，代表星期一到星期天，参数值为3，则返回值为0～6，代表星期一到星期天。
实例：
公式“=WEEKDAY("2006/2/24",2)”返回5(星期五)，“=WEEKDAY("2006/2/26",2)”返回7（星期天），如图2-9所示。
★ 图2-9
动手练
下面练习日期函数的应用。
1.设计倒计时天数显示牌
在日常生活中，我们可以见到各式各样的倒计时显示牌，如奥运会倒计时显示牌，在ExceI中也可以设计出来，下面就练习制作。
1 根据需要和个人喜好制作一个“奥运会倒计时显示牌”表格，如图2-10所示。
★ 图2-10
2 在B2单元格中输入公式“=DATE(2008, 8,8)-TODAY()&"天"”，按下【Enter】键，此时就会在B2单元格中显示出系统当前日期距2008年8月8日还有多少天，返回值默认的是五号、宋体、黑色的文字，显然与漂亮的显示牌不相称。
3 根据需要或自己喜好设置B2单元格的字体、字号和字体颜色等，达到要求即可，如图2-11所示。
★ 图2-11
2.计算指定日期之间的年数、月数和天数
在实际工作过程中有时需要计算指定日期之间的年数、月数和天数，这种烦琐的计算在ExceI中使用函数可以快速完成，下面进行练习，操作如下。
1 打开并定位到需要计算指定日期之间的年数、月数或天数的单元格区域，如图2-12所示。
★ 图2-12
2 在D3单元格中输入公式“=DATEDIF(A3,B3,"Y")”；在D4单元格中输入公式“=DATEDIF(A4,B4,"M")”；在D5单元格中输入公式“=DATEDIF(A5, B5,"D")”，如图2-13所示。
3 公式输入完成后按下【Enter】键，在D列对应的单元格中就会显示出公式计算出的时间差。
3.判断年龄是否已满
判断年龄是否已满是人力资源部门经常遇到的事情，如判断某人年龄是否达到退休年龄或停止从事某项工作的年龄等。使用ExceI，也可以轻松解决，操作如下。
★ 图2-13
1 打开工作表，如图2-14所示。
★ 图2-14
2 在E3单元格中输入公式“=YEAR($E$1)-YEAR(D3)”，计算出职工现在的年龄，然后使用填充柄向下填充，如图2-15所示。
★ 图2-15
3 在F3单元格中输入公式“=IF($E$1<=DATE(YEAR($E$1),MONTH(D3),DAY(D3)),"未满", "已满")”，判断年龄是否已满。然后使用填充柄填充到其他需要判断年龄是否已满的员工对应的单元格中，如图2-16所示。
4.商品过期提醒
在商品管理过程中，商品过期问题是一个很重要的问题，在ExceI中，用户可以轻松判断，下面进行练习，操作步骤如下。
★ 图2-16
1 打开存放商品时间信息的表格，如图2-17所示。
★ 图2-17
2 在F3单元格中输入公式“=IF(E3<TODAY(),"过期啦！", "未过期")”，判断商品是否已过期，然后使用填充柄填充其他需要结果的单元格，如图2-18所示。
★ 图2-18
通过上面的练习，希望读者能够学会使用日期函数。
2.3 时间函数
和日期函数一样，在数据表的处理过程中，时间函数也是相当重要的工具。ExceI提供了相当丰富的时间函数供用户使用。
知识点讲解
ExceI 2007中的时间函数有很多种，下面介绍它们的功能和使用方法。
1.TIME函数
用途：
返回某一特定时间的小数值。
函数TIME返回的小数值为0（零）～0.999999 99之间的数值，代表从0:00:00(12:00:00AM)到23:59:59(11:59:59PM)之间的时间。
语法：
TIME(hour,minute,second)
参数：
hour为0～32767之间的数值，代表小时。任何大于23的数值将除以24，其余数将视为小时。例如，TIME(27,0,0)=TIME(3,0,0)=.125或3:00AM。
minute为0～32767之间的数值，代表分钟。任何大于59的数值将被转换为小时和分钟。例如，TIME (0,750,0)=TIME(12,30,0)=.520833或12:30PM。
second为0～32767之间的数值，代表秒。任何大于59的数值将被转换为小时、分钟和秒。例如，TIME(0, 0,2000)=TIME(0,33,20)=.023148或12:33:20AM。
实例：
公式“=TIME(12,10,30)”返回0.51（数值，小数位数2），相当于12:10:30PM；“=TIME(9,30,10)”返回0.40，等价于9:30:10AM；“=TEXT(TIME(23,18,14), "h:mm:ssAM/PM")”返回“11:18:14PM”，如图2-19所示。
★ 图2-19
2.TIMEVALUE函数
用途：
返回用文本串表示的时间小数值。该小数值为0～0.99999999之间的数值，代表从0:00:00（12:00:00AM）到23:59:59（11:59:59PM）之间的时间。
语法：
TIMEVALUE(time_text)
参数：
time_text是一个用ExceI时间格式表示时间的文本串（如"6:45PM"和"18:45"等）。time_text中的日期信息将被忽略。
实例：
公式“=TIMEVALUE("3:30AM")”返回0.145833333，“=TIMEVALUE ("6:45")”返回0.281 25，如图2-20所示。
★ 图2-20
3.NOW函数
用途：
返回当前日期和时间所对应的序列号。
单元格格式为“常规”时，返回序列号。
语法：
NOW()
参数：
无参数
说明：
序列号中小数点右边的数字表示时间，左边的数字表示日期。例如，序列号.5表示时间为中午12:00。
函数NOW只有在工作表重新计算，或执行含有此函数的宏时改变，它并不会随时更新。
实例：
如果正在使用1900日期系统，且计算机的内部时钟为2006-3-27 16:57，则公式“=NOW()”返回2006-3-27 16:57（日期格式）或38803.71；如果当前系统时间是2006-4-11 12:00，则公式“=NOW()”返回38818.5（.5表示时间为中午12:00）。
4.HOUR函数
用途：
返回时间值的小时数，即介于0(12:00AM)～23(11:00PM)之间的一个整数。
语法：
HOUR(serial_number)
参数：
seriaI_number表示一个时间值，其中包含着要返回的小时数。它有多种输入方式：带引号的文本串（如"6:45PM"）、十进制数（如“0.78125”表示6:45PM），以 及 其 他 公 式 或 函 数 的 结 果（如TIMEVALUE("6:45PM")）。
实例：
公式“=HOUR("3:30:30PM")”返回15，“=HOUR(0.5)”返回12，即12:00:00AM，“=HOUR(29747.7)”返回16，如图2-21所示。
★ 图2-21
5.MINUTE函数
用途：
返回时间值中的分钟，即介于0～59之间的一个整数。
语法：
MINUTE(serial_number)
参数：
seriaI_number是一个时间值，其中包含着要返回的分钟数。时间有多种输入方式：带引号的文本串（如"6:45PM"）、十进制数（如0.78125，表示6:45PM），以 及 其 他 公 式 或 函 数 的 结 果（如TIMEVALUE("6:45PM")）。
实例：
公式“=MINUTE("15:30:00")”返回30，“=MINUTE(0.06)”返回26，“=MINUTE(TIMEVALUE("9:45"))”返回45，如图2-22所示。
★ 图2-22
6.SECOND函数
用途：
返回时间值的秒数，返回的秒数为0～59之间的整数。
语法：
SECOND(serial_number)
参数：
seriaI_number表示一个时间值，其中包含要查找的秒数。时间的输入方式与MINUTE函数相同。
实例：
公式“=SECOND("3:30:26PM")”返回26，“=SECOND(0.016)”返回2，如图2-23所示。
★ 图2-23
动手练
下面练习时间函数的应用。
1.计算租车费
某公园出租双人脚踏车，每小时20元，出租时间小于等于30分钟则按0.5小时计算费用，出租时间大于30分钟而小于等于1小时则按1小时计算费用。如图2-24所示是租车计时收费记录表，要求计算出收费金额。
★ 图2-24
计算收费金额的步骤如下。
1 计算租车“小时数”：在E4单元格中输入公式“=HOUR(D4-C4)”，按下【Enter】键后，使用填充柄填充到E5单元格，如图2-25所示。
★ 图2-25
2 计算租车“分钟数”：在F4单元格中输入公式“=MINUTE(D4-C4)”，按下【Enter】键后，使用填充柄填充到F5单元格，如图2-26所示。
★ 图2-26
3 计算租车“总时间”：在G4单元格中输入公式“=E4+IF(F4<=30,0.5,1)”，按下【Enter】键后，使用填充柄填充到G5单元格，如图2-27所示。
★ 图2-27
4 计算“收费金额”：在H4单元格中输入公式：“=G4*20”，按下【Enter】键后，使用填充柄填充到H5单元格，如图2-28所示。
★ 图2-28
2.计算话吧话费
有一家话吧，通话计费按分钟进行计算，并规定30秒以内按0.5分钟计算，大于30秒按1分钟计算。要求计算出如图2-29所示表中总话费分别是多少。
★ 图2-29
操作步骤如下。
1 计算“小时”：在E3单元格中输入公式“=HOUR(D3)-HOUR(C3)”，然后使用填充柄填充下面需要计算小时值的单元格，如图2-30所示。
★ 图2-30
2 计算“分钟”：在F3单元格中输入公式：“=MINUTE(D3)-MINUTE(C3)”，然后使用填充柄填充下面需要计算分钟值的单元格，如图2-31所示。
★ 图2-31
3 计算“秒”：在G3单元格中输入公式“=SECOND(D3)”，然后使用填充柄填充下面需要计算秒数的单元格，如图2-32所示。
★ 图2-32
4 计算“合计时间”：在H3单元格中输入公式“=E3*60+F3+IF(G3<=30,0.5,1)”，然后使用填充柄填充下面需要计算合计时间的单元格，如图2-33所示。
★ 图2-33
5 计算“总话费”：分别在I3，I4和I5单元格中输入每分钟话费，在J3单元格中输入公式“=I3*H3”，然后使用填充柄填充下面需要计算总话费的单元格，如图2-34所示。
★ 图2-34
疑难解答
问 将公式的计算结果复制到其他单元格时，显示的不是计算结果，而是公式，怎样才能将计算结果复制到其他单元格中呢？
答1 选中并复制公式结果所在的单元格。
2 右键单击需要粘贴公式结果的单元格，在弹出的快捷菜单中选择“选择性粘贴”命令，如图2-35所示。
★ 图2-35
3 在打开的“选择性粘贴”对话框的“粘贴”栏中选中“数值”单选按钮，然后单击“确定”按钮即可，如图2-36所示。
★ 图2-36
问 如果需要在移动公式时，保证公式中的单元格引用不改变，该怎么办呢？
答1 选定包含待移动或复制公式的单元格。
2 移动鼠标指针指向选定区域的边框，可看到鼠标指针变成十字箭头形状。
3 按住鼠标左键拖动单元格公式到目标单元格。
4 释放鼠标即可。
问 在Excel中，怎样保留制表当时的日期呢？
答 选中日期公式（注意不是选中公式单元格，而是选中公式本身），按下【F9】键，将公式转换成具体日期，经过这一转换，以后再打开工作簿时，日期就是当时的日期了，不会变化。