Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting dates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting dates
Since you're having difficulty getting the formatting to work I'd guess these dates are just text. I'd do everything I could to get these dates as proper dates, either by converting them within excel, or by changing 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 *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=130283 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting dates
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 On Sep 1, 12:20*am, p45cal wrote: Since you're having difficulty getting the formatting to work I'd guess these dates are just text. I'd do everything I could to get these dates as proper dates, either by converting them within excel, or by changing 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 *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=130283 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting dates
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? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=130283 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting dates
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. On Sep 1, 1:15*pm, p45cal wrote: 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? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=130283 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting dates
"I think I'll convert to English date format rather than the UK locale." I thought they were the same?! Tell me how you get on. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=130283 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting dates
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. On Sep 1, 3:14*pm, p45cal wrote: "I think I'll convert to English date format rather than the UK locale." I thought they were the same?! Tell me how you get on. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=130283 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting dates | Excel Programming | |||
Sorting dates | Excel Discussion (Misc queries) | |||
Sorting Dates | Excel Discussion (Misc queries) | |||
Sorting Dates | Excel Worksheet Functions | |||
Sorting dates | Excel Worksheet Functions |