sábado, febrero 09, 2008

Administración de horas de trabajo en Excel.

Supongo que si se hiciera una estadística sobre los usos de Excel, las herramientas para la administración de horas de trabajo figurarían entre los primeros lugares.
Excel está "hecho a medida" para estas tareas, pero para construir este tipo de herramientas tenemos que comprender primero como maneja Excel el tema de las fechas y las horas.
En esta nota haremos una revisión integral del tema y daremos algunos ejemplos prácticos.

Excel considera las fechas y las horas como números sucesivos de una serie. Ésta comienza el 1ro. de enero de 1900, fecha a la Excel le asigna el número 1. El 2 de enero está representado por el número 2 y así sucesivamente.
Las horas son la parte decimal del número. Un día completo son 24 horas, de manera que las 12 del mediodía está representado por el número 0.5; las seis de la mañana por el 0.25, las 6 de la tarde por el 0.75, etc. En este momento es el 9 de febrero a las 9:50 de la mañana. El resultado de la función AHORA() es 09/02/2008 09:50:36



Si cambiamos el formato de la celda a "General" veremos el número 39487.4101446759



donde 39487 es el número de días transcurrido desde el 01-01-1900 hasta hoy y 0.4101446759 resulta de dividir 35346 segundos (el equivalente de 9 horas, 50 minutos y 36 segundos) por 86400 (la cantidad de segundos que hay en un día).

Otro aspecto importante a tomar en cuenta, antes de abocarnos a la tarea de construir herramientas para administrar horas, es el formato de números en Excel y el de las fechas y horas en particular. Cambios de formato no alteran el número sino como es representado por Excel en la pantalla. Supongamos una tabla donde ponemos el comienzo de una tarea, las horas a trabajar y el resultado será la hora de finalización



Para ver el resultado, debemos cambiar el formato de la celda, que ha sido "heredado" de la celda A2, por el formato hh:mm



Extraño, ¿no? Para entender este resultado volvemos a cambiar el formato de las celdas a "General"



Sucede que cuando ingresamos 15:30 en la celda A2, Excel la interpreta como el 1ro. de enero de 1900 a las 15:30. Al poner 8 en la celda B2, por ser un número entero, es interpretado como 8 días. El resultado en la celda C2 será el 8 de enero de 1900 a las 15:30. Esto lo podemos ver cambiando el formato de las celdas a dd/mm/yyyy hh:mm



Para evitar resultados erróneos debemos ingresar los datos de horas con formato horario.

En este ejemplo hemos ingresado el dato en la celda B2 como 08:00, lo que Excel interpreta como 8 horas, y por lo tanto el resultado es el esperado



Hay otras cuestiones a considerar cuando trabajamos con horas y fechas en Excel, como el manejo de resultados negativos, suma de horas por encima de 24 horas, distintos sistemas de fechas y más. Todo esto ha sido tratado en distintas notas del blog. Todas las notas sobre fechas y horas pueden verse pulsando el enlace de las etiquetas en la columna izquierda del blog



Consideremos ahora este ejemplo



Todas las celdas de la tabla tienen el formato hh:mm. El Total Bruto es la cantidad de horas transcurridas desde el ingreso hasta la salida, sin tomar en cuenta el descanso. La fórmula de Total bruto es =(E5-B5)+(E5<B5)*24. El objetivo de la expresión (E5<B5)*24 es corregir el resultado cuando la hora del final es menor que la del principio. La explicación completa de esta fórmula la encontrarán en la nota sobre diferencia de horas en Excel. Más adelante veremos que esta fórmula tiene un serio inconveniente.

La fórmula de horas trabajadas es =F5-((D5-C5)+(D5<C5)*24), donde nuevamente usamos la expresión (D5<C5)*24 para corregir el error que se genera cuando la salida sucede al día siguiente de la entrada.
La columna Horas Regulares la calculamos con esta fórmula: =MIN(G5,$E$1) donde $E$1 es la celda que contiene la cantidad de horas regulares (en nuestro caso 8). La función MIN nos da el mínimo entre la jornada regular y las horas trabajadas.
Para calcular las horas extras usamos la fórmula =MAX(0,G5-H5). Es decir, el máximo entre 0 y la diferencia entre las horas trabajadas y las regulares.

Supongamos ahora que queremos totalizar el total de horas del día. Como ya hemos explicado, las celdas del total tendrán el formato [h]:mm.



Como ven, los resultados parecen incorrectos! Esto se debe a que nuestra fórmula de corrección no nos sirve en este caso. En lugar de la fórmula =(E5-B5)+(E5<B5)*24 en Total Bruto, usaremos la fórmula =RESIDUO(E5-B5,1) en la columna de Total bruto y la fórmula =F5-(RESIDUO(D5-C5,1)) en la Horas trabajadas.
Ahora los totales son correctos




Technorati Tags:

