Excel date formatting NOT working? Dates not sorting in Excel?

For images see blog post : https://www.auditexcel.co.za/blog/fix...
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.
------------------------------------------------------------------------------------------------------------------------------------------------------

✅- Free check of your Excel Skill Level- https://excel-skills-assessment.com/s...
🏅- Test employee Excel skill levels- https://excel-skills-assessment.com/e...
🏆- Improve you Excel Skill Levels- https://online-excel-training.auditex...
🇿🇦- Our main website- https://www.auditexcel.co.za/blogs/

------------------------------------------------------------------------------------------------------------------------------------------------------

⏲Time Stamps
00:00 Intro of the date sort order not working
00:36 Reason these 'dates' are sorting badly
00:47 How do we know if a date is a valid Excel date
01:08 Convert the 'dates' to valid Excel dates

------------------------------------------------------------------------------------------------------------------------------------------------------
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.

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

1. Highlight the cells concerned
2. Click on the DATA ribbon and
3. Click the TEXT to COLUMNS button.
4. 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.