The Excel app is highly used across the world for creating and maintaining data sheets for record purpose. However, It can sometimes give you some random errors that can prevent you from working on it or adding data. One of such annoying error is when the date format won’t change in MS Excel.
Yes, this is a common error, and the issue seems to be quite common on a range of platforms and for different version of MS Excel. However, the good thing is, there are is a fix available for this. Let’s see how to fix the DDate format won’t change error in MS Excel.
Method 1: By Enabling the Delimited Option
Step 1: Open the Excel file with data where you want to change the date format, click on the Data tab on the top of the page, and select a column containing data that you want to parse.
Step 2: Now, under the Data tab, click on the Text to Columns option.
Step 3: It will open the Convert Text to Columns Wizard dialogue box. Select the radio button next to Delimited and click on Next.
Step 4: Click Next again in the Step 2 of the wizard to reach the Step 3 box. Now, select the radio button next to Date and set the field to MDY.
Click on Finish to save the changes and exit.
Step 5: Now, click on any empty cell in the spreadsheet and press the CTRL + 1 keys together on your keyboard. This will open the Format Cells dialogue box. Under the Number tab, select Custom and on the right side of the pane, type YYYY/MM/DDD in the text box.
Press OK to save the changes and exit.
This should solve your data format error issue in the MS Excel, but if it doesn’t, you can try the 2nd method.
Method 2: By Changing the Windows Date Format
Changing the default date format of Windows can be a quick remedy if changing data format in Excel is confusing. However, please remember that the changes will also be applied to all other Windows applications on your PC and not just the Excel files.
Step 1: Go to the Start button on your desktop, right-click on it and select the Run option from the menu to open the Run command.
Step 2: In the Run box, type control international and press the OK button to open the Region dialogue box.
Step 3: Now, in the Region window, under the Formats tab, change the available default formats. Click on the drop-down lists to select your desired date and time format.
Press OK to save the changes and exit.
That’s all. Now, when you access the MS Excel files you should not encounter the date won’t change error anymore.
Hi, the first method was really helpful, but for some reason some of my dates remain in the original format of e.g. september 9, 2020. When selecting only these dates and following the steps in the first methos it separates the dates in three horisontal cells instead.
Do you have any tips to fix this? The dates are all imported from the same place so I see no reason for this difference.