手把手教你用Excel做带节气的日历模板(附5个进阶技巧)

今天分享一下用Excel制作日历的方法，学会后你可以掌握以下技能：

WEEKDAY和DATE这两个日期函数的用法；
自定义名称；
自定义格式；
数据有效性验证（下拉菜单）；
条件格式。

Excel日历效果图：

操作步骤：

1. 先把日历的框架画出来

为什么日期区域有6行？因为月份最多横跨6周

2. 创建年份和月份下拉列表

①、在 S1:S11 创建年份列表：2020、2021、2022...一直到2030

T1:T12 单元格创建月份列表：1、2、3、4..一直到12

②、选择C2单元格创建年份下拉列表：

同理，在E2单元格创建月份的下拉列表

3. 自定义名称：定义年份和月份

定义名称：年份 = $C$2
定义名称：月份 = $E$2
定义名称：月初 = DATE(年份,月份,1)
定义名称：星期 = WEEKDAY(月初,2)

4. 创建日期区域

1、首先，可以明确的是B5:H5单元格是当月的第1周

假设现在是2026年4月份，月初 = 2026-4-1 ，星期=3，即月初这一天是周三，那么B5的日期即为月初-2，用公式写出来即为：

B5 = 月初 - 星期 + 1

B5单元格输入以上公式，C5输入 =B5+1，向右拖动到H5单元格，这样第1周的日期都出来了；

2、同上，B6:H6单元格的日期为上一周日期+7，即B6：=B5+7，向右填充到H6

选择B6:h6分别复制到B7:H10单元格；

3、自定义单元格格式：选中B5:H10单元格——设置单元格格式——自定义- 类型填D，效果如下：

5. 设置条件格式

现在是最后一步：让非本月的日期显示为灰色。

怎么实现？很简单，用MONTH函数返回月份，如果该月份<>当月的月份，则字体颜色为灰色！

①、选中B5:H5单元格 — 开始 — 条件格式 — 新建规则 — 使用公式确定要设置格式的单元格，在公式栏输入：

=MONTH(B5)<>月份    ' B5或B$5都可以

公式理解：

B5或B$5：这里为什么不是$B5或$B$5,因为设置条件格式的是B5:H5，一共7列，所以不能固定列序；

MONTH(B5)：返回当前日期的月份；

<>月份：月份是之前已经自定义好的名称，即当月的月份

当前日期月份<>当月月份成立时，值为TRUE，表明该日期不在当月范围内，条件成立，单元格字体颜色为灰色。

同理，选择B9:H10单元格，条件格式公式输入：

=MONTH(B$9)<>月份

OK，再把周六周日这两列的字体设置为红色，日期就完成了。

进阶：

1. 给年份和月份添加微调按钮

①、开发工具—插入—表单控件—数值调节钮(第1排第4个）；

②、把调节钮拖动到年份单元格后面，设置控件格式—最小值2020，最大值2030，步长1，单元格链接：$C$2

③、同理添加月份微调按钮，最小值1，最大值12

2. 给日期添加节气

①、首先在网上下载2020-2030年所有的节气日期，日期在前节气在后，定义名称为：节气；

②、在每一行日期下插入一列用作节气显示

③、输入公式显示日期：

=IFERROR(VLOOKUP(B5,节气,2,0),"")

复盘总结：你顺手搞定的5个硬核技能

回头看看，我们做这个日历，看似轻松，实则一步一坑（知识点）：

DATE函数：日期合成的基石，所有与日期相关的计算都离不开它。

WEEKDAY函数：日期分析的“解构器”，区分工作流、计算周数的核心。

自定义格式：“所见非所得”的智慧，保持数据计算本质，优化视觉呈现的利器。

条件格式：让数据“自己会说话”的规则引擎，是制作动态看板和预警系统的核心。

数据验证：规范输入、创建交互的“守门员”，是制作模板、提升数据质量的关键。
自定义名称：让公式逻辑更清晰、维护更轻松，是提升效率的好帮手。

这些技能，单独拆开个个能打，组合起来威力无穷。它们绝不仅仅用于做日历，更是你处理项目排期、销售仪表盘、考勤统计、数据看板的必备武器。

💡提示：在日期和季气之间插入一行日销售，你就得到了一个日历版的销售日报表。

光看不练，武功全废。我已经为你准备好了本文的全套Excel示例文件。

获取方式：关注本公众号，后台回复关键词「日历」，即可一键领取。

#Excel技巧 #日历 #职场效率 #数据分析 #办公神器 #自定义名称