EXCEL时间函数应用.docx

EXCEL时间函数应用

一、 Excel日期计算的基础
　　事实上，Excel处理日期的方式和我们想象的有些不同。在我们眼中，日期是含有年、月、日的有特定格式的数据。但是，Excel却在内部把日期作为以 1 开始的时间序列数存储。

　　二、 显示当前日期
　　在各种工资表、成绩表等各种类型的工作表中，常常需要显示当前日期，如果插入的日期就是工作时的日期，而且以后不需要改变，则使用组合键Ctrl＋；插入即可。 如果希望这个日期能够自动更新，则使用函数TODAY。

　　三、 使用公式快速输入日期
　　1、快速输入有规律的日期系列
　　如果需要在工作表中输入有固定间隔数(这个间隔可以是年、月、日)的日期系列时，用公式处理非常高效。
　　2、快速输入无规律的日期系列
　　上面是输入有规律的日期系列，如果要输入的日期没有规律，有没有方法提高输入效率呢？回答是肯定的。

　　四、 计算日期对应的星期数
　　有时我们希望求出日期所对应的星期数，以便分析星期对相关数据的影响。

　　五、 计算两个日期之间的天数
　　在实际工作中，需要计算两个日期之间间隔的天数的情形非常多。比如现在是2004年7月27日，2004年10月8日是50周年校庆，我们想计算出距离校庆还有多少天，该使用什么公式呢？

　　六、 计算两个日期之间工作日的天数
　　我们把每个星期得星期一到星期五称为工作日，假如项目经理2004年7月7日接到一个新项目，要求2004年10月8日完成，他需要计算一下这两个日期之间有多少个工作日，以便安排工作的进度。

　　七、 计算若干年、月、日以后的日期
　　计算若干年、月、日以后的日期在实际任务也可能常常遇到。例如，计划用1年2个月零15天来完成一本图书的编写工作，开工日期为2004年7月28日，那么什么时候能完成图书的编写呢？

　　八、 计算两个日期之间的年数
　　如果需要计算两个日期之间的年数，使用YEAR函数实现起来非常简单。

　　 九、 计算年龄
年龄应该为我们生活的完整数的年。具体地说，就是当前的日期与我们出生的日期之差得到的天数，除以365得到的数的整数部分。这里所说的年龄也就是我们常常说的实岁。利用一个名为YEARFRAC和函数和一个取整函数INT可以轻松计算年龄。

一、 Excel日期计算的基础
　　在《Excel数据录入小秘密》部分，我们曾经介绍过在工作表中输入日期的方法，这里我们再学习一下关于日期计算的基本知识，这将有助于理解Excel为何具有对日期进行计算的能力，理解这一点可以方便我们以后的使用。
　　事实上，Excel处理日期的方式和我们想象的有些不同。在我们眼中，日期是含有年、月、日的有特定格式的数据。但是，Excel却在内部把日期作为以 1 开始的时间序列数存储，其中 1 代表 1900 年 1 月 1 日。这就是 Excel 日历的起始时间，之后每增加一天，序列数也就相应加 1。例如，1900 年 1 月 2 日被存储为 2，1900 年 1 月 3日被存储为3 ，后面依此类推。
　　下面来测试一下这个规律。在工作表的A1单元格中输入Excel可以识别的日期格式，如“1900－1－1”，然后用公式填充柄拖到A10单元格，可以看到日期以依次递增一天的规律被填充到相应的单元格中。再把A1:A10区域的日期数据复制到C1:C10单元格区域中，选中C1:C10单元格区域后，选择菜单“格式→单元格”命令，弹出“单元格格式”对话框，在其“数字”选项卡的“分类”列表中选择“常规”类型(图 1)。
图 1

　　单击“确定”按钮后，返回Excel编辑窗口，可以看到先前的日期已经显示为序列数(图 2)。
图 2

