Can Excel express a date with ordinals?

[Wendy D]

Wondering if excel can express mmm dd dates including their ordinal?
Examples are: May 4th, October 1st, June 3rd.

Thanks!
Wendy

 

[Bernie Deitrick]

Wendy,

For a date in A1:

=TEXT(A1,"mmmm ") &
IF(DAY(A1)>0,DAY(A1)&IF(AND(DAY(A1)>10,DAY(A1)<20),"th",CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th")),"")

HTH,
Bernie
MS Excel MVP

 

[David Billigmeier]

Format->Cells->'Number' tab->Click on "Custom" and type this formula:

[$-409]mmmm d

 

[Wendy D]

Dave, that's the type of thing I'm looking for (although I will try the other
suggestion if I don't find anything this easy), however when I use that in
the custom field, I don't get the ordinals, only the full month. EX: I get
Jaunary 4, not January 4th. Do you know what else to add?

Thanks!

Format->Cells->'Number' tab->Click on "Custom" and type this formula:

[$-409]mmmm d


--
Regards,
Dave

Wondering if excel can express mmm dd dates including their ordinal?
Examples are: May 4th, October 1st, June 3rd.

Thanks!
Wendy

 

[Bernie Deitrick]

Wendy,

You can't do it through formatting, which is why I posted a formula.

HTH,
Bernie
MS Excel MVP

Dave, that's the type of thing I'm looking for (although I will try the other
suggestion if I don't find anything this easy), however when I use that in
the custom field, I don't get the ordinals, only the full month. EX: I get
Jaunary 4, not January 4th. Do you know what else to add?

Thanks!

Format->Cells->'Number' tab->Click on "Custom" and type this formula:

[$-409]mmmm d


--
Regards,
Dave

Wondering if excel can express mmm dd dates including their ordinal?
Examples are: May 4th, October 1st, June 3rd.

Thanks!
Wendy

 

[Ron Rosenfeld]

Wondering if excel can express mmm dd dates including their ordinal?
Examples are: May 4th, October 1st, June 3rd.

Thanks!
Wendy

You can Bernie's formula, which results in a text string difficult to use in
further calculations.

You could also use an event triggered macro to change the format appropriately
"on the fly" as it were. This has the advantage of preserving the
characteristics of the entry as a "date". It won't work as written, however,
if the entry is dependent on some cell entry from another worksheet.

One way to set this up:

Right click on the worksheet tab.
Select View Code from the dropdown menu.
Paste the code below into the window that opens.

=============================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range, c As Range
Dim Suffix As String

Set aoi = [a1:a100] 'set this to where you might be entering dates

On Error GoTo Handler

If Not Intersect(Target, aoi) Is Nothing Or _
Not Intersect(Target.Dependents, aoi) Is Nothing Then

For Each c In aoi
If IsDate(c.Value) Then
Select Case Day(c.Value)
Case Is = 1, 21, 31
Suffix = "\s\t"
Case Is = 2, 22
Suffix = "\n\d"
Case Is = 3, 23
Suffix = "\r\d"
Case Else
Suffix = "\t\h"
End Select

c.NumberFormat = "mmm d" & Suffix & " yyyy"
End If
Next c

End If
Exit Sub

Handler: If Not Intersect(Target, aoi) Is Nothing Then Resume Next
If Err.Description <> "No cells were found." Then
MsgBox ("Error #" & Err & " " & Err.Description)
End If
End Sub
============================
--ron