miércoles, 18 de mayo de 2011

Diferencia de horas en negativo.

Explicaré hoy una posibilidad para poder trabajar con diferencias de horas con resultado negativo, que como todos sabemos por defecto nos devuelve un error. La explicación viene motivada por un correo de una usuaria del blog:

...Intento restar horas pero el problema está en resultados negativos:

	
TIEMPO REQUERIDO
	
TIEMPO ESTIMADO
	
DIFERENCIA
		
0:49:00
		
5:00:00
		
=G2-F2
		
0:14:00
		
3:00:00
		
=G3-F3
		
71:30:00
		
23:00:00
		
¿?
...


Realmente lo que intentamos calcular no tiene mucho sentido para Excel, evidentemente sí para nuestros cálculos. Lo que ocurre es que el formato de horas para Excel es justamente eso:
  • una hora puede ir desde las 00:00 hasta las 24:00, que son las 24 horas del día, y si lo visualizas en formato decimal un número de 0 a 1.

De hecho, fijémonos en el valor que tienes en la celda de 71:30:00 veremos que en la barra de fórmula aparece 02/01/1900 23:30:00, ya que lógicamente las 71:30 debe pertenecer a las 23:30 de dentro de dos días.

Diferencia de horas en negativo.


Vemos como, efectivamente, para diferencias positivas obtenemos un resultado válido, mientras que para las diferencias con resultado negativo Excel noos devuelve un error en modo almohadillas #########.
Cómo vamos a resolver esta cuestión, pues con un condicional SI anidándole la función TEXTO para forzar un cambio en el formato de la hora:

=SI(C5< B5;"-"&TEXTO(B5-C5;"DD-hh:mm:ss");TEXTO(C5-B5;"DD-hh:mm:ss"))


Diferencia de horas en negativo.


Tiene especial importancia definir el formato, con la función TEXTO, del valor con "DD-hh:mm:ss" para poder determinar el número de días 'DD' entre las horas.

Analicemos el resultado, para calcular cuantas horas y minutos han transcurrido entre los tiempos estimados y requeridos, restamos estimado menos requerido, para el primer caso se requirió un tiempo de 49 minutos cuando se había estimado necesario emplear cinco horas, por tanto, la diferencia entre ambos ha sido de 4 horas y 11 minutos menos de tiemplo empleado respecto al estimado.
Interpretemos el tercer caso, tiempo requerido 71 horas y 30 minutos frente al estimado de 23 horas, el resultado de acuerdo a nuestra fórmula nos devuleve el resultado correcto, se han empleado en la tarea dos días y 30 minutos más.

