Function tdate(Date_Reference)
dr = Array("0", "First", "Second", "Third", "Fourth", "Fifth", "Sixth", "Seventh", "Eighth", "Ninth", "Tenth", "Eleventh", _
"Twelvth", "Thirteenth", "Fourteenth", "Fifteenth", "Sixteenth", "Seventeenth", "Eighteenth", "Nineteenth", "Twentieth", _
"Twenty First", "Twenty Second", "Twenty Third", "Twenty Fourth", "Twenty Fifth", "Twenty Sixth", "Twenty Seventh", "Twenty Eighth", "Twenty Ninth", "Thirtieth", "Thirty First")
tdate = dr(Day(Date_Reference))
End Function
0 =""
1 First
2 Second
3 Third
4 Fourth
5 Fifth
6 Sixth
7 Seventh
8 Eighth
9 Ninth
10 Tenth
11 Eleventh
12 Twelfth
13 Thirteenth
14 Fourteenth
15 Fifteenth
16 Sixteenth
17 Seventeenth
18 Eighteenth
19 Nineteenth
20 Twentieth
30 Thirtieth
B1: =TRIM(IF(AND(A1>20,A1<>30),VLOOKUP(INT(A1/10),{2,"Twenty";3,"Thirty"},2,0)
,"")&" "&VLOOKUP(IF(OR(A1<=20,A1=30),A1,MOD(A1,10)),$L$1:$M$22,2,0))
B1: =TRIM(IF(AND(A1>20,A1<>30),VLOOKUP(INT(A1/10),{2,"Twenty";3,"Thirty"},2,0)
,"")&" "&VLOOKUP(IF(OR(A1<=20,A1=30),A1,MOD(A1,10)),{0,"";1,"First";
2,"Second";3,"Third";4,"Fourth";5,"Fifth";6,"Sixth";7,"Seventh";8,"Eighth";
9,"Ninth";10,"Tenth";11,"Eleventh";12,"Twelfth";13,"Thirteenth";
14,"Fourteenth";15,"Fifteenth";16,"Sixteenth";17,"Seventeenth";
18,"Eighteenth";19,"Nineteenth";20,"Twentieth";30,"Thirtieth"},2,0))
Excel Workbook | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | 31 | Thirty First | 1 | First | ||||||
2 | 2 | Second | ||||||||
3 | 3 | Third | ||||||||
4 | 4 | Fourth | ||||||||
5 | 5 | Fifth | ||||||||
6 | 6 | Sixth | ||||||||
7 | 7 | Seventh | ||||||||
8 | 8 | Eighth | ||||||||
9 | 9 | Ninth | ||||||||
10 | 10 | Tenth | ||||||||
11 | 11 | Eleventh | ||||||||
12 | 12 | Twelfth | ||||||||
13 | 13 | Thirteenth | ||||||||
14 | 14 | Fourteenth | ||||||||
15 | 15 | Fifteenth | ||||||||
16 | 16 | Sixteenth | ||||||||
17 | 17 | Seventeenth | ||||||||
18 | 18 | Eighteenth | ||||||||
19 | 19 | Nineteenth | ||||||||
20 | 20 | Twentieth | ||||||||
21 | 21 | Twenty First | ||||||||
22 | 22 | Twenty Second | ||||||||
23 | 23 | Twenty Third | ||||||||
24 | 24 | Twenty Fourth | ||||||||
25 | 25 | Twenty Fifth | ||||||||
26 | 26 | Twenty Sixth | ||||||||
27 | 27 | Twenty Seventh | ||||||||
28 | 28 | Twenty Eighth | ||||||||
29 | 29 | Twenty Ninth | ||||||||
30 | 30 | Thirtieth | ||||||||
31 | 31 | Thirty First | ||||||||
Sheet2 |