Misaligned Dates in Excel

Published:
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.
2
8,919 Views

Comments (2)

CERTIFIED EXPERT

Commented:
hiteshgoldeneye,

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

Many thanks,
Brian.

Author

Commented:
Hey thanks for the vote Brian, I am glad the article helped

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community