日期/时间函数
CHAPTER4
91
第四章
日期/时间函数
4.1日期/时间函数基础
4.2日期/时间应用
4.3日期与时间函数实例速查
92
CHAPTER4
日期/时间函数
在数据表的处理过程中,日期与时间的函数是相当重要的处理依据。而
Excel在这方面也提供了相当丰富的函数供大家使用。在Excel内部支持两种
日期系统,不同的日期系统下相同的日期数值串是不同的。所以在进行Excel
应用之前,必须要进行正确的日期系统设定,否则会引发数据问题。
4.1
日期
/
时间函数基础
1.取出当前系统时间/日期信息
用于取出当前系统时间/日期信息的函数主要有NOW、TODAY。
语法形式均为函数名()。
2.取得日期/时间的部分字段值
如果需要单独的年份、月份、日数或小时的数据时,可以使用HOUR、
DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。
比如,需要返回2001-5-3012:30PM的年份、月份、日数及小时数,
可以分别采用相应函数实现。
YEAR(E5)=2001
MONTH(E5)=5
DAY(E5)=30
HOUR(E5)=12
取得日期
/时间的部
分字段值
此外还有更多有用的日期/时间函数,可以查阅附表。下面我们将以一
个具体的示例来说明Excel的文本函数与日期函数的用途。
日期/时间函数
CHAPTER4
93
4.2
日期
/
时间应用
在Excel内部支持两种日期系统,不同的日期系统下相同的日期数值串
是不同的。所以在进行Excel应用之前,必须要进行正确的日期系统设定,
否则会引发数据问题。
4.2.1
日期系统
Excel支持两种日期系统,1900年日期系统和1904年日期系统。在工作
表中使用不同的日期系统将决定使用不同的日期作为参照。
A:1900年系统
规定1900年1月1日为第一天,其序列编号是1,9999年12月31日为最后
一天。
B:1904年系统
规定1904年1月1日是第一天,其序列编号是0,9999年12月31日为最后
一天。
默认情况下系统使用的是1900年日期系统,如果要转换到1904年日期
系统,具体的操作步骤如下。
1.Excel选项
打开Excel2007,点击“Office按钮”,在弹出的菜单中点击
“Excel选项”。
取得日期
/时间的部
分字段值
94
CHAPTER4
日期/时间函数
2.调整日期系统
在随即打开的“Excel选项”窗口中,点击左侧的“高级”选项,在
“计算此工作簿时”子选项中,勾选“使用1904日期系统”。最后点击
“确定”按钮即可完成日期系统的转换。
取得日期/时
间的部分字段
值
如果想在转换回1900年日期系统,只需在同一处撤销“使用1904日期
系统”复选框即可。
4.2.2
日期位数问题
在Excel中输入的日期格式如果不在其支持的范围内,系统只能把它
们知识是别成文本格式、在Excel中如果用两位数标识时间则会出现一些问
题,因为不同版本的Excel对此的处理是不同的。在Excel97以前的版本
中,两位数的年份在00到29的范围内表示2000年到2029年,而30到99则被
解释成1930年到1999年,更糟一些版本则把两位数的00到19表示为2000到
2019年,而20到99则被表示为1920年到1999年。
为了避免出现问题,最简单的办法是使用4位数来表示年份,也可以通
过改变Excel及Windows处理两位数的方式来解决。在WindowsXP中采
取的具体步骤如下:
1.区域选项
单击“开始”〉“控制面板”菜单项打开“控制面版”窗口,然后
双击“区域和语言选项”图标,打开“区域和语言选项”对话框,切换到
“区域选项”选项卡中。
日期/时间函数
CHAPTER4
95
取得日期/时
间的部分字段
值
2.自定义日期
单击“自定义”按钮,打开“自定义区域选项”对话框,切换到“日
期”选项卡中,然后将“日历”组合框中的两个年费设置为“1930”和
“2029”。单击“确定”按钮返回“区域和语言选项”对话框,然后单击
“确定”按钮即可完成设置。
取得日期/时
间的部分字段
值
96
CHAPTER4
日期/时间函数
4.3
日期与时间函数实例速查
例
1DATE(
返回代表特定日期的序列号
)
用途:返回代表特定日期的序列号。
语法:DATE(year,month,day)
参数:year为一到四位,根据使用的日期系统解释该参数。默认情
况下,ExcelforWindows使用1900日期系统,而ExcelforMacintosh使
用1904日期系统。Month代表每年中月份的数字。如果所输入的月份大于
12,将从指定年份的一月份执行加法运算。Day代表在该月份中第几天的
数字。如果day大于该月份的最大天数时,将从指定月份的第一天开始往上
累加。
注意
Excel按顺序的序列号保存日期,这样就可以对其进行计
算。如果工作簿使用的是1900日期系统,则Excel会将1900年1
月1日保存为序列号1。同理,会将1998年1月1日保存为序列号
35796,因为该日期距离1900年1月1日为35795天。
实例:如果采用1900日期系统(Excel默认),则公式“=DATE(2001,
1,1)”返回36892。
例
2DATEVALUE(
返回
date_text
所表示的日期的序列号
)
用途:返回date_text所表示的日期的序列号。该函数的主要用途是
将文字表示的日期转换成一个序列号。
语法:DATEVALUE(date_text)
参数:Date_text是用Excel日期格式表示日期的文本。在使用
1900日期系统中,date_text必须是1900年1月1日到9999年12月31日之
间的一个日期;而在1904日期系统中,date_text必须是1904年1月1日
到9999年12月31日之间的一个日期。如果date_text超出上述范围,则
函数DATEVALUE返回错误值#VALUE!。如果省略参数date_text中
的年代,则函数DATEVALUE使用电脑系统内部时钟的当前年代,且
日期/时间函数
CHAPTER4
97
date_text中的时间信息将被忽略。
实例:公式“=DATEVALUE("2001/3/5")”返回36955,
DATEVALUE("2-26")返回36948。
例
3DAY(
返回用序列号表示的某日期的天数
)
用途:返回用序列号(整数1到31)表示的某日期的天数,用整数1到
31表示。
语法:DAY(serial_number)
参数:Serial_number是要查找的天数日期,它有多种输入
方式:带引号的文本串(如"1998/01/30")、序列号(如1900日期系
统的35825表示的1998年1月30日),以及其他公式或函数的结果(如
DATEVALUE("1998/1/30"))。
实例:公式“=DAY("2001/1/27")”返回27,=DAY(35825)返回30,
=DAY(DATEVALUE("2001/1/25"))返回25。
例
4DAYS360(
返回两日期间相差的天数
)
用途:按照一年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日)或其他公式或函数的
结果(例如,DATEVALUE("1998/1/30"))。Method是一个逻辑值,它指
定了在计算中是采用欧洲方法还是美国方法。若为FALSE或忽略,则采用
美国方法(如果起始日期是一个月的31日,则等于同月的30日。如果终止日
期是一个月的31日,并且起始日期早于30日,则终止日期等于下一个月的1
日,否则,终止日期等于本月的30日)。若为TRUE则采用欧洲方法(无论是
起始日期还是终止日期为一个月的31号,都将等于本月的30号)。
实例:公式“=DAYS360("1998/2/1","2001/2-1")”返回1080。
98
CHAPTER4
日期/时间函数
例
5EDATE(
返回指定日期前后指定月份的日期序列号
)
用途:返回指定日期(start_date)之前或之后指定月份的日期序
列号。
语法:EDATE(start_date,months)
参数:Start_date参数代表开始日期,它有多种输入方式:带引号
的文本串(例如:"1998/01/30")、序列号(如35825表示1998年1月30日)或其
他公式或函数的结果(例如:DATEVALUE("1998/1/30"))。Months为在
start_date之前或之后的月份数,未来日期用正数表示,过去日期用负数
表示。
实例:公式“=EDATE("2001/3/5",2)”返回37016即2001年5月5
日,=EDATE("2001/3/5",-6)返回36774即2000年9月5日。
例
6EOMONTH(
返回指定月份中最后一天的序列号
)
用途:返回start-date之前或之后指定月份中最后一天的序列号。
语法:EOMONTH(start_date,months)
参数:Start_date参数代表开始日期,它有多种输入方式:带引号的
文本串(如"1998/01/30")、序列号(如1900日期系统中的35825)或其他公式
或函数的结果(如DATEVALUE("1998/1/30"))。Month为start_date之前
或之后的月份数,正数表示未来日期,负数表示过去日期。
实例:公式“=EOMONTH("2001/01/01",2)”返回36981即2001年3
月31日,=EOMONTH("2001/01/01",-6)返回36738即2000年7月31日。
例
7HOUR(
返回时间值的小时数
)
用途:返回时间值的小时数。即介于0(12:00A.M.)到23(11:00P.M.)
之间的一个整数。
语法:HOUR(serial_number)
参数:Serial_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。
日期/时间函数
CHAPTER4
99
例
8MINUTE(
返回指定月份中最后一天的序列号
)
用途:返回时间值中的分钟,即介于0到59之间的一个整数。
语法:MINUTE(serial_number)
参数:Serial_number是一个时间值,其中包含着要查找的分钟数。时
间有多种输入方式:带引号的文本串(如"6:45PM")、十进制数(如0.78125
表示6:45PM)或其他公式或函数的结果(如TIMEVALUE("6:45PM"))。
实例:公式“=MINUTE("15:30:00")”返回30,=MINUTE(0.06)返回
26,=MINUTE(TIMEVALUE("9:45PM"))返回45。
例
9MONTH(
返回以序列号表示的日期中的月份
)
用途:返回以序列号表示的日期中的月份,它是介于1(一月)和12(十二
月)之间的整数。
语法:MONTH(serial_number)
参数:Serial_number表示一个日期值,其中包含着要查找的
月份。日期有多种输入方式:带引号的文本串(如"1998/01/30")、
序列号(如表示1998年1月30日的35825)或其他公式或函数的结果(如
DATEVALUE("1998/1/30"))等。
实例:公式“=MONTH("2001/02/24")”返回2,=MONTH(35825)返
回1,=MONTH(DATEVALUE("2000/6/30"))返回6。
例
10NETWORKDAYS(
返回完整的工作日数值
)
用途:返回参数start-data和end-data之间完整的工作日(不包括周
末和专门指定的假期)数值。
语法:NETWORKDAYS(start_date,end_date,holidays)5
参数:Start_date代表开始日期,End_date代表终止日;
Holidays是表示不在工作日历中的一个或多个日期所构成的可
选区域,法定假日以及其他非法定假日。此数据清单可以是包含日期
的单元格区域,也可以是由代表日期的序列号所构成的数组常量。
函数中的日期有多种输入方式:带引号的文本串(如"1998/01/30")、
序列号(如使用1900日期系统的35825)或其他公式或函数的结果(如
DATEVALUE("1998/1/30"))。
100
CHAPTER4
日期/时间函数
注意
该函数只有加载“分析工具库”以后方能使用。
例
11NOW(
返回当前日期和时间所对应的序列号
)
用途:返回当前日期和时间所对应的序列号。
语法:NOW()
参数:无
实例:如果正在使用的是1900日期系统,而且计算机的内部时钟为
2001-1-2812:53,则公式“=NOW()”返回36919.54。
例
12SECOND(
返回时间值的秒数
)
用途:返回时间值的秒数(为0至59之间的一个整数)。
语法:SECOND(serial_number)
参数:Serial_number表示一个时间值,其中包含要查找的秒数。关于
时间的输入方式见上文的有关内容。
实例:公式“=SECOND("3:30:26PM")”返回26,
=SECOND(0.016)返回2。
例
13TIME(
返回某一特定时间的小数值
)
用途:返回某一特定时间的小数值,它返回的小数值从0到0.99999999
之间,代表0:00:00(12:00:00A.M)到23:59:59(11:59:59P.M)之间的时间。
语法:TIME(hour,minute,second)
参数:Hour是0到23之间的数,代表小时;Minute是0到59之间的数,
代表分;Second是0到59之间的数,代表秒。
实例:公式“=TIME(12,10,30)”返回序列号0.51,等
价于12:10:30PM。=TIME(9,30,10)返回序列号0.40,等价于
9:30:10AM。=TEXT(TIME(23,18,14),"h:mm:ssAM/PM")返回
“11:18:14PM”。
日期/时间函数
CHAPTER4
101
例
14TIMEVALUE(
返回用文本串表示的时间小数值
)
用途:返回用文本串表示的时间小数值。该小数值为从0到0.9999
99999的数值,代表从0:00:00(12:00:00AM)到23:59:59(11:59:59PM)之间
的时间。
语法:TIMEVALUE(time_text)参数:Time_text是一个用Excel时
间格式表示时间的文本串(如"6:45PM"和"18:45"等)。
实例:公式“=TIMEVALUE("3:30AM")”返回0.145833333,
=TIMEVALUE("2001/1/266:35AM")返回0.274305556。\
例
15TODAY(
返回系统当前日期的序列号
)
用途:返回系统当前日期的序列号。
参数:无语法:TODAY()
实例:公式“=TODAY()”返回2001-8-28(执行公式时的系统时间)。
例
16WEEKDAY(
返回某日期的星期数
)
用途:返回某日期的星期数。在默认情况下,它的值为1(星期天)到
7(星期六)之间的一个整数。
语法:WEEKDAY(serial_number,return_type)
参数:Serial_number是要返回日期数的日期,它有多种输入方式:带
引号的文本串(如"2001/02/26")、序列号(如35825表示1998年1月30日)或其
他公式或函数的结果(如DATEVALUE("2000/1/30"))。Return_type为确
定返回值类型的数字,数字1或省略则1至7代表星期天到数星期六,数字2
则1至7代表星期一到星期天,数字3则0至6代表星期一到星期天。
实例:公式“=WEEKDAY("2001/8/28",2)”返回2(星期二),
=WEEKDAY("2003/02/23",3)返回6(星期日)。
例
17WEEKNUM(
返回一年中的第几周
)
用途:返回一个数字,该数字代表一年中的第几周。
语法:WEEKNUM(serial_num,return_type)
参数:Serial_num代表一周中的日期。应使用DATE函数输入日期,
或者将日期作为其他公式或函数的结果输入。Return_type为一数字,确
102
CHAPTER4
日期/时间函数
定星期计算从哪一天开始。默认值为1。
例
18WORKDAY(
返回相隔指定工作日的某一日期的值
)
用途:返回某日期(起始日期)之前或之后相隔指定工作日(不包括周末
和专门指定的假日)的某一日期的值,并扣除周末或假日。
语法:WORKDAY(start_date,days,holidays)
参数:Start_date为开始日期;Days为Start_date之前或之后不
含周末及节假日的天数;Days是正值将产生未来日6期、负值产生过去日
期;Holidays为可选的数据清单,表示需要从工作日历中排除的日期值(如
法定假日或非法定假日)。此清单可以是包含日期的单元格区域,也可以是
由代表日期的序列号所构成的数组常量。日期有多种输入方式:带引号的
文本串(如"1998/01/30")、序列号(如1900日期系统时的35825表示1998年1
月30日)或其他公式或函数的结果(例如DATEVALUE("1998/1/30"))。
例
19YEAR(
返回某日期的年份
)
用途:返回某日期的年份。其结果为1900到9999之间的一个整数。
语法:YEAR(serial_number)
参数:Serial_number是一个日期值,其中包含要查找的年份。日期有
多种输入方式:带引号的文本串(例如"1998/01/30")、序列号(例如,如果
使用1900日期系统则35825表示1998年1月30日)或其他公式或函数的结果(例
如DATEVALUE("1998/1/30"))。
实例:公式“=YEAR("2000/8/6")返回2000”,
=YEAR("2003/05/01")返回2003,=YEAR(35825)返回1998。
|
|