EXCEL產品料號轉年月當月第幾週星期五日期
|
1068 |
EXCEL產品料號轉年月當月第幾週星期五日期 |
||||
|
示範檔 |
無 |
範例檔 |
1068.XLSX |
結果檔 |
1068F.XLSX |
葉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：「謝謝，無緣老公。」