repelling date format heist (all)

Topic

New Reply

Know how Excel likes to put every date into “dd-mmm” format the minute it gets its grubby mitts 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)?

Reply | Quote

Replies

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 Personal.xls Tutorial (All).

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).

Reply | Quote

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

Reply | Quote

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:

Input	Result
13-6	13-jun
13-6-03	13-06-2003
13 jun	13-jun
13 jun 03	13-jun-03
13 jun 2003	13-jun-03

As you see, only one one results in the short date format set in Windows.

Reply | Quote

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

Reply | Quote

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

Reply | Quote