根据工龄发补贴的excel公式

Question

已经知道每个员工的工龄
一、工作满1年至2年（含）的，补贴1000元以内；
二、工作满2年至5年（含）的，补贴3000元
三、工作满5年至10年（含）的，补贴6000元
四、工作满10年至15年（含）的，补贴8000元
五、工作满15年至20年（含）的，补贴10000元
六、工作满20年以上的，补贴11000元
请问怎么编辑公式
另外咨询一下工龄计算问题，这个补贴的发放起始时间是每年10月份，所以每次计算工龄的时候都是以当年10月份为统计截止点的，比如有的人1995年12月参加工作的，到2005年10发这个补贴的时候工龄就是9年，请问怎么写公式（知道所有人参加工作的时间），谢谢

Answer 1

假设C2单元格是员工的工龄
=IF(C2<1,0,IF(C2<2,1,IF(C2<5,3,IF(C2<10,6,IF(C2<15,8,IF(C2<20,10,11))))))*1000 下拉复制公式
或
=VLOOKUP(C2,{0,0;1,1;2,3;5,6;10,8;15,10;20,11},2,1)*1000
下拉复制公式

假设B2单元格是参加工作日期,计算到2005年10月时的工龄,公式如下
=DATEDIF(B2,"2005/10","Y") 下拉复制公式

如果要改成到系统当前年份的10月的工龄,则公式为
=DATEDIF(B2,YEAR(NOW())&"/10","Y") 下拉复制公式

公式中YEAR(NOW())&"/10"目前得出的值是"2012/10",到明年1月1日,这个值就是"2013/10"

不满一年要按一年算的话,你要定一下规则,是满多少个月有算一年
假设工作满6个月的算一年
=DATEDIF(B2,"2005/10","Y")+IF(DATEDIF(B2,"2005/10","YD")>=6,1,0)
=DATEDIF(B2,YEAR(NOW())&"/10","Y")+IF(DATEDIF(B2,YEAR(NOW())&"/10","YD")>=6,1,0)追问

规则就这么一条，2011年1月参加工作的，2011年12月统计的可以算做1年，2010年1月参加工作的，2012年12统计可以算作2年，其它没了

追答

B2单元格是参加工作日期,E2单元格是统计日期
=DATEDIF(B2,E2,"Y")+IF(MONTH(B2)=1,1,0)
=DATEDIF(B2,E2,"Y")+IF(MONTH(B2)=1,1,0)

Answer 2

假设工龄在d列,补贴在e列，则补贴公式为：
=IF(D2>20,11000,IF(D2>=15,10000,IF(D2>=10,8000,IF(D2>=5,6000,IF(D2>=2,3000,IF(D2>=1,1000,0))))))
假设以10月15日为计算点：则工龄公式为：
DATEDIF(A2,DATE(YEAR(NOW()),10,15),"y")

Answer 3

问题1:
假设工作年限在C1,在D1输入:
=1000*lookup(C1,{0,1,2,5,10,15,20},{0,1,3,6,8,10,11})
下拉填充
问题2:
假设起始时间在A1,统计终止时间在B1,在C1输入:
=--TEXT(B1-A1,"Y")
下拉填充追问

第一个公式还没测，问题2的公式有问题，员工2011年1月参加工作，2011年12月统计，应该有1年工龄，但用这个公式测算，统计出来是0，麻烦再给解答一下，谢谢

追答

首先,2011年1月参加工作,到2012年1月工作才满1年,而不是2011年12月满1年;
其次,你是每年的的10月份统计工龄,不是12月份统计.

追问

那如果统计时间是12月呢，还有计算规则就是这样2011年1月参加工作，到2011年12月统计的时候，可以算做1年呢？可以解决么？还且刚那个公式还有问题，1990年12月参加工作的，2011年12月统计，应该是21年工龄，但实际显示20年

追答

=SUMPRODUCT(YEAR(A1:B1)*{-1,1})+INT(SUMPRODUCT(MONTH(A1:B1)*{-1,1})/11)
或者
=TEXT(B1-A1,"Y")+INT(MOD(TEXT(B1-A1,"Y.MM"),1)/0.11)
把该列设置成常规格式