如何在excel中计算本年度工龄月数,只要月份(15号(含)之前算当月,15号后入职算下个月入职)如:A员工入职时间:2021年5月14日,B员工入职时间:2021年5月16日,C员工入职时间2019年2月1日,求自动计算这3个员工本年度(2021年12月31日止)在职月数公式?
【答案更新】
刚才想复杂了,换个思路:
如果他的入职年,早于今天,他的入职月份就取1,
否则是今年入职的话:
如果他的入职日了<=15,就取他的实际月份为入职月份,
如果他的入职日子>15,就在他的实际月份上加1作为入职月份。
最后,本月月份-入职月份,就会得出月数了。
看下图:
=MONTH(TODAY())-IF(YEAR(A2)<YEAR(TODAY()),1,MONTH(A2)+(DAY(A2)>15))
完善一下:
上面的公式在A2尚未填数据的时候会得出当前的月分11,
需要判断A2是否为空,A2为空则返回空文本,最终公式如下:
=IF(A2="","",MONTH(TODAY())-IF(YEAR(A2)<YEAR(TODAY()),1,MONTH(A2)+(DAY(A2)>15)))
需要额外+1的话就如下:
=IF(A2="","",MONTH(TODAY())-IF(YEAR(A2)<YEAR(TODAY()),1,MONTH(A2)+(DAY(A2)>15))+1)
你在评论中提出的第二个问题,答案如下:
=IF(A1="","",MIN(2,DATEDIF(A1,YEAR(TODAY())&"-12-31","Y")/10+1))
其中YEAR(TODAY())是从今天的日期中提取年份,
再连接上"-12-31"组成今年最后。
这样就不局限在2021年使用了。
其中YEAR(TODAY())&"-12-31"也可以这样写:
DATE(YEAR(TODAY()),12,31)
追问公司就是规定当月15日(含)前入职才算当月入职。所以15日后入职工龄算下个月开始。
追答当年入职的,比如5月14号入职,那是按5月1号起算,还是按5月14号起算?
截止日期,是截止到本月的1号呢,还是截止到今天当天呢?
5月15日前入职都以5月1日起算。就是相当于当月入职多于15天就算全月了。
追答答案已更新,看主答区。
又发现个问题,如果A2尚未填数据时,A2会被处理成2021-1-1,所以结果也会返回一个月数。你再套上一层IF判断,如果A2为空时,则返回空文本。
=IF(A2="","",那么公式)
好像可以换个思路,再等一下。
整个公式要加1,才能正确显示到2021年12月31日的月份数。
追答答案重新更新了,简单多了。你看需不需要在此公式的基础上再加1,你自己决定。
需要的话,就在公式的末尾+1即可。
Excel知道身份证号怎么计算年龄(周岁)?
后来有人问,如果要精确到实际天数,计算出的结果是“X年X个月零X天”的形式,该怎么算?
要解决这个问题,我们需要用到DATEDIF函数、连接符&,忘了的同学可以看下面这些文章回顾一下。
Excel计算两个日期之间相差的天数、整月数、整年数—DATEDIF函数
Excel合并多个单元格中的内容
1.计算年数
这一步比较简单,我们将公式写为:
=DATEDIF(入职日期,截止日期,”y”)
2.计算月数
大家是否还记得在介绍DATEDIF函数的时候,我们讲过一个参数”ym”,意思是忽略年份计算整月数,我们把”m”和”ym”做一个对比。
=DATEDIF(“2018/4/24”,”2020/3/23”,”m”),结果是22;
=DATEDIF(“2018/4/24”,”2020/3/23”,”ym”),结果是10;
可以看到参数ym满足我们的要求。因此,公式写为:
=DATEDIF(入职日期,截止日期,”ym”)
3.计算天数
参考计算月,DATEDIF函数还有一个参数是md,意思是忽略年、月计算天数,因此,公式写为:
=DATEDIF(入职日期,截止日期,”md”)
4.汇总公式
在实际工龄计算中,如果入职日期是2020/3/23,截止日期是2020/3/24日,如果用公式计算=DATEDIF(入职日期,截止日期,”d”),结果是1,但实际工龄应该是2天,因此如果需要这么计算,需要将截止日期+1。
最终公式写为:
=DATEDIF(入职日期,截止日期+1,”y”)&”年”&DATEDIF(入职日期,截止日期+1,”ym”)&”个月零”&DATEDIF(入职日期,截止日期+1,”md”)&”天”
示例如下图。追问
不是这样的,要求以15日为限,15日(含)前入职算这个月入职,15日后入职算下个月入职。而且只计算本年度的月数,前两年入职也一样,只算本年度在职月数。
本回答被网友采纳