Date sort errror

Please help with this!date sort error.xlsx

October, November , and December dates get placed in front of Feb ones

Because they are not really dates, they're just text strings
And 1 (10 for october begins with 1) comes before 2 (february is 2)

Convert the dates to real dates..

Highlight column A
Data - Text To columns
Deliminated - Next
UNCheck all options - Finish

Now you can sort.

Hello jonmo,

It still is the same after making the changes you've requested.

I download the data from an online source, is there a way to have the inputs as "dates" rather than "text strings" date sort error1.xlsx

So after doing Data - Text To Columns - Delminated
And you did the Sort again, it still shows in the same order?

Put this formula in B2 and fill down
=A2+0

What does it show?

it shows #value!

OK, I think you're pc is on UK date formats
But the dates in A are in US format..

Try this in B2 and filled down
Format column B as a Date

=(MID(A2,FIND(" ",A2)-4,4)&"/"&LEFT(A2,FIND("/",A2,4)-1))+0

Then you can sort on column B

Hello,

What you have suggested solved the "date" problem. But I have a question, as you first suggested with cell=0 and it does not return a value, what ways are there for me to discern whether it showing #Value! when it is an incorrect formula or when it is just not displaying the "return results"?

Many have suggested to use just ,INT(cell) to remove the time and have the date shown but when I do that, it shows #VALUE! despite the formula being correct and all.

THANK YOU~!

I'm not sure I understand what you're asking now...

But the basic problem was that the dates you had in column A (according to Excel) were not really dates at all.
They were just text strings.
As far as Excel was concered, they were just a random grouping of characters to make a text string. No Date or Time value to them at all.

The suggestion of =A1+0
When you do a math operation on a Text string, Excel will 'TRY' to make some sense out of the string into a number of some kind. Dates are reallly just numbers.
In this case it failed, likely because your pc is using UK date format, but the dates in the file were in US date format.
This is also why the Text To Columns Failed - that's another method to make excel try to convert text into numer.
INT(cell) is another.
But still failed because Excel didn't see any numerical (or date) value in the cell. Because the date was in the wrong format.

So we had to treat it as a text string, and use Mid and left functions to extract the bit's and pieces and assemble them into a new text string with a format that Excel recognized as a date..

Generally you can use ISNUMBER to test if a date is really a date, or just a text string..

=ISNUMBER(A1)

TRUE means A1 is a numeric value (or a date)
FALSE means it's a Text String.

it displays a text string.

I have another file of maturity dates that are sort not chronologically. Can you advise?maturity dates sorting.xlsx

Same reason as before, the dates are not really dates, they're just text strings.
But in this case, they do appear to be sorted chronologically.
Because of the consisitent format always using 2 digit month and 2 digit day.
So 0 comes before 1, so the months are sorted corrrectly.


But anyway, to convert them to real dates is easier on this file, also because of the consisitent format..
=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))