Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Custom Date Format - 1st December (e.g.) (any)

    I'm trying to format a range of cells with a specific date format

    1st January
    2nd January
    5th December
    11th May
    and so on

    I can get everything except the "st", "nd", "rd" and "th" to work using d mmmm for the custom date format.

    Can the suffix be added easily to the day? Even nicer, can it go as a superscript (as in Word!)? Thanks!

    PS I'm very ignorant of Excel, so be gentle...!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    As far as I know, you can't do that in a number format, custom or otherwise. You would have to create a VBA function that takes a date and returns the formatted string. You would put "bare" dates in one column (which can be hidden, if you like), and put formulas using the custom function in another column for display.

    The part of the function that creates the suffix could look like this:

    Select Case Day(MyDate) Mod 10
    Case 0, 4 To 9
    strSuffic = "th"
    Case 1
    strSuffix = "st"
    Case 2
    strSuffix = "nd"
    Case 3
    strSuffix = "rd"
    End Select

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    I think that this could only be done by converting it to a text (and losing the "value" of the date). If you did this you could format to also include superscripts.

    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    Hans,

    I ithink this might not work correctly for 11th, 12th,13th.

    The following should <pre>Function OrdDate(oDate As Date)
    Dim oDay As String, Sfx As String
    oDay = Format(Day(oDate), "00")
    Sfx = "th"
    If Left(oDay, 1) <> "1" Then
    Select Case Right(oDay, 1)
    Case "1"
    Sfx = "st"
    Case "2"
    Sfx = "nd"
    Case "3"
    Sfx = "rd"
    End Select
    End If
    OrdDate = Val(oDay) & Sfx & " " _
    & Format((oDate), "mmmm<font color=red> yyyy</font color=red>")
    End Function</pre>

    If the year is not required, just omit the text in red.

    Andrew

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    You're right, of course. Thanks for the correction.

  6. #6
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Date Format - 1st December (e.g.) (any)

    Andrew and Hans

    That looks fine!

    What do I do to load the function and apply it to selected cells?
    Hans, have we not been here before, but in Word?!

    (I told you I was a pre-novice...!)
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Date Format - 1st December (e.g.) (any)

    I may be iggorant of VBA., but does this have the same problem as Hans' original method, that it doesn't handle 11th, 12th and 13th?
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  8. #8
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    You're sort of right. My current macro will give 11th, 12th, 13th, but it will also give you 21th, 22th, 23th. I will post a correction shortly.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    Your code messes up, 21,22,23, and 31 giveing them all "th". Try this.

    Steve

    <pre>Sub FDATE()
    Dim fstr As String
    Dim ndate As Range
    For Each ndate In Selection.Cells
    If IsDate(ndate) Then
    Select Case Day(ndate.Value)
    Case 1, 21, 31
    fstr = "dst mmmm yyyy"
    Case 2, 22
    fstr = "dnd mmmm yyyy"
    Case 3, 23
    fstr = "drd mmmm yyyy"
    Case Else
    fstr = "dth mmmm yyyy"
    End Select
    ndate.NumberFormat = fstr
    End If
    Next ndate
    End Sub</pre>


  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    Activate the Visual Basic Editor (Alt+F11)
    Create a standard module (Insert | Module)
    Copy the code from Andrew (not from my post) and paste it into the module.
    Back in Excel: say you have a date in A1
    In another cell, say B1, enter =OrdDate(A1)

  11. #11
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    Here's a macro you could run. Just highlight a range and run this macro. It will format the dates and preserve the numeric data in the cell. If you <font color=blue>save this in your PERSONAL.XLS and </font color=blue>assign this macro to a button on your toolbar, it would be very convenient for formatting dates on the fly.
    <pre>Sub FDATE()
    Dim fstr As String
    Dim ndate As Range
    For Each ndate In Selection.Cells
    If IsDate(ndate) Then
    Select Case Day(ndate.Value)
    Case 1<font color=blue>, 21, 31</font color=blue>
    fstr = "dst mmmm yyyy"
    Case 2<font color=blue>, 22</font color=blue>
    fstr = "dnd mmmm yyyy"
    Case 3<font color=blue>, 23</font color=blue>
    fstr = "drd mmmm yyyy"
    Case Else
    fstr = "dth mmmm yyyy"
    End Select
    ndate.NumberFormat = fstr
    End If
    Next ndate
    End Sub
    </pre>

    edit: <font color=blue>Bold and blue</font color=blue> are revisions.

  12. #12
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Date Format - 1st December (e.g.) (any)

    Thanks, Hans - as I have said before, you are intergalactically knowledgeable!
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  13. #13
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    Hi macropod,

    I always get January.

    for me ...........text((a1),"MMMM").........works

    Best regards

    Wolf

  14. #14
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Date Format - 1st December (e.g.) (any)

    Macropod

    Thanks for the formula method -- but wouldn't you have thought that Microsoft would have made it ever so slightly easier to generate "1st January" or similar? Perhaps this major feature upgrade is present in Excel 2003?
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Date Format - 1st December (e.g.) (any)

    No change in date formats in Excel 2003.
    Gre

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •