Dates - issue with trying to sort

[WendyHubard]

Hello Guru's. I have an excel spreadsheet that has been compiled by different external files. I'm having issues with trying to sort by date as the formats vary. They appear to be the same "dd/mmm/yyyy" BUT when I have checked the cells, some are dd/mm/yyyy and others are dd/mmm/yyyy. is there anyway to convert them so they are all are the same and i can then sort the data? Have attached, but it's probably confusing as in this thread it displays correctly, but not in my spreadsheet. You might need to copy and paste into a spreadsheet to see issue. Many thanks in advance.

01/Jan/2022

01/Jan/2022

01/Jan/2022

02/Jan/2022

02/Jan/2022

04/Jan/2022

04/Jan/2022

04/Jan/2022

04/Jan/2022

04/Jan/2022

05/Jan/2022

05/Jan/2022

05/Jan/2022

05/Jan/2022

05/Jan/2022

05/Jan/2022

05/Jan/2022

05/Jan/2022

05/Jan/2022

05/Jan/2022

30/Jan/2023

30/Mar/2023

30/Mar/2023

30/Mar/2023

31/Jan/2023

31/Jan/2023

31/Jan/2023

31/Jan/2023

31/Jan/2023

31/Mar/2023

31/Mar/2023

31/Mar/2023

31/Mar/2023

31/Mar/2023

 

[SunnyAlv]

You can use this Formula to convert some incorrect formats to your desired format

=TEXT(A1,"dd/mmm/yyyy")

 

[Alex Blakenburg]

The sort should only be affected if some or all the dates are being recognised as Text by Excel. (Sunny's suggestion would make this worse)
If you want to do a one off conversion
• Highlight the column
• Data > Text to Columns > Delimited
• Delimiters will default to Tab - just click Next
• In the next screen select Date and Selecd D/M/Y
Either change the destination or leave it an copy over the top
• Finish

As a formula:

=VALUE(A1)

or

=--(A1)

Copy the formula to all rows and format the column in your desired date format,

 

[WendyHubard]

Thankyou both for the quick replies. Have gone with Alex's solution and it's worked a treat!!!!! Thank you so much

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.