[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Misaligned Dates in Excel

Published on
9,889 Points
2 Endorsements
Last Modified:
While using dates in excel one may have come across a situation that after entering a date in a cell, it shifts to the left-hand side of the cell. What this means is that Excel has not recognized the input as a valid date; i.e., the date is considered invalid by the application.

The expected format of the date depends on the machine’s regional settings.  For example, if the regional options of your machine is set to English (United Kingdom), then you need to enter dates in dd/mm/yyyy format (or dd-mm-yyyy). When you enter dates in the expected format they are aligned to the right hand side by default. (Note – here, “by default” means formatting of the cell has not been altered to impose left or center alignment).

In this case (U.K. settings), if you type a date in mm/dd/yyyy format (or mm-dd-yyyy), then the date will move to the left-hand side of the cell (Please note that for this to happen the day in the date should be greater than 12).  OR some of the dates will be left-aligned and some right-aligned since many dates appear to be correct in both formats.

If you import data produced by a program other than Excel, it might include dates which are not stored in the expected format and therefore not automatically recognized by Excel.

This tip shows how you can do the necessary data conversion so that Excel can recognize the dates. Here we assume that expected format is dd/mm/yyyy and the dates which were imported are in mm/dd/yyyy format

Select the entire range of cells containing the dates which are not recognized by Excel (they will be aligned left by default).
Select menu item  Data>Text to Columns
Select delimited and click Next
Click Next once again
Now select the Date option button and from the drop down list select MDY
Press Finish

The dates should now be in the expected format and aligned to right by default which means that Excel has recognized the dates.
LVL 26

Expert Comment


Brilliant! I've used formulas and macros for this for years without cottoning on to your idea.

Many thanks,
LVL 13

Author Comment

by:Hitesh Manglani
Hey thanks for the vote Brian, I am glad the article helped

Featured Post

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month