Calcular con Excel horas trabajadas con intervalos

jueves, septiembre 23, 2010

Una consulta frecuente es cómo calcular con Excel las horas trabajadas en un lapso determinado, tomando en cuenta sólo los días hábiles y las horas de trabajo.

Por ejemplo, cuantas horas de trabajo hay desde el 29/09/2010 hasta el 01/10/2010, tomando en cuenta que la jornada de trabajo es de 08:00 a 17:00.

Excel nos permite hacer el cálculo con relativa facilidad si tomamos en cuenta ciertos detalles.
En nuestro ejemplo, el total trabajado es 90 horas que calculamos con esta fórmula

=DIAS.LAB(B1,B2)*(B4-B3)



En nuestra fórmula usamos DIAS.LAB para calcular los días hábiles entre la fecha inicial y la final. DIAS.LAB no toma en cuenta los sábados y domingos. Las horas trabajadas cada día las calculamos fácilmente como la diferencia entre la hora de cierre y la de comienzo.

Un detalle importante es que la celda que muestra el total de horas (B5) debe tener el formato [hh]:mm

Una situación distinta es cuando el turno de trabajo concluye al día siguiente. Por ejemplo, un turno que empieza a las 22:00 y termina a las 06:00

Recordemos que Excel no puede calcular diferencias de horas negativas.



Modificamos nuestra fórmula para que también pueda calcular las horas en casos como ese

=DIAS.LAB(B1,B2)*RESIDUO(B4-B3,1)



En lugar de la fórmula con RESIDUO, podemos usar también

=DIAS.LAB(B1,B2)*(B4-B3+(B4<B3))

 En caso de querer hacer el cálculo complementario, horas trabajadas descontando las horas de descanso, modificamos levemente nuestra fórmula =DIAS.LAB(B1,B2)*(1-RESIDUO(B4-B3,1))



En este caso, el trabajo se interrumpe de 22:00 a las 06:00 del día siguiente, lo que nos deja un día de trabajo de 16 horas que multiplicado por 10 días laborales nos da 160 horas.

49 comments:

Anónimo,  23 septiembre, 2010 17:25  

Tengo una pregunta...como saco la Opcion RESIDUO en OFFICE 2003?????
Acaso sera la funcion TIME?.

Patricia C.

Jorge L. Dunkelman 23 septiembre, 2010 18:08  

Patricia,
el nombre de la función RESIDUO en inglés es MOD

carolsurz 23 septiembre, 2010 19:17  

Hola,
Un pregunta:entonces el [], ¿qué biene siendo, una especie de caracter que indica que multiplique todo?.
Por ejemplo:
B4-B3: 09:00, 09:00*3= 27:00 (Utilizando []), que son 27 horas
B4-B3: 09:00, 09:00*3= 03:00 (con el método normal), sin embargo desde las 09:00 hasta las 03:00 han apsado sólo 18 horas y no 27 horas...es decir y esta también es una pregunta ¿al hacer 09:00*3= 00:00 (que serían 27 horas)?

Quizá lo puse un poco enrevesado quizá...

Jorge L. Dunkelman 23 septiembre, 2010 20:18  

Por defecto, cada vez que una suma de horas llega a 24, Excel empieza la cuenta de nuevo (tiene que ver con el sistema que usa Excel para hacer operaciones con tiempos). Así si tenemos una suma de 27 hora, la primeras 24 son interpretadas como 0 y sólo se muestran las 3 restantes. Si la suma fuera 28:00 veríamos 04:00, si fuera 36:00 veríamos 12:00, etc.
Para ver el total de horas sin que cada 24 horas se conviertan en 0, usamos el formato [h].

Anónimo,  23 septiembre, 2010 23:37  

Gracias Jorge por tus enseñanzas. Pero, ¿cómo decirle a Excel que dentro del rengo de días hay algunos que son festivos y por lo tanto no son laborables? Me explico mejor: Si del 26 de sep 2010 al 9 de oct 2010, los días 29 de sep y 6 de oct son festivos, ¿cómo sería la fórmula?

Jorge L. Dunkelman 24 septiembre, 2010 04:14  

La función DIAS.LAB tiene un tercer argumento (opcional) que permite definir días festivos que no serán tomados en cuenta. Está explicado en la ayuda en línea de Excel.

Jorge L. Dunkelman 24 septiembre, 2010 12:01  

El lector Elbert Villareal me envía un comentario que no puedo publicar dado que contiene un enlace a una página. Mi política es no publicar enlaces sobre los cuales no tengo control. De todas manera, transcribo el contenido:

Acá les dejo una formula que sirve en cualquier caso para medir horas laborares (de 8AM a 5PM, o cual sea la necesidad) y días laborales (de L a V y se le pueden quitar los feriados)...

La formula vendría a ser esta:
=(NETWORKDAYS(A2,B2)-1)*9/24+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),17/24,8/24),17/24)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),17/24,8/24)
donde A2: Hora y fecha inicio
donde B2: hora y fecha fin

Espero les sirva de algo... a mi me va de las mil maravillas!
Saludos desde Costa Rica!

Jorge L. Dunkelman 24 septiembre, 2010 12:18  

Siguiendo con el hilo del comentario de Elbert, en primer lugar te agradezco el aporte. Algunas observaciones:
1 - en general es preferible publicar las fórmulas con las funciones en castellano. En tu caso sería

=(DIAS.LAB(A2,B2)-1)*9/24+SI(DIAS.LAB(B2,B2),MEDIANA(RESIDUO(B2,1),17/24,8/24),17/24)-MEDIANA(DIAS.LAB(A2,A2)*RESIDUO(A2,1),17/24,8/24)

2 - Las normas de buenas prácticas indican que hay que evitar el uso de constantes en las fórmulas. Las constantes 9/24,17/24 y 8/24 pueden ser reemplazadas por referencias a celdas, haciendo de esta manera que la fórmula sea más fácil de administrar.

3 - Me parece que la fórmula no calcula correctamente las horas cuando la jornada laboral pasa de un día al otro (como en mi ejemplo, cuando el turno empieza a las 22:00 y termina a las 06:00 del día siguiente).

Anónimo,  25 septiembre, 2010 10:18  

Una pregunta:
podías explicar un poco lo de la función Resisudo(); la utilizo alguna vez pero tipo Residuo(5;2)=1, que seria el resultado de la división...pero en este caso, en el de las horas, no se muy bien como opera....
gracias

Jorge L. Dunkelman 25 septiembre, 2010 11:52  

El residuo de la división de un número negativo entre 0 y -1, es lo que "falta" para llegar a 1, es decir el complemento. En nuestro ejemplo (jornada de 22:00 a 06:00), la diferencia es 0.25-0.91666667= -0.666667. RESIDUO(-0.666667,1) = 0.3333 que equivale a 8 horas (1/3 de 24)

Anónimo,  26 septiembre, 2010 11:54  

ES curiosa la duda que me surge, viendo una entarda que publicaste hace tiempo en este mismo sitio respecto de la funcion RESIDUO
Decias que por ejemplo:
34/6, el resto era 4
-34/-6, seria el resto 2, y veo valido el razonamiento, es mas creo que en una divison corriente tendia que ser asi, es decir, -34/-6=6,...sin embargo el resultado es el mismo que 34/6.---algo curioso

Juan Piñero M.

Anónimo,  26 septiembre, 2010 23:55  

Gracias Jorge por tu gran aporte. Te molesto con el siguiente caso: Tengo que medir tiempo de respuesta ante un evento que comienza en 22-09-2010 14:00 y finaliza el 23-09-2010 12:00 en lapsos de horas habiles (9:00 a 18:00) El resultado seria 7 Hs. Muchas Gracias

Jorge L. Dunkelman 27 septiembre, 2010 11:03  

Puedes usar la fórmula propuesta por Elbert, con estas adaptaciones para tu caso

=(DIAS.LAB(A2,B2)-1)*9/24+SI(DIAS.LAB(B2,B2),MEDIANA(RESIDUO(B2,1),18/24,9/24),18/24)-MEDIANA(DIAS.LAB(A2,A2)*RESIDUO(A2,1),18/24,9/24)

Como le comantaba a Elbert, hay que evitar usar constantes en las fórmulas. Así que, suponiendo que en la celda D1 pones la hora de comienzo del lapso (09:00 en tu ejemplo) y en la celda D2 la hora de cierre del lapso, la fórmula sería

