Cómo calcular las horas de trabajo entre dos fechas con jornadas diferentes

En este artículo te mostré cómo obtener el número de horas de trabajo entre dos fechas. Mientras estaba escribiéndolo me di cuenta de que muchas de las empresas con las que he trabajado tienen diferentes horarios los viernes, así que me propuse solucionar el problema.

Para calcular las horas de trabajo entre dos fechas  siguiendo un calendario de jornadas irregulares, he utilizado dos funciones indispensables: DIASEM y ELEGIR. Observa la fórmula:

{=SUMA(ELEGIR(DIASEM(FILA(INDIRECTO(B4&":"&C4));2);F4;G4;H4;I4;J4;K4;L4))}

El resultado de esta fórmula es 40 ya que está basada en el calendario de horas diarias que aparece a la derecha. Como puedes ver, la semana laboral se compone de 8 horas diarias de lunes a viernes.

Observa también las demás filas. El resultado sigue calculándose correctamente si se hacen modificaciones tanto en el calendario como en las fechas de inicio y fin.

[intense_highlight color=»#336633″ font_color=»#ffffff»]Importante:[/intense_highlight] Se trata de una fórmula matricial. Para introducirla debes pulsar Ctrl + Mayús + Intro.

¿Cómo funciona la fórmula?

La función principal es DIASEM, que devuelve el día de la semana para cada uno de los días comprendidos entre la fecha de inicio y la de finalización. DIASEM devuelve un número entre 1 y 7 correspondientes al día de la semana. Por defecto, el 1 corresponde al domingo, el 2 al lunes…, pero este orden puede modificarse según la necesidad.

El truco de la fórmula está en crear una matriz de fechas que actúan como argumento de DIASEM. Para ello he utilizado las funciones FILA e INDIRECTO de la siguiente forma:

FILA(INDIRECTO(fecha1&”:”&fecha2))

INDIRECTO permite que las fechas concatenadas “42402;42408” sean interpretadas como números de fila. Así que la función FILA devuelve una matriz como esta:

{42402;42403;42404;42405;42406;42407;42408}

A continuación DIASEM evalúa estos números y los convierte en días de la semana. Observa que el argumento “2” de la función establece que el lunes es el primer día de la semana:

{2;3;4;5;6;7;1}

Esta matriz es la que se le da a ELEGIR para que actúe como índice. Este índice está configurado de la siguiente forma:

• 1: Horas trabajadas los domingos.
• 2: Horas trabajadas los lunes.
• 3:Horas trabajadas los martes.
• 4:Horas trabajadas los miércoles.
• 5: Horas trabajadas los jueves.
• 6: Horas trabajadas los viernes.
• 7: Horas trabajadas los sábados.

Como le estoy introduciendo a ELEGIR más de un valor como índice, devolverá también más de un resultado en la matriz:

{8;8;8;8;0;0;8}

Estos valores son las horas de cada semana. Si el rango de fecha es de, por ejemplo, dos semanas, la matriz devuelta será algo como esto:

{8;8;8;8;0;0;8;8;8;8;8;0;0;8}

Para finalizar, la función SUMA sumará todos los valores de la matriz.

Resumen

Si acabas de aterrizar en el mundo de Excel, toda esta explicación te sonará a chino, sobre todo el concepto de fórmula matricial, pero no debes preocuparte. Descárgate el ejemplo e intenta entenderlo función por función (desde dentro hacia afuera de la fórmula).

[attachments include=»9055″]

Comenta si te ha parecido útil.