已知入职日期和离职日期(离职日期空的为在职),求计算2018年年内(20180101~20181231)在职天数的excel 计算公式?
在C2单元格输入以下公式,然后向下填充公式
=DATEDIF(MAX("2018-1-1",A2),IF(B2="","2018-12-31",B2),"d")+1
详见附图示例
=IFERROR(IF((DATEDIF(MAX("2020-1-1",A2),IF(B2="","2020-12-31",IF(B2>DATE(2020,12,31),"2020-12-31",B2)),"d")+1)>12,12,(DATEDIF(MAX("2020-1-1",A2),IF(B2="","2020-12-31",IF(B2>DATE(2020,12,31),"2020-12-31",B2)),"d")+1)),"")