How to Fix Wrongly Formatted Dates Quickly in MS Excel Spreadsheets with example.

Fix / Correct Wrong formatted Dates very quick in MS Excel. How to use Text to Column in Excel with Example.


Sometimes when we ever copy or import data into Excel from another source and we find that the date formats are all wrong. Take this example, your computer may recognize dates in mm/dd/yy format and you have just copied data from another source where the date format was dd/mm/yy.

So when you download, copy or insert data from another date format and open it in Excel, Excel doesn't recognize the dates. Here is an easy trick that allows you to quickly convert wrongly-formatted dates into a format you can work with.

In the given picture You'll notice that Excel recognizes some of the dates but for others it doesn't. Notice that the dates that Excel has recognized are right-aligned in the cells and those it didn't recognize, and assumed was text, are left-aligned. If you look closely you'll see that even the cells Excel did recognize as dates are sometimes wrong - the months and days are switched.

Date Format

It can be quite a task to fix all of these dates. There can be various techniques to solve this problem, but except this technique all of them are very time consuming. This is a simple little trick using Excel's 'Text to columns' option.

If you find yourself with a column of wrongly formatted dates Here's the method you can use...

1) Highlight the cells (only one column wide) containing the dates. You can select the entire column if you like;

2) On the Data tab click Text to Columns in the Data Tools group;

3) In the 'Convert Text to Columns' dialog, select the 'Fixed width' option and click the Next> button;

4) If there are any column break lines (vertical lines with arrows) through the data area, double-click each of them to remove them and then click the Next> button;

5) In the 'Column data format' section, you will see a dropdown next to the Date option, select the date format of the imported data (not the format you want to convert it to). So if your computer uses a month/day/year format (e.g. USA) and the dates you need to fix are displayed as day/month/year (e.g. UK/Canada), you will need to select the DMY format and Excel will convert them to the default date format of your computer. If your computer uses the dd/mm/yyyy format and you import US dates, you will have to choose MDY. Other formats such as YMD are also available if you import data that matches that format (e.g.20120531);

6) Click the Finish button.

Just like magic, Excel reads all of the imported MDY dates and converts them to DMY format (or DMY to MDY format). All of those dates have been fixed in just a few seconds.

Now, this one is the cool and fast way to do such a little but typical thing.