EXCEL: Obtener horas de trabajo entre fechas programadas personalizadas

Para calcular las horas de trabajo entre dos fechas con un horario personalizado, puede usar una fórmula basada en las funciones DIASEM y SUMAPRODUCTO, con la ayuda de FILA, INDIRECTO y EXTRAE. En el ejemplo que se muestra, la fórmula en F8 es:

=SUMAPRODUCTO(EXTRAE(D6,DIASEM(FILA(INDIRECTO(B6&":"&C6))),1)*ESNOD(COINCIDIR(FILA(INDIRECTO(B6&":"&C6)),holidays,0)))

Que devuelve 36 horas, según un horario personalizado donde se trabajan 8 horas de lunes a viernes, 4 horas se trabaja el sábado y el lunes 3 de septiembre es feriado. Las vacaciones se suministran como el rango con nombre G6: G8. El cronograma 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 un día laboral estándar de 8 horas, esta fórmula es más simple.

Cómo funciona esta fórmula

En esencia, esta fórmula usa la función DIASEM para calcular el día de la semana (es decir, lunes, martes, etc.) para cada día entre las dos fechas dadas. DIASEM 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 reunir una serie de fechas que puede alimentar a la función DIASEM. Esto se hace con FILA con INDIRECTO:

FILA(INDIRECTO(B6&":"&C6))

FILA 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 en la matriz representa una fecha. La función DIASEM luego evalúa la matriz y devuelve una matriz de valores de día 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 EXTRAE como argumento de número de inicio, junto con el valor en D6, "0888884" para el texto:

EXTRAE("0888884",{2;3;4;5;6;7;1},1)

Debido a que le estamos dando a EXTRAE una matriz de números de inicio, devuelve una matriz de resultados como este:

{"8";"8";"8";"8";"8";"4";"0"}

Estos valores corresponden a las horas trabajadas en cada día desde la fecha de inicio hasta la fecha de finalización. Tenga en cuenta que los valores en 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 obliga al texto a valores numéricos.

Días festivos

Para manejar los días festivos, utilizamos ESNOD, COINCIDIR y el rango denominado "días festivos" como este:

ESNOD(COINCIDIR(FILA(INDIRECTO(B6&":"&C6)),holidays,0))

Esta expresión usa COINCIDIR para ubicar fechas que están en los días festivos de rango con el mismo conjunto de fechas generadas anteriormente con INDIRECTO y FILA. COINCIDIR devuelve un número cuando se encuentran vacaciones y el error # N / A cuando no. La función ESNOD "voltea" los resultados para que VERDADERO represente vacaciones y FALSO represente no vacaciones. ESNOD devuelve una matriz o resultados como este:

{FALSO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO;VERDADERO}

Finalmente, ambas matrices se multiplican entre sí dentro de SUMAPRODUCTO. La operación matemática obliga a 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:

=SUMAPRODUCTO({8;8;8;8;8;4;0}*{0;1;1;1;1;1;1})

Después de la multiplicación, tenemos una sola matriz dentro de SUMAPRODUCTO que contiene todas las horas de trabajo en el rango de fechas:

=SUMAPRODUCTO({0;8;8;8;8;4;0})

SUMAPRODUCTO suma todos los elementos de la matriz y devuelve un resultado de 36.