14702K3NTA 取K前面5個數字 14西元年7是月份02是日期
要如何變成日期 2014/07/02
又如果A=10月份 B=11月份 C=12份
那.........
14A02K3NTA
14B02K3NTA
14C02K3NTA
要怎麼變成
2014/10/02
2014/11/02
2014/12/02
Excel 函數取字串變日期
2014-07-04 6:12 pm
回答 (5)
2014-07-07 7:15 am
✔ 最佳答案
試試看=DATE(VALUE(MID("20"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,5),"A","10"),"B","11"),"C",12),1,4)),VALUE(MID("20"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,5),"A","10"),"B","11"),"C",12),5,2)),VALUE(MID("20"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,5),"A","10"),"B","11"),"C",12),7,2)))
上列公式共用到了
1.Date函數->日期函數
2.Value函數->轉數值
3.Mid函數->字串擷取
4.Left函數->左側字串擷取
5.Substitute函數->字串轉換
參考: 自己經驗
2014-07-05 12:26 am
=DATE(20&LEFT(A1,2),FIND(MID(A1,3,1),"123456789ABC"),MID(A1,4,2))
未排錯, 參考即可~
未排錯, 參考即可~
2014-07-04 10:59 pm
=DATE(LEFT(A1,2)+2000,IF(ISERR(--MID(A1,3,1)),CODE(MID(A1,3,1))-55,--MID(A1,3,1)),--MID(A1,4,2)),參考即可
2014-07-04 8:36 pm
若是 2003 版, 改用
=TEXT(20&LEFT(B2,2)&"/"&IF(ISERR(--MID(B2,3,1)),CODE(MID(B2,3,1))-55,MID(B2,3,1))&"/"&MID(B2,4,2),"YYYY/MM/DD")
亦可
=TEXT(DATE(20&LEFT(B2,2),IF(ISERR(--MID(B2,3,1)),CODE(MID(B2,3,1))-55,MID(B2,3,1)),MID(B2,4,2)),"YYYY/MM/DD")
=TEXT(20&LEFT(B2,2)&"/"&IF(ISERR(--MID(B2,3,1)),CODE(MID(B2,3,1))-55,MID(B2,3,1))&"/"&MID(B2,4,2),"YYYY/MM/DD")
亦可
=TEXT(DATE(20&LEFT(B2,2),IF(ISERR(--MID(B2,3,1)),CODE(MID(B2,3,1))-55,MID(B2,3,1)),MID(B2,4,2)),"YYYY/MM/DD")
2014-07-04 7:42 pm
=TEXT(20&LEFT(B2,2)&"/"&IFERROR(--MID(B2,3,1),CODE(MID(B2,3,1))-55)&"/"&MID(B2,4,2),"YYYY/M/D")
2014-07-04 11:55:39 補充：
http://blog.xuite.net/hcm19522/twblog/225322350
2014-07-04 11:55:39 補充：
http://blog.xuite.net/hcm19522/twblog/225322350
收錄日期: 2021-04-27 21:11:49
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20140704000010KK03279