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

Vencimientos en día hábil posterior

En la nota anterior vimos como en el caso que una fecha de vencimiento caiga en día no laboral (feriado o fin de semana) calcular el día hábil inmediato anterior. Por suspuesto no tanscurrió mucho tiempo hasta que un lector me consultó cómo hacer el cálculo al día hábil inmediato posterior. Por ejemplo, si la fecha de vencimiento cae un domingo, calcular la fecha del lunes siguiente.
Ambos casos serían muy sencillos de calcular si las funciones DIA.LAB o  DIA.LAB.INT contaran con la opción "0" (los siete días de la semana son laborales).
Como esa opción no existe, combinamos las funciones MAX, FILA y DIASEM en esta fórmula matricial

=MAX((C5+C4+1-FILA(INDIRECTO("1:"&C4)))*(ESERROR(COINCIDIR(C5+C4+1-FILA(INDIRECTO("1:"&C4));Feriados;0)))*(DIASEM(C5+C4+1-FILA(INDIRECTO("1:"&C4));2)<6))

donde C4 contiene la cantidad de días corridos al vencimiento y C5 la fecha inicial

El funcionamiento de la fórmula está explicado en la primer nota de la serie. Básicamente, creamos un vector de fechas partiendo de la fecha de vencimiento hacia atrás, donde las fechas de los fines de semana o de feriados reciben el valor 0; luego MAX extrae el valor mayor, que es el primer día hábil "hacia atrás".

Volviendo a la consulta de nuestro lector, queremos encontrar el primer día hábil posterior al vencimiento si este cae en día no laboral. Para encontrar la fecha vamos a aplicar una lógica similar: creamos un vector de fechas "hacia adelante" (los días posteriores a la fecha de vencimiento antes de las correcciones); las fechas que caen en días no laborales (fin de semana o feriado) reciben el valor cero. Ahora tenemos que encontrar la fecha hábil más cercana, es decir la menor, lo que podemos hacer con la función MIN (recordemos que en Excel las fechas son miembros de un serie de números enteros). Pero aquí nos encontramos con el problema que el vector de fechas incluye ceros.

Para encontrar el menor de los valores positivos de una serie (en nuestro caso, la primer fecha hábil posterior al vencimiento) excluyendo los ceros usaremos la función K.ESIMO.MENOR combinada con SUMAPRODUCTO en esta fórmula matricial

=K.ESIMO.MENOR(($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6),SUMAPRODUCTO(--((($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6))=0))+1)

Para ahorrar a mis estimados lectores los mareos (y talvez ligeras náuseas) que pueda causar la lectura de semejante fórmula, vamos a explicarla. Podemos dividir esta fórmula en dos partes

La expresión

$C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6

crea un vector de fechas donde el primer miembro es la fecha de vencimiento (fecha inicial + más diás corridos al vencimiento) y los restantes son los números de serie de las fechas siguientes o cero si la fecha cae en día no laboral. "Feriados" es un nombre definido que se refiere al rango que contiene las fechas de los feriados.
Para facilitar la lectura podemos definir un nombre que se refiere a esta fórmula (vector_fechas)

La expresión

SUMAPRODUCTO(--((($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6))=0))+1

calcula cuantos ceros hay en el vector creado por la expresión anterior que hemos encapsulado en el nombre "vector_fechas".

Si usamos el nombre "vector_fechas" en nuestra fórmula, obtenemos una versión mucho más fácil de leer y entender:

=K.ESIMO.MENOR(vector_fechas,SUMAPRODUCTO(--(vector_fechas=0))+1)

Otra ventaja de esta sintaxis es que no hay que ingresar la fórmula en forma matricial.

Veamos este ejemplo

Le fecha de vencimiento, antes de las correcciones necesarias, cae el 15/03/2015 que es un domingo; en la tabla de feriados vemos que el lunes y martes siguientes son feriados, de maner que el resultado de la fórmula es el miércoles 18/03/2015.

También podemos usar esta variante:

