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

 

[p45cal]

Since you're having difficulty getting the formatting to work I'd gues
these dates are just text. I'd do everything I could to get these date
as proper dates, either by converting them within excel, or by changin
the way the data gets into Excel. Sorting them is then dead easy.
Could you post a sample file and relevant code at thecodecage.com

 

[p45cal]

I'm going to assume they're text. Paste the following code into a
standard code module:Sub blah()
For Each cll In Selection.Cells
cll.Value = MakeRealDateTime(cll.Value)
Next cll
End Sub

Function MakeRealDateTime(DateTimeStr)
On Error Resume Next
TestDateError = Replace(DateTimeStr, "*", "") 'removes any asterisks
TestDateError = Application.WorksheetFunction.Trim(TestDateError)
'removes leading and trailing spaces, as well as reducing multiple
spaces to one within the text
TestDateError = Replace(TestDateError, ".", "/") 'replaces full stops
with slashes
TestDateError = DateValue(TestDateError) + TimeValue(TestDateError) 'do
the conversion, assumes you're using UK locale
MakeRealDateTime = IIf(IsDate(TestDateError), TestDateError,
DateTimeStr)
End Function
Select a bunch of date/time cells and run blah. It makes
the conversion in situ. Now sort normally. Tested here and works
(xl2003).
If you select cells which can't be converted to dates it leaves the
value in place, but if you've selected cells with formulae in, then if
they can be converted they will be. All cells processed will lose any
formula, but retain value. It's OK to try and convert the same cell more
than once.
You're using UK style dates and your Excel is using the UK locale,
right?

 

[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.

 

[p45cal]

re:"I think I'll convert to English date format rather than the U
locale."

I thought they were the same?!

Tell me how you get on

 

[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.