11

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:

[intense_code type=”block”]{=SUMA(ELEGIR(DIASEM(FILA(INDIRECTO(B4&”:”&C4));2);F4;G4;H4;I4;J4;K4;L4))} [/intense_code]

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:

[intense_code type=”block”]FILA(INDIRECTO(fecha1&”:”&fecha2))[/intense_code]

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:

[intense_code type=”block”]{42402;42403;42404;42405;42406;42407;42408}[/intense_code]

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:

[intense_code type=”block”]{2;3;4;5;6;7;1}[/intense_code]

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:

[intense_code type=”block”]{8;8;8;8;0;0;8}[/intense_code]

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:

[intense_code type=”block”]{8;8;8;8;0;0;8;8;8;8;8;0;0;8}[/intense_code]

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).

Comenta si te ha parecido útil.

¡Compartir es vivir!
Sergio
 

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Haz clic aquí para dejar un comentario 11 comentarios
John Jairo V

¡Hola Sergio!

Puedes usar también cualquiera de estas dos fórmulas:

(CSE) : =SUMA(INDICE(F4:L4;N(SI(1;DIASEM(FILA(INDIRECTO(B4&”:”&C4));2)))))
(NO CSE): =SUMAPRODUCTO((TEXTO(FILA(INDIRECTO(B4&”:”&C4));”ddd”)=F$3:L$3)*F4:L4)

¡Bendiciones!

Responder
    Sergio

    Muchas gracias, Jairo. Funcionan perfectamente. Me las apunto para utilizarlas en otras ocaciones.

    Responder
Yumi

Sergio, muy útil esta entrada!

Quisiera saber si puedes ayudarme a sumar horas a una fecha… para calcular cuando terminaría un proyecto determinado dentro de la jornada laboral.

Responder
    Sergio

    Hola, Yumi. Para poder sumar una hora a una fecha, el formato de la fecha debe ser dd/mm/aa hh:mm:ss (la fecha seguida de la hora de entrada). Si sumas una hora a esta fecha, dará como resultado la fecha más la hora de finalización de la tarea.

    Responder
Alma

Hola mi caso es similar pero no se como plantearlo con esta fórmula,
Quiero calcular las horas transcurridas entre 2 fechas, una resta de a que horas se inicio una actividad y a que horas terminó.Pero debo considerar que el momento producticvo es de 14 hrs de lunes a viernes (2 turnos ),sábado de 8 hras productivas y el domingo no se labora de manera que si una actividad se inicia casi al termino de una jornada se continuara hasta el próximo día laboral pudiendo extenderse varios dias e incluso pasar de una semana a otra.Pero puede durar de minutos a horas o dias.

Yo lo veo similar a este caso solo que se requiere que reste de la fecha final la inicial y tomar en cuenta los factores de las jornadas para cuando el tiempo se extiende entre varios días .
Que se sugiere en este caso?

Responder
    Sergio

    Hola, Alma!
    Por lo que comentas, parece que utilizarás la misma fórmula muchas veces en el libro, así que, creo que para no ralentizar el cálculo, deberías plantearlo con una función personalizada (VBA) que puedas reutilizar en cada celda donde quieras aplicar el cálculo.

    Entra en el foro de la web (www.ayudaexcel.com/foro) y coméntalo allí. Enseguida te responderemos.

    Responder
David

Hola muy buenas, me a servido muy bien pero tengo el problema de los festivos, se le podria andir una lista de festivos???

gracias

Responder
    Sergio

    David, como dices, podrías crear una lista con los festivos y, mediante la función DIAS.LAB, restarle las horas correspondientes a cada uno.

    Responder
david

gracias por tu respuesta, pero no lo logro hacer, tengo la lista echa que ya utilizo para otra formula y la llamo “diasfestivos”, la utilizo para saber cuantos dias tenemos en el mes =DIAS.LAB(B9;AC9;diasfestivos)

pero no se como acoplar a la otra formula, como puedo decirle que si un festivo cae en viernes debe restar 5 horas en vez de 7.30 un lu,ma,mi o ju.
no se hacerlo.
gracias por tu ayuda

Responder
Ramiro

Muchas gracias por la ayuda.
Estoy buscando algo similar pero me cuesta dar en el punto.
Necesito medir cuantas horas y minutos pasan entre dos fechas, pero de acuerdo a un horario de trabajo.
Por ejemplo el horario de trabajo es de 9 a 18hs, necesito medir el tiempo pero dentro de ese horario.
Encontre varias ayudas pero no puedo dar con lo justo ya que en algun momento no me dan los resultados que necesito. Tenes algun ejemplo asi? en donde el horario de ingreso y salida este fijo o lo pueda editar si cambia?
Gracias!!!

Responder
    Sergio

    Hola, Ramiro!
    Echale un vistazo a este artículo en el que Jorge explica perfectamente cómo calcular lo que necesitas.
    Espero que te sirva.

    Responder

Escribe una respuesta: