With Excel 2010 running on 32 bit W7 (with W7 regional settings to US and date formats of MM/dd/yy and MMMM dd,yyyy and saved again), the date format defaults to dd-mmm, which shows as a custom format. This format is of no value and needs to be mm/dd/yy as default.
I find the following in Excel support: "
Display numbers as dates or times
You can format dates and times as you type. For example, if you type 2/2 in a cell, Excel automatically interprets this as a date and displays 2-Feb in the cell. If this isn't what you want—for example, if you would rather show February 2, 2009 or 2/2/09 in the cell—you can choose a different date format in the Format Cells dialog box, as explained in the following procedure...."
How do I change the default to mm/dd/yy on a permanent global basis instead of having to reformat every cell into which I enter a date?
From your description, I understand that You want to change the default date format in Excel 2010.
I recommend that you refer to the Office link that solved this issue.
Change the default date, time, number or measurement format
That link refers only to the OS default settings, which I have already set as I want them. They have seemingly no effect on Excel 2010, as Excel has been changed to automatically interpret date keystrokes into the format of little value = For example, if you type 2/2 in a cell, Excel automatically interprets this as a date and displays 2-Feb in the cell.
Its this automatic interpretation I want to default change.
The default date/time format depends on the date/time format in Windows. So, to change the default format, you need to change the format in Windows.
If you want to change the format in Excel manually, select the cells that you wish to change, right click it and choose <Format>.
In the Category window, click on <Date>
And see if you can find something that you like in the Type window, Then click OK.
Thanks for getting back to me but things do not work as you describe. If you note my original post, I have the Control Panel|Region and Language date set to MM/dd/yy for short date and MMMM dd,yyyy for long date in W7.
As I told Harry, Excel still returns 2-Feb when I enter 2/2 in any cell. This automatic date keystroke interpretation began with the 2010 beta and continues in the release version. Prior versions yielded the correct interpretation of 02/02/10 when 2/2 is entered into any cell. I tried to report this behavior as a bug during the beta but met with much the same response. I hoped this bug would be fixed in the release version but it is not.
When I type 2/2 in any cell, the cell content bar shows 02/02/2010 but the cell displays 02-Feb. When I check the format of the cell, it is Custom with dd-mmm format.
I do not want to manually reformat cells because that's a pain.
Here's what the 2010 version help says: "For example, if you type 2/2 in a cell, Excel automatically interprets this as a date and displays 2-Feb in the cell."
It is this behavior, which showed up with V2010, that I want to change.
I have the same problem, when i put 1-10, it automatically changes the cell to 10-Jan. I have to keep formating the cells. It's a huge pain. Does anyone know how to fix this?
What I have to say may not be of much help.
Firstly Excel has had this feature for many years, it's not new to 2010.
Secondly the only way to circumvent this feature which appears to be coded in, is to type something that Excel cannot interpret as a date.
Now the last one may sound a totally ridiculous suggestion if you want 2/2, what else could you use? We'll for those of us who have been around since the dark ages of spreadsheets the answer is quite simple.
To get Excel to accept what you want to enter and not reformat preceed your entry with ' .
Typing '2/2 will give you 2/2 and nothing else.
Hope this helps
G North MMI
G North, you are correct that this has been happeneing long before v2010.
However, Excel apparently does recognize 2/2 as a date because it converts it to 2-Feb, it also converts 8/1 to 1-Aug and so on.
I dont understand why there is so much confusion as to what the problem is. I will try to explain the problem.
in many applications you can type 2/4 or 2.4 or 2-4 into a cell and the cell will assume you are talking about the date in the current year and format it accordingly to be presented as 02/04/2011 (assuming this is your default format that you have chosen and that the year is 2011).
we dont want a text field of 2/4 because that is of no use either. We work with dates. And we want to work with dates in a format that the rest of the world used to looking at.
Tom C is absolutely right that it does not matter what the regional settings for windows is set to. it is completely disregarded.
this has been a thorn in my side for years and i had really hoped that common sense would prevail in this release, but alas, we are still doomed to manually format every single date cell unless we type the entire date, including the year, evry time.
thanks for you time!
I have the exact same issue, and came here looking for a solution. Am very disappointed that there doesn't seem to be one. It drives me batty that every time I enter a date, I need to immediately reformat it. This seems like an exceptionally easy problem to solve. Has anyone at Microsoft confirmed that there really is no solution? Will they consider providing one in a future service pack?
Here's what I suggest:
When you go to format a date: Format > Date > (Select the date type) Add a checkbox to Set as default date format for Excel
How to change the default date to mm/dd/yyyy
Hey guys I figured it out! Go to file >option s> advanced > scroll down till you see the "when calculating in this workbook" section > select "use 1904 date system"
Another thing you can do is enter the date you want into a cell for example "01/01/2001" then go to home tab > under the styles grouping click > cell styles > at the bottom click new cell style > uncheck everything but the date and save that style now all you have to do is go to styles and click what ever you named that style to to apply your date format.
I figured it out but it was not as intuitive as I would have hoped and is not exactly what your looking for but it was what I was looking for when I came here. Hopefully this helps someone else trying to use mm/dd/yyyy.
I agree with what was mentioned earlier allowing us to set a default date format would be nice. Hopefully this works for you guys.
- Proposed as answer by _henry Monday, August 29, 2011 4:36 AM
here is a workaround for any given worksheet:
right click on the worksheet tab, select "View Code" to bring up the VBA editor.
Paste this code into the worksheet module code window that is displayed:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.NumberFormat = "d-mmm" Then
Target.NumberFormat = "dd/mm/yyyy"
This is correct for my settings here in the UK, modify as necessary for your settings and locale.
1. This runs every time a cell changes on the worksheet which may be undesirable in some scenarios.
2. It is no longer possible to format a cell as "d-mmm" unless you never edit/change it afterwards.
3. This has been tested in Excel 2010 only.
Hi there. So this fix of _Henry's works, but I can't get it to keeps this setting. I have to change it every time I start a new workbook. Is there any way for this option to always stay ticked off that you know of?
I think your problem is different. I don't want to upset everyone in this forum, but since I struggled with this for a really long time, I want to offer you my solution.
For me, this formatting issue only came from a workbook I inherited from someone else. When I create a new workbook, it's automatically correct. My solution is really just how to fix this issue in a spreadsheet you've already made, preferably one that doesn't have any date cells.
Basically, I just used the "Replace" feature to find all cells formatted as a date and format them instead as "General". Here's how I did it:
I went to the "Home" tab, clicked on "Find & Select" in the "Editing" group on the Ribbon and then "Replace". (You can also just press Ctrl-F and click on the "Replace" tab.
Beside "Find what:" I clicked on "Format.." then the "Number" tab and selected "Date" under "Category" and under "Type" selected the EXACT format my cells were automatically set to by default. Then I went through all of the other tabs including "Alignment", "Font", "Border", "Fill" and "Protection" and made sure absolutely no other formatting was selected. Essentially, the "Clear" button in the bottom right hand corner of each tab should be inactive. If it's active (AKA you can click on it), click on it. Then select OK.
Next to the "Replace with:" row, I again clicked on the "Format..." button. This time, on the number tab, I selected "General" from the "Category" list. Then, I repeated my previous step of going to each tab ("Alignment", "Font", "Border" ... etc.) and made sure there were absolutely NO formats specified (I made sure the clear button was inactive on each tab and clicked it when it was still active). Then click OK.
Finally, next to "Within", I selected "Workbook."
Make sure "Find what" and "Replace with" are left blank.
Then I clicked, "Replace All" and my problem was solved.
Let me know if it works?
I have a similar problem, but it is not being solved by any of the suggestions.
I have several Excel sheets that prompt the user to enter a date through a macro. Date is entered & needs to be dd/mm/yyyy as data is then imported into an accounting system. But is the date is less than the 12th of a month the value returned to the cell is mm/dd/yyyy.
I have the checked the the regional settings. Tried every date & custom formating on the cell. Tried the 1904 date option. Unformated & re-formated. Save as a .xlsm file and through the process again.
Any more suggestions?
The issue is that VBA defaults to mm/dd/yyyy unless it is obviously dd/mm/yyyy (ie, the first two digits are a number greater than 12).
To fix this, present a userform with a calendar control to have the user click - that control returns a date, so there is no ambiguity.
Private Sub Calendar1_Click()
MsgBox "Date value selected is " & Format(Me.Calendar1.Value, "0") & Chr(10) & _
Format(Me.Calendar1.Value, """Month first format: "" mm/dd/yyyy") & Chr(10) & _
Format(Me.Calendar1.Value, """Day first format: "" dd/mm/yyyy")
- Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor Friday, April 26, 2013 5:48 PM
Simple PERMANENT solution (though it drives me nuts that this is necessary) is to create a "Parent" workbook with the date format you want (and nothing else), then create all future workbooks from that Parent (with the date pre-formatted), rather than from scratch. Here's how:
- Open Excel and created a new workbook.
- Highlight the entire workbook and change the date format to the desired format (like MM/DD).
- Save the workbook somewhere convenient with a name indicating that dates are pre-formatted (ex.: "MM/DD Dates").
Then, in the future, you want to create a NEW Excel file that will include DATE data, create it from the pre-formatted file (rather than from scratch), like this:
- Open the pre-formatted file you created above.
- Immediately save it under a new name (ex.: "Inventory") and/or location.
Now you can work with the file as you normally do, entering dates and other data as desired. Only difference is that you won't have to change the date format every time you create a new file.
Like I said, it drives me crazy that this is necessary, but it's a simple workaround.
p.s. Obviously, the process can be modified easily to pre-format only certain columns. In that case, I also enter a column header (like "Date" or "MM/DD") in cell A1, for example, indicating that the date data goes in that column.
As suggested by Harry... Change in the Regional settings solved this issue.
Click on Start >> Control Panel >> Region and Language >> Format (From the drop down Select English (United Kingdom) >> Click on Apply >> Ok.
Hope this will fix the issue.
Amit K Dixit
I find that Amit K Dixit solution is the best for someone outside USA that want different, not only time but also number, currency, etc, formatting. Apparently microsoft office use the default windows format.
I live in Asia pacific, and US formatting is driving me crazy :D.