Top 2 Solutions to Fix Excel Data Format Not Changing

How to Fix Excel Date Format Won't Change

"Hello,
I have an excel worksheet with a date column, the format is set to dd-mmm-yyyy but the dates in the columns display as mm/dd/yyyy. If I double click in each individual cell and then move out of that cell the format changes and the dates appear correct. I have 24854 dates I need to change, I can't take time to double click in each cell. Any suggestions? Thanks."

- from CCM Forum

Excel is a very useful tool in our daily life, especially for users who need to manage a large amount dates information. However, if you meet with the same situation like what we mentioned above. Do you know how to fix excel date format not changing issue? Here comes some solutions which may help you out of trouble.

Solution 1: Change Excel Date Format in Format Cells

If you are suffering the same problem that date format cannot set to dd-mmm-yyyy, then the following steps can be helpful.

Step 1: Select the range you want to change the date format, right click and choose "Format Cells" or press Control + "1".

Step 2: Find "Date" under "Number" section, and you are able to type or locale to change time format in Excel cells.

Step 3: .If the default date formats are not suitable for you, go to Custom below and you can set custom date formats for Excel as you want, like d-mmm-yy.

Solution 2: Replace Wrong Date Import in Excel to Fix

Sometimes, we will type in date by our personal habit which would lead to wrong date format existing in excel. And thus these date may not be able to change. Thus we need to use "Find and Replace" to replace the decimal with a forward slash converts the text string to an Excel serial number in one fell swoop.

Step 1: Select all the dates you want to fix, for example: 1.01.2017.
Step 2: Press CTRL+H to open the Find & Replace dialog box.
Step 3: Enter a decimal place in the ‘Find what’ field, and a forward slash in the ‘Replace with’ field, click "Replace All".

Excel should detect that your text is now a number and format it automatically as a date. You can also use Find & Replace to fix date text strings with other delimiters like spaces, just enter a space or hyphen in the ‘Find what’ field instead of the decimal place.

That's all about how to solve users unable to change date format in Excel issue. Users can also use the methods to change default date format in Excel. Besides. When using Excel 2010/2013/2017 which is protect by password and you are just happened to lost the passcode, you can then get help from Excel Password Recovery, which is the world's best solution to help easily recover/remove password for your Excel files.

Download SmartKey Excel Password Recovery:

comments powered by Disqus