Excel时间函数.docx
《Excel时间函数.docx》由会员分享，可在线阅读，更多相关《Excel时间函数.docx（11页珍藏版）》请在冰豆网上搜索。
Excel时间函数
20个Excel时间函数
1.DATE
用途：
返回代表特定日期的序列号。
语法：
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。
2.DATEVaLUE
用途：
返回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使用电脑系统内部时钟的当前年代，且date_text中的时间信息将被忽略。
实例：
公式“=DATEVaLUE（"2001/3/5"）”返回36955，DATEVaLUE（"2-26"）返回36948。
3.DAY
用途：
返回用序列号（整数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。
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日）或其他公式或函数的结果（例如，DATEVaLUE（"1998/1/30"））。
Method是一个逻辑值，它指定了在计算中是采用欧洲方法还是美国方法。
若为FALSE或忽略，则采用美国方法（如果起始日期是一个月的31日，则等于同月的30日。
如果终止日期是一个月的31日，并且起始日期早于30日，则终止日期等于下一个月的1日，否则，终止日期等于本月的30日）。
若为TRUE则采用欧洲方法（无论是起始日期还是终止日期为一个月的31号，都将等于本月的30号）。
实例：
公式“=DAYS360（"1998/2/1"，"2001/2-1"）”返回1080。
5.EDATE
用途：
返回指定日期（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日。
6.EOMONTH
用途：
返回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日。
7.HOUR
用途：
返回时间值的小时数。
即介于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。
8.MINUTE
用途：
返回时间值中的分钟，即介于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。
9.MONTH
用途：
返回以序列号表示的日期中的月份，它是介于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。
10.NETWORKDAYS
用途：
返回参数start-data和end-data之间完整的工作日（不包括周末和专门指定的假期）数值。
语法：
NETWORKDAYS（start_date，end_date，holidays）
参数：
Start_date代表开始日期，End_date代表终止日;Holidays是表示不在工作日历中的一个或多个日期所构成的可选区域，法定假日以及其他非法定假日。
此数据清单可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。
函数中的日期有多种输入方式:
带引号的文本串（如"1998/01/30"）、序列号（如使用1900日期系统的35825）或其他公式或函数的结果（如DATEVaLUE（"1998/1/30"））。
注意:
该函数只有加载“分析工具库”以后方能使用。
11.NOW
用途：
返回当前日期和时间所对应的序列号。
语法：
NOW（）
参数：
无
实例：
如果正在使用的是1900日期系统，而且计算机的内部时钟为2001-1-2812:
53，则公式“=NOW（）”返回36919.54。
12.SECOND
用途：
返回时间值的秒数（为0至59之间的一个整数）。
语法：
SECOND（serial_number）
参数：
Serial_number表示一个时间值，其中包含要查找的秒数。
关于时间的输入方式见上文的有关内容。
实例：
公式“=SECOND（"3:
30:
26PM"）”返回26，=SECOND（0.016）返回2。
13.TIME
用途：
返回某一特定时间的小数值，它返回的小数值从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”。
14.TIMEVaLUE
用途：
返回用文本串表示的时间小数值。
该小数值为从0到0.999999999的数值，代表从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。
15.TODAY
用途：
返回系统当前日期的序列号。
（从序列号转为日期=TEXT（A1,”yyyy-mm-dd”））
参数：
无
语法：
TODAY（）
实例：
公式“=TODAY（）”返回2001-8-28（执行公式时的系统时间）。
16.WEEKDAY
用途：
返回某日期的星期数。
在默认情况下，它的值为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（星期日）。
17.WEEKNUM
用途：
返回一个数字，该数字代表一年中的第几周。
语法：
WEEKNUM（serial_num，return_type）
参数：
Serial_num代表一周中的日期。
应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。
Return_type为一数字，确定星期计算从哪一天开始。
默认值为1。
18.WORKDAY
用途：
返回某日期（起始日期）之前或之后相隔指定工作日（不包括周末和专门指定的假日）的某一日期的值，并扣除周末或假日。
语法：
WORKDAY（start_date，days，holidays）
参数：
Start_date为开始日期;Days为Start_date之前或之后不含周末及节假日的天数;Days是正值将产生未来日期、负值产生过去日期;Holidays为可选的数据清单，表示需要从工作日历中排除的日期值（如法定假日或非法定假日）。
此清单可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。
日期有多种输入方式:
带引号的文本串（如"1998/01/30"）、序列号（如1900日期系统时的35825表示1998年1月30日）或其他公式或函数的结果（例如DATEVaLUE（"1998/1/30"））。
19.YEAR
用途：
返回某日期的年份。
其结果为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。
20.YEARFRAC
用途：
返回start_date和end_date之间的天数占全年天数的百分比。
语法：
YEARFRAC（start_date，end_date，basis）
参数：
Start_date表示开始日期，End_date代表结束日期。
函数中的日期有多种输入方式:
带引号的文本串（如"1998/01/30"）、序列号（如35829表示1900日期系统中的1998年1月30日），或其他公式或函数的结果（例如DATEVaLUE（"1998/1/30"））。
Basis表示日计数基准类型，其中0或省略为US（NASD）30/360，1实际天数/实际天数，2实际天数/360，3实际天数/365，4欧洲30/360。
实例：
公式“=YEARFRAC（"2001/01/31"，"2001/06/30"，0）”返回0.416666667，YEARFRAC（"2001/01/25"，"2001/09/27"）返回0.67222
Excel函数应用教程：
日期与时间函数
日期与时间函数
在数据表的处理过程中，日期与时间的函数是相当重要的处理依据。
而Excel在这方面也提供了相当丰富的函数供大家使用。
（一）取出当前系统时间/日期信息
用于取出当前系统时间/日期信息的函数主要有NOW、TODAY。
语法形式均为函数名（）。
（二）取得日期/时间的部分字段值
如果需要单独的年份、月份、日数或小时的数据时，可以使用HOUR、DAY、MONTH、YEAR函数直接从日期/时间中取出需要的数据。
具体示例参看图5。
比如，需要返回2001-5-3012:
30PM的年份、月份、日数及小时数，可以分别采用相应函数实现。
YEAR（E5）=2001
MONTH（E5）=5
DAY（E5）=30
HOUR（E5）=12
图5
此外还有更多有用的日期/时间函数，可以查阅附表。
下面我们将以一个具体的示例来说明Excel的文本函数与日期函数的用途。
三、示例：
做一个美观简洁的人事资料分析表
1、示例说明
在如图6所示的某公司人事资料表中，除了编号、员工姓名、身份证号码以及参加工作时间为手工添入外，其余各项均为用函数计算所得。
图6
在此例中我们将详细说明如何通过函数求出：
（1）自动从身份证号码中提取出生年月、性别信息。
（2）自动从参加工作时间中提取工龄信息。
2、身份证号码相关知识
在了解如何实现自动从身份证号码中提取出生年月、性别信息之前，首先需要了解身份证号码所代表的含义。
我们知道，当今的身份证号码有15/18位之分。
早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了18位。
这两种身份证号码将在相当长的一段时期内共存。
两种身份证号码的含义如下：
（1）15位的身份证号码：
1~6位为地区代码，7~8位为出生年份（2位），9~10位为出生月份，11~12位为出生日期，第13~15位为顺序号，并能够判断性别，奇数为男，偶数为女。
（2）18位的身份证号码：
1~6位为地区代码，7~10位为出生年份（4位），11~12位为出生月份，13~14位为出生日期，第15~17位为顺序号，并能够判断性别，奇数为男，偶数为女。
18位为效验位。
3、应用函数
在此例中为了实现数据的自动提取，应用了如下几个Excel函数。
（1）IF函数：
根据逻辑表达式测试的结果，返回相应的值。
IF函数允许嵌套。
语法形式为：
IF（logical_test,value_if_true,value_if_false）
（2）CONCATENATE：
将若干个文字项合并至一个文字项中。
语法形式为：
CONCATENATE（text1,text2……）
（3）MID：
从文本字符串中指定的起始位置起，返回指定长度的字符。
语法形式为：
MID（text,start_num,num_chars）
（4）TODAY：
返回计算机系统内部的当前日期。
语法形式为：
TODAY（）
（5）DATEDIF：
计算两个日期之间的天数、月数或年数。
语法形式为：
DATEDIF（start_date,end_date,unit）
（6）VALUE：
将代表数字的文字串转换成数字。
语法形式为：
VALUE（text）
（7）RIGHT：
根据所指定的字符数返回文本串中最后一个或多个字符。
语法形式为：
RIGHT（text,num_chars）
（8）INT：
返回实数舍入后的整数值。
语法形式为：
INT（number）
4、公式写法及解释（以员工Andy为例说明）
说明：
为避免公式中过多的嵌套，这里的身份证号码限定为15位的。
如果您看懂了公式的话，可以进行简单的修改即可适用于18位的身份证号码，甚至可适用于15、18两者并存的情况。
（1）根据身份证号码求性别
=IF（VALUE（RIGHT（E4,3））/2=INT（VALUE（RIGHT（E4,3））/2）,"女","男"）
公式解释：
a.RIGHT（E4,3）用于求出身份证号码中代表性别的数字，实际求得的为代表数字的字符串
b.VALUE（RIGHT（E4,3）用于将上一步所得的代表数字的字符串转换为数字
c.VALUE（RIGHT（E4,3））/2=INT（VALUE（RIGHT（E4,3））/2用于判断这个身份证号码是奇数还是偶数，当然你也可以用Mod函数来做出判断。
d.=IF（VALUE（RIGHT（E4,3））/2=INT（VALUE（RIGHT（E4,3））/2）,"女","男"）及如果上述公式判断出这个号码是偶数时，显示"女"，否则，这个号码是奇数的话，则返回"男"。
（2）根据身份证号码求出生日期
=CONCATENATE（"19",MID（E4,7,2）,"/",MID（E4,9,2）,"/",MID（E4,11,2））
公式解释：
a.MID（E4,7,2）为在身份证号码中获取表示年份的数字的字符串
b.MID（E4,9,2）为在身份证号码中获取表示月份的数字的字符串
c.MID（E4,11,2）为在身份证号码中获取表示日期的数字的字符串
d.CONCATENATE（"19",MID（E4,7,2）,"/",MID（E4,9,2）,"/",MID（E4,11,2））目的就是将多个字符串合并在一起显示。
（3）根据参加工作时间求年资（即工龄）
=CONCATENATE（DATEDIF（F4,TODAY（）,"y"）,"年",DATEDIF（F4,TODAY（）,"ym"）,"个月"）
公式解释：
a.TODAY（）用于求出系统当前的时间
b.DATEDIF（F4,TODAY（）,"y"）用于计算当前系统时间与参加工作时间相差的年份
c.DATEDIF（F4,TODAY（）,"ym"）用于计算当前系统时间与参加工作时间相差的月份，忽略日期中的日和年。
d.=CONCATENATE（DATEDIF（F4,TODAY（）,"y"）,"年",DATEDIF（F4,TODAY（）,"ym"）,"个月"）目的就是将多个字符串合并在一起显示。
5.其他说明
在这张人事资料表中我们还发现，创建日期：
31-05-2001时显示在同一个单元格中的。
这是如何实现的呢？
难道是手工添加的吗？
不是，实际上这个日期还是变化的，它显示的是系统当前时间。
这里是利用函数TODAY和函数TEXT一起来创建一条信息，该信息包含着当前日期并将日期以"dd-mm-yyyy"的格式表示。
具体公式写法为：
="创建日期：
"&TEXT（TODAY（）,"dd-mm-yyyy"）
至此，我们对于文本函数、日期与时间函数已经有了大致的了解，同时也设想了一些应用领域。
相信随着大家在这方面的不断研究，会有更广泛的应用。
函数名
函数说明
语法
DATE
返回代表特定日期的系列数。
DATE（year,month,day）
DATEDIF
计算两个日期之间的天数、月数或年数。
DATEDIF（start_date,end_date,unit）
DATEVALUE
函数DATEVALUE的主要功能是将以文字表示的日期转换成一个系列数。
DATEVALUE（date_text）
DAY
返回以系列数表示的某日期的天数，用整数1到31表示。
DAY（serial_number）
DAYS360
按照一年360天的算法（每个月以30天计，一年共计12个月），返回两日期间相差的天数。
DAYS360（start_date,end_date,method）
EDATE
返回指定日期（start_date）之前或之后指定月份数的日期系列数。
使用函数EDATE可以计算与发行日处于一月中同一天的到期日的日期。
EDATE（start_date,months）
EOMONTH
返回start-date之前或之后指定月份中最后一天的系列数。
用函数EOMONTH可计算特定月份中最后一天的时间系列数，用于证券的到期日等计算。
EOMONTH（start_date,months）
HOUR
返回时间值的小时数。
即一个介于0（12:
00A.M.）到23（1
升级会员 