Fórmula genérica
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Resumen
Para calcular las horas de trabajo entre dos fechas con un horario personalizado, puede usar una fórmula basada en las funciones WEEKDAY y SUMPRODUCT, con la ayuda de ROW, INDIRECT y MID. En el ejemplo que se muestra, la fórmula en F8 es:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Lo que devuelve 36 horas, según un programa personalizado en el que se trabajan 8 horas de lunes a viernes, 4 horas se trabajan el sábado y el lunes 3 de septiembre es feriado. Los días festivos se suministran como el rango denominado G6: G8. El horario de trabajo se ingresa como una cadena de texto en la columna D y se puede cambiar según se desee.
Nota: Esta es una fórmula de matriz que debe ingresarse con Control + Shift + Enter. Si tiene una jornada laboral estándar de 8 horas, esta fórmula es más sencilla.
Explicación
En esencia, esta fórmula usa la función WEEKDAY para calcular el día de la semana (es decir, lunes, martes, etc.) para cada día entre las dos fechas dadas. WEEKDAY devuelve un número entre 1 y 7. Con la configuración predeterminada, domingo = 1 y sábado = 7.
El truco de esta fórmula es ensamblar una serie de fechas que puede introducir en la función WEEKDAY. Esto se hace con FILA con INDIRECTO:
ROW(INDIRECT(B6&":"&C6))
ROW interpreta las fechas concatenadas como números de fila y devuelve una matriz como esta:
(43346;43347;43348;43349;43350;43351;43352)
Cada número de la matriz representa una fecha. La función WEEKDAY luego evalúa la matriz y devuelve una matriz de valores de días de la semana:
(2;3;4;5;6;7;1)
Estos números corresponden al día de la semana de cada fecha. Se proporcionan a la función MID como argumento de número de inicio, junto con el valor en D6, "0888884" para el texto:
MID("0888884",(2;3;4;5;6;7;1),1)
Debido a que le estamos dando a MID una matriz de números de inicio, devuelve una matriz de resultados como esta:
("8";"8";"8";"8";"8";"4";"0")
Estos valores corresponden a las horas trabajadas cada día desde la fecha de inicio hasta la fecha de finalización. Tenga en cuenta que los valores de esta matriz son texto, no números. Para convertir a números reales, multiplicamos por una segunda matriz creada para administrar las vacaciones, como se explica a continuación. La operación matemática convierte el texto en valores numéricos.
Días festivos
Para manejar las vacaciones, usamos ISNA, MATCH y el rango denominado "vacaciones" de esta manera:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Esta expresión usa COINCIDIR para ubicar fechas que están en el rango de vacaciones con nombre usando la misma matriz de fechas generada anteriormente con INDIRECT y ROW. COINCIDIR devuelve un número cuando se encuentran días festivos y el error # N / A cuando no. La función ISNA "voltea" los resultados para que VERDADERO represente días festivos y FALSO represente no festivos. ISNA devuelve una matriz o resultados como este:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Finalmente, ambas matrices se multiplican entre sí dentro de SUMPRODUCT. La operación matemática coacciona VERDADERO y FALSO a 1 y cero, y los valores de texto en la primera matriz a valores numéricos (como se explicó anteriormente), por lo que al final tenemos:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Después de la multiplicación, tenemos una única matriz dentro de SUMPRODUCT que contiene todas las horas de trabajo en el rango de fechas:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT luego suma todos los elementos de la matriz y devuelve un resultado de 36.
