Calcular horas trabajadas en distintos turnos

Esta nota es la continuación del post anterior sobre el cálculo de horas nocturnas con Excel. Supongamos ahora la existencia de tres turnos de trabajo: día, tarde y noche. Nuestra tarea es calcular dentro de las horas trabajadas por cada operario en una jornada, cuántas pertenecen a cada turno.
En el post anterior vimos cómo calcular las horas nocturnas, que de hecho es calcular cuantas de las horas trabajadas caen dentro de un intervalo determinado.

A los efectos de nuestro ejemplo supongamos que el turno tarde comienza a las 18:00 y concluye a las 22:00 y el turno corre de las 22:00 hasta las 06:00. Las restantes horas del día, de 06:00 a 18:00, corresponden al turno día.

Para calcular cuántas horas caen dentro de cada turno usamos este modelo:




Esta tabla es interactiva y el modelo puede decargarse usando el icono de descarga

Las fórmulas en el modelo son:

Total trabajado: =RESIDUO(C7-B7,1), que ya hemos explicado en el post anterior;

Día: =(B7>C7)*MEDIANA(0,C7-$D$4,RESIDUO($D$4-$C$3,1))+MAX(0,MIN($C$3,C7+(B7>C7))-MAX($D$4,B7))

Noche: =RESIDUO(C7-B7,1)-(C7<B7)*($C$4-$D$4)-MEDIANA(C7,$D$4,$C$4)+MEDIANA(B7,$D$4,$C$4) explicada en el post anterior

Tarde: =D7-E7-G7 - sencillamente resstamos los turnos Día y Noche del Total Trabajado.

Cálculo de horas trabajadas en turno noche

Un tema recurrente en las consultas que recibo es cómo calcular horas trabajadas en turno noche. Supongamos la siguiente situación:

• un operario trabaja desde las 20:00 hasta las 04:00
• el turno noche, cuya tarifa es distinta del horario normal, corre de las 22:00 a las 06:00

¿Cómo calculamos con Excel cuántas horas de las 8 horas trabajadas pertenecen al turno noche? Veamos esta tabla con varios ejemplos

La fórmula en la celda E7, que calcula correctamente las horas trabajadas en turno noche tal como ésta definido en las celdas D3 y E3, es:

=RESIDUO(C7-B7,1)+MEDIANA(B7,$E$3,$D$3)-MEDIANA(C7,$E$3,$D$3)-(C7<B7)*($D$3-$E$3)

Esta fórmula se basa en la función MEDIANA y en la función RESIDUO
.
Para explicar esta fórmula vamos a dividirla en tres módulos:

RESIDUO(C7-B7,1)  - esta fórmula, que ya hemos explicado, calcula el total de horas trabajadas.

+MEDIANA(B7,$E$3,$D$3)-MEDIANA(C7,$E$3,$D$3) - Esta expresión nos permite calcular el total de horas trabajadas fuera del turno noche. Este resultado será siempre negativo o cero, de manera que al restarlo de las horas trabajadas (el primer "módulo" de nuestra fórmula), obtenemos las horas que caen dentro del horario nocturno.

(C7<B7)*($D$3-$E$3) - el objetivo de esta expresión es corregir el resultado cuando el horario trabajado pase de un día al otro. Si la hora de salida es mayor que la hora de entrada, todo el trabajo ha sido realizado dentro del mism día; en este caso la expresión dá cero (FALSO) y no varía el resultado. Si la hora de salida es menor que la de entrada, la jornada terminó al día siguiente; la expresión da VERDADERO (que equivale a multiplicar por 1), y el resultado es corregido de acuerdo.

Este cuadro interactivo permite ver como funciona cada módulo de la fórmula (los valores de entrada y salida y los del horario nocturno pueden ser modificados)





En este post pueden ver una solución para el caso de tres turnos (día, tarde y noche).

La función MEDIANA de Excel o como calcular si un valor se encuentra entre dos valores

Un lector me consultaba cómo calcular horas trabajadas en turno noche. El método tradicional para calcular horas de un turno, por ejemplo cuando el turno noche empieza a las 22:00 y concluye a las 06:00 y el trabajador trabajó de las 20:00 a las 04:00, es usando la función MEDIANA.

En este post mostraré cómo usar la función MEDIANA para calcular si un valor cae entre dos valores. En el próximo post mostraré cómo calcular las horas trabajadas en un turno.

Supongamos que queremos calcular con Excel si el valor en la celda B4 está comprendido entre los valores de las celdas B1 y B2. Podemos hacer usando la función Y

El problema con esta fórmula es que presupone que el valor de B2 es mayor que el de B1; en caso contrario el resultado es erróneo

El uso de la función MEDIANA evita este problema

La función MEDIANA calcula, obviamente, la mediana de un conjunto de números dado. La mediana, según la  ayuda de Excel es:  el número que se encuentra en medio de un conjunto de números.

Nuestra fórmula funciona también con valores no enteros, negativos y positivos. Un punto a tomar en cuenta es cómo definimos "entre". En nuestro ejemplo, ¿10 cae entre los valores del intervalo? Si usamos MEDIANA, la respuesta es si; si usamos la función Y

=Y(B4>=B1,B4<=B2), dará VERDADERO

=Y(B4>B1,B4<B2) dará FALSO