Spell out date

[ProudFoot]

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


Any help is appreciated.

 

[FSt1]

hi
not a formula but 3 formulas with concatinators.
date in b2?......

=DAY(B2)&"th Day of "&TEXT(B2,"mmmm")&", "&YEAR(B2)

regards
FSt1

 

[FSt1]

hi
oops. reread your post. how about a custom format???
dd"th day of "mmmm", "yyyy

regards
FSt1

 

[ProudFoot]

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]

hi
with the custom format, all date entries will automaticly adjust.

Regards
FSt1

 

[ProudFoot]

Thanks,

This helps for most of the dates, what should I add if the date is the 1st,
2nd, 3rd, 21st, 22nd.....?

 

[Pete_UK]

Chip Pearson has some notes (plus formulae and macro) on ordinal numbers
here:

http://www.cpearson.com/excel/ordinal.htm

Perhaps you can incorporate the ideas in your sheet.

Hope this helps.

Pete

 

[Niek Otten]

=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

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

 

[Niek Otten]

Check Pete_UK's answer; I forgot 11, 12 and 13 (and maybe more!)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Gord Dibben]

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

 

[Rick Rothstein \(MVP - VB\)]

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