A3=WORKDAY(IF(ROW(A1)=1,DATE(B$1+1911,D$1,0),A2+COUNTIF(I:I,A2+1)),(COUNTIF(I:I,A2+1)=0)*1,H$3:H$16)
另解{=SMALL(IF((COUNTIF(H:H,DATE(B$1+1911,$D$1,ROW($1:30)))=0)*(COUNTIF(I:I,DATE(B$1+1911,$D$1,ROW($1:30)))+(MOD(DATE(B$1+1911,$D$1,ROW($1:30))-2,7)<5)),DATE(B$1+1911,$D$1,ROW($1:30))),ROW(A1))
週六日補班 全考慮A3{=MIN(IF(IF(ROW(A1)=1,DATE(B$1+1911,D$1,0),A2)+{1,2}=I$3:I$15,I$3:I$15,WORKDAY(IF(ROW(A1)=1,DATE(B$1+1911,D$1,0),A2),1,H$3:H$15)))
留言
張貼留言