Fórmulas de tiempo de Excel: Calcular, Formatear y Manipular el Tiempo en Excel

Excel facilita el trabajo con números, pero el manejo del tiempo (en mi opinión) es otra historia. La parte confusa (que trataré más adelante) es que Excel almacena y procesa los valores temporales como días fraccionarios, lo que puede dar lugar a resultados inesperados si no estás familiarizado.

En este tutorial, te guiaré a través de todo, desde la creación y el formateo de valores de tiempo hasta la resolución de problemas comunes como duraciones negativas y desbordamientos de tiempo. Al final, sabrás cómo construir hojas de cálculo más inteligentes y fiables para la planificación de proyectos, nóminas o cualquier otra cosa en la que estés trabajando.

Comprender el sistema de fechas de Excel

Tenemos que empezar con el sistema de fechas de Excel. Excel almacena las fechas como números de serie secuenciales, empezando por el 1 de enero de 1900, que se representa con el valor 1. Cada día subecuente aumenta el número de serie en 1. Por ejemplo:

• 1 de enero de 1900 = 1

• 2 de enero de 1900 = 2

• 31 de diciembre de 2023 = 45205

Los valores de tiempo se almacenan como fracciones de día. Por ejemplo:

• 6:00 AM = 0.25 (6 horas es el 25% de un día de 24 horas)

• 12:00 PM = 0.5

• 18:00 = 0.75

Combinar fechas y horas es tan sencillo como sumar los dos valores. Por ejemplo, la fórmula siguiente combina una fecha y una hora:

=A2 + TIME(10, 30, 0)

Si A2 contiene 45205 (31 de diciembre de 2023), el resultado será 45205.4375, que representa las 10:30 AM del 31 de diciembre de 2023.

Cómo crear valores de tiempo en Excel

Veamos las dos formas más comunes de crear valores de tiempo válidos en Excel: utilizando la función TIME() y convirtiendo texto con TIMEVALUE().

Utiliza la función TIEMPO()

Ya lo he mencionado antes. La función TIME() te permite construir un valor de tiempo a partir de componentes separados: hora, minuto y segundo. Ésta es la sintaxis:

=TIME(hour, minute, second)

Por ejemplo, si quieres representar las 10:30 de la mañana, puedes escribir

=TIME(10, 30, 0)

Esto crea un valor de hora Excel adecuado para las 10:30 AM, que se almacena internamente como 0,4375 (ya que 10 horas y 30 minutos es el 43,75% de un día de 24 horas).

Excel es flexible con los valores que introduces. Si superas los límites normales de tiempo, Excel vuelca el tiempo extra. Por ejemplo:

=TIME(27, 0, 0)

Esto devuelve las 3:00 AM porque 27 horas es lo mismo que un día completo (24 horas) más 3 horas. Del mismo modo:

=TIME(10, 75, 0)

Esto se interpreta como las 11:15 de la mañana, ya que 75 minutos se convierten en 1 hora y 15 minutos.

Es importante comprender estos casos extremos, sobre todo si generas valores temporales dinámicamente mediante fórmulas.

Utiliza la función TIMEVALUE()

Puedes convertir texto en tiempo con TIMEVALUE(). Así que si tu hoja de cálculo incluye la hora escrita como texto, como "14:30" o "14:45", es posible que Excel no la trate como un valor horario automáticamente. Ahí es donde entra TIMEVALUE().

=TIMEVALUE("2:30 PM")

Convierte la cadena de texto en un valor horario de Excel. Ahora puedes utilizarlo en los cálculos como en cualquier otro momento.

Conviene señalar la diferencia entre TIME() y TIMEVALUE():

• TIME() construye una hora a partir de números separados (hora, minuto, segundo)

• TIMEVALUE() convierte una cadena de texto en una hora

Ambos devuelven valores que Excel reconoce como horas válidas, que es lo que necesitas para realizar cálculos y formatear con precisión.

Cómo calcular las diferencias horarias

Una vez que tienes valores de tiempo válidos en Excel, una de las tareas más habituales es calcular cuánto tiempo ha pasado entre dos puntos. Tanto si estás midiendo la duración de un turno como calculando el tiempo que se tarda en completar una tarea, Excel te ofrece múltiples formas de obtener la respuesta.

Exploremos los enfoques más útiles, desde la simple resta hasta trucos más avanzados.

Restar dos veces directamente

La forma más sencilla de calcular una diferencia horaria es restar la hora de inicio de la hora final. 

Por ejemplo, si la celda A1 contiene 10:00 AM y B1 contiene 1:30 PM, la fórmula tendría este aspecto:

=B1 - A1

El resultado será 0,145833. Esto puede parecer extraño al principio, pero recuerda que Excel representa el tiempo como una fracción de día. En este caso, 0,145833 equivale a 3 horas y 30 minutos.

Para que el resultado sea más útil, normalmente querrás convertirlo a un formato legible por humanos.

Convertir las diferencias horarias en horas, minutos o segundos

