求excel高手函数公式计算工龄工资！！

规则：1.入职1年内的员工年假是80小时

2.年假是按照季度进行提取的

3.入职满2年的员工，每年增加1天的年假

4.年假的增加最高为20天，即160小时

>40小时人数
=COUNTIF(G8:G134,">40")Today=TODAY()（公式已修改，到了下面这个日期年假自动释放）工号姓名入职日期起始年假小时数4191342005当前年假剩余4182141852100093刘卓林4017980.5=IF($B$64015,80,IF(E$7-$C8>3650,72,IF(E$7-$C8>3285,64,IF(E$7-$C8>2920,56,IF(E$7-$C8>2555,48,IF(E$7-$C8>2190,40,IF(E$7-$C8>1825,32,IF(E$7-$C8>1460,24,IF(E$7-$C8>1095,16,IF(E$7-$C8>1095,8,))))))))))))/4)=IF($B$64015,80,IF(F$7-$C8>3650,72,IF(F$7-$C8>3285,64,IF(F$7-$C8>2920,56,IF(F$7-$C8>2555,48,IF(F$7-$C8>2190,40,IF(F$7-$C8>1825,32,IF(F$7-$C8>1460,24,IF(F$7-$C8>1095,16,IF(F$7-$C8>1095,8,))))))))))))/4)=SUM(D8:F8)-SUM(H8:P8)0=16100115胡改亚4023192.6633333333333=IF($B$64015,80,IF(E$7-$C9>3650,72,IF(E$7-$C9>3285,64,IF(E$7-$C9>2920,56,IF(E$7-$C9>2555,48,IF(E$7-$C9>2190,40,IF(E$7-$C9>1825,32,IF(E$7-$C9>1460,24,IF(E$7-$C9>1095,16,IF(E$7-$C9>1095,8,))))))))))))/4)=IF($B$64015,80,IF(F$7-$C9>3650,72,IF(F$7-$C9>3285,64,IF(F$7-$C9>2920,56,IF(F$7-$C9>2555,48,IF(F$7-$C9>2190,40,IF(F$7-$C9>1825,32,IF(F$7-$C9>1460,24,IF(F$7-$C9>1095,16,IF(F$7-$C9>1095,8,))))))))))))/4)=SUM(D9:F9)-SUM(H9:P9)4　100122李玉梅4024176.6666666666667=$B$6 ""
(80+IF(E$7-$C10<730,0,IF(E$7-$C10>4015,80,IF(E$7-$C10>3650,72,IF(E$7-$C10>3285,64,IF(E$7-$C10>2920,56,IF(E$7-$C10>2555,48,IF(E$7-$C10>2190,40,IF(E$7-$C10>1825,32,IF(E$7-$C10>1460,24,IF(E$7-$C10>1095,16,IF(E$7-$C10>1095,8,))))))))))))/4 =$B$6 ""
(80+IF(F$7-$C10<730,0,IF(F$7-$C10>4015,80,IF(F$7-$C10>3650,72,IF(F$7-$C10>3285,64,IF(F$7-$C10>2920,56,IF(F$7-$C10>2555,48,IF(F$7-$C10>2190,40,IF(F$7-$C10>1825,32,IF(F$7-$C10>1460,24,IF(F$7-$C10>1095,16,IF(F$7-$C10>1095,8,))))))))))))/4 =SUM(D10:F10)-SUM(H10:P10)16　100186孟瑞华4050871.8333333333333=$B$6 ""
(80+IF(E$7-$C11<730,0,IF(E$7-$C11>4015,80,IF(E$7-$C11>3650,72,IF(E$7-$C11>3285,64,IF(E$7-$C11>2920,56,IF(E$7-$C11>2555,48,IF(E$7-$C11>2190,40,IF(E$7-$C11>1825,32,IF(E$7-$C11>1460,24,IF(E$7-$C11>1095,16,IF(E$7-$C11>1095,8,))))))))))))/4 =$B$6 ""
(80+IF(F$7-$C11<730,0,IF(F$7-$C11>4015,80,IF(F$7-$C11>3650,72,IF(F$7-$C11>3285,64,IF(F$7-$C11>2920,56,IF(F$7-$C11>2555,48,IF(F$7-$C11>2190,40,IF(F$7-$C11>1825,32,IF(F$7-$C11>1460,24,IF(F$7-$C11>1095,16,IF(F$7-$C11>1095,8,))))))))))))/4 =SUM(D11:F11)-SUM(H11:P11)8=8+4100218刘盼盼4059995.3333333333333=$B$6 ""
(80+IF(E$7-$C12<730,0,IF(E$7-$C12>4015,80,IF(E$7-$C12>3650,72,IF(E$7-$C12>3285,64,IF(E$7-$C12>2920,56,IF(E$7-$C12>2555,48,IF(E$7-$C12>2190,40,IF(E$7-$C12>1825,32,IF(E$7-$C12>1460,24,IF(E$7-$C12>1095,16,IF(E$7-$C12>1095,8,))))))))))))/4 =$B$6 ""
(80+IF(F$7-$C12<730,0,IF(F$7-$C12>4015,80,IF(F$7-$C12>3650,72,IF(F$7-$C12>3285,64,IF(F$7-$C12>2920,56,IF(F$7-$C12>2555,48,IF(F$7-$C12>2190,40,IF(F$7-$C12>1825,32,IF(F$7-$C12>1460,24,IF(F$7-$C12>1095,16,IF(F$7-$C12>1095,8,))))))))))))/4 =SUM(D12:F12)-SUM(H12:P12)0=8+40100232蒋丰姣4069664.3333333333333=$B$6 ""
(80+IF(E$7-$C13<730,0,IF(E$7-$C13>4015,80,IF(E$7-$C13>3650,72,IF(E$7-$C13>3285,64,IF(E$7-$C13>2920,56,IF(E$7-$C13>2555,48,IF(E$7-$C13>2190,40,IF(E$7-$C13>1825,32,IF(E$7-$C13>1460,24,IF(E$7-$C13>1095,16,IF(E$7-$C13>1095,8,))))))))))))/4 =$B$6 ""
(80+IF(F$7-$C13<730,0,IF(F$7-$C13>4015,80,IF(F$7-$C13>3650,72,IF(F$7-$C13>3285,64,IF(F$7-$C13>2920,56,IF(F$7-$C13>2555,48,IF(F$7-$C13>2190,40,IF(F$7-$C13>1825,32,IF(F$7-$C13>1460,24,IF(F$7-$C13>1095,16,IF(F$7-$C13>1095,8,))))))))))))/4 =SUM(D13:F13)-SUM(H13:P13)0　

假如：员工入职日期在B2栏，在C2栏输入工龄工资：=TEXT(TODAY()-B2,"Y")*10就ok了

=rounddown(（目前月份最后一天日期-入职日期）/365,0))*10