Poner formato dias_horas_minutos_segundos

Jaime Franco Jimenez
20 ago 2023
4 Min. de lectura

Para el siguiente ejemplo, tenemos los siguientes datos.

En la columna tiempo, tenemos dias, horas, minutos y segundos sin un orden.

En la columna añadir, tenemos el tiempo a añadir en horas, minutos y, segundos a cada cadena de la columna tiempo.

Debemos de obtener el formato “dd hh:mm:ss”, si dias es cero, no debe de aparecer, si alguna cantidad, es decir, dias, horas, minutos, o, segundos, es de una sola cifra, debe de aparecer dos dígitos, por ejemplo, si tenemos 3 dias, debe de aparecer 03.

Vamos a usar LET, vamos a crear cuatro variables, donde almacenaremos los dias, las horas, los minutos, y, los segundos.

Empecemos…

En la celda C2, ponemos LET, creamos una variable que voy a llamar dias, como valor para la variable, usamos la función HALLAR, que no distingue entre mayúsculas y minúsculas, como argumento texto buscado, entre comillas dobles, ponemos día, como argumento dentro del texto, seleccionamos el A2:A7, he seleccionado dicho rango porque no tenemos que añadir dias, probamos variable.

=LET(dias;HALLAR("dia";A2:A7);dias)

Obtenemos una matriz desbordada en vertical con la posición de la letra “d” de la palabra día.

Si restamos dos al valor obtenido, tendremos la posición donde debemos de empezar a extraer.

=LET(dias;HALLAR("dia";A2:A7);dias)-2

Usamos la función EXTRAE, como argumento texto, seleccionamos el rango A2:A7, como argumento posición inicial, es la expresión anterior, como argumento número de caracteres, ponemos 1.

=LET(dias;EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1);dias)

Obtenemos los dias, y, error donde no hay dias, pero, vemos que nos números obtenidos se alinean a la izquierda, quiere decir que están en formato de texto, pues, antes de EXTRAE, usamos la función ABS, que nos devuelve el valor absoluto de un número, y, si es texto, nos devolverá un error.

=LET(dias;ABS(EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1));dias)

Quitamos el error con la función SI.ERROR, como argumento valor es la expresión anterior, como argumento valor si error, ponemos 0.

=LET(dias;SI.ERROR(ABS(EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1));0);dias)

Ya tenemos los dias.

Vamos a crear otra variable para almacenar las horas, la vamos a llamar horas, volvemos a usar la función HALLAR, como argumento texto buscado, entre comillas dobles, ponemos hora, como argumento dentro del texto, seleccionamos el rango A2:B7.

Probamos variable.

=LET(dias;SI.ERROR(ABS(EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1));"");horas;HALLAR("hora";A2:B7);horas)

Obtenemos una matriz desbordada de dos columnas, en la primera de ellas, aparecen las horas, en la segunda columna, aparecen las horas a sumar, y, error donde no hay coincidencia.

Para extraer las horas, debemos de retroceder dos posiciones, por lo que, a la expresión anterior, restamos 2, ponemos antes de HALLAR la función EXTRAE, como argumento texto, seleccionamos el rango A2:B7, como argumento posición inicial, es la expresión anterior, como argumento numero de caracteres, ponemos 1.

=LET(dias;SI.ERROR(ABS(EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1));"");horas;EXTRAE(A2:B7;HALLAR("hora";A2:B7)-2;1);horas)

Volvemos a obtener una matriz desbordada, en la primera columna, tenemos las horas, en la segunda columna, tenemos las horas a sumar, y, error donde no hay coincidencia, vemos de nuevo, que los valores se alinean a la izquierda, por lo que volvemos a usar la función ABS.

=LET(dias;SI.ERROR(ABS(EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1));"");horas;ABS(EXTRAE(A2:B7;HALLAR("hora";A2:B7)-2;1));horas)

Quitamos el error con la función SI.ERROR, como argumento valor es la expresión anterior, como argumento valor si error, ponemos 0.

=LET(dias;SI.ERROR(ABS(EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1));"");horas;SI.ERROR(ABS(EXTRAE(A2:B7;HALLAR("hora";A2:B7)-2;1));"");horas)

Ya tenemos las horas.

Ahora, debemos de sumar ambas columnas para obtener las horas totales, debemos de realizar la suma de forma independiente, por lo que usamos la función BYROW, como argumento array, es la expresión anterior, como argumento función, ponemos LAMBDA, declaramos una variable, que almacena el argumento array, sumamos la variable.

Probamos variable.

