Cálculos de tiempo con MS Excel – Funciones para cálculos de horas

lunes, mayo 15, 2006

En las últimas entradas he estado hablando sobre cálculos de tiempo con MS Excel, una explicación general sobre el método de calcular fechas y horas de Excel y en la entrada más reciente sobre el problema de "tiempos negativos". En el pasado también he escrito sobre sumas de tiempo con Excel.
En esta entrada haré una breve revisión de las distintas funciones de tiempo de Excel:

AHORA() - Devuelve el número de serie de la fecha y hora actuales. Esta función no utiliza ningún argumento. La función AHORA sólo cambia cuando se realiza un cálculo en la hoja de cálculo o cuando se ejecuta una macro que contiene la función. No se actualiza constantemente. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Esto quiere decir que el resultado de AHORA() aplicado hoy en una celda con formato General es "15/05/2006 18:17".
Si queremos obtener la hora del momento, sin la fecha, podemos usar la fórmula "=AHORA()-HOY()". Para ver el resultado de esta fórmula con formato de hora debemos dar este formato a la celda: "hh:mm".

HORA(núm_de_serie) – Da como resultado la hora correspondiente al número de serie (que indica horas, minutos segundos). Por ejemplo, =HORA(0.885) da 21:00, cuando 0.885 corresponde a la hora 21:14:24

HORANUMERO(texto_de_hora) - Devuelve el número decimal de la hora representada por una cadena de texto. Por ejemplo, =HORANUMERO("21:14:24") da como resultado 0.885.

MINUTO(núm_de_serie) - Devuelve los minutos de un valor de hora. Los minutos se expresan como números enteros comprendidos entre 0 y 59. Siguiendo con nuestro ejemplo, =MINUTO(0.885) da como resultado 14.

SEGUNDO(núm_de_serie) - Funciona como MINUTO respecto a los segundos.

NSHORA(hora;minuto;segundo) - Devuelve el número decimal de una hora determinada. El número decimal que NSHORA devuelve es un valor comprendido entre 0 (cero) y 0, 99999999 que representa las horas entre 0:00:00 (00:00:00 a. m.) y 23:59:59 (11:59:59 p.m.). Por ejemplo, =NSHORA(21,14,24) da como resultado 0.885 (si la celda tiene formato General).
Lo interesante con esta función es que acepta valores aparentemente "inválidos" y los corrige. Por ejemplo, si en la fórmula anterior usamos "65" en lugar de "14" para los minutos, y usamos el formato de "h:mm AM/PM", veremos como resultado 10:05 PM, en lugar de 9:14 PM. Excel corrige automáticamente el exceso de minutos y los transforma en horas.



Categorías: Funciones&Formulas_

Technorati Tags:

26 comments:

Anónimo,  22 agosto, 2007 10:07  

Hola Jorge,
quizá tu puedas ayudarme con la siguiente consulta: ¿Cómo puedo hacer para obtener de un campo con formato de hora (HH:MM:SS) los minutos totales que hay en ella? El resultado sería un número sin decimales, que serían los minutos totales.
Muchas gracias y espero que puedas ayudarme.

Jorge L. Dunkelman 22 agosto, 2007 20:02  

Hola,
lo que tienes que hacer es multiplicar el valor de la celda por 1440 (24 horas X 60 minutos).
Por ejemplo, si en la celda A1 figura el valor 10:15:00, el valor que Excel "ve" es 0,427083333333333. Puedes comprobar esto cambiando el formato de la celda de "hh:mm:ss" a "General".
Al multiplicar 0.4270833 por 1440 obtienes 615, que es la cantidad de minutos comprendida en 10 horas y 15 minutos.
Puedes leer mi nota sobre el tema.

Anónimo,  23 agosto, 2007 09:40  

Gracias por todo Jorge. Ya me funciona correctamente.
Un saludo,

Sergi.

Juan 01 septiembre, 2007 18:52  

HoLA Jorge, escribo porque quiero hacer una planilla para una carrera de aventura en donde se registren los tiempos de los participantes. Se puede usar algun cronometro con Excel o con la funcion hora? (poner la hora de partida y restarle la de llegada) y tambien me gustaria saber como puedo hacer para que a medida q llegan los competidores poner el numero de equipo y que automaticamente en otra celda aparezca el tiempo empleado. Gracias

