sorting dates

[Tom]

Anybody have ideas on how to sort dates when they are in this format:
dd.mm.yyyy hh:mm:ss?

Here is my current code:

'***
Range(Cells(3, 5), Cells(500, 55)).Select

Selection.Sort Key1:=Range(Cells(3, 5), Cells(3, 55)),
Order1:=xlAscending,_ Header:=xlGuess, OrderCustom:=1,
MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
'***

The dates are in row 3 and this is the Key I want to use. The code
above works great as long as the date data is within the same month.
However, once another month of date data is inserted the sorting is
lost because Excel is sorting based on the first "dd".

I tried to use a format() statement in my sort but can't get it to
work. For example, this is what I want to do but this code does not
compile:

Selection.Sort Format(Range("E3").Value, "dd" & "mm" & " yyyy"),_
Key1:=Range(Cells(3, 5), Cells(3, 55)), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight,
_
DataOption1:=xlSortNormal

I've tried several iterations of the above code with no luck. I'm
hoping there is an easy way to do this without re-arranging the date
data in row 3.

thanks for you help...

~Tom

 

[Tom]

The dates are located in Row 3 and look like this:

01.09.2009 13:54:30 05.08.2009 13:54:30  12.08.2009 10:06:56 
12.08.2009 10:38:25 

If the sort function is run on the above four columns of data, it will
return the data in the order above; the reason is because 01 is less
than 05 and less than 12, even though the month changes from August to
September. I have pasted the code that I am using but I cannot get
the format() to work and let Excel know that the data is in dd/mm/yyyy
format.

thanks for any help you could offer.

~Tom

 

[Tom]

Thanks. Let me try your code below. I think once the "." are
replaced with "/" Excel should start recognizing the data as dates.
In the end I think I'll convert to English date format rather than the
UK locale.

 

[Tom]

The UK locale uses day-month-year and the English (U.S) format uses
month-day-year. Sorry, I should've clarified that in my earlier
post. I still haven't coded this in yet. I think I'll give it a go
tomorrow.