Es muy fácil obtener los días laborables en Excel pero en más de una ocasión necesitarás calcular las horas trabajadas entre dos fechas y no existe una función en Excel que pueda hacer eso de manera automática.
Seguramente encontrarás muchas alternativas para resolver este problema, pero la alternativa que yo utilizo implica el uso de la función DIAS.LAB que será de gran ayuda en este cálculo. La solución supone que tenemos los siguientes datos en nuestra hoja:
En primer lugar tenemos el Horario laboral que indica la hora de entrada y la hora de salida que tomaremos en cuenta para nuestro cálculo. En segundo lugar tenemos dos celdas que contienen una fecha y una hora indicando la fecha de inicio y la fecha final del cálculo.
De esta manera, en nuestro ejemplo, calcularemos las horas trabajadas entre el 08 de noviembre a las 8:15 y el 14 de noviembre a las 18:25 considerando un horario de trabajo de 8:00 a 18:00 horas.
Es importante mencionar que al no tener el horario trabajado para los días intermedios, consideraremos un horario de trabajo completo, es decir, desde las 8:00 y hasta las 18:00 horas. Por ejemplo, para el día 09 de noviembre consideraremos 10 horas de trabajo.
Obtener los días laborables
El primer paso será obtener los días laborables con un horario completo entre ambas fechas. Este cálculo lo haremos con la función DIAS.LAB de la siguiente manera:Formula en español =DIAS.LAB(B5, B6) - 2Formula en ingles
=NETWORKDAYS
La función DIAS.LAB calcula los días laborables entre ambas fechas, que en este caso son 5, pero hago una resta de 2 porque deseo excluir los extremos ya que para esos días haré un cálculo especial.
Una vez que tengo los días laborables puedo obtener las horas fácilmente haciendo una multiplicación por las horas totales en el horario laboral:
Formula en español =(DIAS.LAB(B5,B6) - 2) * (B3-B2)Formula en ingles =(NETWORKDAYS(B5,B6) - 2) * (B3-B2)
Esta fórmula nos da el total de las horas trabajadas en los días laborables que se encuentran entre la fecha de inicio y la fecha final. Observa lo que sucede si aplico esta fórmula en los datos de nuestra hoja:
La celda D8 muestra un total de 30 horas que es el total de horas laborables de 3 días. Es importante mencionar que la celda tiene el formato personalizado [hh]:mm el cual nos permite mostrar el total de horas tal como lo observas en la imagen anterior.
Aún no llegamos al resultado final porque todavía nos falta sumar las horas trabajadas durante el día inicial y también sumar las horas de la fecha final.
Calcular las horas trabajadas al inicio
Ya que para el día inicial tenemos la hora exacta en que se comienza a trabajar haremos un cálculo diferente. Para obtener las horas trabajadas en el día inicial utilizaré la siguiente fórmula:=B3 - RESIDUO(B5,1)=B3 - MOD(B5,1)
Para poder explicar lo que hace la función RESIDUO debemos recordar que las fechas en Excel son números. Al aplicar esta función a la fecha de inicio obtendré solamente las horas de manera que pueda hacer una resta con la celda B3 la cual está indicada también en horas.
El resultado de esta fórmula es 09:45 que son las horas trabajadas durante el primer día y es un número que deseo sumar a la fórmula que habíamos comenzado a elaborar:=(DIAS.LAB(B5,B6) - 2) * (B3-B2) + B3 - RESIDUO(B5,1)=(NETWORKDAYS
Observa el resultado de aplicar esta fórmula a nuestros datos:
Calcular las horas trabajadas al final
Por último calcularemos las horas trabajadas en el día final y de igual manera utilizaré la función RESIDUO para obtener las horas:=RESIDUO(B6,1) - B2=MOD(B6,1) - B2
Esta fórmula considera los 25 minutos excedentes en el día final por lo que el resultado es de 10:25 horas. Solo resta sumar este resultado a nuestra fórmula principal:=(DIAS.LAB(B5,B6) - 2) * (B3-B2) + B3 - RESIDUO(B5,1) + RESIDUO(B6,1) - B2=(NETWORKDAYS
Con esta fórmula podemos entonces calcular el total de horas trabajadas entre dos fechas tomando en cuenta las horas de la fecha inicial y final. Observa el resultado:
Tips adicionales
En el resultado final he dejado los minutos, pero es posible hacer un redondeo para tener una cantidad exacta de horas. Para tener más información sobre cómo redondear las horas consulta el siguiente artículo: Cómo redondear horas en Excel.
La función DIAS.LAB considera, de manera predeterminada, los días sábado y domingo como el descanso semanal pero podríamos utilizar la función DIAS.LAB.INTL en caso de tener algún día de descanso diferente. Además, con cualquiera de las dos funciones puedes tener una lista de días festivos que desees omitir del cálculo. Para saber más sobre dicha funcionalidad consulta el detalle para cada función:
FUENTE: http://exceltotal.com/calcular-horas-trabajadas-entre-dos-fechas-en-excel/
Comentarios