点击 蓝字【秋叶 Excel】 👆

发送【方方】

免费领职场人都在用的方方格子插件！

本文作者：小爽

本文编辑：雅梨子、卫星酱

大家好，我是在捣鼓日历的小爽~

前面小兰有介绍过利用 数据透视表 制作日历~

那这种炫酷的日历表，用 函数 该怎么做出来呢？

今天，我就和小伙伴们一起聊一聊 日历的函数做法

操作特别简单，一起来看看吧~

▋ 先做一个样板

❶ 插入一个数值调整器控件。

在【开发工具】选项卡下，单击【插入】-【数值调节控件】：

PS. 如果没有【开发工具】选项卡，在【文件】-【选项】-【高级】中调出即可。

❷ 点击鼠标右键，选择【设置控件格式】。

指定「单元格链接」为 C2 单元格，其他参数如图下所示：

此时月份跟着数值调节控件进行联动。

编写公式

制作完对应的样板，现在我们就来写写函数公式。

做法很简单，如下图：

往下，就是上一个单元格的值加 7；

往右，就是左边单元格的值加 1。

也就是说，只需要确定第一个单元格的日期，就能确定其他单元格的日期。

那么我们怎么 确定第一个单元格的日期呢？

只要确定 每月的 1 号的上一个周一的日期 是多少，就能确定第一个单元格的日期。

这其中就涉及到一个数学逻辑问题了。

比如说，2021 年 7 月 1 号是周四，那么如下图：

7 月 1 号的日期-4+1，就是 7 月 1 号前面的第一个周一的日期。

即，每月的 1 号的上一个周一 = 每月 1 号的日期-每月 1 号的星期数+1。

我们知道：DATE 函数能够返回一个日期。

=DATE(年，月，日)

WEEKDAY 函数可以获取日期的星期数。

WEEKDAY 函数的语法规则：

=WEEKDAY(serial_number,[return_type])
=WEEKDAY(日期,2)

由于我们习惯把周一开始作为每周第一天，所以第 2 参数一般设置为 2。

前面说过，每月的 1 号的上一个周一=每月 1 号的日期-每月 1 号的星期数+1。

对应输入公式为：

=DATE(2021,$C$2,1)-WEEKDAY(DATE(2021,$C$2,1),2)+1
▲ 左右滑动查看

如动图所示，分别输入对应的公式，右拉/下拉填充。

设置条件格式

设置完对应的公式之后，我们还需要把 不是当月的日期进行隐藏， 这里就需要使用到【条件格式】了。

❶ 选中 B5:H10 区域，在【开始】选项卡下，单击【条件格式】-【新建规则】；

❷ 选择【使用公式确定要设置格式的单元格】，输入设置格式的公式：

=MONTH(B5)<>$C$2

❸ 单击格式，格式中字体选择白色。

这样，我们就可以将不是当月日期的字体变成白色，肉眼上就看不到了，从而实现隐藏的效果。

动图操作如下：

PS. 这里条件格式公式的原理是：把显示为 TRUE 的单元格的字体设置为白色。

❹ 最后，我们只需要将数字格式自定义设置为 只保留日 就行了。

① 选中[B5:H10]区域，按住快捷键【Ctrl+1】弹出单元格设置对话框；

② 自定义-类型设置为：d。

到这里，单元格的日期就只显示日了。

延伸拓展

前面我们是将日期的中的月，联动到单元格中，那我们也可以将年联动到单元格中。

原先的第一个单元格公式为：

=DATE(2021,$C$2,1)-WEEKDAY(DATE(2021,$C$2,1),2)+1
▲ 左右滑动查看

那将之前的年份 2021 改为单元格引用，就可以做出如下图的效果：

=DATE($G$2,$C$2,1)-WEEKDAY(DATE($G$2,$C$2,1),2)+1
▲ 左右滑动查看

那如果我们想要在日历表中， 是当天的日期，就高亮显示，这应该怎么做?

要达到上面这种效果，我们只需要设置一个条件格式：

❶ 选择[B5:H10]区域，在【开始】选项卡下，单击【条件格式】，新建规则。

❷ 使用公式确定要设置格式的单元格，输入如下公式：

=AND(YEAR(TODAY())=$G$2,MONTH(TODAY())=$C$2,DAY(B5)=DAY(TODAY()),MONTH(B5)=$C$2)
▲ 左右滑动查看

公式对应的判断如下：

=AND(YEAR(TODAY())=$G$2, 年等于当前的年 MONTH(TODAY())=$C$2,  月等于当前的月 DAY(B5)=DAY(TODAY()),   日等于当前的日 MONTH(B5)=$C$2   每个单元格等于选定的月)
▲ 左右滑动查看

PS. 本文截图时间是 8 月 2 号，所以下图中对应日期显示绿色填充颜色。

当然，有时候，我还会用 加载项-mini calendar。

直接在应用商店添加它就好啦。

如动图所示：

它可以显示对应周的周数。

它也可以切换不同的颜色主题。