Desglose de horas diurnas, nocturnas y mixtas en turnos de trabajo

Interesante problema planteado por un miembro de la comunidad que gestiona turnos de trabajo: dada una tabla con hora de entrada y hora de salida de cada empleado, necesita calcular automáticamente cuántas horas son diurnas (6:00-20:00), cuántas nocturnas (20:00-6:00) y clasificar la jornada como Diurna, Nocturna o Mixta.

El reto está en manejar turnos que cruzan la frontera diurna/nocturna (ej: entrada a las 14:00, salida a las 00:00) y turnos completamente nocturnos que cruzan medianoche (ej: 21:00 a 06:00).

Solución de John

John propuso una fórmula muy bien documentada que descompone el problema en bloques:

``
=LET(
e; C10:C12;
s; D10:D12;
x; s+(s<e);
a; SI(x<D3; x; D3) - SI(e>C3; e; C3);
b; x-C3-1;
d; a(a>0) + b(b>0);
n; x-e-d;
APILARH(d; n; SI(dn; "Mixto"; SI(n; "Nocturno"; "Diurno")))
)
`

Cada variable tiene un propósito claro:
- e/s: entrada y salida
- x: salida extendida (ajusta el cruce de medianoche sumando 1 si salida < entrada)
- a: bloque diurno A (horas diurnas del turno antes de medianoche)
- b: bloque diurno B (horas diurnas del turno después de medianoche)
- d/n: total diurno y nocturno

Solución de Leo

Leo llegó a una solución prácticamente sincronizada con John, usando MAP para manejar cada par entrada-salida individualmente:

`
=LET(
i; C3;
f; D3;
e; C10:C12;
s; D10:D12;
d; MAP(e; s; LAMBDA(a; b;
24(MAX(0; MIN(b+(b<a); f) - MAX(a; i))
+ MAX(0; MIN(b+(b<a); f+1) - MAX(a; i+1)))
));
n; 24(s-e+(s<e)) - d;
APILARH(d; n; SI.CONJUNTO(dn; "Mixto"; d; "Diurno"; n; "Nocturno"))
)
`

La clave es el uso de MAX y MIN para "recortar" cada turno a la ventana diurna, calculando la intersección entre el rango del turno y el rango diurno.

Solución de Óscar

Óscar propuso una versión más compacta, con SI e Y para la clasificación:

`
=LET(
e; C10:C12;
s; D10:D12;
x; s<e;
d; SI(s+x<D3; s; D3) - SI(e>D3; D3; e);
n; s-e+x-d;
APILARH(24d; 24n; SI(Y(d>0; n>0); "Mixto"; SI(d>0; "Diurno"; "Nocturno")))
)
`

Extensión con fechas (Héctor)

Héctor amplió todas las fórmulas para trabajar con fecha y hora en vez de solo hora. Esto permite manejar turnos de vigilancia de 24 horas (entrada día 1 a las 6:00, salida día 2 a las 6:00) donde sin fecha el cálculo daría cero. Compartió un archivo con todas las soluciones juntas y los ajustes necesarios para multiplicar por 24 y obtener horas decimales.

---

Cuatro enfoques distintos para el mismo problema, cada uno con su estilo: John descomponiendo en bloques lógicos, Leo usando MAP+LAMBDA` con intersección de rangos, Óscar apostando por la compacidad, y Héctor extendiendo a fechas completas.

El reto: horas diurnas, nocturnas y mixtas en turnos

Un miembro de la comunidad que gestiona turnos de trabajo planteó un problema clásico de RRHH: dada una tabla con la hora de entrada y la de salida de cada empleado, calcular automáticamente cuántas horas son diurnas (de 6:00 a 20:00), cuántas nocturnas (de 20:00 a 6:00) y clasificar la jornada como Diurna, Nocturna o Mixta.

Lo que parece trivial se complica con dos casos frontera:

  • Turnos que cruzan la frontera diurna/nocturna (por ejemplo, entrada a las 14:00 y salida a las 00:00).
  • Turnos completamente nocturnos que cruzan medianoche (por ejemplo, de 21:00 a 06:00).

La idea común: extender la salida y recortar a la franja diurna

Casi todas las soluciones comparten dos trucos.

Primero, la salida extendida. Si la hora de salida es anterior a la de entrada, el turno ha pasado de medianoche, así que se le suma un día a la salida. Con esto el intervalo del turno queda "estirado" y deja de dar negativos.

Segundo, la intersección con la franja diurna. Para saber cuántas horas caen dentro del horario diurno, se calcula el solapamiento entre el intervalo del turno y la ventana de 6:00 a 20:00, usando MAX y MIN para "recortar" el turno a esa franja. Las horas que sobran son las nocturnas.

Cuatro enfoques de la comunidad

John — descomposición en bloques. Dentro de un LET, define variables muy nombradas: entrada, salida, salida extendida, y dos bloques diurnos (antes y después de medianoche). Suma los tramos diurnos, deduce los nocturnos por diferencia y clasifica la jornada con lógica anidada. Es el enfoque más didáctico: cada variable cuenta una parte de la historia.

Leo — MAP + LAMBDA. Trata cada par entrada-salida por separado con MAP, y para cada uno calcula la intersección con la franja diurna mediante MAX y MIN. La clasificación la resuelve con SI.CONJUNTO. Llegó a una solución prácticamente idéntica a la de John pero por otro camino.

Óscar — versión compacta. La misma lógica con menos texto, apoyándose en SI e Y para etiquetar la jornada. Ideal si valoras la brevedad.

Héctor — extensión con fecha y hora. Amplió todas las fórmulas para trabajar con fecha + hora en lugar de solo hora. Esto es imprescindible para turnos de vigilancia de 24 horas (entrada el día 1 a las 6:00, salida el día 2 a las 6:00), donde sin fecha el cálculo daría cero. También ajustó la multiplicación por 24 para obtener horas en formato decimal.

El detalle de la clasificación

Una vez calculadas las horas diurnas (d) y nocturnas (n), etiquetar la jornada es directo. Un patrón limpio que usaron varias soluciones:

=SI.CONJUNTO(d*n; "Mixto"; d; "Diurno"; n; "Nocturno")

La idea: si tanto d como n son mayores que cero, su producto también lo es y la jornada es Mixta; si solo hay horas diurnas, Diurna; si solo nocturnas, Nocturna. SI.CONJUNTO evalúa las condiciones en orden y devuelve la primera que se cumple.

Funciones clave

  • LET: nombra los cálculos intermedios y evita repetir expresiones.
  • MAP + LAMBDA: procesan cada turno de forma individual.
  • MAX y MIN: recortan el turno a la ventana diurna (el corazón del cálculo).
  • SI.CONJUNTO: clasifica la jornada en una sola función.
  • Multiplicar por 24: convierte la fracción de día de Excel en horas decimales.

Conclusión

Cuatro personas, cuatro caminos y el mismo destino: descomponer el turno, recortarlo a la franja diurna y deducir el resto. El caso incluye un archivo con todas las soluciones en hojas separadas para compararlas, además de la extensión de Héctor con fechas completas. Retos tan reales como este —directos de una nómina— se resuelven cada semana en la comunidad de InflueXcel.

Más casos con estas funciones

Más contenido de Excel en InflueXcel