Welcome to the wonderful world of times in Excel. Puzzling at first, but powerful once you know how they work.
I don't think there's a way to do it with just formatting in your situation. However, this should work (I'm assuming all your times are in Column A)
-Create this formula in B1 and copy it all the way down:
=IF(A1>=1,A1/60,A1)
-Format Column B as h:mm:ss
-Select Column B and Copy, then Paste Special, Values.
-Sorting Column B should now work fine.
That's the short answer. If you want to understand what's going on and how that formula was derived, read on:
1.
-Start a new sheet.
-In A1, type 1:05:00
-Click on A1, then Format, Cells. Note it has applied a custom format of h:mm:ss
Excel is pretty clever and that number is fairly unambiguous so it assumes you meant hours:minutes:seconds and formats accordingly.
2.
-In A2, type 33:15
-Note how it automagically changed it to 33:15:00
-Click on A2, then Format, Cells. Note a custom format of [h]:mm:ss
This is ambiguous. Did you mean "33 minutes and 15 seconds" Or "33 hours and 15 minutes"? Excel isn't sure. Its behaviour is to assume you meant hours and minutes. The [] around the h basically mean "show more than 24 hours in the hour section".
3.
-In A3, type 0:33:15 (note the 0: before)
-Click on A3, then Format, Cells. Note a custom format of h:mm:ss
Since you've taken out the ambiguity, it once again assumes assumes you meant hours:minutes:seconds again and formats accordingly.
4.
-In A4, type 23:15
-Note how it leaves it as 23:15
-Click on A4, then Format, Cells. Note a custom format of h:mm
W..T..F? How come it formatted it differently than in #2? Because you entered a number less than 24 (i.e. hours) - still ambiguous, and it still assumes you meant hours and minutes...but it formats differently.
5.
-In A5, type 1:00:00
-Click on A5, then Format, Cells. Note a custom format of h:mm:ss
-Change the format to General and note that the underlying number is .041667 (i.e. the percentage of a day)
6.
-In A6, type 24:00:00
-Click on A6, then Format, Cells. Note a custom format of [h]:mm:ss
-Change the format to General and note that the underlying number is 1 (i.e. a full day)
Almost there...
7.
-Now click on B2 and enter this formula:
=A2/60 (i.e. convert from hours to minutes)
-Click on B2, then Format, Cells. Note a custom format of [h]:mm:ss
-Note that it now shows 0:33:15, which is what you want
8.
-Now click on B1 and enter the same formula:
=A1/60 (i.e. convert from hours to minutes)
-Click on B1, then Format, Cells. Note a custom format of h:mm:ss
-Note that it shows 0:01:05 - damn - that's *not* what you want.
Stay on target...
9.
-Click on B1 again and enter this formula instead:
=IF(A1>=1,A1/60,A1)
-Click on B1, then Format, Cells. Enter a custom format of h:mm:ss
-Note that it still shows 1:05:00 (i.e. it didn't change it to 0:01:05)
So basically that formula:
-Checks to see if the number in a cell is greater than or equal to 1
-If it is greater than 1, divide by 60 (i.e. convert hours to minutes)
-If it's less than 1, leave it alone.
4What format is your column in? General, Text or Date? – Lance Roberts – 2011-07-29T16:59:50.383