Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Posts
    100
    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,290
    Thanks
    3
    Thanked 197 Times in 183 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,558
    Thanks
    384
    Thanked 1,480 Times in 1,346 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,290
    Thanks
    3
    Thanked 197 Times in 183 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,558
    Thanks
    384
    Thanked 1,480 Times in 1,346 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,290
    Thanks
    3
    Thanked 197 Times in 183 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,422
    Thanks
    52
    Thanked 1,025 Times in 953 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
    2 Star Lounger
    Join Date
    Feb 2008
    Posts
    100
    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
  •