Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
Is there a formula or VBA code to make "July 8, 2008" automatically say "8th
day of July, 2008"? Any help is appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
hi
not a formula but 3 formulas with concatinators. date in b2?...... =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) regards FSt1 "ProudFoot" wrote: Is there a formula or VBA code to make "July 8, 2008" automatically say "8th day of July, 2008"? Any help is appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
hi
oops. reread your post. how about a custom format??? dd"th day of "mmmm", "yyyy regards FSt1 "FSt1" wrote: hi not a formula but 3 formulas with concatinators. date in b2?...... =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) regards FSt1 "ProudFoot" wrote: Is there a formula or VBA code to make "July 8, 2008" automatically say "8th day of July, 2008"? Any help is appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
thanks, I wasn't clearin my post.
The date will change to any day of the month so what can I do about the 1st, 2nd, 3rd, 21st, 22nd, 23rd, etc... "FSt1" wrote: hi not a formula but 3 formulas with concatinators. date in b2?...... =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) regards FSt1 "ProudFoot" wrote: Is there a formula or VBA code to make "July 8, 2008" automatically say "8th day of July, 2008"? Any help is appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
hi
with the custom format, all date entries will automaticly adjust. Regards FSt1 "ProudFoot" wrote: thanks, I wasn't clearin my post. The date will change to any day of the month so what can I do about the 1st, 2nd, 3rd, 21st, 22nd, 23rd, etc... "FSt1" wrote: hi not a formula but 3 formulas with concatinators. date in b2?...... =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) regards FSt1 "ProudFoot" wrote: Is there a formula or VBA code to make "July 8, 2008" automatically say "8th day of July, 2008"? Any help is appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
Thanks,
This helps for most of the dates, what should I add if the date is the 1st, 2nd, 3rd, 21st, 22nd.....? "FSt1" wrote: hi not a formula but 3 formulas with concatinators. date in b2?...... =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) regards FSt1 "ProudFoot" wrote: Is there a formula or VBA code to make "July 8, 2008" automatically say "8th day of July, 2008"? Any help is appreciated. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
Chip Pearson has some notes (plus formulae and macro) on ordinal numbers
he http://www.cpearson.com/excel/ordinal.htm Perhaps you can incorporate the ideas in your sheet. Hope this helps. Pete "ProudFoot" wrote in message ... thanks, I wasn't clearin my post. The date will change to any day of the month so what can I do about the 1st, 2nd, 3rd, 21st, 22nd, 23rd, etc... "FSt1" wrote: hi not a formula but 3 formulas with concatinators. date in b2?...... =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) regards FSt1 "ProudFoot" wrote: Is there a formula or VBA code to make "July 8, 2008" automatically say "8th day of July, 2008"? Any help is appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
=DAY(B1)&CHOOSE(RIGHT(DAY(B1),1)+1,"th","st","nd", "rd","th","th","th","th","th","th")&" day of "&TEXT(B1,"mmmm, yyyy")
-- Kind regards, Niek Otten Microsoft MVP - Excel "ProudFoot" wrote in message ... | Thanks, | | This helps for most of the dates, what should I add if the date is the 1st, | 2nd, 3rd, 21st, 22nd.....? | | "FSt1" wrote: | | hi | not a formula but 3 formulas with concatinators. | date in b2?...... | | =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) | | regards | FSt1 | | "ProudFoot" wrote: | | Is there a formula or VBA code to make "July 8, 2008" automatically say "8th | day of July, 2008"? | | | Any help is appreciated. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
Check Pete_UK's answer; I forgot 11, 12 and 13 (and maybe more!)
-- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... | =DAY(B1)&CHOOSE(RIGHT(DAY(B1),1)+1,"th","st","nd", "rd","th","th","th","th","th","th")&" day of "&TEXT(B1,"mmmm, yyyy") | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "ProudFoot" wrote in message ... || Thanks, || || This helps for most of the dates, what should I add if the date is the 1st, || 2nd, 3rd, 21st, 22nd.....? || || "FSt1" wrote: || || hi || not a formula but 3 formulas with concatinators. || date in b2?...... || || =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) || || regards || FSt1 || || "ProudFoot" wrote: || || Is there a formula or VBA code to make "July 8, 2008" automatically say "8th || day of July, 2008"? || || || Any help is appreciated. | | |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
Private Sub Ordinal_Dates()
Dim cell As Range For Each cell In Selection If IsDate(cell.Value) Then Select Case Day(cell.Value) Case 1, 21, 31 cell.NumberFormat = "d""st day of ""mmm, yyyy" Case 2, 22 cell.NumberFormat = "d""nd day of ""mmm, yyyy" Case 3, 23 cell.NumberFormat = "d""rd day of ""mmm, yyyy" Case 4 To 20, 24 To 30 cell.NumberFormat = "d""th day of ""mmm, yyyy" End Select End If Next cell End Sub Or sheet event code to format as you enter. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim cell As Range For Each cell In Target If IsDate(cell.Value) Then Select Case Day(cell.Value) Case 1, 21, 31 cell.NumberFormat = "d""st day of ""mmm, yyyy" Case 2, 22 cell.NumberFormat = "d""nd day of ""mmm, yyyy" Case 3, 23 cell.NumberFormat = "d""rd day of ""mmm, yyyy" Case 4 To 20, 24 To 30 cell.NumberFormat = "d""th day of ""mmm, yyyy" End Select End If Next cell End Sub Gord Dibben MS Excel MVP On Tue, 8 Jul 2008 08:44:03 -0700, ProudFoot wrote: Thanks, This helps for most of the dates, what should I add if the date is the 1st, 2nd, 3rd, 21st, 22nd.....? "FSt1" wrote: hi not a formula but 3 formulas with concatinators. date in b2?...... =DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2) regards FSt1 "ProudFoot" wrote: Is there a formula or VBA code to make "July 8, 2008" automatically say "8th day of July, 2008"? Any help is appreciated. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spell out date
This formula will do what you have asked...
=DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*( MOD(DAY(A1)-11,100)2)+1),2)&" day of "&TEXT(A1,"mmmm, yyyy") The basic concept behind the ordinal part of the formula was mine, but Biff (T. Valko) tightened it up to make it this amazingly short. Rick "ProudFoot" wrote in message ... Is there a formula or VBA code to make "July 8, 2008" automatically say "8th day of July, 2008"? Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spell out Currency | Excel Discussion (Misc queries) | |||
spell out amount? | Excel Worksheet Functions | |||
Spell checker | Excel Discussion (Misc queries) | |||
Spell number | Excel Discussion (Misc queries) |