Puedes multiplicar el resultado de una resta de tiempo para expresar la diferencia en horas, minutos o segundos. He aquí cómo convertir una diferencia horaria en horas:

=(B2 - A2) * 24

He aquí cómo convertir una diferencia horaria en minutos:

=(B2 - A2) * 1440

He aquí cómo convertir una diferencia horaria en segundos:

=(B2 - A2) * 86400

Estas fórmulas funcionan porque en un día hay 24 horas, 1.440 minutos y 86.400 segundos. Si la diferencia horaria es de tres horas y media, obtendrás 3,5 con la fórmula de las horas, 210 con la fórmula de los minutos y 12.600 con la fórmula de los segundos.

Extraer unidades de tiempo específicas

Si sólo quieres sacar una parte de la hora, digamos sólo los minutos o las horas, puedes utilizar HOUR(). HOUR() devuelve la parte de la hora de una hora.

=HOUR(B2 - A2)

MINUTE() devuelve la parte de los minutos.

=MINUTE(B2 - A2)

SECOND() devuelve los segundos.

=SECOND(B2 - A2)

Estas funciones son útiles, pero se reinician después de 23 horas, 59 minutos o 59 segundos. Por tanto, si la duración es superior a un día completo, no reflejarán el tiempo total transcurrido. En esos casos, es mejor multiplicar la diferencia o utilizar formatos personalizados, de los que hablaremos más adelante.

Añadir valores de tiempo superiores a 24 horas

Si necesitas añadir horas que superen las 24 horas, el uso directo de TIME() no funcionará como se espera. En su lugar, puedes realizar operaciones aritméticas utilizando fracciones de día.

Así es como se suman horas sobre 24.

=A2 + (28/24)  ' Adds 28 hours

Así se suman los minutos por encima de 60.

=A2 + (90/1440)  ' Adds 90 minutes

Así se suman los segundos por encima de 60.

=A2 + (3600/86400)  ' Adds 3600 seconds

Excel almacena el tiempo como un decimal, donde 1 día equivale a 1, por lo que dividir por 24, 1440 u 86400 ajusta el valor para representar una parte de un día.

Manejar valores temporales negativos

Por defecto, si restas un tiempo mayor de un tiempo menor (por ejemplo, 10:00 AM - 2:00 PM), Excel devolverá un error o mostrará una cadena de ###.

Para manejar con seguridad valores de tiempo negativos, utiliza la función MOD(). Esta función evita eficazmente los resultados negativos, envolviéndolos en un tiempo válido.

Por ejemplo, para restar 3 horas con seguridad:

=MOD(A2 - TIME(3, 0, 0), 1)

Esta fórmula garantiza que el resultado sea siempre un valor de tiempo positivo, evitando errores no deseados o problemas de visualización.

Cómo formatear correctamente los resultados temporales

Los cálculos de tiempo en Excel suelen producir valores decimales confusos o resultados inesperados, sobre todo cuando se trata de duraciones superiores a 24 horas o valores de tiempo negativos. Un formato adecuado puede transformar los resultados brutos en formatos legibles y fáciles de interpretar y presentar.

Utilizar el formato básico de la hora

Si quieres mostrar una diferencia horaria como "3:30" en lugar de un decimal, utiliza la función TEXT().

=TEXT(B2 - A2, "h:mm")

Formatea el resultado en horas y minutos. Ten en cuenta que el resultado es un texto que tiene buen aspecto, pero que no podrás utilizar en cálculos matemáticos posteriores a menos que lo conviertas de nuevo.

Aplicar formatos de hora personalizados

Los formatos de hora incorporados de Excel manejan eficazmente los valores de hora estándar, pero los formatos personalizados proporcionan mayor flexibilidad para mostrar duraciones, horas extendidas o componentes de tiempo específicos.

Para aplicar un formato de hora personalizado, sigue estos pasos:

1. Selecciona la(s) celda(s) que contiene(n) los valores de tiempo.
2. Haz clic con el botón derecho y elige Formatear celdas.
3. En el Número selecciona Personalizado.
4. Introduce el código de formato deseado.

Aquí tienes algunos formatos de hora personalizados habituales:

• [h]:mm: Muestra el total de horas y minutos, incluso más allá de las 24 horas. Ejemplo: 36:45 (36 horas y 45 minutos)

• d "days" h:mm:ss: Muestra días, horas, minutos y segundos. Ejemplo: 1 days 12:30:00

• h:mm AM/PM: Muestra la hora en notación AM/PM. Ejemplo: 10:30 PM

• mm:ss: Sólo muestra minutos y segundos. He aquí un ejemplo: 45:15

Los formatos personalizados te permiten presentar los datos de tiempo de forma más intuitiva para casos de uso específicos, como hojas de horas o registros de trabajo.

Mostrar diferencias horarias amigables

Si quieres mostrar una diferencia horaria como una cadena legible por humanos, como "2 días, 4 horas y 15 minutos", puedes combinar varias funciones en una sola fórmula.

He aquí un ejemplo práctico:

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"

