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: Fechas y tiempo en Excel
no se puede descargar el archivo de ejemplo. :(
ResponderBorrarEnlace corregido ! :)
ResponderBorrarGracias por el aporte.... my buen consejo
ResponderBorrarJorge: 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)
ResponderBorrarTu pàgina es excelente, te felicito
Hola
ResponderBorrarfijate en esta nota. Hay un enlace para descargar un ejemplo para calcular horas de trabajo.
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" .
ResponderBorrarMuchas Gracias!
Este comentario ha sido eliminado por el autor.
ResponderBorrarRafael,
ResponderBorrartienes que usar el formato personalizado [h]:mm
como muestro en esta nota
Si tengo que sumar o restar tiempos,
ResponderBorrarEjemplo 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?
Usando el operador de resta (-)
ResponderBorrarNo hay necesidad de ingresar el tiempo como número negativo.
Que tal Jorge, tu blog es excelente y quisiera preguntarte algo.
ResponderBorrarQuisiera 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
Aldo,
ResponderBorrarpodé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.
Hola Joege,
ResponderBorraren 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
Con alguno de los tres métodos explicados en la nota.
ResponderBorrarHola,
ResponderBorrarnecesito 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
Estimado,
ResponderBorrarExcel 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
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!!!
ResponderBorrarQue 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
ResponderBorrarSaludos y gracias
Por supuesto:
ResponderBorrar=NOW()-(1/24)
o:
=NOW()-TIME(1,0,0)
o:
=NOW()-TEXT("01:00","hh:mm")
Hola buenas tardes, como puedo pasar estos segundos 489,32 al formato mm:ss? gracias
ResponderBorrarPrimero tienes que dividir 489,32 por 86400, obteniendo 0,00566343. Al aplicar el formato personalizado mm:ss, verás el valor 08:09.
ResponderBorrar86400 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
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.
ResponderBorrar¿Cómo lo hago?
Enhorabuena por el blog.
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
ResponderBorrar=A2/A1/24
usando el formato de número con dos decimales (el resultado es 9.47 km por hora)
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.
ResponderBorrarGracias
Hmmm...¿qué cosas raras?
ResponderBorrarPor 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.
ResponderBorrarTengo que teclear 0:31:48 para que en la celda quede el 31:48
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).
ResponderBorrarPara 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.
Buen Día,
ResponderBorrarTengo 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
Tienes que udar el formato [hh]:mm
ResponderBorrarBuenas 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
ResponderBorrarHola, es lo que se explica en la nota!
ResponderBorrarcomo le puedo restar al tiempo trabajado por decir media hora que es la hora de comida?
ResponderBorrarSi 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.
ResponderBorrarTqambié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.
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
ResponderBorrarSI, en el ejemplo uso horas "redondas". ¿Qué cálculo estás haciendo y cuál es el resulto inocrrecto?.
ResponderBorrarMuchas 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).
ResponderBorrarestimado 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.
ResponderBorrarmuchas gracias por la ayuda! saludos,
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.
ResponderBorrartengo 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
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.
ResponderBorrarMuy buenas aportaciones !!!!GRACIAS!!!!!
ResponderBorrarhola, como calculo las horas que debe un trabajador al retirarse antes de sus horario de salida
ResponderBorrarDiana,
ResponderBorrarserí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,
Excelente aporte, llevo días quebrandome la cabeza, muchísimas gracias.
ResponderBorrarHola Buenas tardes. No puedo abrir en enlace, Da error.
ResponderBorrarHola, 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