=K.ESIMO.MENOR(vector_fechas,INDICE(FRECUENCIA(vector_fechas,0),1)+1)

donde el número de ceros en el vector es calculado con la función FRECUENCIA. También en este caso no hace falta ingresar la fórmula en forma matricial.

Una tercera variante es usar la función MIN combinada con SI, en esta fórmula matricial

=MIN(SI(vector_fechas=0,"",vector_fechas))

El cuaderno con el ejemplo puede descargarse aquí.

Vencimientos en día hábil - versión mejorada

En la nota anterior mostré como calcular fechas de vencimientos que siempre caigan en días hábiles, no en fines de semana o feriados. En caso de caer en día no hábil o feriado, la fecha calculada debía ser el primer día hábil anterior a la fecha original. (Para calcular el día hábil posterior, ver esta nota)

Para hacerlo propuse esta fórmula usando las funciones DIASEM, MAX, ESERROR y COINCIDIR junto con una constante vectorial. La fórmula en la celda C7 es

=MAX((C5+C4-{7;6;5;4;3;2;1;0})*(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0)))*(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6))

Esta fórmula tiene algunas debilidades.
La notación de las constantes vectoriales cambia de acuerdo a las definiciones regionales. Para ciertas configuraciones, el vector debe anotarse de esta manera:

=MAX((C5+C4-{7\6\5\4\3\2\1\0})*(ESERROR(COINCIDIR(C5+C4-{7\6\5\4\3\2\1\0};Feriados;0)))*(DIASEM(C5+C4-{7\6\5\4\3\2\1\0};2)<6))

usando \ en lugar de ; lo cual puede confundir a alguno de mis lectores. Para evitar este problema podemos usar esta sintaxis:

=MAX((C5+C4+1-FILA(1:7))*(ESERROR(COINCIDIR(C5+C4+1-FILA(1:7);Feriados;0)))*(DIASEM(C5+C4+1-FILA(1:7);2)<6))

donde creamos el vector usando la función FILA e ingresando la fórmula en forma matricial (apretando simultáneamente las teclas Ctrl - Mayúsculas - Enter).

Otro problema más serio es que la constante vectorial es exactamente eso, una constante. Arbritariamente uso un vector de 8 miembros para generar un vector de ocho fechas, de la fecha de vencimiento hacia atrás, suponiendo que no hay feriados que duren más de una semana. Pero esta suposición no tiene que ser necesariamente cierta en todos los casos. Lo ideal sería que ese vector de fechas fuera dinámico. Para hacerlo podemos basarnos en la fórmula matricial anterior usando la función INDIRECTO para generar un rango dinámico de fechas. La fórmula es:

=MAX((C5+C4+1-FILA(INDIRECTO("1:"&C4)))*(ESERROR(COINCIDIR(C5+C4+1-FILA(INDIRECTO("1:"&C4));Feriados;0)))*(DIASEM(C5+C4+1-FILA(INDIRECTO("1:"&C4));2)<6))

INDIRECTO("1:"&C4) crea un vector de tantos valores como días transcurren de la fecha incial a la fecha de vencimiento (en nuestro ejemplo, 30).

Calcular fecha de vencimiento en día hábil

Como muchas otras, también esta nota se originó en la consulta de un lector. Éste necesitaba computar fechas de vencimiento pero de manera tal que si la fecha de vencimiento caía en un feriado, la fecha fuera corregida al día hábil anterior (para el día posterior, ver esta nota). En sus propias palabras:

supongamos que la fecha inicial es 1 de abril del 2015, y en 30 días la persona tiene que pagar, esto quiere decir el 1 de mayo de 2015, PERO la fecha de pago no puede caer ni en dias festivos ni en fines de semana. se que existe la formula DIA.LAB.INTL pero no me sirve porque me omite todos los fines de semana o todos los domingos, para esto la fecha de pago tiene que ir antes (en caso de ser festivo o fin de semana); supongamos, si cae en domingo la fecha pasa para el viernes de esa misma semana, no para el lunes

