大家好,我是小鱼。
今天跟大家分享一个WPS表格中根据出生日期,设置生日提醒,并且根据距离生日天数自动标记数据颜色。简单又实用,一个公式轻松搞定!
接下来我们先学习一下需要使用到的函数,然后再根据实例让大家彻底掌握必备的生日自动提醒技能。
函数简介:
1、TEXT函数
功能:TEXT函数是一个非常强大的转换函数,也是我们日常工作中经常会使用到的。它的结构也比较简单,只需要两个参数。
语法:=TEXT(值,数值格式)
第一参数值:为数值、计算结果为数字值的公式,或对包含数字值的单元格的引用。
第二参数数值格式:为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。
2、DATEDIF函数
功能:DATEDIF函数,主要用于计算两个日期之间的天数、月数或年数。其返回的值是两个日期之间的年\月\日间隔数。
语法:=DATEDIF(开始日期,终止日期,比较单位)
第一参数开始日期:为一个日期,它代表时间段内的第一个日期或起始日期;
第二参数终止日期:为一个日期,它代表时间段内的最后一个日期或结束日期;
第三参数比较单位:为所需信息的返回类型。
3、TODAY函数
功能:获取电脑系统今天的日期
语法:=TODAY()
这个函数没有参数,直接使用即可
4、DATE函数
作用:构建一个指定的日期,经常用于构建一串日期的开始,达到动态构建一段时间的效果
语法:=DATE(年份,月份,日)
5、YEAR、MONTH、DAY函数
功能:获取指定日期的年份、月份、日
语法:
=YEAR(日期),获取指定日期的年份,比如YEAR(2023-12-4),即可得到“2023”,MONTH函数和DAY函数同理,分别得到的是“12”、“4”。
Excel中设置生日自动提醒详细步骤:
如下图所示,我们根据员工出生日期,分别获取距离生日剩余天数,生日提醒备注,以及自动根据距离生日7天、30天自动标注不同的颜色。
一、计算距离生日剩余天数
在目标单元格中输入公式:
=DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(C2),DAY(C2)),"YD")
解析:
①第一个参数通过TODAY()函数当前的日期,然后用DATE函数把出生日期转换为今年的生日为第二参数。
②第三参数按年"YD",就是忽略年,返回整天数。比如说第一个员工“王晓”出生日期是“1995-12-13”,今年的生日就是“2023-12-13”今天日期是“2023-12-4”,那就是12-13减去12-4,返回天数是9天。当返回负数时就是今年的生日已过。
二、制作生日提醒文字备注
在目标单元格中输入公式:
=TEXT(DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(C2),DAY(C2)),"YD"),"还有0天生日;生日已过;今天生日")
解析:
TEXT函数可以格式化数字。公式中第一参数是获取距离生日天数;第二个参数分为四段,依次为天数>0显示“还有多少天生日”,<0显示“生日已过”,=0显示“今天生日”,如果天数不是数值的话,显示本身。
三、通过条件格式,自动标注颜色提醒
我们根据距离生日天数,分别标注距离7天和距离30天的颜色,需要使用条件格式
1、设置距离7天自动提醒
①首先选中除标题行以外的其它数据区域,然后点击【开始】菜单栏下的【条件格式】工具栏中的“新建规则选项“,如下图
②在弹出的“新建格式规则对话框” ,在规则类型下拉菜单中选择“使用公式确定要设置格式的单元格”。输入公式:
=AND($E2>0,$E2<=$H$2)
点击下面的“格式”选择红色,点击确定,这样到期的合同就会自动填充成红色了。
2、设置距离30天自动提醒
①同样首先选中除标题行以外的其它数据区域,然后点击【开始】菜单栏下的【条件格式】工具栏中的“新建规则选项“
②在弹出的“新建格式规则对话框” ,在规则类型下拉菜单中选择“使用公式确定要设置格式的单元格”。输入公式:
=AND($E2>0,$E2<=$H$3)
点击下面的“格式”选择制定颜色,点击确定,这样到期的合同就会自动填充成颜色了。
当我们设置了距离30天自动提醒后,我们发现距离7天自动提醒失效了,这是为什么呢?
其实原因很简单,因为我们设置了两个条件格式,默认会先运行最上面的条件格式,满足条件就不往下运行了。所以我们需要设置一下条件格式先后顺序,把7天自动提醒条件格式放到最上面。
方法:选中数据区域→点击【开始】-【条件格式】-【管理规则】→把7天自动提醒条件格式移动到最上面,最后点击【确定】即可
希望这篇文章能对你有所帮助。点赞收藏不迷路呦!