If you are having date format or sort issues in Excel (dates sort by months instead of day of month, you can’t change the date format of a cell), it is because Excel does not recognise the date you gave it as a valid date.
Even if, to the human eye it is clearly a date, Excel may not recognise it and as a result sort it like words and refuse format changes. This could be for a variety of reasons but the most common is that your regional settings specify a different date format default to the one you are using.
Table of contents
For example on my machine a date 12/25/2010 has no meaning and Excel treats it as a word (my computer works on 2010/12/25). However, to the human eye it is clearly a date and ideally we would like Excel to recognize it as a date so that sorting works correctly and formats can be changed. Other examples that Excel normally does not see as a date include:
- 20101225 (yyyymmdd)
- 2010.25.12 (yyyy.dd.mm)
- 12252010 (mmddyyyy)
- 25.12.2010 (dd.mm.yyyy) etc
This can be a real problem as often long lists of dates are imported from other systems in this format and users need Excel to recognize it. Many users manually change the dates, taking hours or manually re-creating them in a separate column.
The ideal solution is that you get the imported dates to be corrected at the time of export. Failing that however, you can try these options.
Make Excel recognise the dates by specifying the imported setup e.g. yyyymmdd
The Text to Column tool can be tricked to correct date formats in a very logical way in that you just tell Excel what you are giving it (the current format of the dates) and it will convert the date into an acceptable format.
This is especially useful for fixing date format or sort issues in Excel as it works over large numbers of cells and (in our opinion) is very logical and reliable.
As shown below, you need to
- Highlight the cells concerned
- Click on the DATA ribbon and
- Click the TEXT to COLUMNS button.
- Normally you need to think through the next steps but for the dates trick, just click the NEXT button
The next part of the Text to Column tool is also not necessary for this date trick so click Next again as shown below
The next screen is the important one.
Here you can specify that the cells are a date (see below we clicked on the Date radio button) and then tell Excel what the imported date format is.
It is important to remember that the format it is asking for is NOT what you want the format to become, but rather what it currently is. In this example of 2010.05.01, the cells seem to be showing year, then month, then day which is not a correct format on my computer. After clicking on the Date button I choose the YMD option as this is what I see in this screen.
When you click finish you will see that the dates are converted into the format that my machine prefers. At this stage you will then be able to format it in the way you like.
For clarity, the format you would need to choose based on the initial import examples would be:
- 20101225 would be YMD
- 2010.25.12 would be YDM
- 12252010 would be MDY
- 25.12.2010 would be DMY, Etc
This should save you hours of work.
Use functions to create a template that automatically converts dates
As an alternative, perhaps you want to build some formulas into your spreadsheet template to convert ‘dates’ into Excel recognizable dates. This way, any pivot tables or charts attached to the dates will be working with valid dates and sort orders will not be a problem.
In this case you need to break the cell down into its components and then recompile it into a date for Excel. This will involve using the LEFT, RIGHT, MID and DATE functions. These functions are covered in more detail in our Intermediate Excel Course.
Given the dates below in column A, and assuming Excel does not recognize them as dates, the first thing to do is extract the year from the date. The ideal function to use is the LEFT function which extracts, starting from the left of a cell, a set number of characters. In this case we can see we need the first 4 characters.
The next step is to extract the month from the cell. In this case we know we want to extract some characters from the middle so we use the MID function and ask Excel to start at character 6 and extract 2 characters.
We now need the day, so we can use the RIGHT function which takes your specified number of characters out, but starting from the right hand side of column A.
Once we have all these underlying components in their own cells it is easy to get it into a proper date using the DATE function. Simply tell the DATE function where the cells are that contain the Year, Month and Day and Excel will do the rest as shown below.
From now on you can enter unrecognized dates in this format into column A and Excel will convert them into recognized dates in column F. You should have no more date sorting or formatting issues!
Ask Excel to find the date pattern and repeat it
A great new feature in Excel is Flash Fill which is available from Excel 2013 onwards. You can see more on the Flash Fill page, but as a quick introduction, dates can be changed using Excel’s pattern recognition (also called Artificial Intelligence). Another useful tool to address date format or sort issues in Excel.
The steps to follow are
- In the column adjacent to the column with the dates (column B in the image below)
- Type the date how you would like to see it, matching to the adjacent row e.g. cell A2 has 2010.05.01 and we have typed 2010/05/01 in cell B2
- Click on the DATA ribbon
- Click on the Flash Fill button.
If Excel can find a pattern it will replicate it on the cells below as shown in this image. Note that you should check the results as inconsistencies may not be picked up by Excel
DATEVALUE for cells that just need to be clicked
You may come across dates which Excel does not seem to recognise, and when you click in the cell suddenly Excel recognises it. In these cases you can use the DATEVALUE function which just emphasises to Excel that what it is looking at actually is a date as shown below. Note that if the date format is not similar to the default date format, DATEVALUE will not work so you will need to do one of the date tricks above.