Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    repelling date format heist (all)

    Know how Excel likes to put every date into "dd-mmm" format the minute it gets its grubby mitts <img src=/S/pirate.gif border=0 alt=pirate width=22 height=18> on a cell entry? I like that it recognizes dates as they're entered, actually, but I almost never use "dd-mmm" as a format.

    How do I change the default date format setting in Excel? Or, on a slightly different tack, how do I set up toolbar buttons that let me quickly change a date format to something more useful (kind of like the "$" format toolbar button)?

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

    Re: repelling date format heist (all)

    If you type date completely, chances are that Excel will keep it in the format you type; if you type it partially, it may apply one of its preferred formats.

    You can create a little macro that sets your own preferred format, and assign it to a toolbar button and/or shortcut key. If you put the macro in Personal.xls, it'll be available whenever you work in Excel. If you don't know what Personal.xls is, see Legare Coleman's <!post=Personal.xls Tutorial (All) ,118382>Personal.xls Tutorial (All) <!/post>.

    The macro could look like this:

    Sub MyDateFormat()
    On Error Resume Next
    Selection.NumberFormat = "mm/dd/yyyy"
    End Sub

    Replace mm/dd/yyyy by the format you prefer. To assign this macro to a toolbar button, select Tools | Customize... Activate the Commands tab, scroll the Categories list down and select Macros. Drag the Custom Button from the Commands list to a convenient toolbar button. Right click the new button to assign the MyDateFormat macro to it, and to modify the name and icon (button face).

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

    Re: repelling date format heist (all)

    Excel wiil use, as the default, the date format as set up for the Short Date display in the Regional Settings of your system (you set these via the Control Panel).

    Andrew C

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

    Re: repelling date format heist (all)

    Andrew,

    In all (Dutch) versions of Excel I've used, that is almost, but not quite true. My short date format is dd-mm-yyyy. Here is what I get:

    <table border=1><td>Input</td><td>Result</td><td align=right>13-6</td><td align=right>13-jun</td><td align=right>13-6-03</td><td align=right>13-06-2003</td><td align=right>13 jun</td><td align=right>13-jun</td><td align=right>13 jun 03</td><td align=right>13-jun-03</td><td align=right>13 jun 2003</td><td align=right>13-jun-03</td></table>
    As you see, only one one results in the short date format set in Windows.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: repelling date format heist (all)

    This works the same way with my std version of excel set to English (United Kingdom) in the regional options. Looks like another case of Microsoft preferring American dates.

    Peter

  6. #6
    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: repelling date format heist (all)

    I am not sure how Hans' list shows microsoft preferring american dates. Though I believe this seems to be true, the examples, don't show it. It shows that the "regional settings" are NOT a default date. When you enter a date with NO format excel will NOT automatically put it in the regional settings. It does this in the american version also.

    I have the american version and when I can "almost" duplicate Hans' table (though I must use 6-13 and 6-13-2003 and not 13-6 and 13-6-2003 to get valide dates accepted. The latter are considered "text" by the american version).

    Like Hans the ONLY one that gave me the default shortdate was the one MOST like my shortdate 6-13-03 which gives "6/13/2003" (my short date setup)
    Hans entered "13-6-03" and got "13-06-2003" (his shortdate)

    The others, more than "american-based" are (I think) excel's way of trying to find the format that is MOST like you entered and it does a "reasonable" job. It is MUCH better than the old Lotus 123 which would have considered them all equations or given you an "error" since the ones with spaces and letters are NOT numbers. Lotus believed that if you start with a number the cell contained a number (it was annoying to have to enter street addresses).

    If you don't want excel to "guess" what date format you want, define the format of the cell before hand, and excel is usually reasonable about keeping it that format.

    Steve

Posting Permissions

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