Excel制表技巧时间日期.docx

1、Excel 制表技巧（ 40）日期时间 Excel 中日期与时间的快速处理 1、任意日期与时间的输入数字键与 “/”或 “-”配合可快速输入日期，而数字键与 “： ”配合可输入时间：如输入 “3/25”，然后回车即可得到 “3月 25 日 ”。又如输入 “9:25”，回车即得到 “09:25”。 2、当前日期与时间的快速输入选定要插入的单元格，按下 “Ctrl”键与分号键 “； ”，然后回车即可插入当前日期。而要输入当前时间，同时按住 “Ctrl”键、 “Shift”键与 分号键，然后回车即可。 3、日期与时间格式的快速设置如果对日期或时间的格式不满意，可以右击该单元格，选定 “设置单元格格式

2、 数字 日期 ”或 “时间 ”，然后在类型框中选择即可。 一、 Excel 日期计算的基础 事实上， Excel 处理日期的方式和我们想象的有些不同。在我们眼中，日期是含有年、月、日的有特定格式的数据。但是， Excel 却在内部把日期作为以 1 开始的时间序列数存储。 二、 显示当前日期 在各种工资表、成绩表等各种类型的工作表中，常常需要显示当前日期，如果插入的日期就是工作时的日期，而且以后不需要改变，则使用组合键 Ctrl；插入即可。 如果希望这个日期能够自动更新，则使用函数 TODAY。 三、 使用公式快速输入日期 1、快速输入有规律的日期系列 如果需要在工作表中输入有固定间隔数 (这个

3、间隔可以是年、月、日 )的日期系列时，用公式处理非常高效。 2、快速输入无规律的日 期系列 上面是输入有规律的日期系列，如果要输入的日期没有规律，有没有方法提高输入效率呢？回答是 肯定的。 四、 计算日期对应的星期数 有时我们希望求出日期所对应的星期数，以便分析星期对相关数据的影响。 五、 计算两个日期之间的天数 在实际工作中，需要计算两个日期之间间隔的天数的情形非常多。比如现在是 2004 年 7 月 27 日，2004 年 10 月 8 日是 50 周年校庆，我们想计算出距离校庆还有多少天，该使用什么公式呢？ 六、 计算两个日期之间工作日的天数 我们把每个星期得星期一到星期五称为工作日，假

4、如项目经理 2004 年 7 月 7 日接到一个新项目，要求 2004 年 10 月 8 日完成，他需要计 算一下这两个日期之间有多少个工作日，以便安排工作的进度。 七、 计算若干年、月、日以后的日期 计算若干年、月、日以后的日期在实际任务也可能常常遇到。例如，计划用 1 年 2 个月零 15 天来完成一本图书的编写工作，开工日期为 2004 年 7 月 28 日，那么什么时候能完成图书的编写呢？ 八、 计算两个日期之间的年数 如果需要计算两个日期之间的年数，使用 YEAR 函数实现起来非常简单。 九、 计算年龄 年龄应该为我们生活的完整数的年。具体地说，就是当前的日期与我们出生的日期之差得到

5、的天数，除 以 365 得到的数的整数部分。这里所说的年龄也就是我们常常说的实岁。利用一个名为 YEARFRAC 和函数和一个取整函数 INT可以轻松计算年龄。 一、 Excel 时间计算的基础 和日期一样， Excel 处理时间的方式并不像我们看到的那样 时间是由小时、分钟和秒钟组成的，有特殊格式的数据。 Excel 在内部把每 24 小时的时间周期作为一个从 0 至 1 的小数存储。 二、 使用公式快速输入时间 如果需要在工作表中输入很多没有规律的时间系列，假如只用输入 A 列的数字，然后用 Excel 公式自动转换成需 要得到的 C 列的时间格式，就可以大大提高输入的效率。 三、 计算出

6、工作的小时数 在一些类似于考勤表的工作表中，常常需要计算工作的小时数，这也就是前面所提到的，两个时间间隔长度的问题。我们在下面的工作表中进行操作。 四、 计算出考试的分钟数 上面一个实例，计算两个时间的差值，我们希望计算结果的单位为小时。那么，假如我们希望计算结果的单位为分钟，该用什么公式呢？下面用计算考试的分钟数来说明。 五、 对时间的小时、分钟和秒进行处理 下面这个问题是来自于一位读者的提问，大致是这 样的：我希望对时间用如下规则进行处理，如果时间的秒数大于等于 30 秒，则分钟数加 1，秒数归于 0；如果时间的秒数小于 30 秒，则去掉秒数。 一、 Excel 时间计算的基础 和日期一样

