How to solve Date Format problem in Microsoft Excel

Microsoft Excel is one of the best software for organizing data in tabular form; Excel is widely used across the world because of its simple yet powerful features. However, there is a little learning curve to Excel, which many people find it as a hurdle.

Date format is one of thing that most users find it hard to change from the default option. Different countries use different date formats, so many users will want to change the date format in MS Excel. There is no direct option to change date format in the main menu, so basic users face problems while using different date formats.

To make your work more comfortable, we have found out two simple methods to change the date format in your document. The first method changes MS Excel settings to enable different date formats for you, and the second method changes your default date format on your computer, so you don’t have to change the settings every time you make a document. It would also change the default date format in all of your programs. Choose whichever suits you best.

Method 1 – Data setting in Excel

STEP 1: Click on the Data tab in the main menu and then click on Text to columns option.

Data Tab - MS Excel


STEP 2: A pop-up menu will open up, Check the Delimited radio button in ‘Orginal data type’ and then click Next twice to go step 3 of the wizard.

Text to columns options


STEP 3: On 3rd step, First check the date option in column date format and then select MDY from the drop-down list. Click Finish after selecting the date format.

date format - text to column wizard


STEP 4:  Select any empty cell inside your document and press Ctrl + 1, a format cells menu will pop up.

STEP 5: Select Custom from the categories menu on the left pane and then click on the type option. Type down the date format as yyyy-mm-dd (Type whatever format you want or choose by scrolling down).
Format cells manu

STEP 6: Click Ok apply the date format.  Check the date format by typing the date in any of the cells.


Method 2 – Changing default Windows Date format

If you are looking for a permanent solution for the date format in Excel and also all your other apps, changing the default date format will be the best option for you. It will automatically change your date formats in all the apps, and you won’t have to worry about that again. Follow this simple process to change your default date format in your windows machine.

STEP 1: Press Windows + R, it will open the Run box.

STEP 2: Type in control international in the box and hit enter.

Run - control international

STEP 3: It will open up the Region settings menu, choose the desired date format option from the drop-down menu.

Region Date Settings

STEP 4: Click Ok to save your settings.

2 thoughts on “How to solve Date Format problem in Microsoft Excel”

  1. I am SO glad I found this tip. I had a list of dates from two years, about 7000 rows, with multiple different date formats. I had tried all kinds of ways to convert all dates to one format to make them sortable, but I had some that were stuck as 3-Mar-2019 type format. Your first way using text to column WORKED. I have only ever used text to column when separating data, not for converting formatting, and didn’t know this was an option. Thank you so very much.

Comments are closed.