请帮忙看如何计算津贴

cyqiu

有以下500条记录，需要根据入职日期计算工龄，每月根据工龄计算津贴。

假如现在需要计算9月份津贴，员工2024年9月份中旬工龄到达第二个档次时则需要分段计算，即上旬用第一个档次的标准，下旬用第二个档次的标准折算。
工龄与档次标准
5年（含）或以上200元
3年（含）以上5年以下170元
1年（含）以上3年以下150元
1个月（含）以上1年以下80元

具体如下图和附件，请大家多多指导，谢谢。

cyqiu

差不多一个周了，还是没有大神帮忙，顶一顶
希望有大神能帮帮忙，谢谢！

混沌音符

这个天数的计算有点疑问，到底是按天去算还是按月去算，按天的话，具体是怎么算？
1、8月3日入职，9月算的时候，8月3~9月1，这是30天，那假如9月3日入职，10月算的时候，9月3~10月2还是10月1？
2、入职2023/1/31，离职2023/3/2，1月31~3月1是30天，3月2才按满一个月去算吗？

你辅助的单位是月，但第一个例子，不是按60*30天去算，第三第四个例子又是按30天

hxj119

参考公式：

1. =LET(a,IF(C5="",$B$2,C5),b,DATEDIF(B5,SEQUENCE(DAY(a),,a+1,-1),"m"),REDUCE(0,b,LAMBDA(x,y,ROUND(SUM(x+XLOOKUP(y,$J$5:$J$8,$K$5:$K$8,0,-1,-1)/DAY(EOMONTH(a,0))),2))))

复制代码

因按天累加的稍微有些小数点的差异

hxj119

更新一下公式：

1. =LET(a,IF(C5="",$B$2,C5),b,DATEDIF(B5,SEQUENCE(DAY(a),,a+1,-1),"m"),c,REDUCE(0,b,LAMBDA(x,y,x+XLOOKUP(y,$J$5:$J$8,$K$5:$K$8,0,-1,-1))),ROUND(c/DAY(EOMONTH(a,0)),2))

复制代码

ericxzhou

Python+Pandas:
import pandas as pd
import numpy as np

df = pd.read_excel('Sample511.xlsx', usecols='a:g', header=3)
for i in df.index:
    df.loc[i, 'nMonths'] = (pd.Timestamp('2024-09-30').to_period('M') - df.loc[i, '入职日期'].to_period('M')).n
df['bonus1'] = pd.cut(df['nMonths'], bins=[1, 12, 36, 60, np.inf], labels=[80, 150, 170, 200], right=False)
df['bonus2'] = pd.cut(df['nMonths'], bins=[1, 12, 36, 60, np.inf], labels=[0, 80, 150, 170], right=False)

for i in df.itertuples():
    d = i.入职日期.day
    ld = i.离职日期.day if pd.notna(i.离职日期) else 30
    if d <= 2:
        df.loc[i.Index, '结果'] = i.bonus1
    else:
        df.loc[i.Index, '结果'] = i.bonus1 * (ld - d + 2) / 30 + i.bonus2 * (d - 2)/30
print(df.iloc[:, [0,1,2,3,10]])

00JTR00

1. =ROUND(SUMPRODUCT(
   
2. XLOOKUP(INT((IF(C5="",$B$2,C5)-B5)/30),$J$5:$J$8,IFERROR(INDEX($K$5:$K$8, ROW($1:$4) + COLUMN($A1:$B1) - 1),),,-1,-1),
   
3. IF(MIN(ABS(IF(C5="",$B$2,C5)-EDATE(B5,INDEX($J$5:$J$8,ROW($1:$4)))))=0,1,MIN(ABS(IF(C5="",$B$2,C5)-EDATE(B5,INDEX($J$5:$J$8,ROW($1:$4)))))+2)*{1,-1}+{0,1}*DAY(IF(C5="",$B$2,C5))
   
4. )/30,2)

复制代码

丢丢表格

=ROUND(SUM(LET(X,EOMONTH(B$2,-1)+ROW($1:$31),IF(X<=MIN(B$2,C5),LOOKUP(DATEDIF(B5-1,X,"M"),J$5:K$9))))/DAY(B$2),2)

cyqiu

混沌音符 发表于 2024-10-23 10:23
这个天数的计算有点疑问，到底是按天去算还是按月去算，按天的话，具体是怎么算？
1、8月3日入职，9月算的 ...

感谢你的回复。
其实工龄正确来说就是入职第一天到下个月入职对应天数的前一天为满1个月。
2023/1/31入职，2023/2/28就是满1个月。
1、9/3入职，10月算的时候是10/2开始满1个月，10/1是没有津贴，10/2-10/31就是按80/30*30来算了。
2、入职2023/1/31，2023/2/28就是满1个月了，离职2023/3/2，所以算2月份的津贴时，0/30*27+80/30*1
算3月份津贴时，由于3/2离职，所以津贴就是80/30*2

混沌音符

cyqiu 发表于 2024-10-24 09:18
感谢你的回复。
其实工龄正确来说就是入职第一天到下个月入职对应天数的前一天为满1个月。
2023/1/31入 ...

分母也是个问题，“10/2-10/31就是按80/30*30”，那如果10/1-10/31，就是80/30*31？
同理23年2月只有28日，分母一样是30，分子只有28，那23年1月1日入职，23年2月满算也是80/30*28？