lunes, febrero 11, 2008

Redondeo de horas en Excel

Cuando queremos redondear tiempos o fechas en Excel debemos tomar en cuenta que, en lo que tiempo y fechas se refiere, Excel no es WYSIWYG(what you see it's what you get, queriendo decir, lo que ves es lo que es).
Como ya hemos explicado, para Excel la fecha 11 de febrero de 2008 es el número 39489(los días transcurridos desde el 1ro. de enero de 1900). La hora 18:00 es el número 0.75 (que resulta de dividir 18 por 24).
En resumen, existe una diferencia entre como Excel muestra las fechas y las horas en pantalla y como las considera para realizar operaciones.
También cuando queremos redondear fechas y horas debemos tomar en cuenta esta cuestión.
Supongamos, como pedía uno de mis lectores, que queremos redondear la hora 2:20 a la hora más cercana. Si usamos la fórmula =REDONDEAR(A1;0), obtenemos un resultado erróneo




Este resultado se debe a que para Excel la hora 2:20 es el número 0.097222 y por lo tanto, al redondear a un número entero obtenemos 0. Esto podemos verlo si aplicamos el formato General a las celdas



La fórmula correcta para redondear horas es =REDONDEAR(A1*24,0)/24



Es decir: 0.09722 X 24 = 2.3333 ---> REDONDEAR(2.3333;0)= 2 ---> 2/24= 0.8333
Y finalmente, cuando ponemos formato de hora a 0.8333 vemos en la celda 02:00

Si queremos redondear por medias horas, usamos como factor 48 en lugar de 24



Si queremos redondear por múltiplos de 15 minutos (cuarto de hora) usamos 96 (24X4), =REDONDEAR(A1*96,0)/96; para múltiplo de 20 minutos usamos 72, etc.

La regla general es =REDONDEAR(A1*(60/m*24),0)/(60/m*24), donde m es la cantidad de minutos en el múltiplo.

Una variante interesante a esta técnica es la propuesta en Daily Dose of Excel. Ésta consiste en crear un nombre con la constante "=24*60"



De esta manera podemos usar el nombre en nuestra fórmula de esta manera




Technorati Tags:

10 comentarios:

  1. Hola, a ver si me puedes ayudar:
    Mi cliente paga los días 20 de cada mes y a 90 días de la fecha de la factura, si le facturo el 1 de enero me ha de pagar el primer día 20 del mes después de transcurrir 90 días, el 20 de abril.
    Como puedo hacer para que Excel me de la fecha de pago?. Gracias.

    ResponderBorrar
  2. Muchas gracias, he podido resolver el tema con las indicaciones que das.

    ResponderBorrar
  3. Hola.. no tengo mucha idea de excel. Yo necesitaría redondear siempre para arriba si pasan los primeros 10m de una hora, en caso contrario para abajo. A ver.. si son las 03:12 que me redondee a 04:00. Si son las 03:09 que me redondee a 03:00. Existe alguna manera de lograrlo?. Muchas Gracias!

    ResponderBorrar
  4. Por supuesto. Si bien no está tratado explícitamente en esta nota, tienes que seguir las mismas reglas del redondeo de números. Tal vez más adelante publique una nota a partir de tu consulta.

    ResponderBorrar
  5. Excelente ayuda, saludos!.

    ResponderBorrar
  6. Hola, me sirvió muchisimo tu artículo. Pero tengo una duda respecto al trabajo que estoy haciendo, ojala puedas ayudarme. Estoy calculando tiempo extra, si un trabajador labora 31 minutos se le paga como una hora, con tu método si trabaja 1 hora con 3 minutos se va a 2 horas (así lo elegí yo, dividiendo y multiplicando por 12, lo cual es perfecto, el problema viene si sólo fueron precisamente 31 minutos, se va a cero y no a 1 hora.
    Gracias de antemano.

    ResponderBorrar
  7. No se por qué usas 12 en lugar de 24. Si usas la fórmula original, 01:03 se redondea a 01:00.
    Por otro lado 01:31 se redondea a 02:00 (por que es más de la mitad).
    Si quieres fijar arbritariamente que hasta 31 minutos se redondea hacia abajo y de 32 minutos en adelante se redondea hacia arriba, puedes usar una fórmula condicional con REDONDEAR.MENOS y REDONDEAR.MAS. Si la hora a redondear está en la celda A1, podrías usar

    =SI(MINUTO(A1)>31;REDONDEAR.MAS(A1*24;0)/24;REDONDEAR.MENOS(A1*24;0)/24)

    ResponderBorrar
  8. Buenas t puedo hacer una consulta para usar esa formulas en horas, el tema yo calculo mis horas extras con redondeo de 15 minutos.
    Quiero usar el resultado ya con el redondeo hecho asi no agrego otra celda solo para redondedar, nose si me se explicar bien.

    Ej
    Entrada 16:00
    Salida 04:05
    Horas totales 10:05
    Horas extras 01:15 (hora extra a partir de las 08:45 trabajadas y se me paga con redondeo cada 15 minutos)

    Espero q me hayas entendido y muchas gracias por la ayuda, saludos


    ResponderBorrar
    Respuestas
    1. Hola, combinando las fórmulas en una única (lo que llamamos "anidar").

      Borrar

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