Fechas de inicio y finalización

Para el siguiente ejemplo, tenemos una fecha de comienzo, una fecha de final, y, una máquina.

La semana comienza el lunes a las seis de la mañana, y, termina el domingo a las seis de la mañana.

Debemos de calcular las fechas de inicio y finalización junto con la duración en horas para todas las semanas.

Así que, básicamente, tendremos que dividir las filas en semanas en las que la semana comienza a las 6 de la mañana y termina a las 6 de la mañana después de 7 días.

Lo voy a realizar por pasos, después, agregaré el ejercicio con la funcion LET, donde se usan las mismas funciones.

En la celda B5, vamos a restar la fecha mayor menos la fecha menor.

=C2-B2

Nos devuelve:

Usamos la funcion REDONDEAR, donde como argumento numero es la resta, y, como argumento numero de decimales, ponemos 0.

Obtenemos 14.

En la celda C5, usamos SECUENCIA, omitimos el argumento filas, como argumento columnas, ponemos la celda B5, como argumento inicio, ponemos la celda B2, que es la fecha de comienzo.

Obtenemos una matriz desbordada en horizontal con una serie de fechas empezando desde 01/08/23 hasta el 14/08/23.

En la celda B7, dividimos el valor de B5 entre seis días.

=B5/6

Nos devuelve:

Redondeamos hacia arriba con la funcion REDONDEAR.MAS, donde como argumento numero es la división, como argumento número de decimales, ponemos 0.

=REDONDEAR.MAS(B5/6;0)

Nos devuelve 3.

Usamos la funcion SECUENCIA, como argumento filas, es la funcion anterior, omitimos el argumento columnas, omitimos el argumento inicio, como argumento paso, ponemos 6.

Obtenemos una matriz desbordada en vertical con las fechas a rescatar de las fechas obtenidas anteriormente.

En la celda C7, usamos INDICE, como argumento matriz, ponemos la celda C5 junto con el operador de rango derramado, omitimos el argumento numero de fila, como argumento numero de columna, ponemos la celda B7 junto con el operador de rango derramado.

