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