=(DIAS.LAB(A2,B2)-1)*((D2-D1))+SI(DIAS.LAB(B2,B2),MEDIANA(RESIDUO(B2,1),D2,D1),D2/24)-MEDIANA(DIAS.LAB(A2,A2)*RESIDUO(A2,1),D2,D1)

Jorge L. Dunkelman 27 septiembre, 2010 11:15  

Pequeña correción a la última fórmula del comentario anterior



=(DIAS.LAB(A2,B2)-1)*((D2-D1))+SI(DIAS.LAB(B2,B2),MEDIANA(RESIDUO(B2,1),D2,D1),D2)-MEDIANA(DIAS.LAB(A2,A2)*RESIDUO(A2,1),D2,D1)

Anónimo,  27 septiembre, 2010 16:08  

Muchas Gracias Jorge. Funciono perfecto.

zaxl 01 octubre, 2010 20:10  

Jorge,
gracias por tu excelentes posts.
Una duda respecto a gráficas: tengo una gráfica con dos series, digamos Año Anterior y Año Actual. Quiero ocultar el rango Año Anterior y solo dejar el Año Actual, pero sin perder la visibilidad del rango oculto ¿es posible?
Saludos,
Axel

Jorge L. Dunkelman 02 octubre, 2010 07:41  

Axel,
¿qué significa "sin perder la visibilidad del rango oculto?
Si la idea es mostrar u ocultar series en forma dinámica, se puede hacer usando el control casilla de verificación (ActiveX).
Respondeme por mail privado (figura en el enlace Ayuda).

Anónimo,  31 agosto, 2011 20:04  

cordial saludo, de todo lo leido y practicado no consigo que Excel evalue las fechas de un día, para otro.
Ej. el turno de produccion inicia a las 21:00 y termina a las 05:00. de mil formas lo he hecho, y no consigo ese resultado.
si alguien me puede ayudar, le agradeceria ,ucho.
mi correo "rodriguez-otoniel@hotmail.com"
gracias.

Jorge L. Dunkelman 31 agosto, 2011 20:08  

Ese es el caso que muestro en la última parte de la nota, donde muestro el uso de la función RESIDUO.

Anónimo,  07 septiembre, 2011 23:36  

Gracias por el post, pero tengo un problema el cual es el siguiente: necesito calcular el tiempo de respuesta de un pedido hecho 07/07/2011 16:40:00 y se atendio el 13/07/2011 14:35:00 el timepo debe estar dentro la horas habiles Lunes-Viernes (8:00 a 18:00)no me funciona con ninguna de las formulas propuestas.
Gracias

Jorge L. Dunkelman 08 septiembre, 2011 08:25  

Suponiendo que ponemos la fecha + hora del pedido en la celda B1 y la fecha+hora en que fue atendido en la celda B2, podrías usar esta fórmula

=(B2-B1)-((TRUNCAR(B2)-TRUNCAR(B1))-DIAS.LAB(B1,B2)+1)

Jorge L. Dunkelman 10 septiembre, 2011 18:50  

Rodrigo,

fijate en la respuesta anterior.

Anónimo,  23 septiembre, 2011 18:24  

¿Por qué cuando la función residuo resta horas de distintos días calcula el complementario de 1? por ejemplo A1: 21:00 y B1: 9:00, Residuo(B1-A1;1)=12:00, y no -12:00.

Jorge L. Dunkelman 24 septiembre, 2011 14:01  

El resultado dela función RESIDUO tiene el mismo signo que el divisor.
Para visualizarlo puedes usar esta fórmula (donde A1=21:00 y A2=09:00)

=(A2-A1)-(1*ENTERO((A2-A1)/1))

Anónimo,  21 marzo, 2012 02:51  

hola, me pudieran, exlicar, como deberia de meter la formula, si necesito calcular las horas de trabajo de un mes, sumandolas, es decir, el lunes hice 8hras, el martes 7:30 hrs, etc, y luego sumarlas de todo el mes, sin que excel, me ponga la restriccion de 24 hras, lei un comentario anterior que utilizara la funcion "h", pero me gustaria que me dijeran como debería poner la formula utilizando esa funcion, gracias.

Jorge L. Dunkelman 21 marzo, 2012 15:55  

Puedes leer la explicación en esta nota.

Anónimo,  24 marzo, 2012 19:55  

GRACIAS AMIGO JORGE.... me sirvio de mucho este ejemplo. tu tocayo: JORGE

Anónimo,  10 junio, 2012 17:33  

Como quadaria la formula de Elbert si lo que queremos es calcular la hora de fin de una operacion por lo que B2 seria la incognita de la funcion?

Jorge L. Dunkelman 17 junio, 2012 19:18  

¿Sabiendo el total de horas a emplear?

Jorge L. Dunkelman 17 junio, 2012 19:26  

Podrías modificar mi fórmula de la siguiente manera (asumiendo que en B5 se pone una constante, la cantidad de horas a emplear)

=B1+(B5/RESIDUO(B4-B3,1))

Anónimo,  05 julio, 2012 22:13  

Hola, me gustaria saber como puedo resolver la siguiente duda:

Teniendo la fecha final de un evento, debo enviar un mail 30 dias antes que coincida en dia laborable:
=DIA.LAB(A2;-30) donde A2 es mi fecha final.
Ahora:
Como puedo añadir a mi formula un rango de vacaciones a excluir de ese plazo resultado de mi formula(p.ejemplo del 1 al 5 de agosto 2012)

Mil gracias
Sandra

Jorge L. Dunkelman 06 julio, 2012 10:12  

La función DIA.LAB tiene un tercer argumento opcional: vacaciones. Este te permite definir un rango con las fechas a excluir del cálculo. Fijate en la ayuda en línea de Excel.

Anónimo,  19 julio, 2012 14:31  

Hola, necesito aplicar 30% de recargo a unos importes que sean nocturnos (de 18:00 h.-06:00h.). De 06:00 h. a 18:00 h. no llevarían recargo. Cómo aplico la fórmula. Muchas gracias.

Jorge L. Dunkelman 19 julio, 2012 17:19  

Fijate en la técnica que muestro en esta nota.

Daniel 11 septiembre, 2012 23:06  
Este blog ha sido eliminado por un administrador de blog.
Anónimo,  23 enero, 2013 15:08  

Hola sabes que me sirvió mucho tu ayuda en el cálculo de horas nocturnas, pero quedé muy intrigada con respecto a esta parte (B4-B3+(B4<B3))
Me gustaría saber como se usa esta (B4<B3)para que calcule bien la diferencia de horas y como se podría aplicar en otros casos. Que significa el sumar un valor lógico a una diferencia de horas?

Gracias

Jorge L. Dunkelman 23 enero, 2013 17:00  

Las expresiones lógicas (o de cmparación) son un tipo de operación que dan, por lo tanto un resultado. En nuestro ejemplo (B4<B3) da VERDADERO si B4 es menor que B3 y FALSO en caso contrario. Estos resultados son convertdos a 1 )para VERDADERO) o 0 (para FALSO) cuando formanparte de una operación.Por ejemplo, (B4<B3))*1, da 1. Esto significa que los podemos usar en fórmulas como en el ejemplo que mencionas.
En nuestro ejemplo =(B4-B3+(B4<B3)) es equivalente a la fórmula condicional
=SI(B4<B3,B4-B3+1,B4-B3+0)

Anónimo,  02 marzo, 2013 15:04  

Hola...me ha parecido muy util tus explicaciones por cuanto son muy lenta con el uso del excel, programana que aun me cuesta enteder...y en este caso del calculo de las horas trabajadas como o que formula se aplicaria cuando esas horas tambien son en fines de semanas, es decir que se trabajan sabados y domingos, todo ello en base al tipo de contrato y actividad....espero me aclares un poco esa duda...gracias

Jorge L. Dunkelman 02 marzo, 2013 19:02  

En luga de usar DIAS.LAB, puedes usar SIFECHA() o simplemente restar la fecha inicial de la fecha final.

Psicologas Laborales 22 marzo, 2013 19:55  