=INDICE(C5#;;B7#)

Obtenemos las fechas de comienzo.

La ultima fecha debe de ser la fecha de finalización, en la celda D7, usamos el condicional SI, como argumento prueba lógica, preguntamos si la celda C7 junto con el operador de rango derramado mas 6, es mayor al valor de la fecha de finalización (C2), en ese caso, que nos devuelva el valor de la fecha de finalización, en caso contrario, que devuelva el valor de la celda C7 junto con el operador de rango derramado más 6.

=SI(C7#+6>C2;C2;C7#+6)

Obtenemos la fecha de finalización para cada fecha de comienzo, y, vemos que la ultima fecha es la fecha de finalización.

En la celda C11, usamos la funcion APILARH, como argumento matriz1, seleccionamos la celda C7 junto con el operador de rango derramado, como argumento matriz2, seleccionamos la celda D7 junto con el operador de rango derramado.

=APILARH(C7#;D7#)

Vamos por las horas, en la celda C15, preguntamos si algún valor de la matriz C11# es diferente a la fecha de comienzo o de fin.

=C11#<>B2:C2

Obtenemos VERDADERO donde la condición se cumple, y, FALSO donde no se cumple.

En la celda C19, preguntamos si algún valor de la matriz C15# es igual a VERDADERO, que nos devuelva la parte entera, y, sumamos 0,25 que son las seis, en caso contrario, que nos devuelva la matriz C11#.

=SI(C15#;ENTERO(C11#)+0,25;C11#)

Tenemos las fechas de comienzo y de fin, pero vemos que, exceptuando la fecha de inicio, y, de finalización, el resto de las horas aparece como las seis.

Ahora vamos a calcular la cantidad de horas por cada fecha, en la celda E19, restamos el rango D19:D21 menos el rango C19:C21 y multiplicamos por 24 horas.

=(D19:D21-C19:C21)*24

Obtenemos las horas trabajadas para cada fecha.

Usamos la funcion REDONDEAR, y, redondeamos a dos decimales.

=REDONDEAR((D19:D21-C19:C21)*24;2)

Ya tenemos nuestro ejercicio realizado.

Ahora, lo vamos a realizar con la funcion LET, aunque las expresiones a usar son las mismas.

En un ahoja nueva, en la celda B5, ponemos LET, creamos una variable, nos traemos la primera expresión, y, probamos variable.

=LET(a;REDONDEAR(C2-B2;0);a)

Obtenemos 14, igual que antes.

Creamos otra variable, nos traemos la expresión donde tuvimos las fechas, pero, debemos de sustituir B5 por la variable “a”.

=LET(a;REDONDEAR(C2-B2;0);

Pues, obtenemos las fechas.

Creamos otra variable, y, nos traemos la siguiente expresión:

SECUENCIA(REDONDEAR.MAS(B5/6;0);;;6)

Cambiamos B5 por la variable “a”.

=LET(a;REDONDEAR(C2-B2;0);b;SECUENCIA(;a;B2);c;SECUENCIA(;REDONDEAR.MAS(a/6;0);1;6)

Creamos otra variable, nos traemos la siguiente expresión:

INDICE(C5#;;B7#)

Pero usamos la funcion ENCOL para obtener las fechas en una columna, cambiamos C5# por la variable “b”, cambiamos B7# por la variable “c”.

=LET(a;REDONDEAR(C2-B2;0);b;SECUENCIA(;a;B2);c;SECUENCIA(;REDONDEAR.MAS(a/6;0);1;6);d;ENCOL(INDICE(b;;c))

Creamos otra variable para traernos la siguiente expresión:

SI(C7#+6>C2;C2;C7#+6)

Cambiamos C7# por la variable “d”.

=LET(a;REDONDEAR(C2-B2;0);b;SECUENCIA(;a;B2);c;SECUENCIA(;REDONDEAR.MAS(a/6;0);1;6);d;ENCOL(INDICE(b;;c));e;SI(d+6>C2;C2;d+6)

Creamos otra variable, y, nos traemos la siguiente expresión:

APILARH(C7#;D7#)

Cambiamos C7# por la variable “d” y D7# por la variable “e”.

=LET(a;REDONDEAR(C2-B2;0);b;SECUENCIA(;a;B2);c;SECUENCIA(;REDONDEAR.MAS(a/6;0);1;6);d;ENCOL(INDICE(b;;c));e;SI(d+6>C2;C2;d+6);f;APILARH(d;e)

Creamos otra variable, nos traemos la expresión:

C11#<>B2:C2

Cambiamos C11# por la variable “f”.

=LET(a;REDONDEAR(C2-B2;0);b;SECUENCIA(;a;B2);c;SECUENCIA(;REDONDEAR.MAS(a/6;0);1;6);d;ENCOL(INDICE(b;;c));e;SI(d+6>C2;C2;d+6);f;APILARH(d;e);g;f<>B2:C2

Creamos otra variable, nos traemos la expresión:

SI(C15#;ENTERO(C11#)+0,25;C11#)

Cambiamos C15# por la variable “g”, C11# por la variable “f”.

=LET(a;REDONDEAR(C2-B2;0);b;SECUENCIA(;a;B2);c;SECUENCIA(;REDONDEAR.MAS(a/6;0);1;6);d;ENCOL(INDICE(b;;c));e;SI(d+6>C2;C2;d+6);f;APILARH(d;e);g;f<>B2:C2;h;SI(g;ENTERO(f)+0,25;f)

Como argumento cálculo de LET, usamos APILARH, como argumento matriz1, es la variable “h”, como argumento matriz2, usamos la funcion REDONDEAR, como argumento número, entre paréntesis, usamos INDICE, como argumento matriz, es la variable “h”, omitimos el argumento numero de fila, como argumento numero de columna ponemos 2, restamos de nuevo con la función INDICE, y, solo debemos de cambiar el argumento número de columna, y, poner 1, multiplicamos por 24, como argumento número de decimales ponemos 2.

=LET(a;REDONDEAR(C2-B2;0);b;SECUENCIA(;a;B2);c;SECUENCIA(;REDONDEAR.MAS(a/6;0);1;6);d;ENCOL(INDICE(b;;c));e;SI(d+6>C2;C2;d+6);f;APILARH(d;e);g;f<>B2:C2;h;SI(g;ENTERO(f)+0,25;f);APILARH(h;REDONDEAR((INDICE(h;;2)-INDICE(h;;1))*24;2)))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco