Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date to character
Thats it Ron, a VLOOKUP, was what i needed indeed thank you
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
character map | Excel Discussion (Misc queries) | |||
Character Map | Charts and Charting in Excel | |||
Date Formating and building character strings | Excel Discussion (Misc queries) | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
Can I create a special character for the Character Map? | Excel Discussion (Misc queries) |