Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Mar 2009
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Date Format

    I am trying to set a custom format for a long date. The current format under format cells shows the date as, Sunday, April 03, 2016. I would like the dates in the cells to show as Su, Apr 03, 16. Do you have a format formula for this?

    Thanks,

    Mike

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

    As close as you can get with the custom formatting codes is: ddd, mmm dd, yy which yeilds Sun, Apr 03, 16.

    If you must have the 2 digit day name you will need a UserDefinedFunction to create it. I can gen it up for you if you can't live with the 3 character day names. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Here are 2 possibilities:
    Code:
    Public Function zMyDteFmt(Target As Range) As String
    
       Dim zDteAbr As Variant
       
       zDteAbr = Array("Su", "Mo", "Tu", "We", "Th", "Fr", "Sa")
       
       zMyDteFmt = zDteAbr(WorksheetFunction.Weekday(Target)-1) & Chr(32) & _
                   Format(Target, "mmm dd, yy")
                   
    End Function   'zMyDteFmt
    Called as: =zMyDteFmt(A1) where A1 contains the date to be formatted. You can hide A1 after input so it isn't visible. This formula is also copyable down a column where the following is not!

    or
    Code:
    Public Function zMyStrFmt(Target As String) As String
       Dim zDteAbr As Variant
       
       zDteAbr = Array("Su", "Mo", "Tu", "We", "Th", "Fr", "Sa")
       
       zMyStrFmt = zDteAbr(WorksheetFunction.Weekday(Target)-1) & Chr(32) & _
                   Format(Target, "mmm dd, yy")
                   
    End Function  'zMyStrFmt
    Called as: =zMyStrFmt("4/3/2016") of course this doesn't require hiding any columns but it also doesn't allow calculations to be made on the date!

    Test File: Mike Custom Date Format UDFs.xlsm

    HTH
    Last edited by RetiredGeek; 2015-07-29 at 16:42.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    ..you can do it using conditional formatting.
    see attached example file.

    zeddy
    •Rebel Without Applause
    Attached Files Attached Files

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..I suppose you could say I cheated a bit.

    zeddy
    •Excel Station Outreach

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

    Tricky, Very Tricky!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    An alternative VBA:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If IsDate(Target) Then
    Dim wd As String
    Select Case Weekday(Target)
        Case 1:: wd = "Su, "
        Case 2:: wd = "Mo, "
        Case 3:: wd = "Tu, "
        Case 4:: wd = "We, "
        Case 5:: wd = "Th, "
        Case 6:: wd = "Fr, "
        Case 7:: wd = "Sa, "
    End Select
    Application.EnableEvents = False
    Target = wd & Format(Target, "mmm dd, yy")
    Application.EnableEvents = True
    End If
    End Sub

  8. #8
    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
    FWIW, you can simplify RG's UDF a little:
    Code:
    Public Function zMyDteFmt(Target As Range) As String
    
       zMyDteFmt = Left$(Format$(Target.Value, "ddd"), 2) & Format(Target, " mmm dd, yy")
                   
    End Function   'zMyDteFmt
    Regards,
    Rory

    Microsoft MVP - Excel

  9. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2015-08-03)

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..but the function still turns the date into a string???

    ..while conditional formatting keeps the cells as 'dates', doesn't require vba code, and doesn't require another cell for the result

    (those conditional formatting conditions could be reduced to 1 condition of course)

    zeddy
    •Excel Static Rotation Storage Agent
    .

  11. #10
    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
    That's true, but then it still requires a lot more effort than simply accepting a normal three letter day abbreviation. I know which option I'd go for.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by rory View Post
    That's true, but then it still requires a lot more effort than simply accepting a normal three letter day abbreviation. I know which option I'd go for.
    +1

    In my working days my favorite line was, "Yes, I can do that it's going to cost you x $ and x time." Funny how often that requirement disappeared!

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Yes, I agree, it's not like you need to save any space - what's with that first comma anyway???
    Instead of
    Su, Apr 03, 16
    Sun Apr 03, 16 is so much easier.

    But then, hang on a minute, if they really want Su, Apr 03, 16 then by golly they should have it!

    zeddy
    •Strategic Inefficiency Monitor
    .
    Last edited by zeddy; 2015-08-03 at 10:05. Reason: wrong job

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by zeddy View Post
    But then, hang on a minute, if they really want Su, Apr 03, 16 then by golly they should have it!
    Zeddy,

    I agree, the customer is ALWAYS RIGHT! As long as their wallet is thick enough!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..them were the days! I used to trip over my wallet.

  16. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I like the UBER surge pricing algorithm. If they need it by the end of the week: $X; if they need it tomorrow: N times $X.

Posting Permissions

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