最近五篇居然有三篇是关于日期的文章,这是第四篇。
前几天,国家公布了2024年放假安排,做为表哥表妹,日历得亲手打造才合格。下面我们来自已动手做一个漂亮的日历。带放假、调班的那种。
还能根据指定月份的变化而变化。
要用到的知识点:
自定义格式、条件格式、日期函数
下面一步步教大家如何制作这个日历表。
步骤一:先搭框架
D2:E2、B2:C3用合并单元格。
年份使用“数据验证-序列”,输入年份序列,添加下拉箭头选择年份。
第二步,输入公式生成日期
D3单元格公式:
=DATE(D2,B2,1)
使用自定义格式显示为英文月份,自定义格式代码:
mmmm
B5单元格公式:
=DATE(D2,B2,1)-WEEKDAY(DATE(D2,B2,1),2)+1
这是一个用来计算给定年份(D2)、月份(B2)的第一个星期一的日期的 Excel 公式。
下面逐步解释这个公式:
DATE(D2, B2, 1)
: 这部分创建了一个日期,其中 D2
是年份,B2
是月份,1
是日期。这个函数返回的是给定年份和月份的第一天的日期。
WEEKDAY(DATE(D2, B2, 1), 2)
: 这部分使用上面得到的日期,确定这一天是星期几。WEEKDAY
函数中的第二个参数(2
)表示星期一是一周的第一天,星期日是最后一天。
DATE(D2, B2, 1) - WEEKDAY(DATE(D2, B2, 1), 2) + 1
: 这部分将第一步得到的日期减去第二步得到的星期几,再加上1。这样就得到了给定年份和月份的第一个星期一的日期。
C5单元格公式:
=B5+1
然后向右拖动填充
C7单元格公式:
=B5+7
然后将此公式复制粘贴到第7、9、11、13、15行相应的单元格。
第三步:生成农历
用公式生成的农历不太靠谱,还是在网上下载一个公历、农历对照表吧。
用公式查询农历
=VLOOKUP(B5,对照表,3,0)
再用Right函数取右边三个字符,最后用Substitute函数去掉可能存在的“月”字。
=SUBSTITUTE(RIGHT(VLOOKUP(B5,对照表,3,0),3),"月","")
将公式复制到其他相应单元格。
使用自定义格式,将第5、7、9、11、13、15的日期设为只显示“日”
自定义格式代码:
d
对自定义格式不太了解的朋友,可以点击下面的合集,查看相应的文章:
现在日历表的大致样子已经设置好了。
但现在还是清水房,我们还得搞个精装修。
第四步:将不是本月的日期显示为灰色
按住Ctrl键,依次选中B7:H7、B13:H13、B15:H15、B5:H5单元格。
这样选择,是为了让活动单元格是B5。
=MONTH(B5)<>$B$2
如果活动单元格不是B5,那么,条件格式的公式需要相应改变,比如活动活动格是B15时,则条件格式输入下面的公式:
=MONTH(B15)<>$B$2
具体原因,请阅读下面的文章:
同理,使用相同的方法,对第6行、8行、14行、16行相应单元格设置条件格式,让不是本月的日期显示为灰色。
第五步:突出显示将休假的日期
将下面2024年的休假、调休情况表复制到G2:H41单元格
休假及上班情况 | |
2023-12-30 | 休 |
2023-12-31 | 休 |
2024-1-1 | 休 |
2024-2-4 | 班 |
2024-2-10 | 休 |
2024-2-11 | 休 |
2024-2-12 | 休 |
2024-2-13 | 休 |
2024-2-14 | 休 |
2024-2-15 | 休 |
2024-2-16 | 休 |
2024-2-17 | 休 |
2024-2-18 | 班 |
2024-4-4 | 休 |
2024-4-5 | 休 |
2024-4-6 | 休 |
2024-4-7 | 班 |
2024-4-28 | 班 |
2024-5-1 | 休 |
2024-5-2 | 休 |
2024-5-3 | 休 |
2024-5-4 | 休 |
2024-5-5 | 休 |
2024-5-11 | 班 |
2024-6-8 | 休 |
2024-6-9 | 休 |
2024-6-10 | 休 |
2024-9-14 | 班 |
2024-9-15 | 休 |
2024-9-16 | 休 |
2024-9-17 | 休 |
2024-9-29 | 班 |
2024-10-1 | 休 |
2024-10-2 | 休 |
2024-10-3 | 休 |
2024-10-4 | 休 |
2024-10-5 | 休 |
2024-10-6 | 休 |
2024-10-7 | 休 |
2024-10-12 | 班 |
使用条件格式,将第5、7、9、11、13、15行休假的日期设置为红色填充、白色字体,并自动在日期后添加“休"字。
条件格式的公式:
=VLOOKUP(B5,对照表!$G$2:$H$41,2,0)="休"
自定义格式代码:
d休
使用同样的方法,将第6、8、10、12、14、16行休假的日期设置为红色填充、白色字体。
条件格式的公式:
=VLOOKUP(B15,对照表!$G$2:$H$41,2,0)="休"
第五步:突出显示将调班的日期
使用第五步同样的方法,第5、7、9、11、13、15行调班的日期设置为红色边框。
条件格式的公式【注意活动单元格】
=VLOOKUP(B15,对照表!$G$2:$H$41,2,0)="班"
第6、8、10、12、14、16行行调班的日期设置为红色边框。
联系客服