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.
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.
Comments
Post a Comment