Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can Excel express a date with ordinals?
Wondering if excel can express mmm dd dates including their ordinal?
Examples a May 4th, October 1st, June 3rd. Thanks! Wendy |
#2
|
|||
|
|||
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" wrote in message ... Wondering if excel can express mmm dd dates including their ordinal? Examples a May 4th, October 1st, June 3rd. Thanks! Wendy |
#3
|
|||
|
|||
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 a May 4th, October 1st, June 3rd. Thanks! Wendy |
#4
|
|||
|
|||
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 a May 4th, October 1st, June 3rd. Thanks! Wendy |
#5
|
|||
|
|||
Wendy,
You can't do it through formatting, which is why I posted a formula. HTH, Bernie MS Excel MVP "Wendy D" wrote in message ... 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 a May 4th, October 1st, June 3rd. Thanks! Wendy |
#6
|
|||
|
|||
On Fri, 30 Sep 2005 12:38:03 -0700, "Wendy D"
wrote: Wondering if excel can express mmm dd dates including their ordinal? Examples a 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
today's date in excel | Excel Discussion (Misc queries) | |||
Excel Query Wizard Date Format | Excel Discussion (Misc queries) | |||
Excel file modification date | Excel Discussion (Misc queries) |