Unable to change Date Format in MS Excel Easy Solution

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.


ADVERTISEMENT

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.

 

Excel File Data Tab Select A Column That You Want To Parse

 

Step 2: Now, under the Data tab, click on the Text to Columns option.

 


ADVERTISEMENT

Data Tab Text To Columns

 

Step 3: It will open the Convert Text to Columns Wizard dialogue box. Select the radio button next to Delimited and click on Next.

 

Convert Text To Columns Wizard Delimited 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.

 

Convert Text To Columns Wizard Step 3 Date Mdy Finish

 

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.

 

Format Cells Custom Type Date Format In The Text Box Ok

 

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.

 

Desktop Start Right Click Run

 

Step 2: In the Run box, type control international and press the OK button to open the Region dialogue box.


ADVERTISEMENT

 

Run Command Type Command Control International Ok

 

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.

 

Region Formats Tab Select From Drop Down Lists To Change Date And Time Format Ok

 

That’s all. Now, when you access the MS Excel files you should not encounter the date won’t change error anymore.

ADVERTISEMENT

1 thought on “Unable to change Date Format in MS Excel Easy Solution”

  1. 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.

Comments are closed.