制作行事历时用excel实现周时间段自动填充

制作行事历时用excel实现周时间段自动填
充
在制作学校行事历时，进行周次安排后，往往要注明每周的起讫时间，以周一日期开始，以周六日期结束，清楚明了地写清每周所在的月日时间段（见下表）。

通常的做法是查阅日历，手工输入，但这样做常常会出现失误，一日错导致全部错，结果只能从头开始。

笔者经过摸索，结合网友的一些时间填充的做法，探讨出了在excel中只用输入当前日期（制表日期）后，每周的起讫时间段能全部自动填充的方法，现在分享出来，供朋友们参考，不成熟之处，请留言交流。

一、实现思路：
1、计算出从计算机设置的初始时间（1900年1月1日星期日）到制作表格时当前日期（假设
是2011年9月1日）之间的总天数。

2、计算出总天数除以周天数7的余数。

3、计算出总天数减去余数的剩下总天数，让剩下总天数为周天数7的倍数，那么最后一天为星期六。

4、计算出剩下总天数加1之后的新总天数，那么最后一天为星期日。

5、将新总天数转换为日期，作为第一周的开始日期（星期日），将新总天数加上6后转换为日期，作为第一周的结束时间（星期六）。

这样就实现了第一周的时间段填充。

6、为了实现在第二周时起讫日期能分别加7（加一周天数）自动填充，可以读取行号，用行号作为变量参数乘7，分别加到起讫时间的新总天数中，再进行转换，完成第二周的填充。

当然行号参数可以进行加减相应的自然数，以适应行事历表头，（下面实现过程中要讲到）。

7、将第一周的公式向下填充到自己要安排的周数。

8、改变当前日期，所有的周起讫日期便实现自动改变填充。

二、实现过程（以下表为例）：
1、合并A1——F1(A1)作为行事历表格名称。

2、合并A2——F2（A2)作为行事历制作日期（当前日期，并设置此单元格格式为日期：****年**月**日）（此格中的日期很重要，以后表格的使用，全靠此单元格中的日期的改变而自动实现。

）
3、在A3、B3、C3、D3、E3、F3中分别输入表头“周次”、“起讫日期”、“工作安
排”、“主管部门”、“行政值周”和“备注”。

4、在G1中输入计算机初始时间“1900年1月1日”，并设为时间格式。

5、在G3中输入“=$A$2-%G$1”,把从“1900年1月1日”年到“2011年9月1日（笔者假设的，用者可自定）”的总天数计算出来，并存储在G3格中。

(注：行列号前加$是为了防止行改变时，公式读取数据也随之改变。

）
6、在G2中输入“MOD($G$3,7)”，求出总天数除以7的余数（为后来去掉非整周之内的天数算出个参数，以保证总天数正好计数到一周的最后一天，也就是找到总天数中按周算的最后一天“星期六”）。

7、在A4中输入“ROW()-3”以取得行号再减3，得到周
次。

“ROW()”函数作用是读取当前行号，减3是因为上面已经用了三行作为“表格名称”、“当前日期”和“表头”了。

8、最重要的是在B4行（第一周起讫时间行），我们要输入公
式“=TEXT($G$3-$G$2+1+(ROW()-4)*7,"mm.dd")&"～"&TEXT($G$3-
$G$2+7+(ROW()-4)*7,"mm.dd")”，其中，“$G$3-$G$2+1”是求出从1900年1月1日到当前日期最临近的周日的总天数；“$G$3-
$G$2+7”是求出从1900年1月1日到当前日期最临近的周六的总天
数；“ROW()”是读取当前行数，此例中是4，“-4”是为了保证第一周的行数为变量参数为0，乘7是为了确保每隔一周起讫时间都加上7天（第一周除外，因为此例中第一周行数参数减4为0，乘7后还是0，不影响根据日期对天数的转换，到第二周时行数参数减4为1，再乘7，就会给起讫日期都加上7天。

）；TEXT（）函数是将新的总天数文本格式的日期（第一周中起始时间“$G$3-$G$2+1”值为“40787-5+1”也就是40783，进行日期转换后就是“2011年8月28日”，结束时
间“$G$3-$G$2+7”值为“40787-5+7”也就是40789，进行日期转换后是“2011年9月3日”；"mm.dd"是为了确定两位月两位日的日期格式，如果要加上四位年份的话，可将之改为"yyyyy.mm.dd",其
中“y”的个数决定年份的位数；+(ROW()-4)*7是为了在下面的单元格中将起讫日期均根据行号的改变而加上7天；&"～"&中，两个“&”在语法上是“并
且”的意思，在这里是要将“""”中间的“～”与起讫时间合并显示，当然也可以将“～”改为“至”。

到此，第一周的起讫时间填充完成。

9、将B4单元格中的公式向下复制或填充到你所需要的周次，此例中填充到第21周，每周都会以“周日到周六”为日期，今后，只要你改变单元格A2中的年、月、日，所有的A4及以下的单元格中的时间段都会自动改变填充。

可谓事半功倍。

如果周次不够，可以在说明单元格以上插入空白单元格，再投下填充公式即可，至于G列中的辅助公式，可以将其全部隐藏。

10、最后，别忘记将含有公式的单元格全部保护起来。

（方法：1、选取整个工作表 2、单击“格式”菜单 3、单击“单元格”下拉菜单4、单击“保护”按钮，取消“锁定”项目 5、同时选取含有公式和函数的单元格<按ctrl键加选>，如上1——4操作，选定“锁定”项目6、在菜单“工具”——“保护”——“保护工作表”中勾选“保护工作表及锁定的单元格的内容”和“选定未锁定的单元格”两项 7、输入密码—确定—再次输入密码—再次确定。

11、以后只用改变A2单元格中的日期即可一步完成周时间段填充。