2013/05/20

excel 顯示英文月份+日期

=TEXT(A1,"yyyy_mmmm_dd")&IF(AND(DAY(A1)>=11,DAY(A1)<=13),"th",IF(MOD(DAY(A1),10)=0,"th",INDEX({"st","nd","rd","th"},MIN(MOD(DAY(A1),10),4))))
這樣就會秀出完整的月份跟諸如1st,2nd,3rd,4th等了~
跑出來像是這樣



2013 Jan 1st
2013 Jan 2nd
2013 Jan 3rd
2013 Jan 4th
2013 Jan 5th
2013 Jan 6th
2013 Jan 7th
2013 Jan 8th
2013 Jan 9th
2013 Jan 10th
2013 Jan 11th
2013 Jan 12th
2013 Jan 13th
2013 Jan 14th
2013 Jan 15th
2013 Jan 16th
2013 Jan 17th
2013 Jan 18th
2013 Jan 19th
2013 Jan 20th
2013 Jan 21st
2013 Jan 22nd
2013 Jan 23rd
2013 Jan 24th
2013 Jan 25th
2013 Jan 26th

如果年份要在後面,公式可以改成
 =TEXT(A1,"mmmm_dd")&IF(AND(DAY(A1)>=11,DAY(A1)<=13),"th",IF(MOD(DAY(A1),10)=0,"th",INDEX({"st","nd","rd","th"},MIN(MOD(DAY(A1),10),4))))&TEXT(A1,"_yyyy")
或是
=TEXT(A1,"mmmm_dd")&IF(AND(DAY(A1)>=11,DAY(A1)<=13),"th",IF(MOD(DAY(A1),10)=0,"th",INDEX({"st","nd","rd","th"},MIN(MOD(DAY(A1),10),4))))&TEXT(A1,",yyyy")

其中年月份的長度可以由yyyy改yy,mmmm改m


資料來源
http://forum.twbts.com/viewthread.php?tid=2979

沒有留言:

張貼留言