Trouble with sorting cells that are dates (and custom as " d:m:h:s ")... & the sort doesn't always get it right...(?)

[kbishop94]

edit: sorry, but I typed the title incorrect. It wont allow me to edit that either. It should say: " d:h:m:s "

numbers that I have formatted so they appear this way:


like this:


do not sort in the correct order when I use:

ActiveWorkbook.Worksheets("NOT-ACTIVE").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("NOT-ACTIVE").Sort.SortFields.Add Key:=Range("N15", Range("N15").End(xlToRight)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

And it doesnt make any difference how I format the cells, custom (I tried many different ways), general, time, date, text... it always comes out the same.



Any suggestions for how can I change the format or number sequence in order to make it sort correctly?


When sorted from left-to-right, it looks like this: (all is in the correct order until the cells that are circled in red)

 

[arun_eagertolearn]

Hi

I feel the issue is with the way the format is

With the formula INT(N6-N18) & ":"& TEXT(N6-N18, "h:m:s"), a string is formed
In this string (it is not a number), 6>1 implying 6>15

To avoid this, please do a straight subtraction N6-N18 (no INT....&TEXT), then go to cell formatting (Cntl +1), select Custom-->General-->
edit 'General' and type dd hh:mm:ss
This should do the sorting correctly

Please check and let know whether this works

 

[kbishop94]

Hi

I feel the issue is with the way the format is

With the formula INT(N6-N18) & ":"& TEXT(N6-N18, "h:m:s"), a string is formed
In this string (it is not a number), 6>1 implying 6>15

To avoid this, please do a straight subtraction N6-N18 (no INT....&TEXT), then go to cell formatting (Cntl +1), select Custom-->General-->
edit 'General' and type dd hh:mm:ss
This should do the sorting correctly

Please check and let know whether this works

Thank you for the prompt response.

and... that did it!!!

I didnt think that it was going to make a difference with how it was going to sort (but I was loving how much simpler it was!), but sure enough, you were right and it appears to have sorted it out correctly!

(I say 'appears' because I still haven't looked at all the data yet real close, but I think it def did work.) Thanks again!