帮一客户做个EXCEL多条件判断计算,如下：希望设置完公式以后，能自动根据入职日期和离职日期计算出来每个季度在职天数，折腾半天总算搞定：

http://s4/middle/795879f7tc96526fdefa3&690

用到的函数有：

if(条件，true时返回值,false时返回值),

and(条件1，条件2，……)

datedif(日期,日期,"d")

整体函数形式为：if(and(条件1，条件2，……),datedif(),0)

考虑到入职日期、离职日期跟季度开始终止日期的关系分如下情况：

第一种情况：if(and(a6<="2012-1-1",b6>="2012-3-31"),datedif("2012-1-1","2012-3-31","d"),第二种情况)
第二种情况：if(and(a6<="2012-1-1",b6<="2012-3-31"),datedif("2012-1-1",b6,"d"),第三种情况)
第三种情况：if(and(a6>="2012-1-1",a6<="2012-3-31",b6>="2012-3-31"),datedif(a6,"2012-3-31","d"),第四种情况)
第四种情况：if(and(a6>="2012-1-1",a6<="2012-3-31",b6<="2012-3-31"),datedif(a6,b6,"d"),0)

嵌套后：

=if(and(a6-"2012-1-1"<=0,b6-"2012-3-31">=0),datedif("2012-1-1","2012-3-31","d"),if(and(a6-"2012-1-1"<=0,b6-"2012-3-31"<=0),datedif("2012-1-1",b6,"d"),if(and(a6-"2012-1-1">=0,a6-"2012-3-31"<=0,b6-"2012-3-31">=0),datedif(a6,"2012-3-31","d"),if(and(a6-"2012-1-1">=0,a6-"2012-3-31"<=0,b6-"2012-3-31"<=0),datedif(a6,b6,"d"),0))))

但发现有时候会出错：

所以又加了一个IFERROR（）函数屏蔽下：

=IFERROR(IF(AND(A3-"2012-1-1"<=0,B3-"2012-3-31">=0),DATEDIF("2012-1-1","2012-3-31","d"),IF(AND(A3-"2012-1-1"<=0,B3-"2012-3-31"<=0),DATEDIF("2012-1-1",B3,"d"),IF(AND(A3-"2012-1-1">=0,A3-"2012-3-31"<=0,B3-"2012-3-31">=0),DATEDIF(A3,"2012-3-31","d"),IF(AND(A3-"2012-1-1">=0,A3-"2012-3-31"<=0,B3-"2012-3-31"<=0),DATEDIF(A3,B3,"d"),0)))),0)

其他季度跟上面类似，只是季度开始终止日期不同而已：

第二季度：

=IFERROR(IF(AND(A4-"2012-4-1"<=0,B4-"2012-6-30">=0),DATEDIF("2012-4-1","2012-6-30","d"),IF(AND(A4-"2012-4-1"<=0,B4-"2012-6-30"<=0),DATEDIF("2012-4-1",B4,"d"),IF(AND(A4-"2012-4-1">=0,A4-"2012-6-30"<=0,B4-"2012-6-30">=0),DATEDIF(A4,"2012-6-30","d"),IF(AND(A4-"2012-4-1">=0,A4-"2012-6-30"<=0,B4-"2012-6-30"<=0),DATEDIF(A4,B4,"d"),0)))),0)

第三季度：

=IFERROR(IF(AND(A4-"2012-7-1"<=0,B4-"2012-9-30">=0),DATEDIF("2012-7-1","2012-9-30","d"),IF(AND(A4-"2012-7-1"<=0,B4-"2012-9-30"<=0),DATEDIF("2012-7-1",B4,"d"),IF(AND(A4-"2012-7-1">=0,A4-"2012-9-30"<=0,B4-"2012-9-30">=0),DATEDIF(A4,"2012-9-30","d"),IF(AND(A4-"2012-7-1">=0,A4-"2012-9-30"<=0,B4-"2012-9-30"<=0),DATEDIF(A4,B4,"d"),0)))),0)

第四季度：

=IFERROR(IF(AND(A4-"2012-10-1"<=0,B4-"2012-12-31">=0),DATEDIF("2012-10-1","2012-12-31","d"),IF(AND(A4-"2012-10-1"<=0,B4-"2012-12-31"<=0),DATEDIF("2012-10-1",B4,"d"),IF(AND(A4-"2012-10-1">=0,A4-"2012-12-31"<=0,B4-"2012-12-31">=0),DATEDIF(A4,"2012-12-31","d"),IF(AND(A4-"2012-10-1">=0,A4-"2012-12-31"<=0,B4-"2012-12-31"<=0),DATEDIF(A4,B4,"d"),0)))),0)

 当然可以用