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