Hola, muy interesantes las propuestas, tengo un problema que no puedo resolver, necesito calcular las horas trabajadas a la semana, con las siguientes condiciones, de Lunes a Jueves, 8,30 a 18.30, viernes 8.30 a 17,30 y siempre con 45 minutos de colación, me ayudan? Por favor!
Saludos y desde ya, gracias.

Jorge L. Dunkelman 23 marzo, 2013 17:47  

Podrías crear una hoja con el día de la semana en la columna A, la hora de entreada en B, la hora de salida en C y el tiempo de la colación en D. Luego en la columna E (neto trabajado) usas un a sencilla fórmula

=C2-B2-D2

Las horas tiene que registrarse con el formato adecuado (hh:mm)
Si quieres sumar los totales tienes que usar el formato personalizado [hh]:mm

andres romero 14 junio, 2013 01:24  

muy interesante tengo una pregunta: mi ingreso es alas 07:00 pm a laborar, mi turno se cumple alas 01:00 am, ya que es de 6 horas, que formula uso para que despues de la 01:00 am me muestre horas extras y despues de las 10:00 pm me muestre las horas de recargo nocturno?. te quedo agradecido

catalina romero 23 agosto, 2013 00:02  

Hola, necesito obtener ciertos datos.

Tengo una planilla de correos recibidos con días y horas completas, esto quiere decir:

Enviado Tiempo
Jueves 9:01:00
Jueves 9:03:00
Jueves 9:20:00...

y así sucesivamente 7x24 y necesito saber cuantos correos son enviados por hora pero no recuerdo si la formula es con contar si y la verdad necesito me puedan ayudar.

Saludos y muchas gracias.

Jorge Dunkelman 23 agosto, 2013 10:54  

Se puede hacer de varias formas pero primero tendría que saber si los valores son texto o fechas (valor numérico). Piedes enviarme el archivo o un ejemplo siguiendo las instrucciones que aparecen en el enlace Ayuda (en la parte superior de la plantilla).

DIANA PATRICIA ORTIZ CORREA 06 septiembre, 2013 07:28  

Buenas noches Jorge,
Su fórmula para Calcular las horas laboradas entre dos fechas (solo días habiles) en Excel, me ha funcionado correctamente.
Pero tengo una duda, como puedo medir el tiempo de atención entre dos fechas, teniendo en cuenta que el horario laboral es el siguiente:
De lunes a jueves de 7:30am a 12:00m y de 14:00 a 18:30
Viernes de 8:00am a 12:00m y de 12:00 a 18:00

Agradecería tu colaboración al respecto.

Jorge Dunkelman 06 septiembre, 2013 11:44  

Diana
Como una primera eproximación al tema puedes fijarte en el modelo que aparece en la última parte de esta nota.

Anónimo,  16 diciembre, 2013 23:50  

Hola, necesito saber como puedo ordenar los registros que provienen de una Base de Datos Externa.

El punto radica en que necesito calcular 4 registros para poder sacar las horas trabajadas y horas extras del personal que previamente hizo su registro con huella digital. Pero existe el problema que a veces el personal solo se registra 2 o 3 veces al día y quiero que los datos se ordenen de la siguiente manera:
Registro1: 13:02
Registro2: 13:55
Registro3: 18:02
Como vez el personal solo se registró 3 veces y no se registró al momento de entrar a la oficina que es a partir de las 8:00am. Mi problema es que yo necesito 4 registro. Quiero que los registros se ordenen dejando ese espacio que falta en blanco o que por defecto se coloque "08:00"

También debe tener la restricción de que si el personal se registra más de 4 veces, ya sea 5 o 6 veces que al momento de que se ordene solo me muestre 4 registros (Lo más importantes son la hora de ingreso y salida de la oficina que corresponden al primer y último registro)Ejm:
Registro1: 08:22
Registro2: 10:18
Registro3: 13:05
Registro4: 13:58
Registro5: 16:33
Registro6: 18:07

Solo quiero que me tome los registros1 y 6 ya que los otros 4 horarios pueden ser manejables ya que los registros4 y 5 corresponden los horarios que sale y regresa del almuerzo..

Espero me puedas ayudar, mil gracias de antemano.

Jorge Dunkelman 17 diciembre, 2013 06:42  

Ponte en contacto directo conmigo (ver el enlace Ayuda en la parte superior de la plantilla)

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP