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

miércoles, mayo 10, 2006

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:

37 comments:

Anónimo,  24 abril, 2007 11:27  

no se puede descargar el archivo de ejemplo. :(

Anónimo,  30 octubre, 2007 15:34  

Gracias por el aporte.... my buen consejo

Anónimo,  17 septiembre, 2008 22:07  

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

Jorge L. Dunkelman 18 septiembre, 2008 17:15  

Hola

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

Rafael 13 enero, 2010 23:24  

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!

Jorge L. Dunkelman 14 enero, 2010 07:13  
Este comentario ha sido eliminado por el autor.
Jorge L. Dunkelman 14 enero, 2010 07:14  

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

Anónimo,  17 marzo, 2010 19:37  

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?

Jorge L. Dunkelman 18 marzo, 2010 06:35  

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

Aldo 20 julio, 2010 23:28  

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

Jorge L. Dunkelman 21 julio, 2010 07:24  

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.

Anónimo,  02 febrero, 2012 11:38  

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

Jorge L. Dunkelman 02 febrero, 2012 14:19  

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

JOrgeG 18 marzo, 2012 04:03  

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

Jorge L. Dunkelman 18 marzo, 2012 07:06  

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

JOrgeG 18 marzo, 2012 17:28  

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!!!

Anónimo,  01 abril, 2012 02:48  

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

Jorge L. Dunkelman 01 abril, 2012 07:16  

Por supuesto:

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

Anónimo,  28 noviembre, 2012 17:07  

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

Jorge L. Dunkelman 28 noviembre, 2012 18:16  

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

Anónimo,  09 abril, 2013 19:07  

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.

Jorge Dunkelman 09 abril, 2013 21:26  

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)

Anónimo,  11 abril, 2013 17:39  

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

Jorge Dunkelman 11 abril, 2013 17:53  

Hmmm...¿qué cosas raras?

Anónimo,  11 abril, 2013 18:03  

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

Jorge Dunkelman 12 abril, 2013 15:30  

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.

Anónimo,  22 mayo, 2013 21:34  

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

Jorge Dunkelman 22 mayo, 2013 23:35  

Tienes que udar el formato [hh]:mm

Anónimo,  25 julio, 2013 23:33  

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

Jorge Dunkelman 26 julio, 2013 07:23  

Hola, es lo que se explica en la nota!

Jose Alberto Benavente Valencia 22 agosto, 2013 03:19  

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

Jorge Dunkelman 22 agosto, 2013 10:16  

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.

Anónimo,  23 diciembre, 2013 04:19  

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

Jorge Dunkelman 23 diciembre, 2013 07:36  

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

Anónimo,  23 diciembre, 2013 14:56  

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).

Anónimo,  24 diciembre, 2013 15:47  

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,

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP