Misaligned Dates in Excel

Published on
12,125 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.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free