<

Misaligned Dates in Excel

Published on
9,378 Points
3,178 Views
2 Endorsements
Last Modified:
Approved
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
Comment
2 Comments
LVL 26

Expert Comment

by:redmondb
hiteshgoldeneye,

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

Many thanks,
Brian.
0
LVL 13

Author Comment

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

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month