En esta fórmula, la función INT() extrae el número de días completos, mientras que HOUR() y MINUTE() extraen las horas y minutos restantes.

Para evitar que aparezcan valores cero en la salida, puedes anidar estos cálculos dentro de las sentencias IF(). Por ejemplo:

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"

Esta fórmula sólo muestra las unidades relevantes, lo que hace que el resultado sea más limpio y legible.

Funciones temporales clave de Excel que debes conocer

Como has visto hasta ahora, Excel incluye un potente conjunto de funciones de tiempo. Para tener una contabilidad más completa, hhe aquí un rápido resumen de las funciones de tiempo más importantes de Excel.

Utilizar la función TIME() para crear valores de tiempo

TIME() construye un valor de hora válido a partir de tres componentes: horas, minutos y segundos. Esta función es ideal cuando necesitas construir un valor temporal mediante programación.

He aquí un buen ejemplo:

=TIME(14, 30, 0)  

Lo anterior devolverá 2:30 PM, que es el resultado de esta sintaxis.

=TIME(hour, minute, second)

Convertir texto en tiempo con TIMEVALUE()

Si tienes una hora escrita como una cadena de texto (por ejemplo, "2:45 PM"), utiliza TIMEVALUE() para convertirla en un valor de hora propio de Excel que se pueda utilizar en los cálculos.

=TIMEVALUE("2:45 PM")

Por ejemplo, esto devolverá 0.614583, que es una fracción de día:

=TIMEVALUE("14:45")

Obtener la fecha y hora actuales con NOW()

NOW() devuelve la fecha y hora actuales como un único valor. Se actualiza automáticamente cada vez que se recalcula la hoja de cálculo.

=NOW()

He aquí un buen ejemplo. Si la hora actual son las 15:15, NOW() podría devolver 44710.63542, donde 44710 es la fecha y .63542 representa la hora.

Recuperar la fecha actual con TODAY()

Si sólo necesitas la fecha actual sin la hora, utiliza TODAY(). Esta función es especialmente útil para calcular plazos o datos de antigüedad.

=TODAY()

Extraer componentes temporales con HOUR(), MINUTE() y SECOND()

Estas funciones te permiten aislar partes concretas de un valor temporal, lo que las hace útiles para informes y lógica condicional.

HOUR() devuelve la parte de la hora de un valor horario (0-23).

=HOUR(A2)

MINUTE() devuelve la parte de los minutos (0-59).

=MINUTE(A2)

SECOND() devuelve la parte de los segundos (0-59).

=SECOND(A2)

Formatear valores de tiempo con TEXT()

TEXT() convierte un valor de tiempo a un formato específico, por lo que es ideal para mostrar duraciones o crear cadenas de tiempo personalizadas. Sintaxis:

=TEXT(A2, "h:mm:ss AM/PM")

Tratar valores temporales negativos con TEXT()

Al restar valores de tiempo, MOD() se asegura de que el resultado sea siempre positivo, evitando errores causados por valores de tiempo negativos.

=MOD(A2 - B2, 1)

Crear valores de fecha con DATE()

DATE() te permite crear una fecha a partir de componentes separados: año, mes y día. Es útil para combinar fechas y horas en un único valor.

=DATE(year, month, day)

He aquí un buen ejemplo:

=DATE(2023, 12, 31) + TIME(14, 30, 0)

Que vuelve el 31/12/2023 a las 14:30 h.

Crear días laborables con NETWORKDAYS()

NETWORKDAYS() calcula el número de días laborables entre dos fechas, excluyendo los fines de semana y los días festivos especificados opcionalmente. Esta función es ideal para calcular duraciones de proyectos o periodos de nómina.

=NETWORKDAYS(start_date, end_date, [holidays])

Por ejemplo:

=NETWORKDAYS(A2, B2, C2:C5)

Si A2 contiene 12/1/2023 y B2 contiene 12/31/2023, el resultado será el número de días laborables de diciembre, excluyendo los días festivos que figuran en el rango C2:C5.

Crear días laborables y fines de semana con WORKDAY.INTL()

WORKDAY.INTL() es una versión avanzada de WORKDAY() que te permite personalizar qué días se consideran fines de semana.

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Por ejemplo:

=WORKDAY.INTL(A2, 10, "0110000")

Aquí, 0110000 especifica que el martes (0) y el miércoles (1) son fines de semana. La fórmula calcula la fecha 10 días laborables después de A2, excluyendo martes y miércoles.

Conclusión

Manejar el tiempo en Excel puede parecer complicado, pero con las funciones adecuadas, resulta mucho más manejable.

Si quieres reforzar aún más tus conocimientos de Excel, consulta nuestra Introducción a Excel para adquirir conocimientos básicos o el curso Análisis de datos en Excel para practicar el trabajo con datos del mundo real.

Para aplicaciones más avanzadas, el programa de habilidades Fundamentos de Excel ofrece proyectos y ejercicios guiados para ayudarte a dominar los cálculos de tiempo y mucho más.