本帖最后由 1786024734 于 2024-7-20 11:14 编辑
缴费年限挂钩调整- =MAX(11,SUM(TEXT(F4-{0,15,30},"0;!0")*{1.1,0.7,0.4}))
复制代码
高龄倾斜(任选一)- =LOOKUP(DATEDIF(TEXT(MID(B4,7,6+(LEN(B4)=18)*2),"#-00-00"),"2023-12-31","Y"),{50,65,70,80},{0,12.5,25,50})
- =MAX((DATEDIF(TEXT(MID(B4,7,6+(LEN(B4)=18)*2),"#-00-00"),"2023-12-31","Y")>={50,65,70,80})*{0,12.5,25,50})
复制代码
其它两处按楼主原公式。
另外:从楼主D列公式看,兼顾了身份证号码15位的情况,但C列公式却疏漏了这一点,所以C列公式应为:
- =IF(--B4,IF(MOD(RIGHT(LEFT(B4,15+(LEN(B4)=18)*2)),2),"男","女"),)
复制代码 D列公式可去掉VALUE:- =IF(--B4,TEXT(TODAY()-TEXT(MID(B4,7,6+(LEN(B4)=18)*2),"#-00-00"),"y年mm个月"),"")
复制代码
|