Excel time formatting, please help.

[johncena69]

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.

 

[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

=LEFT(A1,LEN(A1)-1)

You can get the time unit with this formula:

=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]

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>