61 comentarios:

  1. Hay una forma de que aparezcan las horas negativas:
    Desde herramientas-->opciones-->calcular:
    marcar "sistema de fechas 1904"

    ResponderEliminar
    Respuestas
    1. Ojo Lluís con esa opción (fechas 1904),
      ya que desconfigura las fechas del libro de trabajo, añadiéndoles 4 años y un día a las existentes...
      generando problemas al copiar y pegar en otras hojas de cálculo!!!
      (ya que se pierden esas 4 años).

      Slds

      Eliminar
  2. Excelente!
    Gracias por el aporte...

    ResponderEliminar
  3. No he podido hacerlo, al poner =SI(J50<I50;"-"&TEXTO(I50-J50;"DD-hh:mm:ss");TEXTO(J50-I50;"DD-hh:mm:ss")) solo me sale para seleccionar el primer J50 Y I50 y cuando quiero seleccionar las demas, no se puede porque me aparecen que ya estan seleccionadas.

    ResponderEliminar
    Respuestas
    1. Hola que tal estás?
      un placer saludarte igualmente para mi.
      Discúlpame, pero no entiendo qué no te deja seleccionar... la fórmula indicada es eso, una fórmula.. o te da un error o te devuelve un valor formateado.
      Sigue los pasos indicados en la entrada, y si no obtienes lo mismo envíame el fichero a
      excelforo@gmail.com
      Slds

      Eliminar
    2. Hola Anónimo, tal vez solamente escribes la fórmula con las celdas correctas y ya... el resultado te da los días, horas y munitos negativos. Yo lo hice de esa manera y si funcionó.

      Por cierto, muchas gracias a Ismael Romero por la ayuda...
      Suerte!!!!

      Eliminar
    3. Gracias Marcos por el detalle!
      Un cordial saludo

      Eliminar
  4. Consulta, tengo forma de que las horas negativas usando el formato 1904 se pongan automaticamente en rojo?

    ResponderEliminar
    Respuestas
    1. Hola Raulo, que tal estás?
      me alegro saludarte.
      Entiendo que con que configures el formato personalizado de la celda será suficiente,
      Lee esta entrada al respecto
      http://excelforo.blogspot.com.es/2013/04/formato-personalizado-avanzado-en-excel.html
      Un cordial saludo

      Eliminar
  5. Estimado Ismael,
    quisiera saber si existe alguna formula para sumar una columna de horas que incluyan horas en negativo ya que cuando selecciono el rango de donde a donde el resultado me sale 00:00:00....e repente habria que personalizarlo..

    a la espera de tu respuesta.

    saludos.

    JANET FARFAN :)

    ResponderEliminar
    Respuestas
    1. Hola Janet,
      si estás sumando celdas de un rango al que has aplicado las fórmulas explicadas en este post, debes tener en cuenta que los valores de horas negativas son TEXTOS realmente, por lo que no son operables.

      Asi que es difícil dar una respuesta o solución al problema que planteas, quizá habria que analizar esas horas negativas de dónde salen, para intentar calcular sobre el origen y no sobre el resltado en formato texto.

      Slds

      Eliminar
  6. Hola Ismael,
    si, lo que pasa es que con la formula explicada en post me ayudo a definir cuantas horas deben o tiene en exceso pero al sacar el resultado mensual me bota ese resultado...habra otra formula que solucione mi problema y pueda ser operable...???

    saludos. = (

    ResponderEliminar
    Respuestas
    1. Hola.. quizá sumando todas las horas finales y restándole las iniciales...
      depende de qué representen esas diferencias.
      Un lector al inicio de los comentarios proponía trabajar con el sistema de fechas 1904... quizá de esa manera puedas trabajar con horas negativas.

      Lo siento, pero no se me ocurre otra forma de trabajr con tiempos negativos...

      Slds

      Eliminar
  7. Hola Ismael,
    A ver si me puedes ayudar, estoy intentando hacer una hoja de calculo para el control de horas de trabajo, la cuestión es que las horas diarias a realizar son 7:00, pero hay veces que se puede hacer algo menos porque son recuperables, la consulta es que si yo por ejemplo hoy he hecho 6:47 y tengo que hacer las 7:00, como hago para que en otra celda me salga -0:13.
    Gracias. Un saludo

    ResponderEliminar
    Respuestas
    1. Hola, sólo tienes que aplicar lo explicado en esta misma entrada...
      Sobre tu celda, con 6:47, y otra con las horas a realizar 7:00, por ejemplo en
      B2 y C2 respectivamente
      =SI(C2< B2;TEXTO(B2-C2;"hh:mm");TEXTO(C2-B2;"-hh:mm"))

      Saludos cordiales

      Eliminar
    2. Gracias Ismael, me ha servido de ayuda

      Eliminar
  8. hola muy buenas tardes
    hay una formula para que un una nueva celda me del el resultado entre 00:44:00- 00:43:00 = 00:01:00 o 00:44:00- 00:45:00 = +00:01:00

    ResponderEliminar
    Respuestas
    1. Hola!
      sólo modifica la fórmula dada en el post:
      =SI(D5< C5;TEXTO(C5-D5;"hh:mm:ss");"+"&TEXTO(D5-C5;"hh:mm:ss"))

      Saludos cordiales!

      Eliminar
    2. ME MANDA ERROR NO IMPORTA QUE SEA EXCEL SEA 2007?

      Eliminar
    3. TIENES UN CORREO DONDE PUEDA MANDARTE LA FOTO DEL ERROR ?

      Eliminar
    4. Hola,
      no importa la versión de Excel...
      la fórmula está probada, asegúrate la copias tal cual, adaptando sólo las celdas en cuestión C5 y D5 a tu caso...

      Un saludo

      Eliminar
    5. SI BROO CREEME LA ESTOY COPIANDO TAL CUAL ES Y ME MANDA ERROR HE TRATADO TAMBIEN DE PONER LOS RESULTADOS EN LAS CELDAS CORRESPONDIENTES PARA APLICARLA Y ME SIGUE MANDANDO EL ERROR SALUDOS

      Eliminar
    6. Envíame el fichero a
      excelforo@gmail.com

      Y por favor, evita escribir en mayúsculas.. es como si me gritaras

      Eliminar
    7. GRACIAS MI CHINGON YA QUEDO LA FORMULA, TENIAS RAZON EN LUGAR DE (PUNTO Y COMA) ERA LA PURA COMA, GRACIAS AHORA TENGO UNA DUDA PARA DARLE EL COLOR AL RESULTADO DENTRO DE ESTA MISMA FORMULA? DISCULPA UNA VEZ MAS

      Eliminar
    8. Hola, de nuevo...
      me alegro haber dado con la solución.
      para dar color a esa celda donde esté la fórmula deberás aplicar un Formato Condicional, indicando cuál es la/s condiciones o reglas que dan qué color...

      Repito, por favor, no escribas en mayúsculas!

      Slds

      Eliminar
  9. Hola, cómo calculo la duración de un vuelo que sale a las 05:20 de Santiago de Chile y llega a Lima Perú a las 07:05 considerando que hay una diferencia horaria de 2 horas y la duración del vuelo debe dar como resultado 3:45

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      suponiendo en C3 y C4 las horas de Santiago y Lima, entonces en C5:
      =C3-C4+NSHORA(2;0;0)

      sería una forma sencilla
      Saludos

      Eliminar
  10. hola ayuda con respeto a este tema.
    =SI(L55>(8/24);L55-N55-(0,333333333333333);TEXTO((8/24)-(L55);"-hh:mm"))
    l: horas expocicion laboral este tiene el dato de las horas laborales esta incluye horas de almuerzo dentro.
    n: horas de almuerzo
    son 8 horas laborales en colombia (8/24) o (0,333333333333333) en decimales.
    el problema que tengo es si la exposiciones laborales son 8 me da problemas, es decir trabajo 6 hora y no almorzó en las horas de trabajo.salio pidió permiso o se fue.
    espero tu ayuda o claracion gracias.
    el problema tambien es que me contar las horas si es un minuto extra o si se debe 1 minuto es restar.

    ResponderEliminar
  11. espero tu ayuda y prender mas o dar apollo a otros con estos caso. mi correo esteban.41a@hotmail.com

    ResponderEliminar
  12. Que tal foro buen día!!!

    Yo tengo el mismo problema pero yo lo necesito en una macro ya que requiero decrementar horas, minutos y segundos hasta llegar a cero automáticamente ejemplo: 1420:20:01 (hhhh:mm:ss) y así hasta cero... Me podrían apoyar? de antemano muchas gracias

    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Oscar,
      en una macro funcionaría siguiendo tu proceso.. pero aplicando el tema de formatos / texto como lo expuesto.
      Obviamente el tratamiento en VBA y en hoja de cálculo es el mismo....

      Saludos

      Eliminar
    2. Te he agradecido en un comentario anterior pero creo que no se publicó... Gracias por tu aportación me encanta el foro y aquí ando por si algo se requiere.

      Saludos

      Eliminar
  13. Buenas tardes,

    Quisieran que me ayudaran para calcular una formula de horas para el trabajo.

    Mi trabajo depende de las horas y minutos que estoy presente en mi puesto. Si entro minutos antes, eso está a mi favor pero si por algún motivo llego tarde de mi hora ya sea de entrada en la mañana o de mi refrigerio, eso me va en contra.

    Necesitaría saber como hacer un cuadro fácil cuatro casillas, dos de entrada y dos de salida es decir:

    Trabajo 9 horas diarias, son 8 de trabajo + 1 de refrigerio. Debo estar 8am en la ofi y salir 5pm. Asimismo, debo salir a almorzar 1:30 y regresar puntual 2:30pm a fin de que mis marcaciones no se vean afectadas y cumpla con las 8hrs de trabajo que debo de tener por día.

    Entonces lo que quiero es ver la forma de crear un excel donde pueda ver por día según mis entradas y salidas cuántos minutos me excedí para poder recuperarlas antes de irme y así cumplir con mis 8 hrs de trabajo.

    Ojalá y me puedan ayudar. Es urgente!

    Gracias.

    Gissel.

    ResponderEliminar
    Respuestas
    1. Este contador te va a funcionar... Solo cambia los parámetros por las horas 9 horas de trabajo que necesitas y si quieres ponerlo en un botón que inicie y ya en otro detienes; cuando detengas, que comience otro contador igual pero para el tiempo de comida... ya solo es necesario usar la imaginación...

      Sub Segundos()
      If Range("D6").Value = 0 Then
      Range("D6").Value = 59
      Call IniciarContador
      If Range("C6").Value = 0 Then
      Range("C6").Value = 59
      If Range("B6").Value = 0 And Range("D6").Value = 59 Then
      Range("C6").Value = 0
      Range("D6").Value = 0
      Call FinalizarContador
      Else
      Range("B6").Value = Range("B6").Value - 1
      'Call IniciarContador
      End If
      Else
      Range("C6").Value = Range("C6").Value - 1
      'Call IniciarContador
      End If
      Else
      Range("D6").Value = Range("D6").Value - 1
      Call IniciarContador
      End If
      End Sub

      Eliminar
    2. Disculpa me faltaron el iniciador y el finalizador...

      Sub IniciarContador()
      Tiempo = Now + TimeValue("00:00:01")
      Application.OnTime Tiempo, "Segundos", , True
      End Sub

      Sub FinalizarContador()
      Ejecutando = False
      Application.OnTime Tiempo, "Segundos", , False
      End Sub

      Eliminar
    3. Hola Gissel,
      a parte de la aportación de Oscar con programación (gracias!), quizá sea más sencillo que construyas una plantilla con cuatro celdas y calcules la diferencia dos a dos, para luego sumar éstas.. así tendrías al final de cada línea las horas totales trabajadas.
      Puedes aplicar la fórmula de este mismo post para controlar el caso de menos horas trabajadas..

      Un saludo

      Eliminar
  14. hola que tal, quisiera ver si me pueden ayudar con una formula de horas de trabajo en el cual se entra alas 7:30 am se sale alas 3:30pm me suma 8:30 horas de trabajo pero solo quiero que me muestre 8 horas ya que la media hora es de comida y no se paga para el sumatorio final, con la formula que puse si alguien falto un dia me pone -00:30 minutos lo cual afecta la sumatoria al final, hay alguna formula con la que se quede en ceros la casilla cuando alguien falte ese dia, muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Sergio,
      puedes aplicar un condicional, del tipo
      =SI(O(HoraEntrada="";HoraSalida="");0;HoraSalida-HoraEntrada-00:30)
      Saludos

      Eliminar
    2. te agradesco mucho la respuesta ismael, olvide mencionar jeje que mi paquete de office esta en ingles, me ayudarias con la formula pero en ingles? deveres otra vez muchas gracias

      Eliminar
    3. Hola Sergio,
      SI es IF
      O es OR
      y probablemente el separador de argumentos sea la coma (,), en lugar de punto y coma (;)
      Slds

      Eliminar
    4. muchisimas gracias ismale te lo agradesco muchisimo, saludos

      Eliminar
  15. Hola Ismael! La fórmula me funciona muy bien pero al pasarlo a tabla dinámica, aunque ponga un campo calculado con esta fórmula, no me coge los negativos. ¿Sabes alguna manera de que aparezcan las horas negativas en dinámicas? Muchas gracias de antemano!!!

    ResponderEliminar
    Respuestas
    1. Hola,
      date cuenta que le estamos dando tratamiento de texto, esto es, no son cantidades operables... así pues, la única forma de trabajar con éstas es como si fueran texto no como campos en el área de valores a resumir por suma o similar
      :(
      Slds

      Eliminar
  16. Gracias por responder tan rápido!!! Saludos!

    ResponderEliminar
  17. Hola Ismael, tengo un gran inconveniente, me han solicitado armar una planilla donde vuelque la informacion de los ingresos y egresos del personal
    tengo varios horarios base,
    por ejemplo
    ENTRADA SALIDA CANT HRS T. Diferencia
    15:45:00 00:40:00 08:55:00 #¡VALOR!
    la parte de (diferencia) es donde me estanque, me da en positivo pero no me da en negativo( o sea, si la persona trabajo menos horas de lo estipulado, no me toma la diferencia en negativo.-
    espero que me puedas ayudar
    gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      las horas negativas no existen, por eso el error.. tal y como se expone en la entrada... prueba aplicando la fórmula del post:
      =SI(C5< B5;"-"&TEXTO(B5-C5;"DD-hh:mm:ss");TEXTO(C5-B5;"DD-hh:mm:ss"))

      Saludos

      Eliminar
  18. hola ismael buenas noches tengo una consulta por ejemplo tengo 2 celdas que restando me dan un tiempo de demora en cargar un producto, pero si ese tiempo es = 00:00 entonces quisiera que me restara las horas de otras 2 celdas para no tener un valor en 00:00 , de antemano gracias por tus respuestas

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrás que aplicar un condicional
      SI(diferencia=0;diferencia2;diferencia)

      Saludos

      Eliminar
  19. Hola, buenas tardes. porfa alguien quien me ayude a obtener esta formula en excel: Será a las Guías que excedan de 50 kilos y se cobrará 1.5 por cada kilo en exceso Guia Peso=65

    ResponderEliminar
  20. lo que estaba realizando es lo siguiente: =SI(B4<=50,0,SI(B4>50,1.5*B4-50)) la respuesta deberia salir la diferencia de 65-50 que seria =15, ello multiplicado por 1.5 que entonces daria como respuesta final 22.5

    porfa alguien que me ayude...

    ResponderEliminar
    Respuestas
    1. Hola,
      es un tema de uso correcto de los paréntesis, prueba con:
      =SI(B4<=50;0;1,5*(B4-50))

      Slds

      Eliminar
    2. Muchas gracias por su apoyo, me ayudo bastante.

      Eliminar
  21. buenas noches, por favor su apoyo con este caso, me piden obtener lo siguiente: Autogenerar código: Las 2 primeras letras del nombre, las 3 ultimas letras del apellido, 3,4 y 5 letra del AFP, 2, 4 Y 6 del cargo. Y el mes de la fecha de ingreso. Todo en mayúscula. Luego copie la fórmula (B1=Código=XXXXX),(C1=Nombre=ELIANA), (D1=Apellidos=ALLCCARIMA CRISOSTOMO), (E1=Sexo=F), (F1=Categoría=A), (G1=AFP=HORIZONTE), (H1=Cargo=CONTADOR), (I1=Fecha de Ingreso=20/04/41983), (J1=Año de Ingreso=1983). porfa su gran ayuda de sus sabios conocimientos, de antemano gracias por su atencion, saludos...

    ResponderEliminar
    Respuestas
    1. Hola,
      se trataría de emplear las funciones IZQUIERDA, DERECHA y EXTRAE de cada una de las celdas que te indican... para finalmente CONCATENAR dichos resultados.

      Saludos

      Eliminar
  22. Tengo este caso en A tengo las 11:00pm y en B las 1:00am del siguiente día, cuan aplico la formula B-A para saber la cantidad de horas que hay entre esos dos rangos me da un resultado negativo, pregunta, Como hago para que ese resultado sea positivo.
    quedo atento a cometarios

    ResponderEliminar
    Respuestas
    1. Al parecer no es muy complicado lo que pides, solo es necesario que B y A tengan formato de dd/mm/aaaa hh:mi:ss al restar puedes utilizar dos funciones una para los días y otra para las horas algo así:
      =ENTERO(B1-A1) --Esto sería para los días
      =RESIDUO(B2-A2;1) --Esto sería para la hora

      Ya solo a la celda que tiene la hora aplica formato de hora y ¡voila!

      Espero te sirva

      Eliminar
    2. Perdón error de dedo en =RESIDUO(B1-A1;1) para que no tengas confusión

      Saludos

      Eliminar
    3. Hola,
      efectivamente, la clave es que las celdas de A y B tengan introducido el valor como (tal como indica Oscar Padrón)
      dd/mm/aaaa hh:mm:ss
      aunque luego le apliques un formato para visualizar solo las hora.
      Al restar una menos otra te aparecerán en positivo... para asegurarte de ver el dato correctamente, en la celda donde calcules la diferencia, aplica el formato personalizado:
      [hh]:mm:ss

      Saludos

      Eliminar