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

Sergio 17/06/2016
11 Comentarios

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

Comenta si te ha parecido útil.

Categorías: Fórmulas y funciones

Respuestas

  1. John Jairo V 03/07/2016

    ¡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!

    1. Sergio 03/07/2016

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

  2. Yumi 02/11/2017

    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.

    1. Sergio 02/11/2017

      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.

  3. Alma 01/02/2018

    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?

    1. Sergio 02/02/2018

      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.

  4. David 19/03/2018

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

    gracias

    1. Sergio 19/03/2018

      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.

  5. david 21/03/2018

    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

  6. Ramiro 27/07/2018

    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!!!

Los comentarios están cerrados.