第7次课 日期和时间

EXCEL处理日期和时间主讲：刘彬Email:[e-mail]6.1EXCEL如何处理日期和时间6.1.1了解日期序列号EXCEL中，日期就是一个简单的数字。更准确的说：日期就是一个序列号。它记录着自1900年1月0日以来的有关天数的数字。这个序列号的1对应着1900年1月1日。这个序列号的2对应着1900年的1月2日。这个系统可使用公式来计算日期。例如,可以创建一个公式来计算两个日期间的天数。EXCEL2000和以后的版本可以支持1900年1月1日到9999年12月31日这个范围的日期(序列号=2958465)如果知道日期序列号，可直接输入序列号，只是显示格式必须日期格式才能显示。日期的输入可以直接输入编号。也可以按照EXCEL能识别的日期输入格式出入日期。输入方式：3-12-113-12-20113/12/113/12/20113-12/11mar-12-2011mar-1212-mar-20112011/3/12模拟表日期判断日期注意事项输入当前年份的日期，不需要输入年份。直接输入月份和时间。输入当前日期：CTRL+分号输入当前时间：CTRL+SHIFT+分号查找日期的时候，可以输入6-18这种方式进行查找。理解时间序列号当需要处理时间值时，只要将EXCEL的日期序列号系统扩展到包含小数。EXCEL中用分数来处理时间。例如中午就相当于0.5.输入日期和时间,注意中间要使用一个空格。当输入的时间超过了24小时，则这个时间的相应的日期同时产生增量。例如输入25:00:00则被解释成1900年1月1日1：00AM。如果输入的时间没有日期，则自动将1900年1月0日作为日期。有关日期的问题Excel设计初很多是模仿LOTUS1-2-3.因此它将很多BUG也一起移植了过来。虽然现在发现了一些问题，但是考虑到兼容性及数据一致性等原因，最终还是保留着这些BUG。1、有关闰年的缺陷每隔4年为一个闰年，这一年会增加一天(2月29日)。虽然1900年不是闰年，但是EXCEL会把它当做是闰年。例如键入2/29/1900系统会把它解释为一个有效日期,并指定一个序列号29.2、1900年以前的日期由于EXCEL从1900年开始计时，在之前的日期，要处理的唯一办法就是把日期作为文本输入单元格。例如JULY,417763、不一致日期的输入在输入两位数作为年号的日期时，需要注意。可以使用EXCEL的某些规则，确定使用的是哪个世纪。利用数据有效性圈释无效的日期。模拟表无效日期与日期有关的函数6.2.1显示当前日期=today()可以在日期前加上文本=”Todayis:”&text(today(),yyyy-m-d)该函数可以显示当前日期，即使你时间发生改变，重新启动EXCEL后，它的日期也自动改变适应新的日期。CTRL+分号可以在工作表中输入当前日期，但是该日期值不会改变。year()函数：计算年份。例如：=year(“2011-1-12”)结果：2011month()函数：计算月份。例如：=month(“2011-1-12”)结果：1day()函数：计算天数。例如：=day(“2011-1-12”)结果：12注意：括号内的分号必不可少。否则结果会出现错误两个日期的年份之差(忽略月份值),=year(b1)-year(a1).不能使用=year(b1-a1)显示任意日期使用date函数创建一个日期，该函数包含3个参数：年、月、日。Date函数可以接受无效的参数，其结果也相应改变。例如：=date(2004,13,2)显示结果为2005-1-2Date函数一般需要附加其它函数作为参数。例如：=date(year(today()),7,4)参考模拟数据表year、month生成系列日期我们经常需要把很多日期插入到一个工作表中。例如：在跟踪做每周销售量时，需要把很多日期插入到工作表中，每个日期都间隔7日。这些日期都被用做分析销售数据。输入日期序列最有效的方法是输入第一个日期，然后利用自动填充柄拖动，同时按下鼠标右键。放开输入按键，在快捷菜单中选择需要使用的选项。可以按年、月、天数、工作日等方式进行填充。注意：如果A1中使用的是today函数，则这个方法无效。使用公式创建日期系列的优点是改变第一个日期，其它日期也随之发生改变。例如假设A1单元格为2011-3-12日现在我们可以在A2中使用公式进行任意次数的公式复制操作。如果要产生7天的间隔：如果产生一个月的间隔：如果产生一年的间隔：=a1+7=date(year(a1),month(a1)+1,day(a1))=date(year(a1)+1,month(a1),day(a1))确定两个日期之间的天数例如银行中需要计算某个储户的利息。而利息取决于账户所开的天数。如果表单包含了开户日期和账户截止日期，就可以计算了。=a1-b1EXCEL会自动格式化这个公式的结果为日期类型，因此要注意设置单元格为日期，而不是数字值。要注意对于实际天数的计算。例如某商场促销活动，2月1日开始，2月6日结束。如果用2月6日减去2月1日就得到5天的促销时间，而事实是促销时间为6天。所以，计算时要注意加上1=a1-b1+1计算人的年龄假设有如下日期依次存放在A1到A4。当前日期2011年2月10日,要求计算年龄11999-3-521987-2-831983-2-1142002-1-7分析：当前日期的月份如果大于出生月份，则年龄应该为当前年份减去出身年份。如果当前月份等于出生月份，则要比较具体天数，如果当前天数大于出身的天数，则年龄还是为当前年份减去出身年份。否则的话，年龄则为当前年份减去出身年份并且要减一。方法1：先找出年、月、日对应值:=year(a1)、month(a1)、=day(a1)B1中输入：=IF(OR(MONTH(TODAY())MONTH(E2),AND(MONTH(TODAY())=MONTH(E2),DAY(TODAY())DAY(E2))),YEAR(TODAY())-YEAR(E2),YEAR(TODAY())-YEAR(E2)-1)条件一条件二条件成立的结果条件不成立的结果方法2:使用datedif函数关于datedif函数：该函数在EXCEL粘贴函数对话框中找不到，使用时必须手工输入。Datedif函数来自于lotus1-2-3。处于兼容考虑。EXCEL提供了这个函数。由于某种原因，EXCEL对此函数保密。EXCEL联机帮助中其实并没有提到此函数。但是我们可以使用。Datedif函数可以计算两日期之间的天数、月数和年数。该函数使用了3个参数“start_date,end_date和一个表示你感兴趣的时间单位的代码。下面显示了第三种参数中的有效代码。(必须用引号括上这个代码)单元代码返回值“y”这个阶段的整年数“m”这个阶段的整月数“d”这个阶段的整天数“md”从start_date到end_date之间的天数差.忽略日期的年和月数“ym”从start_date到end_date的月份之间的差.忽略日期的年和天数“yd”从start_date到end_date的天数之间的差.忽略日期的年数A1中的值为2009-7-8，今天为2011-3-14求以下条件的值：=datedif(a1,today(),”y”)=datedif(a1,today(),”m”)=datedif(a1,today(),”d”)=datedif(a1,today(),”md”)=datedif(a1,today(),”ym”)=datedif(a1,today(),”yd”)12061468249员工工资补贴计算还款期限自动计算应收款滞纳金要求在给定的应收日期、滞纳金率、当前日期（自动取）的基础上自动计算出应收滞纳金。解答：=(DATEDIF(应收日期,NOW(),d))*滞纳金率（每天）*应收金额利用身份证号码计算年龄和生肖如果是已知一个身份证号码(A1单元格)求年龄。可以使用如下公式取得出身年月日(假定当前单元格在B1)：=MID(A1,7,4)&年&MID(A1,11,2)&月&MID(A1,13,2)&日然后利用如下公式求年龄：=datedif(B1,today(),”y”)利用身份证号码计算年龄和生肖=MID(鼠牛虎兔龙蛇马羊猴鸡狗猪,MOD(YEAR(A3)-4,12)+1,1)分析：1、Excel中时间系统是1900-1-0开始算起，1900年是鼠年。2、MOD(1900,12)——将1900对12求余数得到4因此Year(A3)后面要-4，以便得到对应的正确的位置。确定具体年的天数1月1日为一年的第1天，12月31日为最后一天。但是此期间的那些天数如何计算呢？1）、计算从一个具体日期(假定在A1中)返回本年度还剩下的天数：=date(year(A1),12,31)-A1EXCEL会对这个单元格设置好日期格式。所以需要自己调整格式。=weekday(参数1，参数2)2)、确定星期几包含两个参数：第一个参数为具体日期，第2个参数为编号系统，注意日期一定要加双引号2008-04-17,不能是2008-04-17,否则会计算错误.参数有不同的选择.1(或省略)：数字1(星期日)到数字7(星期六)2：数字1(星期一)到数字7(星期日)。3：数字0(星期一)到数字6(星期日)。Weeknum函数Return_type一周的第一天为机制1或省略星期日12星期一1返回特定日期的周数。例如，包含1月1日的周为该年的第1周，其编号为第1周。语法：WEEKNUM(serial_number,[return_type])第一个参数为日期，第二个参数如下表所示：如果weeknum函数不可用，并返回错误值#NAME?，请安装并加载“分析工具库”加载宏。操作方法：在“工具”菜单上，单击“加载宏”。在“可用加载宏”列表中，选中“分析工具库”框，再单击“确定”。如果必要，请遵循安装程序中的指示。3）、确定是否为闰年可以写一个公式，确定在2月或3月中是否出现了2月29日的情况。如果A列中输入了一些日期=IF(MONTH(DATE(YEAR(A1),2,29))=2,TRUE,FALSE)举例若外部数据导入格式为20040320之类的格式，请问如何转换为日期格式。=date(left(a2,4),mid(a2,5,2),right(a2,2))思考：如何动态得到当前时间处理日期和时间=now()函数可以设置固定格式：=text(now(),”h:mmAM/PM”)Time函数也可以表示时间=time(小时，分钟，秒钟)=NOW()-TODAY()超过24小时的时间假如A1到A7中储存日期。B1到B7中存储工作的时间。下面要进行总时间数的计算。B8中使用公式=sum(b1:b7)显示结果为9:15。要看到超过24小时的时间，需要改变单元格的数字格式。可以把显示结果的单元格格式设置为如下数字格式：[h]:mm:ss。HOUR(serial_number)用途：返回时间值的小时数。即介于0(12:00A.M.)到23(11:00P.M.)之间的一个整数。参数：Serial_number表示一个时间值，其中包含着要返回的小时数。它有多种输入方式:带引号的文本串(如“6:45PM”)、十进制数(如0.78125表示6:45PM)。实例：公式“=HOUR(“3:30:30PM”)”返回15，=HOUR(0.5)返回12即12:00:00AM，=HOUR(29747.7)返回16。时间函数MINUTE(serial_number)用途：返回时间值中的分钟，即介于0到59之间的一个整数。参数：Serial_number是一个时间值。多种输入方式:带引号的文本串(如“6:45PM”)、十进制数(如0.78125表示6:45PM)。实例：公式“=MINUTE(“15:30:00”)”返回30，=MINUTE(0.06)返回26。参考模拟表员工考勤表函数结果小数显示结果=TIME(10,15,20)10:15AM0.427314815=