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 转换成小时 |