Excel日历备忘录制作教程，日程管理工具

弑神之狂舞

简介


本模板适用于记录长时间段的循环事项，例如员工生日、年度结算时间、每个月的特定工作时间节点、纪念日。同时可以用于记录个人备忘事项，采用日历的形式，按要求创建sheet页可以自动生成对应月份日历，支持阴历、阳历、节日、周数等信息展示
实现效果

• 通过日历的形式展示备忘内容
  
• 日历支持阴历、阳历显示，支持节日显示，支持根据月份生成对应时间日历
  

• 节日信息可配置，支持自定义节日、纪念日等特定日期
  

制作步骤

1.制作思路

1.1 明确日历切换、生成的方式

切换方式决定了整个工具的设计思路，本模板采用平铺式的备忘信息展示方式。
识别sheet页名称信息生成对应的日历，新建一个sheet页，命名为年月的六位数字，即可生成对应日历，例如：“202212”识别为2022年12月。
知识点：使用cell函数获取文件名、sheet页名称信息
1.2 实现阳历生成

根据1.1获取的年月信息，展示对应的日历
日历需要和星期进行对应，每个月需要展示上月和下月的衔接日，当日需要有醒目标识


1.3 实现阴历生成

根据1.2的阳历日期生成阴历，阴历有自己独有的文字排版，如11月叫冬月，12月叫腊月，二十叫做年，十以内为初
目前采用的是直接由excel公历时间生成农历时间后进行格式调整，这里实际上是不严谨的，因为农历有闰月规则，不过目前得出的日期近十年抽样看没有什么差别，所以就直接采用这种方式了
知识点：公历转农历
1.4 添加节日

有了公历时间和农历时间，展示对应时间的节日，需要建另一张节日的表，支持自己新增节日或纪念日
1.5 添加备忘事项

日历的基础格式完成了，增加备忘事项列
1.6 优化样式

最后优化一下表格样式，成品样式配色模仿百度万年历
2.详细步骤

2.1获取指定年月

2.1.1创建excel表，创建两个sheet页，第一个命名为“配置”，第二个命名为当前年月，效果如图



2.1.2获取sheet2的名称，在sheet2对应位置填入如下公式



①D1：=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,1000)
【截取文件名中“]”符号后面的内容，也就是202210这个文本了】
②A2：=DATE(MID(D1,1,4),MID(D1,5,6),1)
【分别提取了D1文本中的年月，组成了当前月1号的日期】
③C2：=WEEKDAY(A2,2)
【求出A2所代表的当月1号是星期几，本模板采用的是周一是一周第一天，所以函数第二个参数是2】
2.2实现公历

2.2.1先搭建基础日历架构，格式及各部分内容见下图



2.2.2添加日期辅助计算单元格

如下图（数字为这一天在这个表是第几天，可以看到这个日历是一个六周的表，由于当月一号可以是周一到周日任何一天，所以整个表比实际的31天要多，通常日历展示都是这样设置的，如win电脑系统日历）




2.2.3添加公历日期单元格，公式为=$A$2+B10-$B$2

【当月1号日期+当日在表格天数-当月1号星期几】进行拖拽填充，公式效果如下


2.2.4将辅助单元格文本颜色调为白色，通过右键自定义单元格将公历日期单元格改为只展示日，居中加粗，效果如下





公历日历的基础功能到此完成，可以右键sheet页名称→移动或复制→选择移至最后，勾选建立副本，修改sheet页名称为指定年月格式，可以看到新页面的日期自动生成为对应月份


2.3实现农历

2.3.1配置sheet页中，创建农历月和农历日表



2.3.2在农历日期单元格填入公式，公式以及填充后效果如下

=INDEX(配置!$A$2:$A$13,TEXT(B6,"[$-130000]m"))&INDEX(配置!$C$2:$C$31,TEXT(B6,"[$-130000]d"))
【农历转换函数和INDEX函数的组合使用，分别返回的是配置表中对应的农历月和农历日，用&做了拼接展示】


填充公式，调整样式，居中换个字体，效果如下图


2.4实现节日展示

2.4.1在配置sheet页添加农历节日和公历节日，注意列的位置

注意阳历日期列为文本格式


2.4.2在节日单元格填入公式

=IFERROR(VLOOKUP(TEXT(B6,"MM/DD"),配置!$I:$J,2,0),"")&IFERROR(VLOOKUP(B7,配置!$F:$G,2,0),"")
【三层公式嵌套，一一拆解
IFERROR函数作用是当没有返回数据的时候显示为空
里面是公历节日和农历节日的拼接，如果同一天有两个节日，就进行拼接展示
公历节日查询方法是将公历日期转成特定格式文本，用vlookup函数查询（为什么要转成特定格式文本，为什么上面配置表中的公历日期要用文本格式，如果用日期格式的话，每年的年份会变，VLOOKUP就找不到对象了，所以采用文本格式）
农历节日查询方法是用农历日期进行VLOOKUP】
拖拽填充
之后进行一下数据验证，在配置中的节日列添加两个当月的测试节日，看一下日历中是否有显示


显示结果如下图，正好对应上面添加的数据，准确无误


2.5添加备忘事项列

插入备忘事项列，如下图


2.6优化表格样式

通过条件格式，设置在当前表格中非当前月日期置灰，设置今天日期醒目展示
2.6.1设置周一到周五单元格条件格式，公式、选中区域、样式，步骤如下



条件格式→新建规则→使用公式确定要设置的单元格格式，公式如下，设置格式--文字颜色为灰色
=MONTH(B6)<>MONTH($A$2)
【日期的月份不等于当前展示月的月份，则置灰】
确定后再次点击条件格式→管理规则，可以看到刚才新建的规则，点击箭头设置应用区域


依次选中周一到周五的公历日期，用英文逗号隔开，选好后点击应用，会发现非本月内容成功变成了灰色，效果如下图


2.6.2设置周六周日公历日期单元格条件格式，公式、选中区域、样式，步骤如下

条件格式→新建规则→使用公式确定要设置的单元格格式，公式如下，设置格式--文字颜色为浅红色
=MONTH(B36)<>MONTH($A$2)
确定后再次点击条件格式→管理规则，可以看到刚才新建的规则，点击箭头设置应用区域


效果如下


2.6.3设置当前日期高亮显示

条件格式→新建规则→使用公式确定要设置的单元格格式，公式如下，设置格式--填充
=B6=TODAY()
【单元格日期等于今天日期，则填充单元格】
确定后再次点击条件格式→管理规则，可以看到刚才新建的规则，点击箭头设置应用区域，效果如下


2.6.4细节优化

增加表格颜色、边框，添加列表头当前周，公式为B5="第"&WEEKNUM(B6,2)&"周",依次复制粘贴即可，最终样式如下


至此，简单的日历备忘录就做好了
2.7 进阶版日历备忘录

进阶版的日历备忘录，部分使用VBA实现，更加简洁美观


2.7.1支持点击时间切换对应备忘录



2.7.2支持循环按周次和按年度展示备忘信息



2.7.3支持备忘信息分类型展示



2.7.4三类颜色显示当天是否有节日（纪念日）、周次循环时间/年度循环事件、分类备忘事项



2.7.5直接可用，生成日历无需新建sheet页



公众号：产品大白，回复  备忘录 获取源文件（下方链接也可以）
或者评论区留邮箱，我发给你，不过回复不及时，会晚几天。
源文件下载链接

加群交流

历史作品