最近在公司要幫忙追蹤大家資料繳交的狀況，主管希望我可以幫忙寫一個公式來計算大家從需求提出到繳交資料的耗時天數，來評量每個部門的回答效率，一開始只是寫了很簡單的A-B計算，但因這個案子持續將近一個月，中間又有雙十連假，因此希望公式可以升級，自動扣除掉例假日與國慶日。

來拆解這個公式：

=IF(ISBLANK(B2)," ",IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(TODAY(),2)-WEEKNUM($K$1,2,1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

先以必要輸入資料格B欄來做判定，若B欄值為空值，則G欄亦為空值，此辦法可以一次套用整個G欄，就不用每次都拉一次公式。

=IF(ISBLANK(B2),” “,IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(TODAY(),2)>WEEKNUM($K$1,2),1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

如果未繳交資料，則以現在時間Now()減掉需求提出時間(B欄)，得到總經歷時間

=IF(ISBLANK(B2),” “,IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(TODAY(),2)>WEEKNUM($K$1,2),1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

用WEEKNUM()的公式，來計算現在是一整年當中的第幾週，Today(),2，後面的2是表示一個禮拜的第一天為星期一，再將兩個週數相減*

=IF(ISBLANK(B2),” “,IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(TODAY(),2)>WEEKNUM($K$1,2),1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

如果今天大於K1(國慶連假)，則再扣掉一天

=IF(ISBLANK(B2),” “,IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2–IF(WEEKNUM(TODAY(),2)>WEEKNUM($K$1,2),1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

如果已繳交(F欄)則改以繳交日期減掉需求提出日期(B欄)作為所耗時間(G欄)，同樣扣除相減週數*2與國慶連假