D3:E11=ROUND(SUMPRODUCT(FREQUENCY(ROW(INDIRECT(LEFT($C3,FIND("-",$C3)-1)*1440+1&":"&(MID($C3,FIND("-",$C3)+1,9)+(LEFT($C3,FIND("-",$C3))-MID($C3,FIND("-",$C3)+1,9)>0))*1440)),{5,21,29}*60)*IF(COLUMN(A1)=1,{0;1;0;0},{1;0;1;1}))/60,1)
另解=ROUND(SUMPRODUCT(FREQUENCY(ROW(INDIRECT(LEFT($C3,FIND("-",$C3)-1)*1440+1&":"&(MID($C3,FIND("-",$C3)+1,9)+(LEFT($C3,FIND("-",$C3))-MID($C3,FIND("-",$C3)+1,9)>0))*1440)),{5,21,29}*60)*IF(COLUMN(A1)=1,{0;1;0;0},{1;0;1;1}))/60,1)
留言
張貼留言