Microsoft Excel is a great spreadsheet software for Windows computers. You can use it to manage data. While managing data, sometimes, you need to change the date format. You can do that easily by formatting the selected cells. However, some users noticed that the date format won’t change in their spreadsheet documents. If you are unable to change the date format in Microsoft Excel, the solutions provided in this article will help you.
Unable to change date format in Excel
If you are unable to change the date format in Excel, the cells might have been formatted incorrectly. If the cells in which you have entered the date are in Text format, changing the date format will not work.
This problem can be reproduced in Excel easily. I have also tried reproducing this issue in Excel. I created a sample worksheet in Excel and formatted some selected cells in the Text format. After that, I entered dates in those cells. You can refer to the above screenshot).
After that, when I changed the date format, it did not work. I tried different date formats but none of them worked.
To fix this issue, you simply have to change the format of the cells by following the steps provided below:
- Select the cells containing the dates in the Text format.
- Go to the Data tab.
- Now, click on the Text to Columns.
- On the first screen of the Convert Text to Columns Wizard, select Delimited. Click Next.
- On the next screen, select any Delimiter or you can leave all the options blank. Click Next.
- Select Date and then select the date format in the drop-down menu, like DMY.
- Click Finish.
Now, you can easily change the date format in Excel. To change the date format in Excel, select the cells, then right-click on the selected cells. Now, select the Format Cells option. In the Format Cells window, select the Number tab. Now, select the Date category from the left side and then select your preferred date format.
You can also set a custom format to dates in Excel. To do this, select the cells, then right-click on them, and select the Format Cells option. Alternatively, you can also press the Ctrl + 1 keys to open the Format Cells window. Under the Number tab, select the Custom option. Now, you can enter the required format for the dates in the required field in your Excel spreadsheet.
That’s it. I hope this helps.
Read: How to remove Blank Cells from Excel spreadsheet
Why is my Excel not recognizing dates?
The reason why Excel does not recognize dates entered in the cells is the incorrect format. The cells in which you have entered dates may be in the Text format. Check the format of the cells. You need to change the format of the cells so that the Text values will be converted into Numeric values. After that, Excel will start recognizing dates.
How to convert text date to mm dd yyyy in Excel?
There are two ways you can change the text date format to mm dd yyyy in Excel. Right-click on the selected cells and select Format Cells. Under the Number tab, select the Date option from the left side. Now, you can select the mm dd yyyy format from the right side. Alternatively, select the Custom category and type mm dd yyyy in the required field to change the date format to mm dd yyyy.
Read next: How to prevent Excel from removing leading Zeros.
