Wondering if excel can express mmm dd dates including their ordinal?
Examples are: May 4th, October 1st, June 3rd.
Thanks!
Wendy
Wondering if excel can express mmm dd dates including their ordinal?
Examples are: May 4th, October 1st, June 3rd.
Thanks!
Wendy
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
"Wendy D" <[email protected]> wrote in message
news:[email protected]...
> Wondering if excel can express mmm dd dates including their ordinal?
> Examples are: May 4th, October 1st, June 3rd.
>
> Thanks!
> Wendy
Format->Cells->'Number' tab->Click on "Custom" and type this formula:
[$-409]mmmm d
--
Regards,
Dave
"Wendy D" wrote:
> Wondering if excel can express mmm dd dates including their ordinal?
> Examples are: May 4th, October 1st, June 3rd.
>
> Thanks!
> Wendy
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!
"David Billigmeier" wrote:
> Format->Cells->'Number' tab->Click on "Custom" and type this formula:
>
> [$-409]mmmm d
>
>
> --
> Regards,
> Dave
>
>
> "Wendy D" wrote:
>
> > Wondering if excel can express mmm dd dates including their ordinal?
> > Examples are: May 4th, October 1st, June 3rd.
> >
> > Thanks!
> > Wendy
Wendy,
You can't do it through formatting, which is why I posted a formula.
HTH,
Bernie
MS Excel MVP
"Wendy D" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
> "David Billigmeier" wrote:
>
>> Format->Cells->'Number' tab->Click on "Custom" and type this formula:
>>
>> [$-409]mmmm d
>>
>>
>> --
>> Regards,
>> Dave
>>
>>
>> "Wendy D" wrote:
>>
>> > Wondering if excel can express mmm dd dates including their ordinal?
>> > Examples are: May 4th, October 1st, June 3rd.
>> >
>> > Thanks!
>> > Wendy
On Fri, 30 Sep 2005 12:38:03 -0700, "Wendy D"
<[email protected]> wrote:
>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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks