Is there a formula or VBA code to make "July 8, 2008" automatically say "8th
day of July, 2008"?


Any help is appreciated.

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.

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.

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.

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.

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.

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.

=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.

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.
|
|

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.

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.