=LET(dias;SI.ERROR(ABS(EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1));"");horas;BYROW(SI.ERROR(ABS(EXTRAE(A2:B7;HALLAR("hora";A2:B7)-2;1));"");LAMBDA(x;SUMA(x)));horas))))

Ya tenemos las horas totales.

Vamos por los minutos, la expresión es la misma que la anterior, pero, cambiamos el argumento texto buscado de HALLAR por “minu”, en vez de restar 2 al resultado de HALLAR, restamos 3, a la variable la vamos a llamar minutos.

Probamos variable.

Vemos que tenemos un resultado de 100, y, otro de 74, quiere decir que exceden de una hora, entonces, esa hora, u, horas de más, debemos de sumarla a las horas obtenidas anteriormente.

Pero antes, vamos a guardar en otra variable, los segundos, la expresión es la misma que para los minutos, solo debemos de cambiar el argumento texto buscado de HALLAR por “según”.

Probamos variable.

Vemos una cantidad que excede de 60 segundos que es 81.

Estos son los valores que tenemos hasta ahora.

Vemos que tenemos 4 horas y 100 minutos.

Quiere decir, que, de los 100 minutos, debemos de quitar 60 minutos (1 hora), y, sumarlos a las horas, por lo que quedarían 40 minutos.

Creamos otra variable que llamaremos horas totales, como valor a la variable horas, vamos a dividir la variable minutos entre 60, nos quedamos con la parte entera, para ello, usamos la función ENTERO.

Probamos variable.

Ya tenemos las horas con la suma de las horas de los minutos.

Creamos otra variable, la llamaremos solo_minutos, abrimos un paréntesis, dividimos la variable minutos entre 60, restamos por la parte entera de la variable minutos entre 60, cerramos paréntesis, y, multiplicamos por 60.

Obtenemos los minutos habiendo restado 60, vemos que donde aparecía 100, ahora, aparece 40, que es la resta de 100 menos 60.

Creamos otra variable, la llamaremos solo_segundos, abrimos un paréntesis, dividimos la variable segundos entre 60 y restamos por la parte entera de dividir la variable segundos entre 60, cerramos paréntesis, multiplicamos por 60 segundos.

=LET(dias;SI.ERROR(ABS(EXTRAE(A2:A7;HALLAR("dia";A2:A7)-2;1));"");horas;BYROW(SI.ERROR(ABS(EXTRAE(A2:B7;HALLAR("hora";A2:B7)-2;1));"");LAMBDA(x;SUMA(x)));minutos;BYROW(SI.ERROR(ABS(EXTRAE(A2:B7;HALLAR("minu";A2:B7)-3;2));0);LAMBDA(x;SUMA(x)));segundos;BYROW(SI.ERROR(ABS(EXTRAE(A2:B7;HALLAR("segun";A2:B7)-3;2));"");LAMBDA(x;SUMA(x)));horas_totales;horas+ENTERO(minutos/60);solo_minutos;(minutos/60-ENTERO(minutos/60))*60;solo_segundos;(segundos/60-ENTERO(segundos/60))*60;solo_segundos)

Obtenemos los segundos.

Si probamos la variable segundos, vemos que uno de los valores es 81.

Y, ahora, aparece 21, porque solo hemos calculado la parte decimal.

Creamos otra variable, la llamaremos minutos_totales, ponemos la variable solo_minutos, sumamos la parte entera de dividir la variable segundos entre 60.

Probamos variable.

Tenemos los minutos habiéndole sumado aquellos segundos que eran mas de 60.

Ya tenemos los datos preparados para unirlos, para ello, creamos otra variable llamada Resultado, usamos la función BYROW, como argumento array, usamos la función APILARH, como argumento matriz1, ponemos la variable dias, como argumento matriz2, ponemos la variable horas_totales, como argumento matriz3, ponemos la variable minutos_totales, como argumento matriz4, ponemos la variable solo_segundos, cerramos paréntesis, como argumento función, ponemos LAMBDA, creamos una variable, usamos la función UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos dos puntos (:), ignoramos celdas vacías, como argumento texto1, ponemos la variable “x”, cerramos paréntesis, probamos variable.

Aceptamos, y, ya lo tenemos.

Pero, debemos de poner un cero cuando solo tenemos una cifra, para ello, antes de APILARH, ponemos la función TEXTO, como argumento valor es la función APILARH, como argumento formato, entre comillas dobles, ponemos “00”, cerramos paréntesis.

Aceptamos, y, ya tenemos nuestro ejemplo resuelto.

Miguel Angel Franco

¿ Listo para empezar a usar Microsoft Excel?

Poner formato dias_horas_minutos_segundos

Entradas recientes

Comentarios