Excel中日期与时间的快速处理.docx

Excel制表技巧（77）日期时间2006年12月19日 星期二 19:43Excel中日期与时间的快速处理1、任意日期与时间的输入数字键与“/”或“-”配合可快速输入日期，而数字键与“：”配合可输入时间：如输入“3/25”，然后回车即可得到“3月25日”。又如输入“9:25”，回车即得到“09:25”。2、当前日期与时间的快速输入选定要插入的单元格，按下“Ctrl”键与分号键“；”，然后回车即可插入当前日期。而要输入当前时间，同时按住“Ctrl”键、“Shift”键与分号键，然后回车即可。3、日期与时间格式的快速设置如果对日期或时间的格式不满意，可以右击该单元格，选定“设置单元格格式数字日期”或“时间”，然后在类型框中选择即可。4.日期转换为中英文的星期几。工作表中有一列日期数据（如C3单元格的日期为“2006-12-25”）如何将它转换为中文或英文的星期几呢？如果要转换为中文的星期几可输入公式“=TEXT（WEEKDAY（C3），aaaa)”来实现，如果要转换为英文的星期几可输入公式“=TEXT（WEEKDAY（C3），dddd)”来实现.5.用函数求工龄。Datedif函数是Excel函数表中未曾提及的函数，利用它可以方便地求出两个日期间的相差的年数，月数和天数，如果要计算工龄，这个函数是再方便不过了。如C3单元格中日期是“1995-9-1”利用公式“=datedif(c3,today(),y）”就可方便地计算出工龄了。一、Excel时间计算的基础和日期一样，Excel处理时间的方式并不像我们看到的那样时间是由小时、分钟和秒钟组成的，有特殊格式的数据。Excel在内部把每24小时的时间周期作为一个从0至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”函数，任务就完成了。快速创建自己的行事年历习惯使用YearPlan的用户通过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-1518:25=NOW()Excel快速输入时间值法如果您对Excel的时间显示方式不满意的话，您还可以在“单元格格式”里进行修改，Excel内置了10种时间显示方式，调一款您喜欢的吧！大家都知道，在Excel中输入时间是需要带上冒号的，如果有大量的时间值等待输入的话，这个冒号可得耽误不少功夫，而且又容易输入错误。有什么好办法可以解决这个难题呢？当然是用嵌套在Excel里的利器VBA了！以下就是所有源代码，只要您按照我的说法一步一步做下去，即使对这些代码一点也不懂，也能依葫芦画瓢，迅速实现在Excel中不必输入冒号就能输入时间值的效果！一、启动Excel，打开您需要大量输入时间值的那个工作簿，然后按Alt-F11，启动VBA编辑器。二、选择菜单中“插入”“添加模块”命令。这时，在屏幕左上方“工程”窗口中，会出现一个“模块”的子目录，在它的右侧，显示着刚刚添加进来的模块“Module1”，您可以将它改名为您所喜欢的名字如“fasttime”。三、双击模块“fasttime”（如果您修改过模块名称的话），在右侧的代码编辑器中输入以下代码（或者从我的示例工作簿中复制）。OptionExplicitSubAuto_Open()Application.OnEntry=Fast每当工作表内容发生变化时就运行指定过程EndSubSubFast()OnErrorGoToEnterError如果有错误就跳往EnterTimeError语句IfIntersect(Application.Caller,Range(fasttime)IsNothingThenExitSub如果是非fasttime区域的单元格被改变，则结束过程EndIf如果输入值小于1或者大于2400，则显示对话框用户输入值非法IfApplication.Caller2400ThenMsgBox对不起，您的输入值非法！,vbExclamationApplication.Caller.Value=ExitSubEndIf将输入值改成HH:MM格式Application.Caller.Value=Format(Application.Caller,00:00)ExitSubEnterError:ExitSubEndSub四、把您将要大量输入时间值的单元格区域命名为“fasttime”。命名方法为，先将需要命名的单元格区域抹黑，然后选择Excel的菜单命令“插入”“命名”“定义”，在“在当前工作簿中的名称”栏中输入“fasttime”，最后按确定即可。好了，完工！现在往“fasttime”区域里输入时间值试试看，是不是不用冒号了！？这里需要注意的是，输入值必须介于1到2400之间，否则将被视作非法值。举个例子来说，如果您需要输入上午九点二十分这样一个时间值，您就只需要输入0920这样一个数字就行了，Excel将在单元格中显示为“09:20AM”；如果您需要输入晚上一十一点一十五分这样一个时间值，您就只需要输入2315这样一个数字就行了，Excel将在单元格中显示为“11:15PM”。是不是很方便？Excel让表头自动填写当前月份和日期假设我们要做一个标题为“某公司6月份电费统计表”的Excel表格，其中“6”为进行电费统计的当月月份，第二行有制表日期。电费每月公布一次，那么每月打开此表修改编辑时，都要对标题中的月份和制表日期进行修改，万一只改动了表内数据，而忘记以上两个时间的改