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.
41 comments:
Tengo una pregunta...como saco la Opcion RESIDUO en OFFICE 2003?????
Acaso sera la funcion TIME?.
Patricia C.
Patricia,
el nombre de la función RESIDUO en inglés es MOD
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á...
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].
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?
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.
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!
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).
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
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)
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.
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
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)
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)
Muchas Gracias Jorge. Funciono perfecto.
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
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).
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.
Ese es el caso que muestro en la última parte de la nota, donde muestro el uso de la función RESIDUO.
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
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)
Rodrigo,
fijate en la respuesta anterior.
¿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.
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))
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.
Puedes leer la explicación en esta nota.
GRACIAS AMIGO JORGE.... me sirvio de mucho este ejemplo. tu tocayo: JORGE
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?
¿Sabiendo el total de horas a emplear?
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))
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
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.
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.
Fijate en la técnica que muestro en esta nota.
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
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)
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
En luga de usar DIAS.LAB, puedes usar SIFECHA() o simplemente restar la fecha inicial de la fecha final.
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.
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
Publicar un comentario