錦子老師

葉XX：「無緣老公，我最近因工作需要，要將一串英文+數字共7碼轉換成日期YYYYMMDD。

例：ZUJ3X61，只須取第2-4碼。

第2碼代表年份，第3碼代表月份，第4碼代表該月第幾週。

第2碼：如A代表1998年、B代表1999年...以此類推。

第3碼：如A代表01月、B代表02月...以此類推。

第4碼：如1代表當月第1週、2代表當月第2週，『顯示須為當週的週五日期』。

故結果會是20181019。

錦子老師：「無緣老婆，其實這有點麻煩，讓無緣老公一步一步教妳如何完成。」

首先建立一個英文字母與年、月的對照表，如下圖所示。

點取B2儲存格，輸入公式「=VLOOKUP(MID(A2,2,1),$H$2:$J$27,2,0)」後，按ENTER鍵完成輸入，並將公式複製到B3:B8儲存格，完成各個物料料號代表的年計算。

【公式說明】

1  MID(A2,2,1) 傳回A2儲存格內容第2個字元。

2  VLOOKUP(MID(A2,2,1),$H$2:$J$27,2,0) 將1傳回的字元與H2:J27儲存格範圍的H欄作比對，若找到相同的時，再傳回第2欄(I欄)資料。

這邊公式也可以更改成「=CODE(UPPER(MID(A2,2,1)))+1933」。

Code(A)會傳回65這個值，依此類推，由於A是1998，故要加上933這個值才可以，UPPER是將抓到的英文字轉換為大寫方便計算。

點取C2儲存格，輸入公式「=VLOOKUP(MID(A2,3,1),$H$2:$J$13,3,0)」後，按ENTER鍵完成輸入，並將公式複製到C3:C8儲存格，完成各個物料料號代表的月計算。

這邊公式也可以更改成「=TEXT(CODE(UPPER(MID(A2,3,1)))-64,”00”)」。

點取D2儲存格，輸入公式「=TEXT(IF(WEEKDAY(DATE(B2,C2,1),1)=7,MID(A2,4,1)*7,0-WEEKDAY(DATE(B2,C2,1),1)+MID(A2,4,1)*7),"00")」後，按ENTER鍵完成輸入，並將公式複製到D3:D8儲存格，完成各個物料料號代表的日計算。

【公式說明】

1  DATE(B2,C2,1) 傳回B2儲存格(年)、C2儲存格(月)的第1天數值。

2  WEEKDAY(DATE(B2,C2,1),1) 傳回1的日期數值的星期值，第二個參數1的定義如下圖所示。

3  MID(A2,4,1)*7 傳回A2儲存格第4個字元，表示該月第幾週。

4  IF(WEEKDAY(DATE(B2,C2,1),1)=7,MID(A2,4,1)*7,0-WEEKDAY(DATE(B2,C2,1),1)+MID (A2,4,1)*7) 如果傳回的星期值是7，則直接將3的值乘7，否則0減掉星期值再加上3的值乘上7。

5  TEXT(IF(WEEKDAY(DATE(B2,C2,1),1)=7,MID(A2,4,1)*7,0-WEEKDAY(DATE(B2,C2,1),1)+ MID(A2,4,1)*7),"00") 將4的結果不足2位數的數值自動補0。

點取E2儲存格，輸入公式「=IF(DATE(B2,C2,D2)>EOMONTH(DATE(B2,C2-1,D2),1),"錯誤",VALUE(B2&C2&D2))」後，按ENTER鍵完成輸入，並將公式複製到E3:E8儲存格，完成各個物料料號代表的日期顯示。

【公式說明】

1  DATE(B2,C2,D2) 傳回B2儲存格(年)、C2儲存格(月)與D2儲存格(日)的日期。

2  EOMONTH(DATE(B2,C2-1,D2),1) 傳回1結果減一個月後的下一個月後一天日期數值。

3  VALUE(B2&C2&D2) 將B2與C2與D2的資料變成數值。

3  IF(DATE(B2,C2,D2)>EOMONTH(DATE(B2,C2-1,D2),1),"錯誤",VALUE(B2&C2&D2)) 如果1.的結果大於2的結果，則顯示錯誤，否則傳回3的結果。

葉XX：「謝謝，無緣老公。」

相關主題：

362常用Excel日期函數的用法

1065注塑機問題統計表製作-問題次數BY(年、季、月)

405常用的日期函數的用法和傳回的結果