　　说明：通过这个方法，可以查看能够被Excel识别的日期所对应的序列数。同时，也可以从这里明白，在 Excel公式中，不能使用 1900 年 1 月 1 日以前的日期。
　　可能有读者朋友问，Excel为何要将日期作为序列数存储呢？答案其实很简单，因为这样就可以方便地对进行日期运算。例如，要计算两个日期间的相隔天数，Excel 只需对两个日期的序列数执行相减操作即可，后面将会用实例介绍。
二、 显示当前日期
　　在各种工资表、成绩表等各种类型的工作表中，常常需要显示当前日期，如果插入的日期就是工作时的日期，而且以后不需要改变，则使用组合键Ctrl＋；插入即可。
　　如果希望这个日期能够自动更新，则使用函数TODAY，这个函数不需要任何参数，在恰当的单元格中，如图4所示的工作表中的F2单元格，输入公式“=TODAY()”即可。下面的操作设置日期显示的格式。
　　输入公式后回车，再选中该单元格，选择菜单“格式→单元格格式”命令，打开“单元格格式”对话框，可以看到Excel自动把“分类”类别设置为“日期”，我们在右边的“类型”列表框中选择一种恰当的显示格式(图 3)。
图 3

　　单击“确定”按钮后，返回Excel编辑窗口，日期按照设置的格式显示(图 4)。
图 4
三、 使用公式快速输入日期
　　1、快速输入有规律的日期系列
　　如果需要在工作表中输入有固定间隔数(这个间隔可以是年、月、日)的日期系列时，用公式处理非常高效。
　　例如，销售经理为了统计每月1日的销售情况，工作表中则需要输入月间隔为1的日期系列。
　　先在工作表的A2单元格中输入日期“2004-1-1”，然后选中A3单元格，输入公式：
　　=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2))
　　回车确认后在选中A3单元格，利用公式填充柄复制公式到恰当单元格，如A13即可(图 5)。
图 5

　　然后可以用前面介绍的方法为日期设置一种恰当的显示格式。
　　说明：上面的公式中我们用到了四个函数DATE、YEAR、MONTH和DAY。这四个函数都最是常用的日期函数。DATE函数的功能为返回代表特定日期的序列号。它的语法为DATE(year,month,day)，其中参数year为代表年份的数字，参数month为代表每年中月份的数字，参数day为代表月份中第几天的数字。公式中YEAR(A2)返回A2单元格中日期的年份，MONTH(A2)+1返回A2单元格中日期的月份再加上1，DAY(A2)返回A2单元格中日期的天数。
　2、快速输入无规律的日期系列
　　上面是输入有规律的日期系列，如果要输入的日期没有规律，有没有方法提高输入效率呢？
　　回答是肯定的。用常规方法输入Excel能识别的日期，我们必须在年、月、日之间用“－”或“/”连接，显然，这些连接符号的输入影响了我们的速度。如果需要输入日期“2004-7-1”，只用输入“20040701”就可以的话，那么速度自然可以提高不少。
　　如下图所示的工作表，先在A列对应单元格中输入日期数字，不要输入连接符号，Excel当然不认识这些数字是日期，因此我们需要使用公式进行转换，把这些数字转化成Excel可以识别的日期格式。
图 6

　　选中C2单元格，输入公式：“=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))”
　　说明：这个公式再次用到了DATE函数，其中LEFT(A2,4)的含义为取出A2单元格中数字，从左边开始取，长度为4个字符。MID(A2,5,2)的含义为取出A2单元格中数字，从第5个字符开始取，长度为2个字符。RIGHT(A2,2)的含义为取出A2单元格中数字，从右边开始取，长度为2个字符。
　　回车后在选中C2单元格，然后用公式填充柄复制公式到恰当单元格。
　　下面我们要把转换后的日期放到A2:A5单元格区域，先选中C2:C5单元格区域，然后执行复制命令，选中一个空白的列中的单元格，比如E2单元格，单击鼠标右键，在弹出的菜单中选择“选择性粘贴”命令(图 7)。
图 7

　　在弹出“选择性粘贴”对话框中，点选“粘贴”区域的“数值”单选框(图 8)。
图 8

　　单击“确定”按钮后，返回Excel编辑窗口，可以看到被复制的日期以序列数显示(图 9)，此时只要通过“单元格格式”对话框，将显示格式调整为日期即可(图 10)。
图 9

图 10

　　接下来，把A2:A5和C2:C5中的数据删除，然后将E2:E5中的日期数据剪切到A2:A5即可。
　　说明：把C2:C5中的数据复制后，也可直接选中A2单元格，执行“选择性粘贴”命令，把“数值”复制到A2:A5中，再调整A2：A5的显示格式为日期，最后删除C列数据即可，之所以采用上面的操作步骤，是为了方便介绍下面这个知识点。
　　初学的朋友要注意体会一下，为什么要在“选择性粘贴”对话框中选择“数值”选项呢？其目的是让复制后得到的数据和A列中的数据“断绝关系”。这样一来，当把A列中的数据删除后，就不会对E列中的数据有影响了，而会对C列中的数据造成影响(图 11)。
图 11
　四、 计算日期对应的星期数
　　有时我们希望求出日期所对应的星期数，以便分析星期对相关数据的影响。
　　例如，销售经理为了分析星期对销售额的影响，则可以在如下图的工作表中进行处理(图 12)。
图 12

