Change a number to date text

Siesta

New Member
Joined
Mar 5, 2009
Messages
7
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.

Thanks in advance,

Ed
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top