Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Upper Case Date Format (2007 SP2)

    There is an old thread about this, and it seems if there is still no solution--at least as far as I can see.

    I want to create a date format where the month name is all caps: 25DEC2015. All I can manage to get is 25Dec2015.

    Is there a way to accomplish this easily?
    [I have been here for years; I had to get things restarted]

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It's not possible with just a format. You'd need code or a separate formula column.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    Here's a UDF (User Defined Function) that will do the trick.

    Code:
    Option Explicit
    
    Function MyDateFmt(dteDate As Date) As String
    
       Dim zMonth(1 To 12) As String
       
       zMonth(1) = "JAN"
       zMonth(2) = "FEB"
       zMonth(3) = "MAR"
       zMonth(4) = "APR"
       zMonth(5) = "MAY"
       zMonth(6) = "JUN"
       zMonth(7) = "JUL"
       zMonth(8) = "AUG"
       zMonth(9) = "SEP"
       zMonth(10) = "OCT"
       zMonth(11) = "NOV"
       zMonth(12) = "DEC"
      
    
       MyDateFmt = Format(Day(dteDate), "##") & _
                   zMonth(Month(dteDate)) & _
                   Format(Year(dteDate), "####")
                   
    End Function
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Ucase(format(dteDate,"ddmmmyyyy"))would be easier
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Right you are!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    So no real need for a UDF when you can just use
    =UPPER(TEXT(A1,"ddmmmyyyy"))
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 983 Times in 913 Posts
    The downside is you can no longer use the date in calculations as it's now a string.

    cheers, Paul

  8. #8
    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
    You can use A1 for the date calculations...

    Steve

  9. #9
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Thanks for the help.

    Because it is in a column with other dates in various formats, and I need to be able to sort it by date from time to time, I will stick with what I can get.

    But, if in the future I need to do this, I will save and remember the technique.
    [I have been here for years; I had to get things restarted]

Posting Permissions

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