更新时间2019-01-13 04:55:27
入职日期 年终奖 每月金额 月数
2018/5/4 1180 150 7月26天
2018/1/1开始
2018/12/31结束
在单元格D2录入公式:
=((YEAR(TODAY())-YEAR(A2))*12+(MONTH(TODAY())-MONTH(A2))+IF(DAY(TODAY())<DAY(A2),-1,0))&"月"&(IF(DAY(TODAY())<DAY(A2),(TODAY()-DATE(YEAR(TODAY())+IF(MONTH(TODAY())=1,-1,0),IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1),DAY(A2))),DAY(TODAY())-DAY(A2))&"天")
在单元格B2录入公式:
=MIN(1800,IF(--LEFT(D2,FIND("月",D2)-1)<3,0,--LEFT(D2,FIND("月",D2)-1)*C2))
用手工可实现
判断年终奖是否大于等于1800,是返回1800,不是用12.31-入职日期-90然后除以30取整乘以150
下一篇:关机蓝屏