Como se trata de días corridos, el proceso de cálculo tiene que ser el siguiente:

1. partimos de la fecha inicial y le sumamos 30;
2. evaluamos el resultado para saber si cae en un fin de semana o día festivo (para los días festivos necesitaremos un rango que los contenga);
3. si el resultado cae en día hábil, el cálculo queda concluido;
4. si el resultado cae en feriado, calculamos cuál es el día hábil anterior más cercano.

Este último punto es el más problemático ya que puede darse la situación de dos o más días festivos corridos.

Consideremos este ejemplo:

• En el rango F4:F8 tenemos la lista de feriados (arbitrarios, a los únicos efectos del ejemplo); 
• en la celda C4 el período en días corridos al vencimiento; 
• en la celda C5 la fecha inicial; 
• en la celda C6 el cálculo de la fecha de vencimiento sin correcciones que calculamos con la trivial fórmula =C5+C4. 
• Para mayor comodidad he definido un nombre que contiene los días de la semana lo que permite calcular el día de semana de cada fecha, como en la celda D5 con esta fórmula =INDICE(rngSemana,DIASEM(C5,2)).

Como podemos ver el vencimiento cae un sábado, por lo cual debemos corregir al viernes anterior, es decir, un día atrás. Si este fuera siempre el caso nuestra fórmula sería relativamente sencilla: si el día de la semana es sábado, restar un día del resultado; si es domingo, restar dos.

El cálculo se complica si el resultado coincide con un feriado. En este caso debemos encontrar la primer fecha anterior que no es ni feriado ni fin de semana.

Consideremos ahora este caso:

La fecha inicial es el 13/05/2015 y por lo tanto la fecha de vencimiento tendría que ser el 12/06/2015 que es viernes. Pero como podemos ver en la lista de feriados, el viernes 12/06/2015 y también el jueves 11/06/2015 son feriados, por lo que el resultado de nuestro cálculo deberá ser el miércoles 10/06/2015.

La fórmula en la celda C7, donde obtenemos el resultado correcto, es ésta:

=MAX((C5+C4-{7;6;5;4;3;2;1;0})*(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0)))*(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6))

Vamos a explicar esta fórmula paso por paso.

C5+C4 - La fecha inicial más 30 días;

{7;6;5;4;3;2;1;0} - un vector vertical cuyos miembros son números enteros del 0 al 7. Dependiendo de las definiciones del sistema, en lugar del punto y coma (;)  tendremos que usar \ (por ejemplo: {7\6\5\4\3\2\1\0}). También podemos crear el vector vertical usando comas y la función TRANSPONER, por ejemplo: TRANSPONER({7,6,5,4,3,2,1,0}).

C5+C4-{7;6;5;4;3;2;1;0} - esta expresión crea un vector de fechas, en nuestro caso del 05/06/15 al 12/06/15. Podemos visualizarlo usando la auditoría de fórmulas, donde las fechas aparecen con el correspondiente número de serie

(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0))) - crea un vector del mismo tamaño con valores VERDADERO si la fecha no se encuentra en la tabla de los feriados (COINCIDIR dá en ese caso el error #N/A) y FALSO si se trata de un feriado.

(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6) - crea un vector de valores VERDADERO si la fecha cae entre lunes y viernes (incluidos) y FALSO si cae en el fin de semana.

Al multplicar los últimos tres vectores entre si creamos un vector de valor 0 (cuando alguno de los valores es FALSO) o el número de serie de la fecha (cuando los tres valores correspondientes son VERDADERO). Podemos visualizar estas operaciones en esta tabla que muestra en cada fila un elemento de cada vector

Finalmente, la función MAX extrae el valor máximo del producto de los tres vectores (los valores que aparecen en la columna H en la imagen), que es la fecha del primer día hábil anterior a la fecha de vencimiento sin correcciones.

El archivo con el ejemplo puede descargarse aquí

Ver post con una versión mejorada.