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
