Fórmula genérica
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Resumen
Para calcular el total de horas de trabajo entre dos fechas y horas, puede utilizar una fórmula basada en la función NETWORKDAYS. En el ejemplo que se muestra, E5 contiene esta fórmula:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
donde "inferior" es el rango con nombre H5 y "superior" es el rango con nombre H6.
Nota: este ejemplo se inspiró en un desafío de fórmulas en Chandoo, y una solución más completa proporcionada por el maestro de fórmulas Barry Houdini en el foro MrExcel.
Explicación
Esta fórmula calcula el total de horas de trabajo entre dos fechas y horas, que ocurren entre un tiempo "inferior" y "superior". En el ejemplo que se muestra, la hora inferior es las 9:00 a. M. Y la hora superior es las 5:00 p. M. Estos aparecen en la fórmula como los rangos con nombre "inferior" y "superior".
La lógica de la fórmula es calcular todas las horas de trabajo posibles entre las fechas de inicio y finalización, inclusive, luego retroceder las horas en la fecha de inicio que ocurran entre la hora de inicio y la hora inferior, y cualquier hora en la fecha de finalización que ocurra entre la hora de finalización y la hora superior.
La función NETWORKDAYS maneja la exclusión de fines de semana y feriados (cuando se proporciona como un rango de fechas). Puede cambiar a NETWORKDAYS.INTL si su horario tiene días laborables no estándar.
Formateo de salida
El resultado es un número que representa el total de horas. Como todos los tiempos de Excel, deberá formatear la salida con un formato de número adecuado. En el ejemplo que se muestra, estamos usando:
(h):mm
Los corchetes impiden que Excel se renueve cuando las horas son superiores a 24. En otras palabras, permiten mostrar horas superiores a 24. Si necesita un valor decimal para las horas, puede multiplicar el resultado por 24 y formatearlo como número regular.
Versión simple
Si las horas de inicio y finalización siempre se producirán entre las horas inferior y superior, puede usar una versión más simple de esta fórmula:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Sin hora de inicio y hora de finalización
Para calcular el total de horas de trabajo entre dos fechas, asumiendo que todos los días son días laborales completos, puede usar una fórmula aún más simple:
=NETWORKDAYS(start,end,holidays)*hours
Consulte la explicación aquí para obtener más detalles.
