如何用excel制作月份自动变更的考勤表

对于做考勤的都希望能做一个自动变更月份的考勤表，只要系统时间一到，表中的日期就会自动变更为当前日期。这就不需要再每个月手动去更改时间了，能不能实现这样的呢？

答案是肯定的！博主溪门飞雪今天就给大家分享一下，我在13年时制作的一张自动更改时间考勤表。

首先，我们观察一下我这张表，这张表在使用时，只需要将左上角黄色区域的月份和年份选择为本月即可，考勤区，有下拉，直接选择即可。这个表做起来有点复杂，不过本文博主会详细介绍步骤。

1、做框架

框架的话，看各个公司的需求，样式自己定就好，没有说统一的标准。考勤表主表区域需要有31列，因为每月最多的时候有31天。而行嘛，至少两行作日期，日期一行，星期一行，其他的就随意。

2、本月应出勤数

对于正规的企业来说，出勤数和国家规定的工作日是一样的，该放假就放假，该双休就双休。这里我们说的就是按照国家规定的来，如果你的公司不是这样的，则根据各自情况确定。

比如，按照国家的标准，本月应上班21天。这是通过函数公式计算出来的，公式是：=NETWORKDAYS(DATE($G$1,$J$1,1),EOMONTH(DATE($G$1,$J$1,1),0))

【公式解读：“NETWORKDAYS”计算的是两个日期之间的完整工作日的数量；“DATE”计算的是一个日期，“EOMONTH”这个函数计算的是每个月的最后一天，不一定是31号，可能是30号，也可能是29号、28号。】

3、表头公式设置

表头“溪门飞雪有限公司2019年9月考勤表”，这个也是设置了公式的，公式为：=TEXT(DATE(C1,I1,1),'溪门飞雪有限公司e年m月考勤表')

【公式解读：“TEXT”是将单元格的值转换为文本；“date”就是日期，“e年m月”自动匹配结果中的年月值。】

当然，这里我们不一定要弄这么复杂，如果你搞不懂“e年m月”，不会用，也可以直接使用“yyyy”和“mm”，四个“y”表示显示四个字符的年份，如果改为两个“y”，则结果显示为“19”年，月份也是一样的，一般使用一个“m”就可以。公式：=TEXT(DATE(C1,I1,1),'溪门飞雪有限公司yyyy年mm月考勤表')

4、日期的公式设置

接下来，就是最重要的部分，日期函数公式的设置。日期的设置主要是随着月份的变化，天数变化，小月将不显示31号，2月不能显示30,31号。公式：=IF(MONTH(DATE($C$1,$I$1,COLUMN(A1)))=$I$1,DATE($C$1,$I$1,COLUMN(A1)),'')   输入公式后，一直将公式拖动或者复制到最后边即可。

【注意事项：公式输入在日期这一行，刚才我们输入“1，2,3……”只是为了方便大家看，这些位置其实是需要由公式显示日期的。那么，结果出现了上图这样的情况，这个时候我们需要对日期这31个单元格进行格式自定义，全部自定义为“d”。】

【公式解读：“if”是判断函数，主要是判断月大月小；“MONTH”返回的是月份；“COLUMN(A1)”返回结果是“1”，这里主要为了向右拖动公式日期能够自动变化，“COLUMN(A2)”返回结果是“2”，依此类推。这里有个判断，假如“某一天”是“某月的天”，则显示这天的日期，否则变为空。】

5、星期的公式设置

这也很重要，这得根据上边的日期来，和日期对应。公式：=IF(MONTH(DATE($C$1,$I$1,COLUMN(A1)))=$I$1,DATE($C$1,$I$1,COLUMN(A1)),'') 输入后，依次往右拖动或者复制到最右边即可。

【注意事项：和日期一样，也出现这样的情况，这时候我们需要自定义格式，将格式设为“周三”或者直接“星期三”，或者自定义为“aaa”即可。】

【公式解读：其实这个公式和日期公式是一样的，就是显示格式不一样而已。】

6、设置颜色变换

当做好之后，不是很好看，我们可以将每个周的周日用特殊颜色标记出来，这样，就将月分成了周。我们先选中日期和星期，两行都全部选中，然后依次点击功能区中的“条件格式”，“新建规则”，“使用公式确定要设置格式的单元格”。

在“为符合此公式的值设置格式”下方的输入框中输入公式：=WEEKDAY(D3,2)=7 ，

然后点击下方的“格式”，打开格式面板，设置自己喜欢的格式，确定即可。

7、设置主表下拉选项

为方便打考勤，我们可以将考勤的选项设置好，比如，缺勤的打“×”，出勤的打“√”，婚假填“婚”等等。设置方法也比较简单，运用的是“数据有效性”。这里要注意的是，之前的版本，比如07版，03版，这个功能叫做数据有效性，而在13版中，改名为“数据验证”。

在输入时，我们先在表格，或者其他地方写好，中间用英文的逗号隔开，比如，这里我们写入：√,事,病,婚,差,年,旷,迟1,迟2,迟3,迟4,迟5,迟6,迟7,迟8,迟9,迟10 。“迟10”指的是迟到10分钟，当时笔者在公司时，迟到一分钟视为迟到一小时，迟到一小时视为旷工，这里可以根据自己公司的情况设置。

设置好之后，只需将格式复制到所有单元格即可。

补充：在最上方当天日期那里最好设置为手动填入日期，这样的话以保证日期不会自动变更。比如，有的人在月末最后一天的时候做考勤表，结果，没做完，到了下月了，这时候，日期自动变化了，就不太好，所以，这里可以直接手动填写。当然，如果你想要自动变化也可以。输入公式：=YEAR(NOW())  自动年份；输入公式：=MONTH(NOW())  自动月份。

有什么不明白的地方，可以在评论区留言，也可以直接加我微信答疑。