Jorge L. Dunkelman 03 septiembre, 2007 18:24  

Hola Juan

se puede hacer con cierta facilidad, usando macros. Por ejemplo, en la celda A2 queremos poner la hora de partida y en la celda B2 la de llegada. En la celda C2 ponemos =B2-C2 lo que os dará el lapso transcurrido. En el editor de Vba ponemos estas dos macros

Sub largada()
[A2].Value = Now
End Sub

Sub llegada()
[B2].Value = Now
End Sub

Al hacer correr la primer macro, Excel pone la hora del momento en la celda A2. Al correr la segunda la hora en la celda B2. En la C2 obtenemos la diferencia.

Por supuesto se puede refinar el modelo. Tal vez sea tema de una nota (después de las vacaciones)

Anónimo,  29 enero, 2008 00:52  

buenas tardes Jorge espero te encuentres bien, mi pregunta es como puedo sumar horas en excel, lo que pasa es que cuando la suma da 24horas en vez de seguir contando te resta me imagino que es porque excel nada reconoce de 0:00 23:59, te pongo un ejemplo con el cual espero me pueda dar a explicar:

dia 1
h entrada 08:30 h salida 21:30
dia 2
h entrada 08:30 h salida 21:30

el total de las horas deberia ser 26, pero nada mas me suma 2, espero me puedas ayudar, saludos, te mande tambien un correo el cual si fueras tan amable de poder contestarmelo.

saludos.

Jorge L. Dunkelman 29 enero, 2008 23:00  

Tienes que usar el formato personalizado [h]:mm
Puedes fijarte en esta nota

Anónimo,  08 octubre, 2008 23:50  

Hola Jorge. Practicando un poco en excel me surgio la idea de hacer un cronometro regresivo x dias hrs min y seg. Pense que seria tan facil como utilizar la funcion ahora en una celda y poner en otra la fecha final. Me surgieron infinidades de problemas e incognitas. Tienes a mano en el manual detu sabiduria alguna solucion.
ejemplo:
(ahora)A1=6/10/08 13:20:00
(fecha limite)B2=6/11/08 13:20:00
resultado: B1-A1

pepe

Jorge L. Dunkelman 09 octubre, 2008 15:14  

Hola
se puede hacer, pero no me queda claro cual pueda ser el motivo para hacerlo en Excel. Si se trata de algo estático, basta con calcular la diferencia entre la fecha final y la corriente (que provendrá de la función HOY) y cada tanto pulsar F9 para actualizar el valor de HOY().
También se puede programar una macro que recalcule la hoja cada tantos segundos (o cada segundo) creando así la ilusión de un cronometro.
Pero sigo sin entender para quieres hacerlo.

Anónimo,  25 septiembre, 2010 18:04  

Hola Jorge.
Quisiera consultarte como poder medir el lapso de tiempo en horas:minutos entre dos fechas tomando en considerecion que solo debemos contemplar horas 10 Hs. habiles. Ej. Comienzo 22/09/2010 11:00 Fin 23/09/2010 15:00 = 11Hs
Muchas Gracias.

Jorge L. Dunkelman 25 septiembre, 2010 18:49  
Este comentario ha sido eliminado por el autor.
Anónimo,  20 agosto, 2012 18:45  

QUE DEBO HACER PARA QUE NSHORA ME ACTUALICE LA HORA

Jorge L. Dunkelman 21 agosto, 2012 12:20  

Obviamente, cambiar el valor de la referencia. Aunque me parece que tu consulta se refiere a otra cosa, tal vez como poner la hora en forma automática. ¿Podràs explicarte un poco más y sinponer todo en mayúsculas?

Carlos Kaifer Blanco 23 marzo, 2014 12:04  

Buenos días Jorge, mi nombre es Carlos.
Soy nuevo en estos lares y me surge una necesidad que imagino sera muy sencilla de resolver.
Tengo una suma de celdas definida como general o numero con un valor de 27624 que representan segundos y quiero poner en una celda contigua este tiempo expresado en horas:minutos:segundos.
me puedes decir por favor como debo definir las celdas para que me lo muestre automáticamente.
Muchísimas gracias.

Jorge Dunkelman 23 marzo, 2014 15:25  

Carlos, te sugiero que veas esta nota en mi blog donde explico cómo funcionan los cálculos de fechas y horas en Excel.
Esa nota es indispensable para entender la explicación que sigue:
1 - para transformar 27624 en el número que usa Excel para representarlo tienes que hacer, en una celda, esta operación =27264/(24 x 60 x 60) que da 0.3197222
2 - en la celda que contiene 0.3197222 aplicas el formato hh:mm:ss lo que hará que ese número aparezca como 07:40:24

Catalina Montesino Wenzel 06 agosto, 2014 22:17  

Hola Jorge, mi nombre es Catalina.
Quisiera consultarte sobre algo que debe ser muy sencillo.
Tengo muchas celdas con códigos de tiempo, expresados en ms, en formato general (ejemplo: 159890) y quiero poner en una celda contigua este tiempo expresado en 00:00:00:00.
Me puedes decir por favor cómo debo definir las celdas para que me lo muestre automáticamente.
Muchas gracias.

Jorge Dunkelman 07 agosto, 2014 07:48  

Estableciendo el formato deseado en la columna contigua (seleccionar la columna o el rango de celdas, abrir el menú de formato de cledas y aplicar el formato).
No me queda claro que es el formato 00:00:00:00. ¿hh:mm:ss?
Si se trata de mostrar décimas o centésimas de segundo tienes que usar el formato que muestro en esta nota.

Rubén Rielo 06 abril, 2015 18:02  

No se si llegó mi consulta por las dudas la repito.
Estimado Jorge: Cuál sería la forma más sencilla que colocando la hora de comienzo de un partido de fútbol, (y sumando su duración 1:45:00) comparando con AHORA() me arroje pendiente, en juego o finalizado.
Muchas Gracias

Jorge Dunkelman 07 abril, 2015 18:19  

No, no llegó.
Si en la celda A1 ponemos la hora de comienzo y en la celda A2 la duración del partido, esta fórmula te dara el resultado

=SI((AHORA()-HOY())<A1;"pendiente";SI((AHORA()-HOY())<(A1+A2);"en juego";"finalizado"))

Sucede que AHORA() te dá la fecha y la hora; por eso siempre hay que restarle HOY() para obtener la hora completa (con minutos y segundos).

Habría que agregar el tiempo de descuento.

Rubén Rielo 13 abril, 2015 18:18  

Estimado Jorge
¿Hay que dar algún formato específico a alguna de las celdas?
Gracias
Rubén

Jorge Dunkelman 13 abril, 2015 20:39  

Si te refieres a la fórmula =SI((AHORA()-HOY()...., no, ya que el resultado es un texto.
Si te refieres a la celda que contiene la duración de un partido (A1), si, hay que darle formato hh:mm o poner la duración en notación decimal. (fijate en esta nota).

Anónimo,  07 agosto, 2015 19:08  

Si tengo una base que me da datos en horas en 00:00:00 a.m. y 00:00:00 p.m. en formato de 12 horas, como puedo cambiar las horas 00:00:00 p.m. a formato de 24 horas ¿?

Jorge Dunkelman 08 agosto, 2015 18:21  

Cambiando el formato de la/s celda/s de "h:mm:ss AM/PM" a "hh:mm:ss"

César Gnome 14 marzo, 2016 05:52  

Hola Jorge excelente tarde, yo necesito ayuda con un reporte en el que tengo una columna que indica el tiempo de duración de varios eventos en formato "0:04:00" y necesito obtener en otra columna la media de tiempo de duración de todos los eventos generados.

1.- La información la obtengo de la columna W y no todas las celdas tienen información.
2.- Necesito que la media de tiempo se refleje en el mismo formato "0:04:00".

Gracias de antemano por tu valiosa ayuda.

Jorge Dunkelman 14 marzo, 2016 06:36  

Hola César, por favor fijate en las instrucciones en el enlace Ayuda (en la parte superior del blog).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP