Sorting Dates

[alf40]

Hi all

My dates are sorting as per the image below.How do I get them to sort in chronological order (e.g. 01 January, 02 January etc)

Excel Forum.PNG

[wenqq3]

right click your column, format cell as "DATE". Then you can simply sort ascending or descending.

[alf40]

It didn't work mate. I've formatted the column as 'short date'. No idea what's wrong with it. Any other suggestions?

Thanks anyway though.

[FDibbins]

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info)

[wenqq3]

Do you format as dd/mm/yyyy ?

[alf40]

Yea formatted as dd/mm/yyyy

[wenqq3]

Suspect your format cell is no apply to your column.
You can try this, format G & H as dd/mm/yyyy, then at G2=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)),
drag to right(H) and drag down again. Copy and PasteSpecial (Value) to replace the A&B. Sort again base on G/H. I should worked.

[FDibbins]

none of your "dates" are actually dates, they are all text (except for B10, that I can see). Dates are actually just "special" numbers - a 5 digit number - that counts the days from 1/1/1900. A quick way to test is to use =isnumber(cell_ref) FALSE indicates text

a quick way to fix that is to high-light the data in column A, select TexttoColumns, delimited, next, date
repeat for column B

[alf40]

It works. You're a legend. Thanks

[alf40]

Great. Both worked, thanks!

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)