需求说明

制造业企业在对原材料日常管理中，需要对供应商提供的原材料的保质期信息进行管理，确保材料在保质期内得到妥善储存和使用，以保证产品质量和安全，保质期信息包括生产日期、有效期限以及储存条件。保质期、有效期的表示方式通常为“X天”、“X个月”、“X年”，要求仓库人员快速计算原材料“到期日”，对已过期的原料在“到期状态”一栏标注“已过期”，并对该行记录标红；对一周内即将到期的原料在“到期状态”一栏标注“一周内即将到期”，并对改行记录标浅红色；对10天内即将到期的原料在“到期状态”一栏标注“10天内即将到期”，并对改行记录标黄色。

思路

根据不同的保质期，思路如下：

1. 保质期为“X天”时，到期日为【生产日期】+X。

2. 保质期为“X个月”时，到期日为EDATE(【生产日期】,X)。

3. 保质期为“X年”时，到期日为EDATE(【生产日期】,X*12)。

公式的关键在于如何将保质期中的X提取出来，因为保质期中包含的中文字符个数不确定，所以我们借助SUBSTITUTE函数进行替换。

解决方案

使用WPS表格的公式及条件格式完成上述需求。

1. 在E2单元格中输入公式“=IFS(ISBLANK(D2),"",RIGHT(D2,1)="天",C2+SUBSTITUTE(D2,"天","")*1,RIGHT(D2,2)="个月",EDATE(C2,SUBSTITUTE(D2,"个月","")*1),RIGHT(D2,1)="年",EDATE(C2,SUBSTITUTE(D2,"年","")*12))”。

公式解释如下：

通过IFS函数进行判断，满足不同条件下，返回与第一个TRUE条件对应的值。

当【保质期】为空时，【到期日】返回空。

当【保质期】最后一个字符为“天”时，先将【保质期】的“天”替换为空，即将【保质期】变为数值型X，【到期日】等于【生产日期】加上X天。

当【保质期】最后两个字符为“个月”时，先将【保质期】的“个月”替换为空，即将【保质期】变为数值型X，【到期日】等于【生产日期】加上X个月。

当【保质期】最后一个字符为“年”时，先将【保质期】的“年”替换为空，即将【保质期】变为数值型X，【到期日】等于【生产日期】加上X*12个月（因为1年为12个月）。

2. 在F2单元格中输入公式“=IFS(E2<TODAY(),"已过期",E2<(7+TODAY()),"一周内即将到期",E2<(10+TODAY()),"10天内即将到期",E2>=(10+TODAY()),"")”。

公式解释如下：

通过IFS函数进行判断，满足不同条件下，返回与第一个TRUE条件对应的值。

当【到期日】早于当前日期，【到期状态】等于“已过期”。

当【到期日】早于“当前日期+7天”时，【到期状态】等于“一周内即将到期”。

当【到期日】早于“当前日期+10天”时，【到期状态】等于“10天内即将到期”。

当【到期日】晚于或等于“当前日期+10天”时，【到期状态】不显示。

3. 根据不同【到期状态】设置条件格式，以颜色标注不同记录行。

新建格式规则，规则类型选择“使用公式确定要设置格式的单元格”。分别设置3条规则，公式分别为：①“=$F1="已过期"”②“=$F1="一周内即将到期"”③“=$F1="10天内即将到期"”。格式就根据需求选择对应底色即可。

结果展示

结束语

本方案以原料保质期到期日统计为例进行展示，而实际工作中到期日提醒应用十分广泛，比如：企业各项资质到期管理、员工退休管理、工作任务到期提醒等方面。这些应用虽然各有不同，但基本思路是一致的，希望通过这个案例能够为大家提供一个通用的思路。