7、， 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。从上一

8、个午夜零点开始至下一个午夜零点开始，正好经历了 24 小时，这就是我们所说的， Excel处理时间的一个 24 小时周期。 将时间作为小数存储使得 Excel 可以对时间进行运算。例如 ,要计算两个时间之间的间隔长度，执行结束时间对应的小数减去开始时 间对应的小数的操作即可，具体应用下面会有实例介绍。 巧用 分列 更改日期 刚进办公室，小赵就急急忙忙地跑过来说： “快帮兄弟一把！ ”我走到他的电脑前，看到的是一张名为 “职工简况 ”的 Excel 工作表。他说： “领导让我根据表上面的 出生年月 ，快速算出截止到 2004 年 5 月 31日每个人的年龄，下午就得交差。 ”这有何难，我胸有成竹

9、地坐到了电脑前，立马在 “出生年月 ”的右面插入了一列，在 E3 单元格里输入公式 “=DATEDIF（ D3，“2004-5-31“， “y“） ”，一回车， E3 单元格里竟是 “#VALUE！ ”。仔细一看，原来图 1 表格中 “出生年月 ”里的数据不符合要求，程序不能识别出这是日期，因此不能利用这些数据进行有关日期的操作。如何将它们改为程序能够识别的日期形式呢？一个一个地改，不是办法，好几百人呢！ 如果 “出生年月 ”中的数据最右面都没有小数点（如 68.9.26），只需用 “替换 ”将数据中的小数点 “。 ”替换为 “-”，程序就会识别出这些数据是日期。 数据最右面有一个小数点，即使

10、用上面的方法替换，程序也不认为这些数据是日期，可恰恰数据里有好多是这样的。提取字符吧！需要的字符又多少不一；删除最右面一个小数点吧，一时还找不到这样的函数。“天无绝人之路 ”，几经摸索还真找到了一个好办法，虽然还是有些麻烦，但总比一个一个地改要好的多。思路是将数据分为年、月、日三部分（如 59.10.5），每部分各占一列，然后再将这三部分用 “-”，连接起来。显然我们应以小数点 “。 ”作为分隔标志，即分隔符号。 1. 因为数据的最右面还有一个小数点，所以应在 “出生年月 ”列的右面插入 3 个空白列 (请见图 1)。 2. 选择 “出生年月 ”下面的所有数据，即 D3:D8，单击 “数据 分

11、列 下一步 ”，在 “分隔符号 ”中勾选 “其他 ”，在其右面的空白处输入一个小数点 “.”，去掉 “分隔符号 ”中的其他所有选项。 3. 单击 下一步 完成 按钮。数据按要求分成了三列 (请见图 2)。 4. 选中 G3 单元格，输入公式 “=（ IF（ F30， D3&“-“&E3&“-“&F3， D3&“-“&E3） +0”，回车后， G3 单元格里是 “21575”用填充柄向下拖动至 G8.经过上述操作，字符串已转成数值，只不过还不是我们需要的形式。公式中 “+0”，就是为了使文本型的数字转化为数值型的数字（请见图 3）。 因为 G 列的数据是由左面单元格引用过来的，所以我们不能删除

12、D、 E、 F 列，要使 G 列的数据不受 D、E、 F 的制约，可再做如下操作。 5. 选定 G3:G8，单击 “复制 ”，选择 D3 单元格，再单击菜单 “编辑 选择性粘贴 ”，在打开的对话框的 “粘贴 ”中选择 “数值 ”，然后单击 确定 按钮。 6. 选中 D3:D8，单击菜单 “格式 单元格 ”，在打开的 “单元格格式 ”对话框中，单击 “数字 ”标签，在 “分类 ”中选择 “日期 ”，在右面的 “分类 ”中选择上级要求的 或是您喜欢的日期格式，删除不需要的 E、 F、 G 列，就大功告成了 (请见图 4)。按照本文开头的方法输入 “DATEDIF”函数，任务就完成了。 快速创建自己

