☰

Cómo ordenar por hh:mm:ss (duración) en Excel

tag-icon • tag-icon microsoft-excel sorting
Cómo ordenar por hh:mm:ss (duración) en Excel

Tengo una columna de datos que representa la duración, por ejemplo, 33:15 - 30 minutos y 15 segundos; 1:05:00 - 1 hora y 5 minutos, etc.

Si intento ordenarlo AZ, entonces 1 hora se ordena antes de 30 minutos.

¿Hay alguna manera de formatear los datos para que se ordenen correctamente? Se prefiere la solución de formato a convertir estos datos en segundos o cualquier otra cosa.

Respuesta1

Bienvenido al maravilloso mundo de los tiempos en Excel. Desconcertante al principio, pero poderoso una vez que sabes cómo funcionan.

No creo que haya una manera de hacerlo simplemente formateando en tu situación. Sin embargo, esto debería funcionar (supongo que todos tus tiempos están en la Columna 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.

Esa es la respuesta corta. Si quieres entender qué está pasando y cómo se derivó esa fórmula, sigue leyendo:

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 es bastante inteligente y ese número es bastante inequívoco, por lo que supone que quiso decir horas: minutos: segundos y lo formatea en consecuencia.

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

Esto es ambiguo. ¿Quiso decir "33 minutos y 15 segundos" o "33 horas y 15 minutos"? Excel no está seguro. Su comportamiento es asumir que te refieres a horas y minutos. El [] alrededor de la h básicamente significa "mostrar más de 24 horas en la sección de horas".

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

Dado que eliminó la ambigüedad, una vez más se supone que quiso decir horas: minutos: segundos nuevamente y formatea en consecuencia.

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

¿Qué...? ¿Cómo es que lo formateó de manera diferente que en el n.° 2? Debido a que ingresó un número menor a 24 (es decir, horas), sigue siendo ambiguo y todavía se supone que quiso decir horas y minutos... pero tiene un formato diferente.

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)

Casi llegamos...

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.

Manténgase en el objetivo...

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)

Básicamente esa fórmula:

-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.

Respuesta2

Si Excel interpreta sus valores como tiempos reales, entonces se ordenan numéricamente, no alfabéticamente. Pero hay problemas.

Si ingresa minutos y segundos como 30:00, Excel interpretará que son 30 horas y 0 minutos. Tienes que ingresar 30 minutos como 0:30:00 o 0:30. Por lo tanto, 30 minutos ingresados ​​de esta manera se interpretarán como mayores que una hora y treinta minutos ingresados ​​como 1:30. Siempre debe verificar la barra de fórmulas después de ingresar números para asegurarse de que Excel haya registrado el valor que desea ingresar.

CompWiz pasó por alto esta complejidad y las rutinas de Craig simplemente corrigen sus problemas específicos. Pero saber que debe ingresar los tiempos como h:mm:ss incluso si el valor es inferior a una hora significará que sus valores siempre se interpretarán correctamente.

Respuesta3

En resumen... tiempos... como en número de horas/minutos/segundos en realidad se ordena como datos de caracteres. 1 es inferior a 30 y 1 h es inferior a 30 m porque 1 es inferior a 3. Debe almacenar sus datos como segundos... es decir, 30 minutos se ingresan como 1800 y 1 hora como 3600 y usa alguna celda compleja. formateándolos para mostrarlos como hora/minuto/segundo...oingrese los datos como 00:30:00 y 01:00:00 respectivamente.

el primero es eladecuadométodo pero muy complejo de implementar... el segundo es mucho más simple y siempre funcionará porque 00: viene antes de 01: siempre.

información relacionada