谢谢, 我原来一直以为我函数还可以...看了这个....哎......回家研究一下...非常感谢!!! K5=SUMPRODUCT(1*(ABS(ABS(MOD(I5+ROW(INDIRECT("1:"& TEXT((J5-I5)*1440,"0")))/1440,1)-0.5)-2.5/24)<=1/12))/60 ------------------ 解释一下: TEXT((J5-I5)*1440,"0") 两个时间相差分钟数 ROW(INDIRECT("1:"& TEXT((J5-I5)*1440,"0"))) 按分钟取所有时间 I5+ROW(INDIRECT("1:"& TEXT((J5-I5)*1440,"0")))/1440 按天取所有时间 MOD(I5+ROW(INDIRECT("1:"& TEXT((J5-I5)*1440,"0")))/1440,1)排除日期干扰,取时间 ABS(MOD(I5+ROW(INDIRECT("1:"& TEXT((J5-I5)*1440,"0")))/1440,1)-0.5)某分钟跟12点相差时间绝对值 ABS(ABS(MOD(I5+ROW(INDIRECT("1:"& TEXT((J5-I5)*1440,"0")))/1440,1)-0.5)-2.5/24)<=1/12你的上班时间 SUMPRODUCT(1*(ABS(ABS(MOD(I5+ROW(INDIRECT("1:"& TEXT((J5-I5)*1440,"0")))/1440,1)-0.5)-2.5/24)<=1/12))累计真正工作分钟 SUMPRODUCT(1*(ABS(ABS(MOD(I5+ROW(INDIRECT("1:"& TEXT((J5-I5)*1440,"0")))/1440,1)-0.5)-2.5/24)<=1/12))/60 转换成小时 |