13、的行事年历 习惯使用 Year Plan 的用户通过 Excel 函数功能 和 条件格式 可以快速创建自己的行事年历。 1）使用 Excel 函数自动判断本月最后日期是 28、 29、 30 或 31 方法： =IF(MONTH(DATE(年 ,月 ,日 )=月 ,日 ,“) 说明：其中 “年 ”“月 ”“日 ”指存放 “年 ”“月 ”“日 ”数值的单元格，使用 Month 函数自动提取 “月 ”数值，通过 IF 函数选择 2）条件格式用于自动显示星期六、日的日期 方法： =WEEKDAY(DATE(年 ,月 ,日 ),2)=6 说明：使用函数 weekday 功能提取星期值 举例 日期计算公

14、式 （设单元格 C2已输入当前日期） 1) 今日 =TODAY() 2) 明日 =TODAY()+1 3) 次日 =C2+1 4) 下一个月 =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 =DA

15、TE(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),M

16、ONTH(C2)+1,DAY(C2) 本月月底 2006-12-31 =DATE(YEAR(C2),MONTH(C2)+1,1)-1 2006-12-31 =DATE(YEAR(C2),MONTH(C2)+1,0) 下月月底 2007-1-31 =DATE(YEAR(C2),MONTH(C2)+2,1)-1 2007-1-31 =DATE(YEAR(C2),MONTH(C2)+2,0) 季度末 2006-12-31 =DATE(YEAR(C2),INT(MONTH(C2)+2)/3)*3+1,1)-1 2006-12-31 =DATE(YEAR(C2),INT(MONTH(C2)+2)/3)*

17、3+1,0) 本半年底 2006-12-31 =DATE(YEAR(C2),INT(MONTH(C2)+5)/6)*6+1,1)-1 2006-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 中输入时间是需要

18、带上冒号的，如果有大量的时间值等待输入的话，这个冒号可得耽误不少功夫，而且又容易输入错误。 有什么好办法可以解决这个难题呢？当然是用嵌套在 Excel里的利器 VBA了！以下就是所有源代码，只要您按照我的说法一步一步做下去，即使对这些代码一点也不懂，也能依葫芦画瓢，迅速实现在 Excel中不必输入冒号就能输入时间值的效果！ 一、 启动 Excel，打开您需要大量输入时间值的那个工作簿，然后按 Alt-F11，启动 VBA 编辑器。 二、 选择菜单中 “插入 ”“添加模块 ”命 令。这时，在屏幕左上方 “工程 ”窗口中，会出现一个 “模块 ”的子目录，在它的右侧，显示着刚刚添加进来的模块 “Mo

19、dule1”，您可以将它改名为您所喜欢的名字如“fasttime”。 三、 双击模块 “fasttime”（如果您修改过模块名称的话），在右侧的代码编辑器中输入以下代码（或者从我的示例工作簿中复制）。 Option Explicit Sub Auto_Open() Application.OnEntry = “Fast“ 每当工作表内容发生变化时就运行指定过程 End Sub Sub Fast() On Error GoTo EnterError 如果有错误就跳往 EnterTimeError 语句 If Intersect(Application.Caller, Range(“fasttim

20、e“) 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

21、Sub EnterError: Exit Sub End Sub 四、把您将要大量输入时间值的单元格区域命名为 “fasttime”。命名方法为，先将需要命名的单元格区域抹黑，然后选择 Excel 的菜单命令 “插入 ”“命名 ”“定义 ”，在 “在当前工作簿中的名称 ”栏中输入“fasttime”，最后按确定即可。 好了，完工！现在往 “fasttime”区域里输入时间值试试看，是不是不用冒号了！？这里需要注意的是，输入值必须介于 1 到 2400 之间，否则将被视作非法值。举个例子来说，如果您需要输入上午九点二十分这样一个时间值，您就只需要输入 0920 这样一个数字就行了， Excel 将在单元格中显示为 “09:20 AM”；如果您需要 输入晚上一十一点一十五分这样一个时间值，您就只需要输入 2315 这样一个数字就行了， Excel将在单元格中显示为 “11:15 PM”。是不是很方便？