mix of date formats - converting to one

[martyp]

Hi all,

I have a spreadsheet that has a few different date formats in nearly 2000 rows in column A. I want to be able to somehow organise the sheet by date order.
The date formats I have are:
dd/mm/yyyy hh:mm
dd/mm/yyyy hh:mm:ss
yyyy.mm.dd hh:mm

Can anyone advise on the best way of sorting into date order or converting the dates?

Many thanks

 

[Datsmart]

As long as your dates are true Excel serial dates/times they will sort properly.
Excel will see all dates the same, no matter what they are formated to.
If some are text rather than dates, that is a different story.

Give your data a sort and see what you get. If it doesn't sort as you expect, you may need to convert text to dates first.

 

[martyp]

Hi John,
Thanks for your reply. When it was sorted, I got dates in the order below:
format of dd/mm/yyyy hh:mm in order (2001-2008)
then the format dd/mm/yyyy hh:mm:ss (ordered by dd rather than chronological e.g. 01/02/2002 then 02/01/2002 then 03/01/2002)
then the dates in the format yyyy.mm.dd hh:mm which are in chronological order.

 

[martyp]

Hi again,

I've sorted the dates starting with yyyy by replacing the . with /. That got those in order. I'm not sure how to sort the dates which have the hh:mm:ss at the end. I'd like to remove the :ss part...?

 

[ddadmin2009]

Check the below article for the solution.
http://funwithexcel.blogspot.com/2009/04/sorting-of-dates-with-different-formats.html
Hope this helps. Let me know if you have any questions.

 

[martyp]

Many thanks for the link. I should have thought to simply separate the date and time into columns by space and then convert the time then to hh:mm.
I've got them seperate now but could easily merge cells if needed.