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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 14, 2009
Messages
2,316

ADVERTISEMENT

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?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,113
Members
414,125
Latest member
iQQ

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
Top