MS Excel dates not sorting correctly et al

victor2

If the "rogue" dates are still showing as a date and not a number when formatted to numeric, then they have been entered incoorectly. For example, if you enter " 04/03/2021" (note the leading space) into a cell, that is text and not what I referred to as a proper date. If you enter it as "04/03/2021" (without a leading space), or even just "4/3/21" it will display as a date, but if you change the cell format to numeric, it will show 44259.00.

I have seen people use the space prefix to enter US style dates into a UK configured system and vice versa. Causes problems eventually!

Croeso69

Try formatting the cells as DD-MMM-YYYY (eg 15-MAR-2021) as it mabe getting confused between UK and US date formats.

Heedtheadvice

To try and get a bit of clarity....

Assuming your dates are in column A and col M is free (or choose as appropriate!)

In col M cells enter the formula =A2+1 (for row 2 etc.).....=An+1 for row n.

What value is shown in column M cells?

WaywardDriver

Re-reading the OP, seems similarities between my problem with dates from csv files, also Goggle comes up with others with similar problems with newer versions of Excel. It's possible some of the suggestions here might work but fail in the future with new Excel versions and possible changes to the format of the raw data.
Since I couldn't see a sustainable solution, I ended up writing a macro to import the data row by row with appropriate date conversions.

Cornucopia

Date problems are notorious in spreadsheets, and have been for a fair while.    The first stage in fixing the issue is to work out what you have (problematic because Excel tries to find a sensible way to format the data for presentation which is not always what was intended).

I agree with Croeso above:   set the date formatting to "DD-MMM-YYYY".   This will show dates as "15-Mar-2021" and it should be obvious where the problems are.   These can then be fixed using formulas or by fixing the import.   You can use the DATE function to build a proper Excel date from its component day, month and year.

lr1277

victor2 said:

If the "rogue" dates are still showing as a date and not a number when formatted to numeric, then they have been entered incoorectly. For example, if you enter " 04/03/2021" (note the leading space) into a cell, that is text and not what I referred to as a proper date. If you enter it as "04/03/2021" (without a leading space), or even just "4/3/21" it will display as a date, but if you change the cell format to numeric, it will show 44259.00.

I have seen people use the space prefix to enter US style dates into a UK configured system and vice versa. Causes problems eventually!

Thanks. That tip seems to have worked. I checked the cells and there weren't rogue spaces.

However I then re-entered the date as 4/3/2021 (and then filled down) and not only did the width of the text change, but then the data sorted correctly. Prior to this I had changed the cell format of the column to 14/03/2021* in the format cell column dialog.

TrickyDicky101

Just select your column of dates (you can select the entire column - no need just to choose the 'invalid' ones) and go:

Data>Text To Columns>Delimited>Next and under Column Date Format choose the Date option and specify in the adjacent dropdown how these 'invalid dates' appear (eg if 02/03/2020 means 2nd March you would choose DMY, if it means 3rd February then MDY) and click Finish.

Formatting does not change the value of a cell - it just changes the appearance; hence, if the cell doesn't contain a date (because Excel thinks it's text) changing the format won't convert it to a proper date.