date to character
 

Hi everyone,
i have in a acolumn a series of dates:
09.15.2007
04.09.2008
12.24.2007
09.22.2007
06.05.2006
11.30.2006
i want to make them like:
september fifteenth 2007
etc..
for the month part i did text(a2,"mmmm") it gives me the month, for
the last mart i ll use year(a2)
ok but for the middle part like first, second, third, twenity
second ..... how will i be able to switch them? i tried of making a
table and use index-match combinations but it seems too much to me,
any ideas?
Thanks in advance

date to character
 

On Sun, 16 Nov 2008 15:16:54 -0800 (PST), Totti
wrote:

Hi everyone,
i have in a acolumn a series of dates:
09.15.2007
04.09.2008
12.24.2007
09.22.2007
06.05.2006
11.30.2006
i want to make them like:
september fifteenth 2007
etc..
for the month part i did text(a2,"mmmm") it gives me the month, for
the last mart i ll use year(a2)
ok but for the middle part like first, second, third, twenity
second ..... how will i be able to switch them? i tried of making a
table and use index-match combinations but it seems too much to me,
any ideas?
Thanks in advance

Since your range of values will only be 1-31, setting up a table should be
relatively simple; then use VLOOKUP.

For example, assuming your dates are real Excel dates (and not text strings),
then the formula might be:

=TEXT(A1,"mmmm ") & VLOOKUP(DAY(A1),DayTable,2) & " " &YEAR(A1)

Where DayTable is a table looking like:

1 First
2 Second
3 Third
4 Fourth
5 Fifth
6 Sixth
7 Seventh
8 Eigth
9 Ninth
10 Tenth
11 Eleventh
12 Twelfth
13 Thirteenth
14 Fourteenth
15 Fifteenth
16 Sixteenth
17 Seventeenth
18 Eighteenth
19 Nineteenth
20 Twentieth
21 Twenty-First
22 Twenty-Second
23 Twenty-Third
24 Twenty-Fourth
25 Twenty-Fifth
26 Twenty-Sixth
27 Twenty-Seventh
28 Twenty-Eighth
29 Twenty-Ninth
30 Thirtieth
31 Thirty-First
--ron

date to character
 

Thats it Ron, a VLOOKUP, was what i needed indeed thank you

date to character
 

On Sun, 16 Nov 2008 18:59:54 -0800 (PST), Totti
wrote:

Thats it Ron, a VLOOKUP, was what i needed indeed thank you

You're welcome. Glad to help. Thanks for the feedback.
--ron