If you asked ten different people how they enter dates in their MS Excel worksheets, you would most likely get ten different answers.
When you enter information into a cell Excel will try to deduce what type of information you are entering. For example, if you enter a number like 02262011, Excel will assume you are entering a numeric value and will treat it accordingly. Suppose the number you enter is a date without any separators? Do you think Excel can understand what you are entering? Unfortunately, it cannot.
Why you ask? The answer is because you have given Excel no indication that you want this to be a date. MS Excel keys on separators instead of numeric values. If you cannot change your input habits so that separators are also entered then you need a work-around to convert your entered information to an actual date value.
You may consider using a custom format to display the information such as ##”/”##”/”####.
This format would display the number 02262011 as 2/26/2011. The problem is that it only changes the display of the number. If you want to use the date as a real Excel date, you cannot do so because you have not converted the value into something that MS Excel will recognize as a date.
If the values you have input were very consistent in their format and if they were input as text rather than numeric values, then, there is an easy method whereby you can convert them to dates. When I say “very consistent,” I mean that the input always used two digits for the month and day and four for the year. Also, the cells containing the values must be formatted as text.
Follow the steps below to learn how:
- Select the column of dates.
- Be certain there is nothing in the column just to the right of the dates.
- Display the Data tab of the Ribbon.
- Click the Text to Columns tool.
Excel will display the Convert Text to Columns Wizard.
- Select the Fixed Width option and click on Next.
- Click on Next again.
- In the Column Data Format area, select Date.
- Select the range in the Destination box, then in your worksheet, click the cell just to the right of the first value you selected above.
- Click on Finish.