I have a column of data that represents duration, e.g. 33:15 - 30 minutes and 15 seconds; 1:05:00 - 1 hour and 5 minutes, etc.
If I try to sort it A-Z, then 1 hour gets sorted before 30 minutes.
Is there a way to format the data to make it sort correctly? Formatting solution is preferred to converting this data into seconds or whatnot.
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.
If your values are interpreted by Excel as actual times, then they are sorted numerically, not alphabetically. But there are problems.
If you enter minutes and seconds as 30:00, Excel is going to interpret is as 30 hours and 0 minutes. You have to enter 30 minutes as 0:30:00 or 0:30. So 30 minutes entered like this will be interpreted as larger than one hour thirty minutes entered as 1:30. You should always check the formula bar after entering numbers to make sure Excel recorded the value you intended to enter.
CompWiz missed this intricacy, and Craig's routines just correct for your specific problems. But knowing you have to enter times as h:mm:ss even if the value is less than an hour will mean your values will always be interpreted correctly.
In short... times... as in number-of hours/minutes/seconds is actually sorted as character data. 1 is lower than 30 and 1h is less than 30m because 1 is less than 3. You need to store your data as all-seconds... i.e. 30-minutes is entered as 1800 and 1 hour as 3600 and use some complex cell-formatting to display them as hour/minute/seconds... or enter the data as 00:30:00 and 01:00:00 respectively.
the first is the proper method but very complex to implement... the second is much simpler and always will work because 00: comes before 01: every time.
