miércoles, mayo 10, 2006

Cálculos de tiempo con MS Excel – Diferencia de horas

En la entrada de ayer hablé sobre como Excel realiza cálculos de fechas y tiempo
Vimos que Excel representa las fechas como una serie de números enteros. Si trabajamos con la base del año 1900, el primer número de la serie, el 1, representa el 1 de Enero de 1900. Si usamos la base 1904, el 1 representa el 1 de Enero de 1904.
Las horas, minutos y segundos del día son representados por fracciones del número 1. De esta manera la hora 12:00 (mediodía) es representada por el número 0.5. Las seis de la tarde, 18:00, por el número 0.75.
Cuando efectuamos cálculos de fechas y tiempos, de hecho estamos trabajando con números, que Excel representa con formatos especiales como "dd-mm-yy", "h:mm:ss", o "dd-mmm-yyyy hh:mm". Cuando vemos la fecha 9-05-2006 en una celda, Excel "ve" el número 38846. Cuando queremos calcular cuantos días transcurrieron entre el 17-07-1991 y el 9-07-1992, restamos la última fecha de la primera. De hecho estamos efectuando la resta 33794-33436 = 358.
Si trabajamos con la base 1900 hay que tener en cuenta que Excel no "sabe" representar tiempos y fechas negativos.
Para aclarar este punto, mostraré un ejemplo de cálculo de lapsos entre dos horas.
Supongamos que queremos calcular el tiempo trabajado por operarios. Cuando el comienzo y el final de la jornada no caen en el mismo día, la hora del final es menor que la del principio y Excel no sabrá representar el resultado




El resultado ######## nos indica que Excel no sabe "traducir" la diferencia a formato de tiempo (tiempo negativo).
En este caso no podemos utilizar la función ABS (valor absoluto) a que el resultado sería incorrecto (ABS daría 16 horas en lugar de 8 horas).
En caso de tener que realizar este tipo de operaciones hay varias soluciones:
1 – Utilizar las horas con sus correspondientes fechas, como "08/05/2006 19:00" (ver en el archivo del ejemplo);
2 - Utilizar la fórmula que aparece en la fila 20 del ejemplo, =C20 + (C20"<"B20) - B20. En esta fórmula la expresión (C20"<"B20) puede dar como resultado 1 (verdadero) o 0 (falso). Si la hora del final es menor que la del comienzo, (C20"<"B20)=1, por lo tanto agregamos 1 a la hora del final, que es como agregar un día entero.
3 – El mismo efecto se puede conseguir con la fórmula que aparece en la fila 26 del ejemplo: =RESIDUO(C26-B26,1);


Categorías: Varios_


Technorati Tags:

45 comentarios:

  1. no se puede descargar el archivo de ejemplo. :(

    ResponderBorrar
  2. Gracias por el aporte.... my buen consejo

    ResponderBorrar
  3. Jorge: entrè a tu pàgina porque necesito saber como calcular dias y horas de trabajadores. Pero no me sale el excel de ejemplo. Mi pregunta es como puedo usar una formula que calcule tiempo trabajado al comienzo y final de una tarea que no cae el mismo dìa y me calcule sòlo hras trabajadas y no 24 hrs (son 8 hrs laborales)
    Tu pàgina es excelente, te felicito

    ResponderBorrar
  4. Hola

    fijate en esta nota. Hay un enlace para descargar un ejemplo para calcular horas de trabajo.

    ResponderBorrar
  5. Buenas Tardes, Jorge excelente tu blog de verdad que no tienes idea de como me has ayudado, PERO! siempre hay uno que otro detallito por ahí... Jorge estará en tus posibilidades en ayudarme con un caso de suma de horas y días; Me explico tengo una fecha "01/01/1900 10:00" y quisiera ver cuanto ha transcurrido hasta "10/03/1900 17:00" me suma las horas y los dias no pasan de 18 ó 20, estoy usando este formato para la celda dd "d" hh "h" mm "m" .

    Muchas Gracias!

    ResponderBorrar
  6. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  7. Rafael,
    tienes que usar el formato personalizado [h]:mm
    como muestro en esta nota

    ResponderBorrar
  8. Si tengo que sumar o restar tiempos,

    Ejemplo 10 Hs de trabajo menos 2 hs de descanso.

    Representado en excel(hh:mm) : 10:00
    Suponiendo que el descanso descuente -02:00
    La suma de las 2 celdas deberia dar 08:00,
    ¿Como se puede hacer esto si no se puede representar tiempo negativo?

    ResponderBorrar
  9. Usando el operador de resta (-)
    No hay necesidad de ingresar el tiempo como número negativo.

    ResponderBorrar
  10. Que tal Jorge, tu blog es excelente y quisiera preguntarte algo.
    Quisiera saber si hay alguna forma de que pueda extraer de la funcion =now()la hora para que pueda restar ese tiempo con respecto al tiempo en que llegue a trabajar.
    P ej:
    Entro a las 08:00 y que al momento que el tiempo va cambiando me vaya diciendo cuanto tiempo va transcurriendo.
    Espero mi pregunta sea clara.. Saludos

    ResponderBorrar
  11. Aldo,
    podés poner la hora con el atajo de teclado Ctrl+:
    Pero no veo cual es el problema. EN realidad necesitás le fecha junto con la hora para los casos en que tu trabajo empieza un día y termina al día siguiente (turno noche, por ejemplo). Si no incluís la fecha, Excel no puede calcular la diferencia.

    ResponderBorrar
  12. Hola Joege,
    en una celda tengo un sumatorio de horas extra.
    en otra celda tengo otro sumatorio de horas que falto.

    en una tercera celda resto una menos otra para obtener el total, pero si es negativo me da error. Cómo debo hacer para obtener ese dato?

    Gracias por tu blog

    ResponderBorrar
  13. Con alguno de los tres métodos explicados en la nota.

    ResponderBorrar
  14. Hola,

    necesito ayuda con esta suma de horas (positivas y negativas)

    (00:12)+(-01:14)+(01:06)+(00:25)+(00:18), el resultado deberia darme 00:47, pero no lo consigo. Con el formato hh:mm me da un resultado de 23:13, y con el formato[hh]:mm me sale 119:13, por favor ayudenme con este dato, necesito terminar este informe a la brevedad posible.

    Gracias de antemano,

    Jorge

    ResponderBorrar
  15. Estimado,
    Excel no puede interpretar la expresión (-01:14) como hora. Tienes que ingersar todos los valores horarios como números positivos. La forma de realizar el cálculo es

    (00:12)-(01:14)+(01:06)+(00:25)+(00:18)

    que efectivamente da 00:47

    ResponderBorrar
  16. Gracias por tu ayuda, solución sencilla. Pero cuando uno esta enfocado en el problema rara vez puede ver la solución... una vez más, gracias!!!

    ResponderBorrar
  17. Que tal, alguno sabe como restar una hora al format now()? es decir; si el resultado del format now() dentro de la celda es 3/31/2012 7:12:23 AM como haria para que sea el mismo resultado menos una hora quedando 3/31/2012 6:12:23
    Saludos y gracias

    ResponderBorrar
  18. Por supuesto:

    =NOW()-(1/24)
    o:
    =NOW()-TIME(1,0,0)
    o:
    =NOW()-TEXT("01:00","hh:mm")

    ResponderBorrar
  19. Hola buenas tardes, como puedo pasar estos segundos 489,32 al formato mm:ss? gracias

    ResponderBorrar
  20. Primero tienes que dividir 489,32 por 86400, obteniendo 0,00566343. Al aplicar el formato personalizado mm:ss, verás el valor 08:09.
    86400 es el resultado de multiplicar 24 X 60 X 60, es decir, la cantidad de segundos que hay en un día. Por qué será explicado en una próxima nota.
    Si quieres expresar también las décimas de segundos tienes que aplicar el formato mm:ss,000

    ResponderBorrar
  21. Quiero poner en una celda 31minutos y 40segundos, en otra una distancia (cinco kilómetros por ej.) y que un una tercera me de el tiempo por kilómetro en minutos y segundos.

    ¿Cómo lo hago?
    Enhorabuena por el blog.

    ResponderBorrar
  22. Digamos que el tiempo esta en la celda A1, con el formato hh:mm:ss (00:31:40); en la celda A2 la distancia con formato de número (5), para calcular la velacidad (km/hora) usamos esta fórmula

    =A2/A1/24

    usando el formato de número con dos decimales (el resultado es 9.47 km por hora)

    ResponderBorrar
  23. OK, he visto que aunque el formato de la celda sea mm:ss hay que meter el dato como hh:mm:ss para que no haga cosas raras.

    Gracias

    ResponderBorrar
  24. Por ejemplo el formato de la celda es mm:ss y si quiero meter 31m y 48s al teclear 31:48 me lo cambia por 48:00.
    Tengo que teclear 0:31:48 para que en la celda quede el 31:48

    ResponderBorrar
  25. Lo "raro" tiene que ver con el método que Excel usa para exponer las fechas y las horas (una nota sobre el tema será publicada en breve).
    Para Excel las fechas son una serie de números enteros donde el 1 es el 01/01/1900, el 2 es la fecha 02/01/1900 y asi sucesivamente. Las hora son la parte decimal de número (0.5 es la hora 12:00, 0.75 las 18:00, etc.).
    Así que cuando ingresas 31:48, Excel "traduce" el valor ingresado al número 1.325 (1 + 0.325), es decir el 01 de enero de 1900 a las 7:48.
    Si usas el formato personalizado mm:ss, ves 48:00 porque el formato expone sólo los minutos y segundo, pero no cambia el valor ingresado (que sigue siendo 1.325).
    Como ya has descubierto, tienes que ingresa siempre las horas y los minutos.

    ResponderBorrar
  26. Buen Día,
    Tengo una duda, en mi caso tengo que poner 48 hrs= 48:00 (hh:mm) y cuando lo pongo al formato
    hh:mm, me apararece en celda 00:00 pero cuando la seleciono me muestra 02/01/1900 12:00:00 a.m. y veo que me realizo el cambio pero en los dias, como puedo hacer para que me muestre las horas solamente en formato hh:mm

    ResponderBorrar
  27. Tienes que udar el formato [hh]:mm

    ResponderBorrar
  28. Buenas tardes. Cual seria la formula para calcular las horas nocturnas , estas comienzan a las 20:00 hs (a1)y finalizan a las 6:00 hs.(b2) Gracias

    ResponderBorrar
  29. Hola, es lo que se explica en la nota!

    ResponderBorrar
  30. como le puedo restar al tiempo trabajado por decir media hora que es la hora de comida?

    ResponderBorrar
  31. Si en la celda A1 tenemos el total de horas trabajadas (digamos 08:00) y en la celda B1 el valor 00:30, una simple resta A1-B1 dará el resultado.
    Tqambién podemos usar A1-0.0208333333333333. Este número es el resusltado de 0.5/24.
    Fijate en esta nota para ver cómo Excel maneja los cálculos de fechas y de horas.

    ResponderBorrar
  32. estimado Jorge, muy claras las explicaciones, ayudan muchísimo. tengo una duda: en los ejemplos las horas son completas pero cuando he probado con fracciones de hora los cálculos no son correctos. quedo a la espera de tu amable respuesta

    ResponderBorrar
  33. SI, en el ejemplo uso horas "redondas". ¿Qué cálculo estás haciendo y cuál es el resulto inocrrecto?.

    ResponderBorrar
  34. Muchas gracias. A modo de ejemplo: si en la 1a columna tengo las horas de ingreso, en la segunda la hora de salida, en la tercera el cálculo de horas trabajando (incluyendo minutos) y en la 4a el jornal a pagar, cuando multiplico la 3a y la 4a no calcula bien la fracción de hora (p.e. 15 minutos debería ser un cuarto de hora pero no es lo que sale, creo que usa el número de serie incorporado en el formato de tiempo).

    ResponderBorrar
  35. estimado Jorge, revisando tus blogs encontré unas sugerencias que creo me han servido: combiné Hora() + Minuto()/60 y así pude multiplicar el resultado por otros valores. Hasta donde pude verificar, los cálculos son correctos.
    muchas gracias por la ayuda! saludos,

    ResponderBorrar
  36. Hola Jorge de antemano felicitaciones por blog excellente aporte para nosotros los neofitos en excel, mira quiero tengo un problema en el calculo de tiempos y es con las llegadas tarde del personal, como politica de la empresa donde trabajo ahi que descontar el tiempo de las llegadas tarde que son basicamente en minutos.
    tengo una hora de entrada que redondeo a una hora expecifica si llegan temprano por decir alguien entra a las 9:00 am y llega a las 8:45 redondeo a 9:00 am pero si esta persona llega a las 9:15 am como hago para que deje esta hora y pueda hacer los calculos de restar estos minutos de llegar tarde. ayudame plis que me urge calcular estos tiempos. gracias de nuevo

    ResponderBorrar
  37. Rey, tendrías que comparar la hora de llegada (digamos 09:15) con la hora que debe llegar (09:00); si la diferencia es mayot que 0, entonces calcular la diferencia y si no es mayor de 0, redondear a 09:00.

    ResponderBorrar
  38. Muy buenas aportaciones !!!!GRACIAS!!!!!

    ResponderBorrar
  39. hola, como calculo las horas que debe un trabajador al retirarse antes de sus horario de salida

    ResponderBorrar
  40. Diana,
    sería la duración de la jornado menos las horas trabajadas o la hora de finalización del turno menos la hora de salida efectiva.
    Estas cálculos son obvios, por lo que supongo que te estás enfrentando a algún otro tipo de problema,

    ResponderBorrar
  41. Excelente aporte, llevo días quebrandome la cabeza, muchísimas gracias.

    ResponderBorrar
  42. Hola Buenas tardes. No puedo abrir en enlace, Da error.

    ResponderBorrar
  43. Hola, el enlace funciona pero es un poco lento; requiere paciencia. Si estás trabajando en una red, puede ser que el administrador haya bloqueado las descargas.

    ResponderBorrar

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