#### johncena69

##### New Member
I am having trouble sorting my column C from largest to smallest in time.

I need a way to format it quick because my program scrapes in seconds, minutes, and hours.

S=Seconds
M=Minutes
H=Hours

Here is a screenshot:
Screenshot by Lightshot

I can't sort based on numbers because my numbers have different units. I need to be able to do it quickly because I have a lot of data that I'll be working with so if theres a way that I can tell excel that S is seconds, M is minutes and H is hours and then with a click of a button to sort from highest to smallest that'd be great. Can anyone advise please.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### starl

The only thing I can recommend is to split your time into two columns - one with the numerical value and the other with the time unit.

So, if your combined time value is in column A, you can get the numerical value with this formula
Code:
``=LEFT(A1,LEN(A1)-1)``

You can get the time unit with this formula:
Code:
``=RIGHT(A1,1)``

Then, you need to create a custom list for sorting (Home tab, Sort & Filter drop-down, Custom Sort, choose Custom List from the Order drop-down, choose Add from the new dialog box, enter the sort order (h,m,s) press OK till you get back to the Sort dialog box and then Cancel

Select all your data and do a Sort (using the Data, Sort button), and from the drop-down, select Custom List which will open the dialog box you saw earlier and you can choose the custom sort (don't forget to choose the correct column - the one with the time units). Add a Level to your Sort dialog and choose to sort your time values.

#### vogel997

##### Active Member
 59s 00:00:59 =IF(RIGHT(A1)="h",TIME(LEFT(A1,LEN(A1)-1),,),IF(RIGHT(A1,1)="m",TIME(,LEFT(A1,LEN(A1)-1),),TIME(,,LEFT(A1,LEN(A1)-1)))) 23h 23:00:00 =IF(RIGHT(A2)="h",TIME(LEFT(A2,LEN(A2)-1),,),IF(RIGHT(A2,1)="m",TIME(,LEFT(A2,LEN(A2)-1),),TIME(,,LEFT(A2,LEN(A2)-1)))) 61m 01:01:00 =IF(RIGHT(A3)="h",TIME(LEFT(A3,LEN(A3)-1),,),IF(RIGHT(A3,1)="m",TIME(,LEFT(A3,LEN(A3)-1),),TIME(,,LEFT(A3,LEN(A3)-1)))) 49m 00:49:00 =IF(RIGHT(A4)="h",TIME(LEFT(A4,LEN(A4)-1),,),IF(RIGHT(A4,1)="m",TIME(,LEFT(A4,LEN(A4)-1),),TIME(,,LEFT(A4,LEN(A4)-1)))) 5m 00:05:00 =IF(RIGHT(A5)="h",TIME(LEFT(A5,LEN(A5)-1),,),IF(RIGHT(A5,1)="m",TIME(,LEFT(A5,LEN(A5)-1),),TIME(,,LEFT(A5,LEN(A5)-1)))) Formatting Cell ---> hh:mm:ss Try this

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Replies
3
Views
1K
Replies
11
Views
887
Replies
1
Views
369
Replies
3
Views
657
Replies
5
Views
543

### Forum statistics

1,190,796
Messages
5,982,971
Members
439,810
Latest member
phobo3s ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back