常用EXCEL函数详解及应用实例(分类汇总版)——日期与时间函数

页码
1 1 2 2 3 3 4 4 4 5 5 6 6 6 7 7 8 8 9 9
整理日期：2013年6月
Zylkfxy
常用Excel函数详解及应用实例
A 1 B C D E F G H I
一、日期与时间函数
1.DATE 返回特定日期的序列号 2 3 函数定义： 合并年、月、日三个数为完整的日期格式,从指定的年、月、日来计算日期序列号值. 4 使用格式： DATE(year,month,day) 5 格式简义： DATE(年,月,日) 6 参数定义： year 参数 year 可以为一到四位.Excel 将根据所使用的日期系统解释 year 参数 7 Excel支持1900年和1904年两种日期系统,这两种日期系统使用了不同的日期作 8 为参照基础,00年日期系统规定1900年的1月1日为第一天,其存储的日期系列编 9 号为1,最后天是9999年12月31日.04日期系统规定1904年1月1日为第一天,基存 10 储的日期系列为0,最后一天同上.系统默认为1900日期系统. 以整数形式指定日期的"月"部分的数值,或者指定单元格引用.若指定数大于12,则被视 11 month 为下一年的1月之后的数值.如果指定的数值小于0,则被视为指定了前一个月份. 12 13 day 以整数的形式指定日期的"日"部分的数值,或者指定单元格引用.如果指定数大 14 于月份的最后一天,则被视为下一月份的1日之后的数值.如果指定的数值小于0, 15 则被视为指定了前一个月份. 16 注意事项： 此函数也可以将公式指定为参数.当参数中指定了数值范围外的值时,返回错误值"#VALUE!". 17 因此,使用函数时要注意确认参数是否正确. 18 19 例1 求日期时间相加(date,year,month,day,time,minute,second) 20 基数日期时间： 2013-5-24 16:58:28 21 年 月 日 时 分 秒 22 5 4 6 12 48 58 2013-5-24 4:58 2013-5-24 17:46 2013-5-24 16:59 23 2018-5-24 2013-9-24 2013-5-30 24 增加年： =DATE(YEAR(D20)+B22,MONTH(D20),DAY(D20)) 25 增加月： =DATE(YEAR(D20),MONTH(D20)+C22,DAY(D20)) 26 增加日： =DATE(YEAR(D20),MONTH(D20),DAY(D20)+D22) 27 增加时： =DATE(YEAR(D20),MONTH(D20),DAY(D20))+TIME(HOUR(D20)+E22,MINUTE(D20),SECOND(D20)) 28 增加分： =DATE(YEAR(D20),MONTH(D20),DAY(D20))+TIME(HOUR(D20),MINUTE(D20)+F22,SECOND(D20)) 29 增加秒： =DATE(YEAR(D20),MONTH(D20),DAY(D20))+TIME(HOUR(D20),MINUTE(D20),SECOND(D20)+G22) 30 31 例2 有关日期时间的判断(day,eomonth,today) 32 31 =DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))(计算本月总天数) 33 31 =DAY(EOMONTH(TODAY(),0))(计算本月总天数) 34 29 =DAY(EOMONTH(TODAY(),0))-DAY(TODAY())(计算本月还剩几天） 35 31 =DAY(EOMONTH(TODAY(),1))(计算下个月总天数） 36 3 =INT((MONTH(TODAY())+2)/3)(计算本月属第几季度) 37 3 =INT(MONTH(TODAY())/3.1+1)(计算本月属第几季度) 38 3 =MONTH(MONTH(TODAY())&0)(计算本月属第几季度) 39 3 =ROUNDUP(MONTH(TODAY())/3,)(计算本月属第几季度) 40 3 =CEILING(MONTH(TODAY())/3,1)(计算本月属第几季度) 41 3 =LEN(POWER(2,MONTH(TODAY())))(计算本月属第几季度) 42 工作日 =IF(WEEKDAY(TODAY(),2)>5,"双休日","工作日")(计算当天是休息/工作日) 43 27 =WEEKNUM(TODAY(),2)(计算当天是本年的第几周) 44 183 =TODAY()-DATE(YEAR(TODAY()),1,0)(计算当年已经过的天数) 45 2013-9-10 =WORKDAY(TODAY(),50)(第50个工作日后日期) 46 47 例3 从身份证中提取出生日期、性别及计算退休日期 48 身份证号码 342626195810260017 49 1958-10-26 =TEXT(MID(C48,7,8),"#-00-00")(提取出生日期) 50 男 =IF(MOD(MID(C48,17,1),2)=1,"男","女")(判断性别) 51 2018-10-26 =DATE(YEAR(B49)+IF(B50="男",60,55),MONTH(B49),DAY(B49))（退休日期） 52 2018-10-26 =DATE(YEAR(TEXT(MID(C48,7,8),"#-00-00"))+IF(IF(MOD(MID(C48,17,1),2)=1,"男","女")="男", 53 60,50),MONTH(TEXT(MID(C48,7,8),"#-00-00")),DAY(TEXT(MID(C48,7,8),"#-00-00")))(综合公式)
3.DATEVALUE 日期换成数字 92 93 函数定义： 从表示日期的文本中计算出表示日期的数值(序列号值). 94 使用格式： DATEVALUE(date_text) 95 格式简义： DATEVALUE(要计算的单元格) 96 参数定义： date_text 以文本的形式指定的日期. 97 要点： 此函数引用的单元格内的日期必须是文本格式,即在先写"'"(单引号),然后输入日期 98 注意事项： 当参数中引用了日期形式的文本内容时,返回错误值"#VALUE!" 99 100 例6 函数的几种写法 101 公式结果 公式显示 102 2011-8-22 =DATEVALUE("2011-8-22") 103 2011-8-22 =DATEVALUE("22-AUG-11") 104 2011-2-23 =DATEVALUE("2011-2-23") 105 2013-7-5 =DATEVALUE("5-JUL") 106 107 例7 计算员工进入公司的天数 调查日： 2011-11-2 注：这个日期是文本型的 108 工作时间(天) 姓名 进入公司日期 109 张三 2010-3-2 610 =DATEVALUE(E107)-DATEVALUE(C109)(工作时间) 110 注:将调查日期与进入公司日期的日期转换成数值,再减, 李四 2011-6-5 150 111 差数即为其在公司工作的总天数. 王五 2009-10-6 757
函数定义
通过年、月或日返回日期 计算期间内的天数、月数或年数 将以文字表示的日期转换成系列数 从日期中返回"日" 返回数月前或数月后的日期 返回数月前或数月后的月末 将序列号转换为小时 将序列号转换为分钟 从日期中提取出"月" 返回日期之间的全部工作日(除周六、周日和休息日之外的工作天数) 返回计算机系统的当前日期和时间 返回时间值的秒数(为0至59之间的一个整数) 把分散的日期合并换成AM或PM形式的时间表示方式 返回由文本字符串所代表的时间的小数值 返回系统当前日期的序列号 返回某日期对应的星期数 返回一个数字，该数字代表一年中的第几周 计算给定日期之前或之后的除节假日和双休日之外的日期 返回某日期的年份 返回start_date和end_date之间的天数占全年天数的百分比
常用Excwk.baidu.coml函数详解及应用实例
A 58 59 60 61 62 63 64 65 66 67 68 69 70 例4 姓名 入职日期 到现在工作年数 到现在总月数 到现在总天数 年内相差月数 月内相差天数 71 1971-1-11 42 509 15513 5 张三 22 72 1976-2-17 37 448 13650 4 李四 16 73 1993-9-7 19 237 7238 9 王五 26 74 2007-10-5 5 68 2097 8 赵六 28 75 =DATEDIF(C71,TODAY(),"y")(到当前工作的整年数) 76 =DATEDIF(C71,TODAY(),"m")(到当前工作的总月数) 77 =DATEDIF(C71,TODAY(),"d")(到当前工作的总天数) 78 =DATEDIF(C71,TODAY(),"ym")(月份相差数) 79 =DATEDIF(C71,TODAY(),"md")(天数相差数) =DATEDIF(C71,TODAY(),"y")&"年"&DATEDIF(C71,TODAY(),"ym")&"月"&DATEDIF(C71,TODAY(),"md")&"天"(综合) 80 81 =TEXT(SUM(DATEDIF(C71,TODAY(),{"y","ym","md"})*{10000,100,1}),"#年##月##日")(综合) 82 83 例5 计算工龄工资（工龄足5年的每月加100，足10年的每月加200，足20年的每月加300，20年以上的每月加500） 84 姓名 入职日期 工龄 工龄工资 绩效工资 实发工资 85 2000-1-20 张三 13 2600 1800 4400 86 2003-2-20 王五 10 2000 1800 3800 87 1991-9-10 洋洋 21 6300 1800 8100 88 2005-4-16 李小军 8 800 1800 2600 89 2012-12-1 李阳 0 0 1800 1800 90 =IF(DATEDIF(C85,TODAY(),"y")<5,0,IF(DATEDIF(C85,TODAY(),"y")<10,DATEDIF(C85,TODAY(),"y")*100, 91 IF(DATEDIF(C85,TODAY(),"y")<20,DATEDIF(C85,TODAY(),"y")*200,DATEDIF(C85,TODAY(),"y")*300)))(工龄工资) C D E F G H I DATEDIF(start_date,end_date,"yd")；DATEDIF(date1,date2,"md") 格式简义： DATEDIF(开始日期,结束日期,要计算的单位) 参数定义： start_date 指定表示日期的数值(序列号值)或单元格引用."start_date"的月份被视为"0"进行计算 指定序列号值或单元格引用. end_date y、m、 y:计算满年数,返回值为0以上的整数;m:计算满月数,返回值为0以上的整数; d、ym、 d:计算满日数,返回值为0以上的整数;ym:计算不满一年的月数,返回值为1~11之 yd、md 间的整数;yd计算不满一年的天数,返回值为0~365之间的整数;md:计算不满一个 月的天数,返回值为0~30之间的整数. 要点： 不能从"插入函数"对话框中输入（隐藏函数）.在使用时必须直接键盘输入单元格中. 注意事项： 当start_date或end_date中指定的值无法识别为日期时返回错误值"#VALUE!".当返回值为 负数时,或者y、m、d、ym、yd、md参数没有用双引号括住时,返回错误值"#NAME!". B
54 55 56 57
一、日期与时间函数
2.DATEDIF 计算期间内的年数、月数、天数
函数定义： 以指定的"单位"进行天数计算,通过更改"单位",可以进行6种类型天数的计算. 使用格式： DATEDIF(start_date,end_date,"y")；DATEDIF(start_date,end_date,"m") DATEDIF(start_date,end_date,"d")；DATEDIF(start_date,end_date,"ym")； 第 2 页，共 10 页
常用Excel函数详解及应用实例
一、日期与时间函数
序号
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
函数
Date Datedif Datevalue Day Edate Eomonth Hour Minute Month Networkdays Now Second Time Timevalue Today Weekday Weeknum Workday Year Yearfrac