108 comentarios:

  1. Como se hace para redondear o aproximar horas?, por ejemplo si el resultado es 2:20 que me la aproxime a 2.

    Graciias

    ResponderBorrar
  2. Depende lo que quieras hacer. Si el objetivo que en la pantalla se vea la hora sin los minutos, basta con dar a la celda formato personalizado "hh".
    Si realmente queremos redondear, es decir aproximar al entero más cercano, debes usar esta fórmula
    =REDONDEAR(A1*24,0)/24
    suponiendo que en A1 se encuentra el valor 02:20

    ResponderBorrar
  3. Hola Jorge: quiero felicitarte por tu excelente trabajo. Acababa de no entender de la ayuda de Excel el ingreso de fórmulas matriciales, y googleando encontré tu blog y me quedó clarísimo, al igual que estos ejercicios de fechas, los mismos que he tenido que cranear en múltiples oportunidades producto de mi trabajo.

    Sigue adelante y estoy muy contento porque tengo muchísimo más que revisar en tu blog.

    David

    ResponderBorrar
  4. Intento calculñar el precio total del tiempo trabajado en horas, minutos y segundos, a una tarifa constante. En el casillero de tiempo (A2)pongo formato celda hora hh:mm:ss. en el casillero de al lado (B2)la tarifa 120 (€ por ejemplo)
    En el casillero de resultado, en formato de celda numérico, pongo la fórmula= 24*A2*B2
    Pues bien, el resulktado es correcto para cualquier valor de tiempo inferior a 10000:00:00 horas. A partir de ahí no puede calcularlo.

    ¿Puede ayudarme a subsanar mi error?

    ResponderBorrar
  5. El problema reside en la forma en que Excel trata los datos que vamos ingresando en las celdas. Si pones un número que parece una fecha, Excel le dará a la celda un formato de fecha. Lo mismo con número que parecen horas (cuando usamos el ":" como separador). Si pones en una celda con formato General "9999:00:00", Excel lo interpreta como horas. Pero si pones algo que no encuadra en las reglas que Excel usa para determinar el tipo de dato, Excel lo transformará en texto. Por eso recibes un resultado de error.
    Lo mismo te sucedería si pusieras el 9999:60:00, pero no si pusieras 9999:59:00. Por lo visto si la cantidad de horas supera los cuatro dígitos, Excel decide que se trata de texto.
    La solución que te sugiero es la siguiente:
    1 - calculas 10000 dividido por 24 que te dará 416,66667
    2 - pones este valor en la celda correspondiente (A2 en tu ejemplo).
    Esto forzará a Excel a interpretar el valor como número. Para que el valor exhibido sea 10000:00:00, el formato tiene que ser [hh]:mm:ss.

    ResponderBorrar
  6. Hola,
    Una consulta yo tengo dos fechas una de ingreso y la otra la fecha de salida del trabajador, y deseo clacular los dias laborados por el trabajador sin contar los fines de semana, use la funcion dias.lab pero me bota un resultado no esperado.

    Podrias decirme por favor que pasos debo seguir?

    Muchas gracias.

    ResponderBorrar
  7. Hola Daniel

    mandame el archivo para que vea dónde pueda estar el problema

    ResponderBorrar
  8. Hola de nuevo, Jorge.
    ¿Y si en la columna de horas extras se quisiera tener también un resultado en negativo? Se tiene que trabajar 8 horas pero se ha trabajado 7:45?
    Muchas gracias.

    ResponderBorrar
  9. Como fue mencionado en la nota y también explicado en esta otra nota, para trabajar con resultados negativos hay que pasar al sistema basado en el año 1904.

    ResponderBorrar
  10. hola jorge, tu pagina me sirvio mucho para el lio que tenia con las horas extras. pero tengo un dilema con la sumatoria total de horas, no se como utilizar la opcion sumar, si se puede usar igual que una suma cualquiera....como lo puedo hacer...saludos desde chile, por favor me puedes ayudar... Dionisia

    ResponderBorrar
  11. Hola
    como hemos expplicado en varias notas, horas y fechas en Exel son números, y como tales se los puede sumar y restar. No entiendo cabalmente a que te refieres con "utilizar la opción suma", pero tienes que tomar en cuenta el asunto del formato, como explico en esta nota.

    ResponderBorrar
  12. Hola Jorge soy un fiel seguidor de tus anotaciónes y en mas de una vez me han sido mas que útiles. Mi consulta es la siguiente, hace un tiempo que estoy complicado con una planilla que cree para calcular el tiempo y el costo que representa cada movimiento x. El tema es que estoy trancado y no consigo avanzar. Es posible que tu la veas y me digas en que me estoy equivocando?
    Espero tu respuesta.
    Saludos!

    ResponderBorrar
  13. Hola Oscar

    tendrás que mandarme el archivo con una explicación de lo que quieres hacer

    ResponderBorrar
  14. Gracias por la respuesta Jorge. Dime a que dir quieres que te lo envie....

    ResponderBorrar
  15. La que figura en el blog: jorgedun@gmail.com

    ResponderBorrar
  16. Hola, de verdad muy buena la informacion, per presento un problema al copiar las formulas, cuando utilizo el simbolo $ me arroja error de formula, cual seria el simbolo equivalente a este? Desde Venezuela. Gracias

    ResponderBorrar
  17. El símbolo $ se usa en todos los idiomas para señalar el tipo de dirección (absoluta, relativa o semirelativa). Por lo visto estás usando erróneamente el símbolo, o al usarlo estás creando una referencia equivocada.

    ResponderBorrar
  18. Hola!
    Gracias de antemano por tu tiempo. Te quería hacer la siguiente consulta yo tengo en una columna actividades y en otra duración de cada actividad en formato hh:mm:ss, pero cuando clickeo la celda o quiero hacer la sumatoria total de la duración de cada actividad transforma el formato 00:20:30, en 12:20:30 am.
    Muchas Gracias!
    Silvana

    ResponderBorrar
  19. Para sumar horas tienes que usar el formato [h]:mm:ss.
    Fijate en esta nota.

    ResponderBorrar
  20. Hola Jorge

    Perdoname que haga de abogado del diablo, pero creo que hay algo que se podría solucionar de otra manera alternativa. Todo surge a partir de tus soluciones, por lo que el mérito sigue siendo tuyo. Paso a explicar:
    =(E5-B5)+(E5 < B5)*24 para solucionar el tema de la hora de salida "anterior" a la hora de entrada.
    La podríamos reemplazar por:
    =(E5-B5)+(E5 < B5)
    Es decir, sin multiplicar por 24 porque lo que verdaderamente importa es que sume 0 o sume 1, no 24 enteros. Creo que de ahí viene la necesidad posterior de corregir esto con la función Residuo.
    De todos modos, con la fórmula:
    =RESIDUO(E5-B5,1) es cierto que también se soluciona el problema de cuajo.

    Gracias por prestarme atención

    ResponderBorrar
  21. No hay nada que perdonar y todo comentario es bienvenido.

    ResponderBorrar
  22. Hola!
    Necesito sumar horas en un calendario. Le he dado el formato [h]:mm para que el sumatorio sea superior a 24h pero me gustaría que al poner en una celda 8 y pasar a la siguiente directamente me lo convierta en 8:00. Es posible? Muchas gracias.

    ResponderBorrar
  23. Oscar
    se puede hacer con fórmulas o programando un evento (macros). Podés leer esta nota para darte una idea.

    ResponderBorrar
  24. Hola,
    Cómo puedo hacer para que en una celda sólo me permita escribir un valor concreto? Sería poner una X y que escribiendo cualquier otra cosa me salga un menaje de error. Es en Validación pero no se cómo decirle que me deje poner X.
    Gracias.

    ResponderBorrar
  25. Usando la opción Fórmula con

    A1="X"

    donde A1 es la celda que estás evaluando

    ResponderBorrar
  26. Que tal jorge una duda... si tengo dos fechas con hora dd-mm-aaaa(Fecha inicial 01/12/2009 12:00:00 pm y fecha final 02/12/2009 09:00:00 am) necesito saber el tiempo en horas que hay entre fecha fina y fecha inicial, pero sin que me tome el domingo por ejemplo, ya tengo las horas entre una fecha y otra pero necesito discriminar el domingo o dia festivo... intente con dias.lab o NETWORKDAYS, pero no logro tener el resultado... me pueden echar la mano para saber como resolverlo

    ResponderBorrar
  27. NETWORKDAYS no nos sirve ya que calcula solamente días enteros y además no toma en cuenta tanto los sábados como los domingos.
    Una solución sencilla sería restar las fechas cuidando de poner el formato personalizado [hh]:mm para ver el resultado en horas y luego restar la cantidad de domingos X 24.
    Tal vez publique una nota con una solución más elaborada. Mientras tanto puedes leer la nota que publiqué sobre una versión mejorada de la función DIAS.LAB.

    ResponderBorrar
  28. Saludos;

    Exelente sitio. Me ayudo mucho la manera de calcular horas extras pero me queda salgo por resolver. Resulta que estoy calculando las horas extras del personal y lo hice como tu lo indicas aca y funciono bien pero falta un detalle, resulta que las horas extras tienen 2 valores distintos, si es entre las 6 pm y las 10 pm cuestan 25% mas del valor normal de la hora trabajada y si es entre las 10 pm y ñas 6 am entonces cuesta 75% mas. No se como indicarle a excel esta condicion de valor de las horas extras dependiendo de la hora.

    De antemano gracias por tu ayuda

    ResponderBorrar
  29. Tienen que crear dos columnas de horas extras, en lugar de una como muestro en el ejemplo.

    ResponderBorrar
  30. Como hago para calcular la cantidad de hora trabajadas de 5:00pm a 12:00 donde antes de las 9:00 pm son horas normales y luego de las 9:00pm son horas nocturnas...????

    ResponderBorrar
  31. Estaré publicando una nota sobre el tema. En tu consulta me falta saber cuando termina el turno noche (empieza a la 09:00 PM y termina..?), o cuando empieza el turno normal.

    ResponderBorrar
  32. HOLA JORGE EXCELENTE TRABJO, ME PUEDES AYUDAR APARTE DE SACAR TODO LO ANTERIOR, NECESITO EL TOTAL DE RETARDOS HORA ENTRADA; HORA DE SALIDA, ADEMAS SALIDA DESCANSO;ENTRADA DESCANSO Y UNA COLUMNA MAS DE EL TOTAL DE AMBOS, PODRIA MOLESTARTE ME URGE!!!!!!!!!

    AYUDA!!!!!!!

    ResponderBorrar
  33. Ponte en contacto conmigo por mail privado. La dirección figura en el enlace "Ayuda".

    ResponderBorrar
  34. YO TENGO EN MI TRABAJO FLEXIBILIDAD EN LA SALIDA, POR LO QUE UNOS DIAS SALGO ANTES Y OTROS DESPUES , TENGO UNA JORNADA DE 7.30 AL DIA PERO EL COMPUTO SE HACE SEMANAL POR LO TANTO UNAS VECES HAGO DE MAS Y OTRAS DE MENOS , PARA HACER UNA TABLA DONDE ME CUENTE LOS MINUTOS U HORAS NEGATIVAS CUANDO LAS DEBA Y ASI IR SABIENDO EL ACUMULADO TANTO POSITIVO COMO NEGATIVO , YO LO HE INTENTADO CON LAS HORAS Y MINUTOS POSITIVOS BIEN, PERO LOS NEGATIVOS EL RESULTADO ME DA ######### AYUDA CON LA FORMULA GRACIAS!!!!!!

    ResponderBorrar
  35. Estimado,

    el tema ha sido tratado en los albores de este blog, fijate en esta nota.
    Y por favor, no escribas todo en mayúsuculas; es cmo si estuvieras gritando.

    ResponderBorrar
  36. Gracias Jorge, perdona por los gritos, le echare un vistazo a la nota. Si no me aclaro para sacar
    HORA ENTRADA, SALIDA . DIFERENCIA Y ACUMULADO para cuantificarlo diariamente. tendrás noticias mías aunque no sean gritando, au!!!!! Saludos.

    ResponderBorrar
  37. por favor podrias enviarme este archivo excel a mi correo ?? podrias hacerlo porfavor

    ResponderBorrar
  38. Puedes descargarlo usando el enlace que aparece en la nota (donde dice "consideremos este ejemplo")

    ResponderBorrar
  39. Hola Jorge estupendo trabajo el tuyo, felicidades todo muy claro, sabes estoy tratando de hacer en excel algo asi como un control de entrada y salida de los empleados, ya que no tenemos reloj biometrico donde trabajo. todo va bien pero tengo un problemita con la formula de la fecha y hora ya que la que estoy usando es =ahora(),fecha(),hora(), todo parece perfecto pero al actualizar cualquier casilla se actualizan todas las demas donde tengo estas formula. no se si me entiendes? quisiera una formula donde el tiempo de llegada o de salida del empleado no cambie si actualizo otra casilla.

    gracias de antemano.

    Alex.-

    ResponderBorrar
  40. Alex,
    AHORA() es una función volátil que Excel recalcula cada vez que hay un cambio en la hoja. Por ese motivo no puedes usar fórmulas para crear constantes (valores que no cambian).
    La solución a este tipo de situaciones es introducir la fecha/hora manualmente o programar un evento (macro) que lo haga.

    ResponderBorrar
  41. Muchas Gracias, continuare googliando en busca de un macro adecuado, que se adapte a lo que le explique, salvo que usted tenga algún consejo al respecto o idea que me pueda explicar para hacerlo, estaría muy agradecido.

    nueva vez gracias y bendiciones...

    Alex.

    ResponderBorrar
  42. Buenas tardes!

    Solo queria felicitarte por tu blog, llevo una semana volviendome loca en el trabajo intentando hacer formulas para calcular las horas de los trabajadores, nocturnidad, horas extra nocturnas... Hoy he encontrado tu blog y he podido resolver todas mis dudas!! Ya la tengo lista para empezar y todo gracias a ti! Muchisimas gracias de verdad por compartir tus conocimientos con los demas!! :)
    Eres un fenómeno!!

    ResponderBorrar
  43. Hola, necesito saber si hay posibilidad de trabajar solo con los minutos de una celda, esto por la necesidad de emplear los minutos sin redondear ni tomar en cuenta segundos u horas, para trabajarlos en una formula de calculo. Saludos

    ResponderBorrar
  44. Por supuesto, con la función MINUTO. Por ejemplo, si la celda A1 contiene la hora 18:57:43,

    =MINUTO(A1) da como resultado 57

    ResponderBorrar
  45. Hola Jorge, vos sos como la Madre Teresa de Calcuta....pero de Excel.
    Admiro la predisposición que tenés a las preguntas de la gente....yo no soy la excepción.

    Para mi uso la planilla está perfecta pero necesitaria una columna mas que se llame "horas no extras". Es decir cuando el empleado se retira antes de cumplir las 8hs (ej: trabajó 6:30hs entonces le faltaron 1:30hs para cumplir las 8hs ó trabajó 4:15hs y le faltaron 3:45hs para completar las 8hs). La columna "horas no extras" quiero que me muestre las horas y minutos que le faltaron para completar las 8hs. Al final del mes sumaría todas las horas extras y le restaría el total de las horas no extras para que me de como resultado las verdaderas horas extras o en caso contrario que me muestre las horas que estaría debiendo.

    Espero que me hayas entendido

    te mando un abrazo....y desde ya muchas gracias

    Javier

    ResponderBorrar
  46. Javier,
    en el ejemplo de la nota podrias agregar una columna con esta fórmula

    =SI(H5<$E$1;$E$1-H5;0)

    donde $E$1 contiene la duración de la jornada (8 horas) y H5 es el total de horas trabajadas.

    ResponderBorrar
  47. buenas tardes estoy haciendo una tabla de tiempo tengo una horas positivas y unas negativas y quiero sumarlas pero no he podido me pueden ayudar como lo hago gracias..

    Min. (-) Perdidos o Ganados

    00:16:00
    -00:10:00
    -00:13:00
    aqui abajo quiero colocar el total de min perdidos y no he podido con la formula les agradecería..

    ResponderBorrar
  48. Hola, en Excel no existen horas (o fechas) negativas. Cuando ingresas -00:10:00 Excel lo convierte en texto y por lo tanto no puede usarse en operaciones aritméticas.
    Estaré úblicando una nota sobre el tema.

    ResponderBorrar
  49. Hola buenas tardes me podria ayudar, donde trabajo tengo muchos compañeros que diario llegan tarde, la empresa opto por descontarles los minutos de retraso pero no se cual formula aplicar para descontarles los minutos de retardo, con el ejemplo que puso ya puedo saber cuantas horas extras les voy a pagar. agradecere mucho que me ayude.

    rbk

    ResponderBorrar
  50. Si te explico tus compañeros me van a odiar!!! :)
    Y ahora en serio, sencillamente con una resta; si en la celda B1 está la hora de llegada (digamos, 07:15) y en la celda A1 la hora de entrada establecida (digamos, 07:00), la fórmula

    =B1-A1

    calcula la diferencia (00:15)

    ResponderBorrar
  51. Hola como estas, ante todo muchas gracias... eh aprendido bastante con tus indicaciones... Tengo una duda, tengo una columna con los siguientes valores como ejemplo; M5=00:26, M6=-00:17, M7=-00:23. Que es el resultado de la resta de "Horas solicitadas" - "Horas realizadas", de eso valores indicados (M5,M6;M7) quiero sumarlos en una sola casilla por ejm. M10. Podrias por favor ayudarme?. Muchas gracias de antemano.

    ResponderBorrar
  52. EN Excel las horas y las fechas son números (puedes ver algunas de las notas que he publicado sobre el tema). Por lo tanto, la suma y la resta se aplica de la misma manera. La única limitación es que, por defecto, Excel no reconoce fechas u horas "negativas".
    Por eso no me queda claro tu ejemplo, ya que Excel no puede exhibir u operar con un valor horario como -00:17.

    ResponderBorrar
  53. Jorge, muchas gracias por tu respuesta, eh revisando mas a fondo tu blog veo que una persona ya te habia formulado la misma pregunta que yo (Losung SAS, 15 marzo, 2013 19:24). Tratare de comentarte con mayor detalle. Yo debo cumplir 9:36 horas trabajadas si entro a las 22:00 y salgo a las 7:00 solo estaria cumpliendo 9 horas, por ende me faltaria 36 min. es por eso que en una columna se salen -0:36 (por que debo los 36 min.) otro dia ingreso a las 22:00 y salgo a las 7:46, por ende tengo 10 min. a mi favor, osea en esa misma columna se almacenaria 0:10, y asi sucesivamente quedándome algo como esto.

    -0:36
    0:15
    0:14
    -0:05

    Al final de mes yo debo consolidar todo eso en un solo cuadro (osea sumar -0:36+0:15+0:14+(-0:05)) y asi tener como resultado ver si debo o tengo horas extras a mi favor. a ese resultado mostrado llego con la siguiente formula =SI(L7<G7;"-"&TEXTO(G7-L7;"hh:mm");TEXTO(L7-G7;"hh:mm")), donde L7=Son las horas que eh realizado y G7=Son las horas que debo realizar.

    Tu me dices que no se puede realizar una suma con datos negativos, entonces que solución podría tomar?? espero puedas ayudarme. Muchas gracias de antemano.

    ResponderBorrar
  54. Hay varias formas de tratar el tema de horas negativas. En tu caso tendrías que tomar en cuenta también la fecha, con lo que se soluciona el tema de las horas trabajadas. Si entras el 12 de mayo a las 22:00 y sales el 13 de mayo a las 07:00, el resultado es 9 horas. Como he señalado en muchas oprotunidades, las fechas y horas en Excel son números. Así la fecha/hora 12/05/2013 22:00 en Excel es el número 41406.9166666667 (la parte entera son los días transcurridos desde el 01/01/1900 y la parte decimal la hora dividido por 24); el 13/05/2013 07:00 es el número 41407.2916666667 y la diferencia es 0.375 (que multiplicado por 24 es 9).
    También puedes fijarte en lo que describo en esta nota.
    Una acotación: las fórmulas que utilizan funciones de texto dan valores de texto, no númericos y por lo tanto no pueden usarse en cálculos.

    ResponderBorrar
  55. Excelente, muchas gracias

    Saludos,

    Victor (ECU)

    ResponderBorrar
  56. hola! tengo una duda que no puedo resolver...tengo tiempo inicial y final de un trabajo, y calculo con ellos el tiempo de trabajo. Con ese dato y las piezas realizadas intento calculas las piezas/hora..pero cuando efectuo la division no me da el resultado correcto. Creo que es una cuestion de formatos..agradecería tu ayuda! saludos
    Laura

    ResponderBorrar
  57. La fórmula para calcular la piezas por hora es:

    =piezas/((final-inicial)*24)

    El motivo por el cual debes multiplicar por 24 la diferencia de horas está explicado en esta nota.

    ResponderBorrar
  58. Buenas tardes
    Quisiera sacar cuantas horas efectivamente laborales hay entre las 9:25 am y 5:48 pm de un mismo dia. Hay que tener en cuenta que la hora de comida es de 12:00 a 12:30 y también hay un descanso de 15:00 A 15:10. Si es tan amable de de ayudarme a crear una formula donde se realicen todos los cálculos y me de el resultado en un celda. En ocasiones los trabajadores entran después de de la hora de comida y del descanso y por consiguiente no se les toma como horas efectivas.

    ResponderBorrar
  59. Hola,

    podrías adaptar el modelo que publico en la nota agregando otro descanso (para el de diez minutos) o restándolo del total de horas.

    ResponderBorrar
  60. Hola Jorge, ante todo espero que se encuentren bien tu y los tuyos.

    Si ingreso en A1 la fecha y hora de inicio de un trabajo, en A2 las horas estimadas, en A3 las horas reales no tengo ningun problema, pero en A4 necesito definer la hora real final, tomando en cuenta que el horario de trabajo es desde las 6:00 am hasta las 8:00 pm, lo que quiere decir que si un trabajo comienza el 30/10/2013 a las 6:00pm, dura 4 horas, debe registrarme en A4 como hora final, el 31/10/2013 8:00 am.

    Como puedo hacer esta formulacion.

    saludos

    Juan Annunziata

    ResponderBorrar
  61. Hola Juan, disculpas por el tiempo que me tomó llegar a tu comentario. Creo que la técnica que muestro en esta nota te puede ayudar.

    ResponderBorrar
  62. En una hoja excell estamos anotando las horas de los trabajos de una máquina y la fórmula nos va sumando esas horas y las traduce a fecha/hora, ej., 09/07/2014 9:30.
    Quisiera saber como realizar la fórmula para que a medida que va sumando horas saltara al día siguiente teniendo en cuenta que esa máquina sólo trabaja 16 horas. Por ej. arranca una jornada a las 6.00 a.m. y finaliza a las 22:00. Qué la siguiente acumulación de horas las estableciera a partir del día siguiente a las 6.00 a.m. o a otr hora que se determinara.

    Gracias

    ResponderBorrar
  63. Te sugiero que te pngas en contacto connmigo por mmail privado (fijate en el enlace Ayuda, en la parte superior de la plantilla).
    Mientras tanto: una tabla dinámica sería la mejor solución, y la más sencilla.

    ResponderBorrar
  64. Han pasado años desde esta publicación y quiero dejar contancia de que se sigue ocupando, excelente blog. ¡¡¡¡¡¡Felicidades!!!!!!!

    ResponderBorrar
  65. Hola, por favor me colaboran con lo siguiente: requiero que me reste la hora de ingreso 22:00 y la hora de salida 06:00, pero me arroja un error, Gracias

    ResponderBorrar
  66. Karen, tendrás que ser más precisa en tu consulta. Te sugiero que veas lo que escribo en el enlace Ayuda (en la parte superior de la plantilla del blog).

    ResponderBorrar
  67. Hola, buen día, Gracias a lo visto en este blog, me ayudé para obtener resultados de asistencia del personal de mi trabajo (puntualidad, tolerancia, retardos mayores y menores y días de suspensión por acumulo de retardos), también obtuve las salidas tempranas, de los cuales cuenta como un "pase de salida" y aquí es donde encuentro el problema, pues en pantalla me imprime las salidas 0:00 y me lo cuenta como "pase de salida", lo curioso es que, por ejemplo en horarios 8:00-8:00 ó 7:00-7:00 no hay problema, pero en horarios 8:30-8:30 ó 7:30-7:30 es donde me imprime "0.00". Alguien me podría ayudar en este caso que no resuelvo???

    ResponderBorrar
  68. Jugu, fijate en el enlace ayuda, en la parte superior delo blog. También te sugiero consultar alguno de los muchos foros de Excel que existen en la Internet (ese es un blog de manera que sólo recibirás respuestas mías).

    ResponderBorrar
  69. Buenos días, me gustaria que me ayudaras. Tengo que realizar un formato de asistencia que al leer con un lector el carnet llene el formato automatico sin que la persona tenga que firmar, Ahora bien mi consulta es: que en el caso de las personal que vienen de otro turno a realizar jornadas de horas extras necesito que de acuerdo a su turno original me salga en una columna la fecha y la hora y de que turno es y si es de un turno distinto me diga en otra columna llamada Overtime "Si". Espero que entiendas mi consulta y me puedas ayudar.

    ResponderBorrar
  70. Por favor, fijate en las instrucciones que pongo en el enlace Ayuda (en la parte superior del blog).

    ResponderBorrar
  71. Necesito hacer una toma de tiempos de un proceso, como puedo hacerle para que en cada celda de la columna me valla marcando el tiempo por operacion

    ResponderBorrar
  72. además de una mente brillante tienes un alma altruista! gracias! bendiciones!!! excelente blog!! una consulta, necesito calcular horas nocturnas, se consideran horas nocturnas desde las 22:00 a las 06:00. En el blog aparece una pregunta como esta y tu respuesta fue que lo ibas a publicar pero no lo encontré. Podrías ayudarme?? gracias desde ya! bendiciones!

    ResponderBorrar
  73. Hola Silvana, estoy terminando de preparar una nota sobre el tema. Espero publicarla en los próximos días.

    ResponderBorrar
  74. jorge eres el mejor, no sabes como me has ayudado con tu post, te deseo miles de bendiciones. como silvana a mi también me gustaría calcular las horas nocturnas en el mismo formato que después de las 22:00 son nocturnas hasta las 6:00

    ResponderBorrar
  75. Hola Pablo, gracias por los conceptos.
    En respuesta a la consulta de Silvana publiqué dos notas:

    Horas trabajadas en turno noche;

    Horas en turnos distintos.

    Espero que te sean útiles.

    ResponderBorrar
  76. hola jorge ayudame porfavor

    como hago para que mi formula me redondee a 1 hora si llega a 50 min o mas , si revasa 30 min que me dedondee a 30 min y si es menos de 30 min que redonee a cero

    ResponderBorrar
  77. NO ME QUEDA MUY CLARO LO QUE QUIERO ES ESTO APLICADO AUNA SOLA FORMULA

    SI HORA LLEGA A 3:50 MIN REDONDEAR A 4 HORAS , PERO SI LLEGA ANTES DE LOS 50 MIN REDONDEAR A 30 MIN Y SI LLEGA ANTES DE 30 MIN REDONDEAR HACIA CERO
    03:55 04:00
    03:42 03:30
    03:25 03:00

    ResponderBorrar
  78. Alberto,
    en la nota mencionada muestro todas las técnicas para hacerlo. Tal vez publique un post basándome en tu consulta para mostrar cómo aplicar las técnicas para situaciones como las tuyas.

    ResponderBorrar
  79. Hola, Jorge!
    Gracias por tus explicaciones. Tengo una consulta también. Necesito tu ayuda para hacer el siguiente cálculo, teniendo en cuenta estos datos (doy datos enteros, para hacerlo fácil):

    Hora de Entrada 8:00;
    Inicio Pausa: 14:00;
    Fin Pausa: 15:00;
    Hora Salida = X

    Si la jornada laboral son 8.30h, me gustaría que la celda de Hora de Salida "X" calculara automáticamente la que debería ser la hora de salida para cumplir las 8.30h de jornada, teniendo en cuenta los valores de inicio, y la pausa de comida.

    ¿me puedes ayudar? no logro solucionarlo! Muchas gracias

    ResponderBorrar
  80. Estafania, con tus datos sería muy fácil, Sencillamente:

    hora de entrada + jornada + (fin pausa - inicio pausa) = hora salida

    todo usando la notación de horas: 08:00 + 08:30 +(15:00 - 14:00) = 17:30

    Dos preguntas: ¿la pausa siempre es de una hora o sólo de 14:00 a 15:00?; ¿la hora de salida puede darse al día siguiente de la entrada (turnos nocturnos)?

    ResponderBorrar
  81. Estimado, necesito llevar un registro de tiempo por actividades, el formato debe ser en minutos y no en horas, como puedo registrar en el resultado del calculo de inicio y termino (horario) y de como resultado solo en minutos, es decir, 15, 20, 90, etc.

    Slds

    ResponderBorrar
  82. Hola Ricardo, tenés que multiplicar la diferencia por 1440 y usar el fomato Número en la celda del resultado.
    Por ejemplo, si al hora de inicio es 06:00 (celda A1) y la de finalización 09:15 (celda A2), usás el formato Número en la celda A3 y ponés la fórmula =(A2-A1)*1440 que te dará 195 (3 horas * 60 + 15 minutos).
    ¿Por qué multiplicar por 1440? Te remito a este post de mi blog.

    ResponderBorrar
  83. Hola Jorge,
    Muchas gracias por tu respuesta.
    Respondiendo a tus preguntas: La pausa puede ser de una hora o de menos, media hora (puede variar). Y no, los turnos siempre acaban en el mismo día.

    Utilizando tu respuesta, he logrado con esta fórmula que funcione :)
    =C3+$B$1+(E3-D3)
    (= celda hora de entrada + celda con jornada + (celda fin de pausa - celda inicio de pausa))

    Gracias de nuevo! :)

    ResponderBorrar
  84. Hola jorge que tal estoy intentando sumar una horas que si las sumo a mano en un papel me dan 15:35:00 pero en excel no me da ese resultado me da 17:35:00 como hago para que me de el resultado anterior, es decir me la esta redondeando como hago para que no lo haga....gracias carlos desde venezuela

    ResponderBorrar
  85. Carlos, como pongo en esta nota, tienes que usar el formato [hh]:mm. Te sugiero que leas <a href="http://jldexcelsp.blogspot.co.il/2013/05/manejo-de-fechas-y-tiempo-en-excel.html" rel="nofollow'>este post</a> sobre operaciones con fechas y horas.

    ResponderBorrar
  86. Hola Jorge, Yo estoy tratando de utilizar una formula if por que quiero que si la celda esta entre menos de las 10:00PM me ponga cierta fecha (ejemplo 08/18/15), pero que si esta entre 10:00PM y 10:30PM me ponga el dia anterior a esa fecha. Ya logre que lo haga si la hora es igual a 10:00PM pero no encuentro la forma de que relacione hasta las 10:30. Mil gracias por tu ayuda!

    ResponderBorrar
  87. Podés hacerlo usando la función MEDIANA. Fijate en esta nota
    Por ejemplo, si en la celda A1 pones la hora 10:00 y en la celda A2 la hora 10:30, en la celda A3 la hora a considerar, esta fórmula

    =SI(MEDIANA(A1,A2,A3)=A3,HOY()-1,HOY())
    te dará la fecha de hoy si la celda A3 contiene una hora anterior a las 10:00 o posterior a las 10:30; si la celda A3 cae entre las 10:0 y las 10:30 te dará la fecha de ayer.

    ResponderBorrar
  88. que formula debo utilizar para saber que cantidad de horas extras son diurnas y cuantas son nocturnas, al ejemplo que haces es para calcular las horas extras totales,

    ResponderBorrar
  89. Yovanina, fijate en este post para calcular horas en turno noche y luego en este. para calcular distintos turnos.

    ResponderBorrar
  90. Jorge, primero agradecerte por el apoyo que nos das.

    Tengo el mismo problema que Estefania, pero en mi caso la hora de salida si se da al dia siguiente (Turno nocturno).

    teniendo en cuenta estos datos (doy datos enteros, para hacerlo fácil):

    Hora de Entrada 8:00;
    Inicio Pausa: 14:00;
    Fin Pausa: 15:00;
    Hora Salida = X

    Si la jornada laboral son 8.30h, me gustaría que la celda de Hora de Salida "X" calculara automáticamente la que debería ser la hora de salida para cumplir las 8.30h de jornada, teniendo en cuenta los valores de inicio, y la pausa de comida.

    ¿me puedes ayudar? no logro solucionarlo! Muchas gracias

    ResponderBorrar
  91. Hola RIchard, hay varias técnicas para calcular el lapse transcurrido cuando la salida se da al día siguiente. Yo recomiendo usar la función RESIDUO. Por ejemplo, supongamos estos datos

    Jornada 08:30 (B1)
    Entrada 18:00 (B2)
    Inicia Pausa 23:45 (B3)
    Fin Pausa 00:15 (B4)
    Salida 03:00 (B5)

    La fórmula en la celda B5 es

    =B2+B1+RESIDUO(B4-B3,1)

    ResponderBorrar
  92. Buenas Tardes, alguien sabe que formula en Excel debo de poner si quiero tener una celda que diga a que turno corresponde, en funcion de una columna que viene la hora,minutos,segundos


    Truno Inicio Termino
    Matutino 06:00 14:59
    Vespertino 15:00 22:59
    Noctuno 23:00 05:59

    ResponderBorrar
  93. Hola, fijate en este post del blog y en el post del enlace que aparece ne la nota.

    ResponderBorrar
  94. NECESITO SABER COMO SACAR LA DIFERENCIA DE HORAS (HORA DE ENTRADA DEL TRABAJADOR Y LA HORA DE ENTRADA DE LA EMPRESA .. LO E ECHO CON LA FORMULA SI PERO ME BOTA UNA HORA Q NO ES POR FAVOR AYUDA URGENTE O ALGUN CORREO DONDE COMUNICARME CONTIGO JORGE PORFAA !!

    ResponderBorrar
  95. Fijate en el enlace Ayuda, en la parte superior del blog.

    ResponderBorrar
  96. Buenos dias!! Felicidades por tu trabajo nos ha servido de gran ayuda.
    Yo llevo un formato de entrada, en una celda he puesto una formula para que me vaya sumando los minutos tardes; pero el problema que tengo es que cuando se pasa de la hora de llegadas tardes vuelve a iniciar desde el minuto.. como hacer para que me muestre la hora y minutos tardes?

    ResponderBorrar
  97. Hola, usando el formato [mm]. Fijate en este post, en los últimos párrafos.

    ResponderBorrar
  98. Otra aclaración, si sumas horas y minutos, el formato será [hh]:mm

    ResponderBorrar
  99. Hola, Jorge tengo una duda, mi pesonal ENTRA 8:00 y SALIDA 4:00, pero en ocasiones salen a las 6:00 P.M. y deseo poner una formula para copiarla y salga en automatico y no están que sumando linea por linea. MIL GRACIAS POR TU AYUDA.

    ResponderBorrar
  100. ¿Podrías replantear la consulta? No termino de entender cuál es la tarea.

    ResponderBorrar
  101. Este blog ha sido eliminado por un administrador de blog.

    ResponderBorrar
  102. Buenas dias jorge, soy nuevo en esto de los blogs, me ha parecido interesante todos tus blogs, excelente trabajo.

    Tengo una consulta, estoy controlando los tiempos en unos procesos, pero quiero descontar los 15 minutos de descanso, la hora y media de almuerzo

    Ejemplo

    PROCESO INICIO FIN TOTAL
    Corte 7:30 8:00 0:30
    Pulido 8:00 10:00 2:00
    Punzonado 10:10 13:00 2:50
    Doblez 13:00 15:00 2:00
    En el proceso de pulido necesito descontar los 15 minutos de descanso y en el de punzonado y doblez los minutos correspondientes.

    teniendo en cuenta que el descanso es de 9:45 a 10:00 y el almuerzo es de 12:30 a 14:00

    Espero me ayudes Jorge, gracias por tu conocimiento

    ResponderBorrar
  103. Puedes usar las técnicas que muestro en este post, agregando otros "descansos".

    ResponderBorrar
  104. COMO SUMAR LAS HORAS DE UN EQUIPO SI FALLO A LA 17:00 Y SALIO OPERATIVO 20:00 PM Y VOLVIO A FALLAS A HORAS 23:00 Y SALIO OPERATIVO A HORAS 01.30 AM TODO ESO SUCEDE EN UN MISMO DIA DE TRABAJO
    NOTA SOLO NO ME SUMA LA ULTIMA OBSERVACION DE 23:00 A 01:30 AM

    ResponderBorrar
    Respuestas
    1. Supongo que el problema se debe a que no todo sucede en un mismo día; fíjate que el último punto es a las 01:30 AM que es al día siguiente.
      En el post explico como corregir este problema. Fijate en donde dice: "La fórmula de horas trabajadas es =F5-((D5-C5)+(D5<C5)*24), donde nuevamente usamos la expresión (D5<C5)*24 para corregir el error que se genera cuando la salida sucede al día siguiente de la entrada"

      Borrar

Nota: sólo los miembros de este blog pueden publicar comentarios.