Excel制表技巧时间日期.docx

Excel制表技巧（40）日期时间 Excel中日期与时间的快速处理1、任意日期与时间的输入数字键与“/”或“-”配合可快速输入日期，而数字键与“：”配合可输入时间：如输入“3/25”，然后回车即可得到“3月25日”。又如输入“9:25”，回车即得到“09:25”。 2、当前日期与时间的快速输入选定要插入的单元格，按下“Ctrl”键与分号键“；”，然后回车即可插入当前日期。而要输入当前时间，同时按住“Ctrl”键、“Shift”键与 分号键，然后回车即可。3、日期与时间格式的快速设置如果对日期或时间的格式不满意，可以右击该单元格，选定“设置单元格格式数字日期”或“时间”，然后在类型框中选择即可。一、 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 在内部把每 24 小时的时间周期作为一个从 0 至 1的小数存储。二、 使用公式快速输入时间 如果需要在工作表中输入很多没有规律的时间系列，假如只用输入A列的数字，然后用Excel公式自动转换成需要得到的C列的时间格式，就可以大大提高输入的效率。 三、 计算出工作的小时数 在一些类似于考勤表的工作表中，常常需要计算工作的小时数，这也就是前面所提到的，两个时间间隔长度的问题。我们在下面的工作表中进行操作。四、 计算出考试的分钟数 上面一个实例，计算两个时间的差值，我们希望计算结果的单位为小时。那么，假如我们希望计算结果的单位为分钟，该用什么公式呢？下面用计算考试的分钟数来说明。 五、 对时间的小时、分钟和秒进行处理 下面这个问题是来自于一位读者的提问，大致是这样的：我希望对时间用如下规则进行处理，如果时间的秒数大于等于30秒，则分钟数加1，秒数归于0；如果时间的秒数小于30秒，则去掉秒数。 一、 Excel时间计算的基础 和日期一样，Excel处理时间的方式并不像我们看到的那样时间是由小时、分钟和秒钟组成的，有特殊格式的数据。Excel 在内部把每 24 小时的时间周期作为一个从 0 至 1的小数存储(图 1)。 图 1说明：把A列中的时间数据复制到C列，然后通过“单元格格式”对话框，将C列的格式设置为“常规”即可看到如图所示的效果。 从图1中可以看出，每 24 小时的周期都从午夜零点(0:00:00)开始，存储为0。正午（12:00:00）则存储为 0.5，因为从午夜零点至正午恰好是一个整天的一半。到下一个午夜零点时(0:00:00)，Excel把它存储为1。从上一个午夜零点开始至下一个午夜零点开始，正好经历了24小时，这就是我们所说的，Excel处理时间的一个24小时周期。 将时间作为小数存储使得 Excel 可以对时间进行运算。例如,要计算两个时间之间的间隔长度，执行结束时间对应的小数减去开始时间对应的小数的操作即可，具体应用下面会有实例介绍。 巧用分列更改日期刚进办公室，小赵就急急忙忙地跑过来说：“快帮兄弟一把！”我走到他的电脑前，看到的是一张名为“职工简况”的Excel工作表。他说：“领导让我根据表上面的出生年月，快速算出截止到2004年5月31日每个人的年龄，下午就得交差。”这有何难，我胸有成竹地坐到了电脑前，立马在“出生年月”的右面插入了一列，在E3单元格里输入公式“=DATEDIF（D3，2004-5-31，y）”，一回车，E3单元格里竟是“#VALUE！”。仔细一看，原来图1表格中“出生年月”里的数据不符合要求，程序不能识别出这是日期，因此不能利用这些数据进行有关日期的操作。如何将它们改为程序能够识别的日期形式呢？一个一个地改，不是办法，好几百人呢！ 如果“出生年月”中的数据最右面都没有小数点（如68.9.26），只需用“替换”将数据中的小数点“。”替换为“-”，程序就会识别出这些数据是日期。数据最右面有一个小数点，即使用上面的方法替换，程序也不认为这些数据是日期，可恰恰数据里有好多是这样的。提取字符吧！需要的字符又多少不一；删除最右面一个小数点吧，一时还找不到这样的函数。“天无绝人之路”，几经摸索还真找到了一个好办法，虽然还是有些麻烦，但总比一个一个地改要好的多。思路是将数据分为年、月、日三部分（如59.10.5），每部分各占一列，然后再将这三部分用“-”，连接起来。显然我们应以小数点“。”作为分隔标志，即分隔符号。1. 因为数据的最右面还有一个小数点，所以应在“出生年月”列的右面插入3个空白列(请见图1)。2. 选择“出生年月”下面的所有数据，即D3:D8，单击“数据分列下一步”，在“分隔符号”中勾选“其他”，在其右面的空白处输入一个小数点“.”，去掉“分隔符号”中的其他所有选项。3. 单击下一步完成按钮。数据按要求分成了三列(请见图2)。4. 选中G3单元格，输入公式“=（IF（F30，D3&-&E3&-&F3，D3&-&E3）+0”，回车后，G3单元格里是“21575”用填充柄向下拖动至G8.经过上述操作，字符串已转成数值，只不过还不是我们需要的形式。公式中“+0”，就是为了使文本型的数字转化为数值型的数字（请见图3）。因为G列的数据是由左面单元格引用过来的，所以我们不能删除D、E、F列，要使G列的数据不受D、E、F的制约，可再做如下操作。5. 选定G3:G8，单击“复制”，选择D3单元格，再单击菜单“编辑选择性粘贴”，在打开的对话框的“粘贴”中选择“数值”，然后单击确定按钮。6. 选中D3:D8，单击菜单“格式单元格”，在打开的“单元格格式”对话框中，单击“数字”标签，在“分类”中选择“日期”，在右面的“分类”中选择上级要求的或是您喜欢的日期格式，删除不需要的E、F、G列，就大功告成了(请见图4)。按照本文开头的方法输入“DATEDIF”函数，任务就完成了。快速创建自己的行事年历习惯使用Year Plan的用户通过Excel函数功能和条件格式可以快速创建自己的行事年历。1）使用Excel函数自动判断本月最后日期是28、29、30或31方法：=IF(MONTH(DATE(年,月,日)=月,日,)说明：其中“年”“月”“日”指存放“年”“月”“日”数值的单元格，使用Month函数自动提取“月”数值，通过IF函数选择2）条件格式用于自动显示星期六、日的日期方法：=WEEKDAY(DATE(年,月,日),2)=6说明：使用函数weekday功能提取星期值举例日期计算公式（设单元格C2已输入当前日期）1) 今日=TODAY()2) 明日=TODAY()+13) 次日=C2+14) 下一个月=DATE(YEAR(C2),MONTH(C2)+1,DAY(C2)5) 本月月底=DATE(YEAR(C2),MONTH(C2)+1,1)-1=DATE(YEAR(C2),MONTH(C2)+1,0)6) 下月月底=DATE(YEAR(C2),MONTH(C2)+2,1)-1=DATE(YEAR(C2),MONTH(C2)+2,0)7) 季度末=DATE(YEAR(C2),INT(MONTH(C2)+2)/3)*3+1,1)-1=DATE(YEAR(C2),INT(MONTH(C2)+2)/3)*3+1,0)8) 本半年底=DATE(YEAR(C2),INT(MONTH(C2)+5)/6)*6+1,1)-1=DATE(YEAR(C2),INT(MONTH(C2)+5)/6)*6+1,0)9) 本年底=DATE(YEAR(C2),12,31)10) 当前日期时间=NOW()测试如下Excel显示内容单元格公式今日2006-12-15=TODAY()明日2006-12-16=TODAY()+1次日2006-12-16=C2+1下一个月2007-1-15=DATE(YEAR(C2),MONTH(C2)+1,DAY(C2)本月月底2006-12-31=DATE(YEAR(C2),MONTH(C2)+1,1)-12006-12-31=DATE(YEAR(C2),MONTH(C2)+1,0)下月月底2007-1-31=DATE(YEAR(C2),MONTH(C2)+2,1)-12007-1-31=DATE(YEAR(C2),MONTH(C2)+2,0)季度末2006-12-31=DATE(YEAR(C2),INT(MONTH(C2)+2)/3)*3+1,1)-12006-12-31=DATE(YEAR(C2),INT(MONTH(C2)+2)/3)*3+1,0)本半年底2006-12-31=DATE(YEAR(C2),INT(MONTH(C2)+5)/6)*6+1,1)-12006-12-31=DATE(YEAR(C2),INT(MONTH(C2)+5)/6)*6+1,0)本年底2006-12-31=DATE(YEAR(C2),12,31)当前日期时间2006-12-15 18:25=NOW()Excel快速输入时间值法如果您对Excel的时间显示方式不满意的话，您还可以在“单元格格式”里进行修改，Excel内置了10种时间显示方式，调一款您喜欢的吧！ 大家都知道，在Excel 中输入时间是需要带上冒号的，如果有大量的时间值等待输入的话，这个冒号可得耽误不少功夫，而且又容易输入错误。 有什么好办法可以解决这个难题呢？当然是用嵌套在Excel里的利器VBA了！以下就是所有源代码，只要您按照我的说法一步一步做下去，即使对这些代码一点也不懂，也能依葫芦画瓢，迅速实现在Excel中不必输入冒号就能输入时间值的效果！ 一、 启动Excel，打开您需要大量输入时间值的那个工作簿，然后按Alt-F11，启动VBA编辑器。 二、 选择菜单中“插入”“添加模块”命令。这时，在屏幕左上方“工程”窗口中，会出现一个“模块”的子目录，在它的右侧，显示着刚刚添加进来的模块“Module1”，您可以将它改名为您所喜欢的名字如“fasttime”。 三、 双击模块“fasttime”（如果您修改过模块名称的话），在右侧的代码编辑器中输入以下代码（或者从我的示例工作簿中复制）。 Option Explicit Sub Auto_Open() Application.OnEntry = Fast 每当工作表内容发生变化时就运行指定过程 End Sub Sub Fast() On Error GoTo EnterError 如果有错误就跳往EnterTimeError语句 If Intersect(Application.Caller, Range(fasttime) Is Nothing Then Exit Sub 如果是非fasttime区域的单元格被改变，则结束过程 End If 如果输入值小于1或者大于2400，则显示对话框用户输入值非法 If Application.Caller 2400 Then MsgBox 对不起，您的输入值非法！, vbExclamation Application.Caller.Value = Exit Sub End If 将输入值改成 HH:MM格式 Application.Caller.Value = Format(Application.Caller, 00:00) Exit Sub EnterError: Exit Sub End Sub 四、把您将要大量输入时间值的单元格区域命名为“fasttime”。命名方法为，先将需要命名的单元格区域抹