How do I permanently change Date format 06-Jun to short date 6/6/14 in all Excel workbooks all the time

Hi Kate,

Thanks for your note. No, this is still a problem. Even entering MM/DD/YYYY defaults to DD-MMM ("10/20/2017" becomes "20-Oct")

I solved the problem by creating three macros and then assigning the macros to Ribbon buttons in a new group called "Formats." I also created some other number formatting macros and added them to this group.

Here are the date macros:

To format to a short date:

Sub FormatDateShort()

'

' FormatDateShort Macro

' Formats to the short date: 6/25/2017

'

    Selection.NumberFormat = "m/d/yyyy"

End Sub

To format to a long date:

Sub FormatDateLong()

'

' FormatDateLong Macro

' Formats to the long date: June 25, 2017

'

    Selection.NumberFormat = "mmmm d, yyyy"

End Sub

To format to MMM and YYYY:

Sub FormatDateMonYR()

'

' FormatCellDateMonYR Macro

' Sets format of date cell to MMM YYYY

'

    Selection.NumberFormat = "mmm yyyy"

End Sub

You can use this same code to use other formats as well. For example, I created a macro to format billions and millions to "##0.0B" and "##0.0M":

Selection.NumberFormat = "#,##0.00,,""M"""

Selection.NumberFormat = "#,##0.00,,,""B"""

Obviously these need to be in to separate Sub routines, which are formatted just like the date subroutines.

Attention MS programmers, for user's sakes. Can't you get rid on the DD-MMM default format? No one in the U.S. uses it.

I know there are several "solutions" out there to PERMANENTLY change the date format from the never-used "01-Jan" to pretty much anything else, but they are ONLY for the current sheet.  Even the rather creative idea of using a macro and saving the now macro-enabled workbook in your XLSTART directory (which opens up a whole new can of worms) only works on existing sheets.  Once a new sheet is started, you are back to "01-Jan".  So my question is....When is Microsoft going to add this seemingly simple and constantly requested function?  Please don't respond with yet another way to change the date format in the existing worksheet.  There are about 1,548,193 pages with that advice and most people already know how to do it.

I know this is piling on because I am sure there are thousands of requests for this ability, but it would be nice to know why, with all of the constant development happening in MS, this can't be accomplished.

Thanks.

Thank you for responding.

Whenever I enter a date in Excel 2013, 6/21/14, 6/21, and even "Jun 21", Excel converts the date to 21-Jun.

1. Control panel short date setting is: 6/21/14
2. In the spreadsheet itself, I made a universal date format change, selecting all cells, and changing format to short date, 6/21/14
3. If I insert a row or column and enter a date 6/21/14 or 6/21 into the newly created cell, Excel shows the date as 21-Jun no matter what setting is on Control Panel or formatted through the rest of the spreadsheet.
4. Excel's default date format is 21-June. How do I permanently change the date default so that no matter what spreadsheet I open, no matter what cells, rows, columns I add, the default format for a date is 6/21?

You do not understand the question.

This does not work. I know how to change the date format within a worksheet, and it only applies to cells already in place. If I add cells, rows, or columns the date in the new cells defaults to "06-June"

How do I permanently change the default date in Excel 2013 to the short date on every workbook that I open.

In other words, how do I get rid of the "06-June" date forever.

My Control Panel region date defaults are all correctly set. The 06-June format appears absolutely nowhere else in my system.

I read somewhere that the change can only be deployed through some arcane code or VBA routine that has to be put in the PERSONAL.XSLX or PERSONAL.XLSM files. I cannot find the reference when Googling the issue.

Thank you