Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Posts
    103
    Thanks
    13
    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,313
    Thanks
    3
    Thanked 211 Times in 194 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,777
    Thanks
    403
    Thanked 1,551 Times in 1,405 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,313
    Thanks
    3
    Thanked 211 Times in 194 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,777
    Thanks
    403
    Thanked 1,551 Times in 1,405 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,313
    Thanks
    3
    Thanked 211 Times in 194 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,686
    Thanks
    59
    Thanked 1,065 Times in 990 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
    103
    Thanks
    13
    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
  •