Es posible crear un formato personalizado para escribir 1530 y que se vea como 15:30. Pero para que Excel reconozca eso como un dato en horas y minutos, hay que hacer algo más. Comencemos por el formato:
Seleccionamos el rango donde vamos a escribir las horas.
1. Tomamos las opciones [Formato/ Celdas].
2. En la ficha [Número] seleccionamos la categoría [Personalizada].
3. Dentro de [Tipo] escribimos 00“:”00.
4. Hacemos clic en [Aceptar].
Con el formato creado, Excel tomará el dato que hayamos escrito e insertará los dos puntos entre las dos primeras cifras y las dos últimas.
Hasta aquí logramos que, escribiendo 1530, el dato se vea como 15:30. Pero el valor de la celda seguirá siendo 1530.
Hay que hacer algo más para que eso represente “quince horas con treinta minutos”.
La fórmula que hace esta última conversión es bastante compleja. Primero la decimos y luego la explicamos. Si el dato original está en [A2], el valor convertido a horas y minutos se obtiene con:
=ENTERO(A2/100)/24+RESIDUO(A2;1 00)/1440.
Vamos por partes. La expresión ENTERO(A2/100) divide el valor de [A2] por 100 y se queda con la parte entera. Si [A2] vale 1530, el resultado de esta expresión es 15. Al dividir ese 15 por 24, estamos convirtiendo el quince en la fracción de día que corresponde a quince horas.
La expresión RESIDUO(A2;100) divide el valor de [A2] por 100 y se queda con el resto. Si [A2] vale 1530, el resultado de esta expresión es 30. Al dividir ese 30 por 1440, estamos convirtiendo el quince en la fracción de día que corresponde a 30 minutos (hay 1440 minutos en un día).
Para finalizar, el resultado de sumar ambas expresiones es el valor, en días, de las horas y minutos obtenidos en [A2]. En Excel, los datos que representan tiempos (fechas u horas) deben expresarse en días, independientemente del formato aplicado. Tenemos que hacer esta transformación tanto con la hora de entrada como con la de salida, para poder restarlas y calcular el tiempo transcurrido entre ambas.
Así creamos un formato personalizado para que Excel tome un número de cuatro cifras y separe las dos mitades con dos puntos.
La fórmula de la celda [B2] convierte el número de cuatro cifras de la celda [A2] en un valor de horas y minutos. Todo el cálculo expresa ese valor en días, como corresponde a un dato “tipo hora”.
Hola, me inicio en esto e hice tal cual esta, pero a la hora de sumar me da lo siguiente:
columna A columna B columna C
18:30 21:00 02:70
————————————————
Con el Formato ..( hh:mm)
columna A columna B columna C
18:30 21:00 02:30
acá tengo que colocar los dos puntos.
————————————————
se puede solucionar la suma de 02:70
Porque esta muy bueno no colocar los dos puntos.
Tendría alguna solución??????
muchas gracias!
consulta: cómo calculás el intervalo de tiempo entre dos datos de horas ingresadas como indicás en este post?
MIL GRACIAS!! me salvaste!
Amigo, se que han pasado 4 años… pero encontré una solución para mi y es que la formula tiene un “error” o mejor dicho modificación en excel 2010… por lo que debe quedar asi:
=ENTERO(A2/100)/24+RESTO(A2;100)/1440
si te fijas cambiamos “RESIDUO” por “RESTO”, cabe mencionar que la celda que llevará la formula debe tener formato hh:mm.
Gracias a quien escribió el articulo, me fue de mucha ayuda.
Saludos.
Muchas gracias, me sirvio mucho.
Saludos colega. Aplicando la primera alternativa que plantea el articulo. En vez de colocar en el formato de celda personalizado 00″:”00 tendrías que colocar 00″:”00″:”00 y listo. Super sencillo mi pana 😉
Hola muy buen aporte, pero tengo un problema, si tengo las horas en seis cifras y no están separadas por puntos porque asi vino en la base de datos, como la paso a hh:mm:ss, por ejemplo: vino 143525 que son en realidad las 14:35:25 (hh:mm:ss), como podría pasar esto con los dos puntos? Para que asi excel lo pueda leer como tiempo y no como numero. Gracias por su ayuda.
Esta consulta aparecerá publicada en una futura edición de USERS. Si querés, escribime a [email protected] y decime tu nombre completo y lugar de residencia.
Se puede hacer convirtiendo a texto, pero deja de ser una hora para Excel. Si el dato tipo hora está en B2, la fórmula es:
=SI(HORA(B2)=0;24;TEXTO(HORA(B2);”00″))&”:”&TEXTO(MINUTO(B2);”00″)
Esta fórmula reemplaza el 00 de las horas por 24 y concatena los minutos.
Excelente trabajo, pero una pregunta como puedo consistencia para que la hora sea de 1 a 24 y minutos de 0 a 59
Hola, todo bien con la primera parte, aparecen los dos puntos sin problemas pero cuando en la cela pongo la segunda formula para que excel reconozca el numero en formato hora no aparece el numero, aparece #¿nombre? a que se debe?
Si te referís a la celda de la columna B donde escribís la fórmula, aplicale luego formato de horas y minutos.
Tenés que aplicarle formato de horas y minutos.
En la celda que contiene la fórmula me quedó un valor con decimales
Gracias me fue de mucha ayuda!!! saludos.
No me funciono, aparece una cifra con ese formato que parece representar dias..