Simplify this complex formula I've come up with for sorting mixed format time.

I have a worksheet of around 750 rows, each row having a time, accurate to the tenth of a second, and falling anywhere between 10 seconds and 9 minutes . I need to be able to sort the worksheet by the 'Time' column, however my problem was that, for reasons it would take me a long time to explain here, the times below 1 minute e.g. 53.4 seconds had to be displayed in exactly that format (53.4, not 00:53.4 "mm:ss.0"). However for the times above or equal to a minute, these do need to be formatted as mm:ss.0 This means I essentially have a list where excel thinks some of the values are numbers, and some of the values are times but needed to work out how to sort it correctly. For example this list:
5:00.1, 1:45.6, 9.3, 9:13.4, 34.5, 1.8 would be sorted into this: 1.8, 9.3, 34.5, 1:45.6, 5:00.1, 9:13.4

I have come up with a long winded method I feel will be able to be simplified, but it seems to work. Any input or suggestions would be appreciated.

The times are in column A, with the column cells formatted as "general". I next added a 'number column' in column B referencing column A but formatted as "number". This was to to convert the times of a minute or more to their time code value e.g. if there is the time 1:12.4 in a row in column A, that becomes 0.000837963 in column B but 59.6 for example remains as 59.600000000. In column C I then came up with this mean looking formula:

=ROUNDDOWN(IF(D2>=60,D2/60,D2),0)&IF(D2>=60,":",".")&IF(D2>=60,RIGHT(IF(IF(MID(A2,LEN(A2)-1,1)=".",
0,ROUND((VALUE(IF(B2>1,A2/86400,A2))*1433.3333333),0))=0,TEXT(IF(IF(B2>1,A2/86400,A2<60,IF(B2>1,A2/86400,A2),""),"ss.0")
,IF(MID(A2,LEN(A2)-1,1)=".",0,ROUNDDOWN((VALUE(IF(B2>1,A2/86400,A2))*1433.3333333),0))&":"&TEXT(IF(IF(B2>1,A2/86400,A2<60
,IF(B2>1,A2/86400,A2),""),"ss.0")),4),RIGHT(IF(IF(MID(A2,LEN(A2)-1,1)=".",0,ROUND((VALUE(IF(B2>1,A2/86400,A2))*1433.3333333),0))
=0,TEXT(IF(IF(B2>1,A2/86400,A2<60,IF(B2>1,A2/86400,A2),""),"ss.0"),IF(MID(A2,LEN(A2)-1,1)=".",0,ROUNDDOWN((VALUE(IF
(B2>1,A2/86400,A2))*1433.3333333),0))&":"&TEXT(IF(IF(B2>1,A2/86400,A2)<60,IF(B2>1,A2/86400,A2),""),"ss.0")),1))

Finally I put this formula into column D: =IF(A2<1,A2*86400,A2)

Sorting A1:D750 by column D, which is essentially a 'seconds' column (and hiding columns B,C, and D) this method seems to work in Excel 2013 as I intended, but i got a feeling i've massively over complicated the long formula in column C and it would be cool to find a method without so many helper columns needed

Hello timiop2011
A friend of mine always says me "don't change anything, if it's working good"

PS: upload sample wb if it is possible. Let's see

Maybe this could work for column B?

=IF(A1>1,IF(A1<10,"0:0"&A1,"0:"&A1),TEXT(A1,"MM:SS.0"))

Then you can sort by column B and still use column A. Do I understand your problem correctly?

excel thinks some of the values are numbers, and some of the values are times

Actually times are numbers too. Ttime is a decimal of 1 (day), so for instance 06:00 AM is actualy just 0.25, 12 noon is .05 and 06:00 PM (18:00) is 0.75. Excel formats it to something we see as a time

So you cpuld probably add a helper column to test for >1, then apply a conversion formula

Edit: Pete beat me to it

NOTE: I've just realised it's column C (not col A) that should say "visible" in it's title. column C on my worksheet is what I need (without the 00:, and to the tenth for every value including integers), so, as the raw time values are referenced from another worksheet into column A, I would sort by Column D (seconds) and have all columns except column C hidden

It appears I cant upload files yet?? so i've uploaded it to filedropper

What error message are you getting when you try to upload a WB? There are not, as far as I know, any restrictions on uploading files, except the size. If that is a concern, reduce the file to just a few samples that show a representation of what you have and what you want

We prefer that you upload to the forum, as not all members are able - or willing - to upload files from file-hosting sites

Hi FDibbins
I just opened my account here, for this thread in fact, and am not familiar with the forum. Please could you help me in telling me how I can upload a workbook? Thanks. A lot of the buttons above my reply box are greyed out, I thought this may be because I am a new member and don't yet have enough posts to post links and files etc. as I know this is how a lot of forums work.

I think Pete's method is superior, so, let's use that:

=IF(A1<1,TEXT(A1,"MM:SS.0"),TEXT(A1/60/60/24,"S.0"))

Originally Posted by npatridge

I think Pete's method is superior, so, let's use that:

=IF(A1<1,TEXT(A1,"MM:SS.0"),TEXT(A1/60/60/24,"S.0"))

Thanks. This formula now seems to get the desired time format I was looking for, however it does not help me sort these mixed times into order (quickest to slowest), so perhaps I still need a 'seconds' column using Pete's formula to allow this? Also, using Pete's formula, for all the times in mm:ss.0 format (>1min), I now seem to be getting a small gap between the start of the cell and the time. I don't get this with my initial long formula and I really don't want this gap if possible as these times will end up in col A of a professional business report and it would make the left edge look very messy.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

SortMixedTimeFormats_SampleWB.xlsx

Thanks FDibbins. I've attached the Sample Workbook
timiop is the worksheet with my original solution in,
np uses npatridge's suggested formula,
and pete is the worksheet using Pete's initial formula

This should be a good formula to fix your format issue:

=TEXT(IF(A1<1,A1,A1/60/60/24),"[<0.000694444444444444] S.0;[>=0.000694444444444444] MM:SS.0")

This might also fix your alignment issue? If not, I'm not sure what to do about that.

Try this for the conversion....
=IF(A2>1,TIMEVALUE(SUBSTITUTE(IF(ISERROR(FIND(".",A2,1)),A2&":",A2),".",":")),A2)

Originally Posted by npatridge

This should be a good formula to fix your format issue:

=TEXT(IF(A1<1,A1,A1/60/60/24),"[<0.000694444444444444] S.0;[>=0.000694444444444444] MM:SS.0")

This might also fix your alignment issue? If not, I'm not sure what to do about that.

Thanks a lot. Yes, this seems to work perfectly now. . It fixes the alignment issue and also seems to work when I choose the top sort option in excel 2013 ("sort anything that looks like a number, as a number"). This is great as it means I no longer need the seconds column. Thank You.

I made 1 small change to reduce the number of calculations in the formula so the Final formula I plan to use is this. Thanks everyone:
=TEXT(IF(A1<1,A1,A1/86400),"[<0.000694444444444444] S.0;[>=0.000694444444444444] MM:SS.0")