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

This seems like it should be a no-brainer, but I cannot find the answer or a simple solution.

I have multiple 3D workbooks with dates. All dates are formatted as short date -- and the short date in Control Panel > Region > Short Date is aw well: m/d/yy

When I add a column or rows to Excel 2013 in a column that is already formatted at short date, I get the 6-Jun date or 6-Jun-2014.

Using a worksheet date format only works as long as I do not add any rows or columns. Once I do, new entries revert back to the 6-Jun format.

I once read somewhere that there something you have to change in your Personal.xlsx or xlsm workbook.

What's the solution to this problem.

If anyone from Microsoft is reading this ... why do you guys constantly use defaults that no one in the world uses? Don't you test these things against focus groups or ask real users? If you want to use arcane defaults, why can't you make it simple to change a universal default as simple as a date?

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Hello Toll,

In order to achieve the same date format for all rows and columns in a particular Excel worksheet – even on new rows and columns – follow these steps:

  1. Open your Excel worksheet.
  2. Press and hold down the Ctrl key on the keyboard.
  3. Press and release the letter " A " key on the keyboard without releasing the Ctrl key.
  4. All cells in the worksheet should be selected.

Now right-click on one of the cells in the worksheet and click on Format Cells. Format the date as required.

Once the above is completed – check if you can add/insert a new row or column and achieve uniform date format.

Note: As you have already changed the short date format in Control Panel > Region settings, in case you are trying to format the date on an existing worksheet and if it doesn’t work the way you wanted, then try on a blank new Workbook and check if your desired short-date format works. If it works then the current worksheet on which it does not work could be due to formatting issues on that particular file, as it may be remembering formats from another file from which you may have copied and pasted the data into the current file.

Please let us know if you need further assistance.

Thank you.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Still hoping for an answer

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi.   See if the following Help article is what you are looking for:

Title:  Format a date the way you want

<Short Copy from Help>

When you type something like 2/2 in a cell, Excel knows you’re typing a date and formats it based on the date setting in Control Panel. So for example, Excel might format it as 2-Feb. If you change your date setting in Control Panel, the default date format in Excel will change as well. If you don’t like the default date format, you can pick a different one in Excel, like February 2, 2012 or 2/2/12. You can also create your own custom format in Excel.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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?

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

This article: http://excelribbon.tips.net/T011575_Setting_a_Default_Date_Format.html

describes a few methods for setting default date format.  I think the template approach is what you are looking for, for future workbooks.

You set the date in the format dialog, have you checked the Windows system default format, as described in the tip?

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I'm just throwing this out, as I haven't tested it.

Sometimes when one changes a control panel setting, perhaps reboot the computer to see if changes take effect.   

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I tried the template but I still have to load a new worksheet with that template. the blank worksheet that loads when you load excel will not have it. I'm tired of the extra steps just to make sure I have a shortcut to convert my dates to "mm/dd/yyyy hh:mm". At least the template gives me only one extra step where before I had to add the custom format to the number format list and then add it as a style so I have a shortcut instead of having to search the custom list for it every time or using format painter.

Control panel settings made no difference. I changed them yesterday for an unrelated reason but I'm pretty sure the short date wasn't "Feb-29" by default.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

You do not understand the question.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated August 10, 2020 Views 5,780 Applies to: