用excel做日历表,思路很简单:先设年份、月份两个输入格来控制要显示的月份,再用 date、weekday 和 sequence 生成6行7列的日期区域,最后靠单元格格式和条件格式把非本月日期、周末显示效果调整好就行。本次操作基于microsoft 365版本的excel,如果你用的是wps或者旧版excel,先确认下软件支不支持 sequence 这个动态数组函数,避免公式报错。
这个方法做出来的日历,特别适合改造成每月值班表、排班表、打卡表或者活动计划表。重点不是把格子做得多花哨,而是只要改年份和月份两个数,整张表所有内容都会自动同步更新,这才是好用的万年历。
第一步:准备年份和月份输入格
先在表格上方留出“年份”和“月份”两个输入位置,比如把年份放在 C2,月份放在 F2。如果想点两下就能切换月份,可以去「开发工具」选项卡插入微调按钮,把控件分别绑定到年份或月份的输入单元格;要是只做自己用的私人表格,直接手动输年份、月份就足够了。

第二步:用公式生成日历日期区
直接在日历区域的左上角单元格输入日期生成公式就行。常规的写法是先算出目标月份第一天所在周的星期一,再用 SEQUENCE(6,7,0) 向右、向下铺满42个日期位置。就拿年份在 C2、月份在 F2 的例子来说,完整公式可以写成:=DATE($C$2,$F$2,1)-WEEKDAY(DATE($C$2,$F$2,1),2)+1+SEQUENCE(6,7,0)。

第三步:把日期显示成日历样式
公式返回的是完整标准日期,如果直接显示成“2026/7/1”,整个日历区会挤得放不下。选中这6行7列的日期区域,右键进“设置单元格格式”,把数字格式改成只显示日期里的“日”就行,自定义格式栏直接输 d 就生效。表头手动填上“周一”到“周日”,读起来就和普通日历的使用习惯完全一致了。

第四步:用条件格式处理非本月日期
生成的日期区里会包含上月末和下月初的几天,这是故意这么设计的,保证不管当月1号是星期几,整个日历始终是规整的6行7列。全选日期区域后新增条件格式,用 =MONTH(B6)$F$2 这类公式筛选出所有非本月的日期,把字体调成浅灰甚至接近背景的白色就不会干扰查看;周末的样式也可以再加一条条件格式,用 =WEEKDAY(B6,2)>5 来判断,给周末单元格加个浅色底纹区分开。

函数语法和参数怎么理解
DATE(year, month, day) 用来拼接出指定的年月日,year 是年份,month 是月份,day 是日期。它有个很实用的特性:如果输入的月份超过12或者日期超过当月最大天数,Excel会自动往前进位,比如 DATE(2026,13,1) 就会返回2027年1月1日。
| 函数 | 语法 | 关键参数 | 在日历里的作用 |
|---|---|---|---|
| DATE | DATE(year, month, day) |
年份、月份、日期 | 得到目标月份的第一天 |
| WEEKDAY | WEEKDAY(serial_number, [return_type]) |
serial_number 是日期;return_type 控制星期编号方式 |
判断本月第一天是星期几 |
| SEQUENCE | SEQUENCE(rows, [columns], [start], [step]) |
rows 行数;columns 列数;start 起始值;step 步长 |
一次生成 6 行 7 列的连续日期 |
| MONTH | MONTH(serial_number) |
需要判断月份的日期 | 识别日期是不是当前月份 |
WEEKDAY(DATE($C$2,$F$2,1),2) 末尾的这个 2 非常关键,它指定了返回规则是周一到周日分别对应数字1到7,这样做从周一开始的日历时,日期偏移的逻辑会特别好理解。如果你的日历表头是从周日开始排的,不能只改表头文字,整套日期偏移的逻辑得同步调整才行。
常见错误先看这几个位置
公式返回 #NAME?,大概率是你的Excel版本不支持 SEQUENCE,或者函数名输错了。公式返回 #VALUE!,先检查年份、月份单元格是不是纯数字,别直接把“2026年”“7月”这种带文字的内容放在引用单元格里。要是日期区只显示一串莫名其妙的数字,说明还没把单元格格式改成日期,或者没设置好自定义 d 的格式。
切换到2月之后日期区域没自动更新的话,先检查公式里引用的年份、月份单元格是不是绝对引用了正确的位置,再看条件格式里的起始单元格,是不是和你选中的日期区域左上角单元格对应上了。注意条件格式公式里写的 B6 只是示例,你要根据自己表格里实际选中的左上角单元格来改。
扩展示例:做排班万年历
日期区能自动更新之后,你可以在每个日期单元格下面多留一行填班次信息,也可以单独做一张排班明细表,配合 XLOOKUP、FILTER 自动查出当天的值班人员。更稳妥的做法是先把基础日历日期区做稳,再单独新建一张叫“排班明细”的表,里面设好“日期、姓名、班次、备注”这几个字段,别把查询公式和手动填的备注内容都塞在同一个单元格里,后面维护起来特别麻烦。
到这一步你做出来的日历,已经具备万年历的核心逻辑:只要改年份和月份两个输入值,整个日期区、周末颜色、非本月淡显的效果都会同步更新。后续要加节假日标记、高亮当天日期或者按部门筛选,本质上都是在已经做好的日期区基础上,继续叠加新的条件格式和查找公式就行。


















