Diferencia entre fechas

Tenemos una fecha y hora de comienzo, y, una fecha y hora de finalización.

Debemos de crear un reporte, o, informe, pero, la hora de comienzo siempre debe de empezar a partir de las 9 de la mañana, y, la hora de finalización debe de ser igual o menor a las 6 de la tarde, por ejemplo, si tenemos una fecha con la siguiente hora, 8:00, pues, dicha hora de ser reemplazada por 9:00, si tenemos una fecha y la siguiente hora, 19:00, dicha hora debe de ser sustituida por 18:00, y, después calculamos la diferencia de horas.

Debemos de excluir los fines de semanas.

En una celda, usamos LET, creamos una variable, seleccionamos el rango A2:A10, creamos otra variable, y, nos quedamos con la parte entera de la variable “a”, creamos otra variable, seleccionamos el rango B2:B10, creamos otra variable, y, nos quedamos con la parte entera de la variable “c”.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c)

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, preguntamos que si la resta de la variable “a” menos la variable “b”, es decir, la parte decimal que corresponde con la hora, y, comparamos con menor a la celda E1, como argumento valor si verdadero, ponemos la variable “b” y sumamos 0,375, este valor corresponde a las 9:00, ¿Cómo lo he calculado?, pues con una regla de tres, he puesto que, si 24 horas equivale a 1 día, a cuanto equivale el número 9.

La operación seria:

Pues, 0,375 equivale a las 9:00.

Como argumento valor si falso, ponemos la variable “a”.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c);e;SI(a-b<$E$1;b+0,375;a);e)

Seleccionamos las fechas, pulsamos CTRL mas 1 para ir a formato de celdas, en la ventana de la izquierda, seleccionamos personalizada.

En la ventana tipo, ponemos dd/mm/aaaa hh:mm.

También podemos seleccionar el formato de uno de los valores de la ventana inferior a la ventana tipo.

Aceptamos.

Podemos ver que donde la hora de cada fecha era inferior a las 9:00, ahora, pone 9:00.

Creamos otra variable, volvemos a usar el condicional SI, como argumento prueba lógica, preguntamos que, si la diferencia entre la variable “c” y la variable “d” es mayor al valor de la celda E2, como argumento valor si verdadero, ponemos la variable “d” y sumamos 0,75, que son las 18:00, como argumento valor si falso, ponemos la variable “c”.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c);e;SI(a-b<E1;b+0,375;a);f;SI(c-d>E2;d+0,75;c);f)

Tenemos las fechas y horas de fin, pero donde la hora es superior a las 18:00, pone las 18:00.

Creamos otra variable, restamos la variable ”f” con la variable “e”.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c);e;SI(a-b<$E$1;b+0,375;a);f;SI(c-d>$E$2;d+0,75;c);g;f-e;g)

Lo ponemos en formato de hora, y, ya tenemos la diferencia de horas.

Creamos otra variable, usamos la funcion APILARH, como argumento matriz1, ponemos la variable “e”, como argumento matriz2, ponemos la variable “f”.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c);e;SI(a-b<$E$1;b+0,375;a);f;SI(c-d>$E$2;d+0,75;c);g;f-e;h;APILARH(e;f);h)

Tenemos una matriz desbordada de dos columnas, con las fechas de inicio, y, de fin.

Antes de APILARH, usamos la funcion TEXTO, como argumento valor es la funcion APILARH, como argumento formato, entre comillas dobles, ponemos “dddd” para obtener el día de la semana.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c);e;SI(a-b<$E$1;b+0,375;a);f;SI(c-d>$E$2;d+0,75;c);g;f-e;h;TEXTO(APILARH(e;f);"dddd");h)

Creamos otra variable, usamos la funcion BYROW, como argumento array, ponemos la variable “h”, como argumento funcion, ponemos LAMBDA, declaramos una variable, ponemos el operador lógico O, como argumento valor logico1, ponemos la variable “x”, y comparamos con sábado, como argumento valor logico2, ponemos la variable “x”, y, comparamos con domingo.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c);e;SI(a-b<$E$1;b+0,375;a);f;SI(c-d>$E$2;d+0,75;c);g;f-e;h;TEXTO(APILARH(e;f);"dddd");i;BYROW(h;LAMBDA(x;O(x="sábado";x="domingo")));i)

Obtenemos VERDADERO donde es sábado o domingo en cualquiera de las dos columnas, y, FALSO donde no lo es.

Creamos otra variable, usamos la funcion APILARH, como argumento matriz1, ponemos la variable “e”, como argumento matriz2, ponemos la variable “f”, como argumento matriz3, ponemos la variable “g”.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c);e;SI(a-b<$E$1;b+0,375;a);f;SI(c-d>$E$2;d+0,75;c);g;f-e;h;TEXTO(APILARH(e;f);"dddd");i;BYROW(h;LAMBDA(x;O(x="sábado";x="domingo")));j;APILARH(e;f;g);j)

Usamos el argumento cálculo de LET, usamos la funcion FILTRAR, como argumento array, ponemos la variable “j”, como argumento Include, ponemos la variable “i” e igualamos a FALSO, con esto, vamos a quitar los registros que sean sábados o domingos.

=LET(a;A2:A10;b;ENTERO(a);c;B2:B10;d;ENTERO(c);e;SI(a-b<$E$1;b+0,375;a);f;SI(c-d>$E$2;d+0,75;c);g;f-e;h;TEXTO(APILARH(e;f);"dddd");i;BYROW(h;LAMBDA(x;O(x="sábado";x="domingo")));j;APILARH(e;f;g);FILTRAR(j;i=FALSO))

Seleccionamos la columna de horas, pulsamos CRTL mas 1, en la ventana de categorías, seleccionamos personalizada, en la ventana tipo, ponemos hh:mm, con esto quitamos los segundos.

Miguel Angel Franco