　　那么B列中日期对应的星期数是不是手动输入的呢？如果这样做效率也太低了，让我们利用公式来快速处理吧。
　　先选中A2单元格，输入Excel可以识别的日期格式“2004-7-27”，然后使用公式填充柄向下拖拉到恰当单元格，如A11单元格，可以看到Excel自动以依次递增1天的规律生成了一个日期系列(图 13)。
图 13

　　然后，在A2:A11单元格区域处于被选中的状态下，打开“单元格格式”对话框，为日期设置一种需要的格式。
　　接着在B2单元格中输入公式：
　　=CHOOSE(WEEKDAY(A2,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日")
　　回车后，即可得到A2单元格中日期对应的星期数，然后利用公式填充柄，复制公式到B11单元格即可(图 14)。
图 14

　　说明：WEEKDAY函数的语法为：WEEKDAY(serial_number,return_type)，其中参数serial_number代表要查找的那一天的日期，参数return_type为确定返回值类型的数字。如果return_type为2，函数返回数字 1（星期一）到数字 7（星期日）。
　　CHOOSE函数的语法为：CHOOSE(index_num,value1,value2,...)，其中参数Index_num用以指明待选参数序号的参数值。Index_num 必须为 1 到 29 之间的数字、或者是包含数字 1 到 29 的公式或单元格引用。如果 index_num 为 1，函数 CHOOSE返回value1；如果为 2，函数 CHOOSE 返回 value2，以此类推。
五、 计算两个日期之间的天数
　　在实际工作中，需要计算两个日期之间间隔的天数的情形非常多。比如现在是2004年7月27日，2004年10月8日是50周年校庆，我们想计算出距离校庆还有多少天，该使用什么公式呢？
　　为了解决这个问题，可以在下图的C3单元格中输入公式：　=B3-A3
图 15

　　回车后得到计算答案“1900-4-2”(图 16)。
图 16

　　怎么得出答案是一个日期格式，而不是我们希望看到的具体数值呢？
　　这是因为，该公式中的B3和A3中的数据均为日期格式，在这种情况下，Excel自动把计算结果保持为日期格式，我们想要看到具体的数值，还需要转换一下格式。
　　选中C3单元格，选择菜单“格式→单元格格式”命令，打开“单元格格式”对话框，在“数字”选项卡的“分类”列表框中选择“常规”(图 17)。
图 17

　　单击“确定”按钮后，返回Excel编辑窗口，可以看到日期格式的结果已经转换为序列数格式。即这两个日期之间还有93天(图 18)。
图 18

　　说明：Excel是怎么算出这个答案的呢？还记得刚开始时介绍过的Excel处理日期的方式吗？由于日期2004-10-8的序列数为38268，日期2004-7-7的序列数为38175，由它们的序列数相减就得出了答案(38268－38175=93)。怎么样，明白了Excel处理日期的方式，是不是理解得更加深刻了？
六、 计算两个日期之间工作日的天数
　　我们把每个星期得星期一到星期五称为工作日，假如项目经理2004年7月7日接到一个新项目，要求2004年10月8日完成，他需要计算一下这两个日期之间有多少个工作日，以便安排工作的进度。
　　为了解决这个问题，需要用到一个名为NETWORKDAYS的函数，由于这个函数位于分析工具库中，因此需要安装分析工具库之后才能使用，因此我们先来安装一下。
　　进入工作表之后，选择菜单“工具→加载宏”命令，打开“加载宏”对话框，勾选“可用加载宏”下方列表框内的“分析工具库”复选框，单击“确定”按钮完成安装(图 19)。
图 19

　　返回Excel编辑窗口后，在如图20所示的工作表的C3单元格中输入公式：
　　=NETWORKDAYS(A3,B3)
　　回车后，可以看到得出的答案为68，即这期间还有68个工作日(图 20)。
图 20

　　说明：NETWORKDAYS函数的语法为NETWORKDAYS(start_date,end_date,holidays)。其中的start_date 位置放置计算的起点日期参数，end_date位置放置终点日期参数。如果交换这个放置顺序，得出的答案为负值。holidays位置放置除了周末外还需要排除在工作日以外的日期。
　　这里可以和上面那个计算两个日期间隔天数的计算结果比较一下，起点日期和终点日期都一样，但是计算结果却差别很大，这是因为用NETWORKDAYS函数计算的两个日期之间的差，不包含周末，即星期六和星期日。
　　NETWORKDAYS函数还可以处理更复杂的情况，假如除了星期六和星期日外，期间还有一些单位自己安排的节假日，实际的工作日数还应该把这些日期排除在外。
　　在如图21所示的工作表中，A6：A9单元格区域中存放着单位自己安排的解决日期，为了把这些日期排除在外，可以在C3单元格中输入公式：
　　=NETWORKDAYS(A3,B3,A6:A9)
图 21

　　回车后可以看到得出的答案为64(图 21)，Excel已经把这四天排除在工作日之外。
　　在实际的计算中，除了单位自己安排的节假日外，国家的法定节日如五一、十一等，Excel都没办法确认，因此要把这些日期排除在工作日外，必须在放置参数A6:A9的位置通知Excel。
七、 计算若干年、月、日以后的日期
　　计算若干年、月、日以后的日期在实际任务也可能常常遇到。例如，计划用1年2个月零15天来完成一本图书的编写工作，开工日期为2004年7月28日，那么什么时候能完成图书的编写呢？
　　在如下图22所示的工作表中，选中B8单元格，输入公式：
　　=DATE(YEAR(B2)+B4,MONTH(B2)+B5,DAY(B2)+B6)
图 22

　　回车后可以看到得出的计算结果为“2005-10-13”，也就是说，将在2005年10月13日完成图书编写工作，现在就可以提前通知好友准备庆祝活动了哦。
　八、 计算两个日期之间的年数
　　如果需要计算两个日期之间的年数，使用YEAR函数实现起来非常简单。
　　在如图23所示的工作表中，选中B5单元格，输入公式：=YEAR(B3)-YEAR(B2)
图 23

　　回车后向右拖动公式填充柄，复制公式到C5单元格。和前面一样，得到的计算结果显示为日期格式，想看到具体的数值结果，需要通过“单元格格式”对话框，将B5和C5单元格的“数字”格式设置为“常规”。
　　实际应用中，为了方便，也可以用这个公式计算人的年龄，不过它并不一定能得出正确的结果。比如图23中的B5单元格中的计算结果为1，但实际上起点日期2003年12月31日与终点日期2004年1月1日之间只有1天的差距，这就意味着，如果用这个公式计算年龄，我们就把刚出生1天的婴儿的年龄作为1岁了。
　　那么该用什么公式计算年龄呢？这便是下面要讨论的问题。
九、 计算年龄
　　为了问题的顺利解决，先来理解一下年龄的确切含义。年龄应该为我们生活的完整数的年。具体地说，就是当前的日期与我们出生的日期之差得到的天数，除以365得到的数的整数部分。这里所说的年龄也就是我们常常说的实岁。
　　那么计算年龄是不是很复杂呢？其实不然，利用一个名为YEARFRAC和函数和一个取整函数INT可以轻松解决。
　　说明：由于函数YEARFRAC也位于分析工具库中，因此使用前要先确定已经安装了分析工具库，如果没有安装则参照前面介绍的方法安装。
　　在下图(图 24)所示的工作表中选中B7单元格，输入公式：=INT(YEARFRAC(B2,B3,3))
图 24

　　回车后用公式填充柄复制公式到D7单元格，B8单元格中的公式为：
　　=YEARFRAC(B2,B3,3)
　　回车后也用公式填充柄复制公式到D8单元格。
　　从图24工作表中三组数据的比较可以看出，用计算两个日期之间年数的差的公式并不一定能得出年龄的正确结果。
　　说明：函数YEARFRAC的功能为返回 start_date 和 end_date 之间的天数占全年天数的百分比。它的语法为YEARFRAC(start_date,end_date,basis)。其中参数Start_date??代表计算的开始日期，End_date?代表终止日期，Basis为日计数基准类型，如果取3则日计数基准为实际天数/365。
　　如果不想使用YEARFRAC函数，则可以在B7单元格中输入公式：=INT((B3-B2)/365)，最后得到的计算结果与使用函数完全一样。
　　计算日期的常用公式实例就先介绍到这里，是不是感觉这种小实例学习起来比较轻松呢？而且它们都是来自于真实的实际任务，所以学会之后相信大有用武之地的，下一小节介绍计算时间的常用公式实例，也包含了很多实用的例子。

例:
A列=签订日期 B列=撤销日期
现在A列日期不同 而B列日期都是2010.4.10
现在我想根据月份给补助 不满一个月的按一个月计算。
=DATEDIF(A1,B1,"m")&"月"&DATEDIF(A1,B1,"md")&"天"
可得到具体的月数和天数
至于你所说的闰月的问题,EXCEL当然会帮你计算好了
如果你想直接得到总月数,不满一个月的按一个月计算
公式改成
=IF(DATEDIF(A1,B1,"md")=0,DATEDIF(A1,B1,"m"),DATEDIF(A1,B1,"m")+1)

例:
要求我在单元格A中输入X后，单元格B中显示2010-3-X，也就是自动加上当前月份，且单元格C中显示2010-4-X，也就是下月的这一天，我设置单元格格式不管用，自动变成了1900-1-X
(1)单元格B中的公式为：=DATE(YEAR(TODAY()),MONTH(TODAY()),A1)
单元格C中的公式为：=DATE(YEAR(TODAY()),MONTH(TODAY())+1,A1)
(2)=TEXT(YEAT(TODAY)&"-"&MONTH(TODAY)&"-"A,"YYYY-M-D")
=TEXT(YEAT(TODAY)&"-"&MONTH(TODAY)+1&"-"A,"YYYY-M-D")
(3)在b1中写入公式：
=DATE(YEAR(TODAY()),MONTH(TODAY()),A1)
在c1中写入公式：
=EDATE(B1,1)
并将c1单元格设置为日期格式。

EXCEL函数，A列=日期，B列=姓名，C列=RPN值，求A列日期为2012-1-1到2012-3-9之间，B列“曹广帅”的RPN值的和。
=SUM((日期>=2012-1-1)*(日期<=2012-3-9)*(姓名="曹广帅")*RPN值)
=SUMIFS(C:C,A:A,">=2012-1-1",B:B,"曹广帅",A:A,"<=2012-3-9")

例:有ABC三列数，A列为日期，B列为code，C列为金额。如果A列的年份早于2003年，B列不为空，那么C列的单元格就是满足条件的，要求把所有满足条件的单元格相加。
=SUMPRODUCT((--LEFT($A$2:$A$77,4)<2003)*($B$2:$B$77<>"")*($C$2:$C$77))
多条件求和：
(1).sumproduct （（条件1）*（条件2）*（条件3）……*（求和列））
(2){sum（（条件1）*（条件2）*（条件3）……*（求和列））}
(3){sum（if（（条件1）*（条件2）……，（求和列），0））}
2,3输入完按ctrl+shift+enter结束。

COUNTIF函数的16种公式设置 [复制链接]
1、返加包含值12的单元格数量

=COUNTIF（DATA,12）

2、返回包含负值的单元格数量

=COUNTIF（DATA，"<0"）

3、返回不等于0的单元格数量

=COUNTIF（DATA，"<>0"）

4、返回大于5的单元格数量

=COUNTIF（DATA，">5"）

5、返回等于单元格A1中内容的单元格数量

=COUNTIF（DATA，A1）

6、返回大于单元格A1中内容的单元格数量

=COUNTIF（DATA，“>”&A1）

7、返回包含文本内容的单元格数量

=COUNTIF（DATA，“*”）

8、返回包含三个字符内容的单元格数量

=COUNITF（DATA，“???”）

9、返回包含单词"GOOD"(不分大小写)内容的单元格数量

=COUNTIF（DATA，“GOOD”）

10、返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量

=COUNTIF（DATA，“*GOOD*”）

11、返回包含以单词"AB"(不分大小写)开头内容的单元格数量

=COUNTIF（DATA，“AB*”）

12、返回包含当前日期的单元格数量

=COUNTIF（DATA，TODAY()）

13、返回大于平均值的单元格数量

=COUNTIF（DATA，">"&AVERAGE(DATA))

14、返回平均值上面超过三个标准误差的值的单元格数量

=COUNTIF（DATA，“>"&AVERAGE(DATA)+STDEV(DATA)*3)

15、返回包含值为或-3的单元格数量

=COUNTIF（DATA，3）+COUNIF（DATA，-3）

16、返回包含值;逻辑值为TRUE的单元格数量

=COUNTIF（DATA，TRUE）