# Change a number to date text

#### Siesta

##### New Member
Is there a way to change the number of the day of the month to text?

eg. I would like to have "twenty third" as my result from typing 23 into a cell.

Ed

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### jasonb75

##### Well-known Member
I'm sure somebody will have a fancy method for this but this one works.

First paste the code into a VBA module, then use the formula = tdate(A1) Where A1 contains the date to convert
or enter the date into the formula as =tdate("01/01/09")

Code:
``````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``````

Hope this helps

#### Ron Coderre

##### MrExcel MVP
With A1 containing an integer between 1 and 31, inclusive...

A couple non-vba options:

Using a lookup range...
L1:M22 contains this:
Code:
``````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``````
Code:
``````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))``````

OR...for a self-contained (but longer) formula that doesn't refer to a range:
Code:
``````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))``````
I know...not very elegant...but, is either of those something you can work with?

#### Siesta

##### New Member
Hey thanks everyone,

I will give these a try. Keep up the good work.

#### Scott Huish

##### MrExcel MVP
If you use a lookup table, the formula will get much simpler if you list all the values in the table:

Excel Workbook
ABCDEFGH
131Thirty First1First
22Second
33Third
44Fourth
55Fifth
66Sixth
77Seventh
88Eighth
99Ninth
1010Tenth
1111Eleventh
1212Twelfth
1313Thirteenth
1414Fourteenth
1515Fifteenth
1616Sixteenth
1717Seventeenth
1818Eighteenth
1919Nineteenth
2020Twentieth
2121Twenty First
2222Twenty Second
2323Twenty Third
2424Twenty Fourth
2525Twenty Fifth
2626Twenty Sixth
2727Twenty Seventh
2828Twenty Eighth
2929Twenty Ninth
3030Thirtieth
3131Thirty First
Sheet2

Last edited:

Replies
5
Views
106
Replies
3
Views
112
Replies
3
Views
67
Replies
2
Views
176
Replies
5
Views
98

1,171,864
Messages
5,877,961
Members
433,304
Latest member
niresh28

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back