- ¿Cómo maneja Excel la fecha y la hora?
- Fórmulas para calcular la diferencia de tiempo entre dos tiempos
- Calcular el tiempo cuando cambia la fecha (calcular y mostrar tiempos negativos en Excel)
- Sumar / restar tiempo en Excel
- Cómo sumar tiempo en Excel
- Resultados que muestran hash (###) en lugar de fecha / hora (Razones + corrección)
Dado que las fechas y horas se almacenan como números en la parte posterior de Excel, puede usar fácilmente operaciones aritméticas y fórmulas simples en los valores de fecha y hora.
Por ejemplo, puede agregar dos valores de tiempo o valores de fecha diferentes o puede calcular la diferencia de tiempo entre dos fechas / horas determinadas.
En este tutorial, le mostraré un par de formas de realizar cálculos usando el tiempo en Excel (como calcular la diferencia de tiempo, sumar o restar tiempo, mostrar el tiempo en diferentes formatos y hacer una suma de valores de tiempo)
¿Cómo maneja Excel la fecha y la hora?
Como mencioné, las fechas y horas se almacenan como números en una celda en Excel. Un número entero representa un día completo y la parte decimal de un número representaría la parte del día (que se puede convertir en valores de horas, minutos y segundos)
Por ejemplo, el valor 1 representa el 01 de enero de 1900 en Excel, que es el punto de partida desde el cual Excel comienza a considerar las fechas.
Entonces, 2 significaría 02 de enero de 1990, 3 significaría 03 de enero de 1900 y así sucesivamente, y 44197 significaría 01 de enero de 2021.
Nota: Excel para Windows y Excel para Mac siguen fechas de inicio diferentes. 1 en Excel para Windows significaría el 1 de enero de 1900 y 1 en Excel para Mac significaría el 1 de enero de 1904Si hay dígitos después de un punto decimal en estos números, Excel los consideraría como parte del día y se puede convertir en horas, minutos y segundos.
Por ejemplo, 44197.5 significaría 01 de enero de 2021 12:00:00 p.m.
Entonces, si está trabajando con valores de tiempo en Excel, esencialmente estaría trabajando con la parte decimal de un número.
Y Excel le brinda la flexibilidad de convertir esa parte decimal en diferentes formatos, como solo horas, solo minutos, solo segundos o una combinación de horas, minutos y segundos.
Ahora que comprende cómo se almacena el tiempo en Excel, echemos un vistazo a algunos ejemplos de cómo calcular la diferencia de tiempo entre dos fechas u horas diferentes en Excel.
Fórmulas para calcular la diferencia de tiempo entre dos tiempos
En muchos casos, todo lo que desea hacer es averiguar el tiempo total que ha transcurrido entre los valores de dos tiempos (como en el caso de una hoja de tiempo que tiene el tiempo de entrada y el tiempo de salida).
El método que elija dependerá de cómo se menciona la hora en una celda y en qué formato desea obtener el resultado.
Echemos un vistazo a un par de ejemplos.
Resta simple de calcular la diferencia de tiempo en Excel
Dado que el tiempo se almacena como un número en Excel, encuentre la diferencia entre 2 valores de tiempo, puede restar fácilmente la hora de inicio de la hora de finalización.
Hora de finalización - Hora de inicio
El resultado de la resta también sería un valor decimal que representaría el tiempo que ha transcurrido entre los dos valores de tiempo.
A continuación se muestra un ejemplo en el que tengo la hora de inicio y finalización y he calculado la diferencia de tiempo con una simple resta.
Existe la posibilidad de que sus resultados se muestren en formato de hora (en lugar de decimales o en valores de horas / minutos). En nuestro ejemplo anterior, el resultado en la celda C2 muestra 09:30 AM en lugar de 9.5.
Eso está perfectamente bien, ya que Excel intenta copiar el formato de la columna adyacente.
Para convertir esto en un decimal, cambie el formato de las celdas a General (la opción está en la pestaña Inicio en el grupo Números)
Una vez que tenga el resultado, puede formatearlo de diferentes maneras. Por ejemplo, puede mostrar el valor solo en horas o solo en minutos o una combinación de horas, minutos y segundos.
A continuación se muestran los diferentes formatos que puede utilizar:
| Formato | Que hace |
| h | Muestra solo las horas transcurridas entre las dos fechas |
| S.S | Muestra las horas en dos dígitos (como 04 o 12) |
| hh: mm | Muestra las horas y los minutos transcurridos entre las dos fechas, como las 10:20. |
| hh: mm: ss | Muestra horas, minutos y segundos transcurridos entre las dos fechas, como 10:20:36 |
Y si se pregunta dónde y cómo aplicar estos formatos de fecha personalizados, siga los pasos a continuación:
- Seleccione las celdas donde desea aplicar el formato de fecha
- Mantenga presionada la tecla Control y presione la tecla 1 (o Comando + 1 si usa Mac)
- En el cuadro de diálogo Formato de celdas que se abre, haga clic en la pestaña Número (si aún no está seleccionada)
- En el panel izquierdo, haga clic en Personalizado
- Ingrese cualquiera de los códigos de formato deseados en el campo Tipo (en este ejemplo, estoy usando hh: mm: ss)
- Haga clic en Aceptar
Los pasos anteriores cambiarían el formato y le mostrarían el valor según el formato.
Tenga en cuenta que el formato de número personalizado no cambia el valor de la celda. Solo cambia la forma en que se muestra un valor. Por lo tanto, puedo elegir mostrar solo el valor de la hora en una celda, mientras que todavía tendría el valor original.
Consejo profesional: si el número total de horas supera las 24 horas, utilice el siguiente formato de número personalizado en su lugar: [hh]: mm: ssCalcule la diferencia horaria en horas, minutos o segundos
Cuando resta los valores de tiempo, Excel devuelve un número decimal que representa la diferencia de tiempo resultante.
Dado que cada número entero representa un día, la parte decimal del número representaría esa parte del día que se puede convertir fácilmente en horas, minutos o segundos.
Calcular la diferencia de tiempo en horas
Suponga que tiene el conjunto de datos como se muestra a continuación y desea calcular la cantidad de horas entre los valores de dos tiempos
A continuación se muestra la fórmula que le dará la diferencia horaria en horas:
= (B2-A2) * 24
La fórmula anterior le dará el número total de horas transcurridas entre los valores de dos tiempos.
A veces, Excel intenta ser útil y también le dará el resultado en formato de hora (como se muestra a continuación).
Puede convertirlo fácilmente en formato numérico haciendo clic en la pestaña Inicio y, en el grupo Número, seleccionando Número como formato.
Si solo desea devolver el número total de horas transcurridas entre los dos tiempos (sin ninguna parte decimal), utilice la siguiente fórmula:
= INT ((B2-A2) * 24)
Nota: Esta fórmula solo funciona cuando ambos valores de tiempo son del mismo día. Si el día cambia (donde uno de los valores de tiempo es de otra fecha y el segundo de otra fecha), esta fórmula dará resultados incorrectos. Eche un vistazo a la sección donde cubro la fórmula para calcular la diferencia de tiempo cuando la fecha cambia más adelante en este tutorial.
Calcular la diferencia de tiempo en minutos
Para calcular la diferencia de tiempo en minutos, debe multiplicar el valor resultante por el número total de minutos en un día (que es 1440 o 24 * 60).
Suponga que tiene un conjunto de datos como se muestra a continuación y desea calcular el número total de minutos transcurridos entre la fecha de inicio y la de finalización.
A continuación se muestra la fórmula que hará eso:
= (B2-A2) * 24 * 60
Cálculo de la diferencia de tiempo en segundos
Para calcular la diferencia de tiempo en segundos, debe multiplicar el valor resultante por el número total de segundos en un día (que es o 24 * 60 * 60 o 86400).
Suponga que tiene un conjunto de datos como se muestra a continuación y desea calcular el número total de segundos que han transcurrido entre la fecha de inicio y finalización.
A continuación se muestra la fórmula que hará eso:
= (B2-A2) * 24 * 60 * 60
Calcular la diferencia de tiempo con la función TEXTO
Otra forma fácil de obtener rápidamente la diferencia horaria sin preocuparse por cambiar el formato es utilizar la función TEXTO.
La función TEXTO le permite especificar el formato directamente dentro de la fórmula.
= TEXTO (Fecha de finalización - Fecha de inicio, Formato)
El primer argumento es el cálculo que desea hacer y el segundo argumento es el formato en el que desea mostrar el resultado del cálculo.
Suponga que tiene un conjunto de datos como se muestra a continuación y desea calcular la diferencia de tiempo entre los dos tiempos.
Aquí tienes algunas fórmulas que te darán el resultado con diferentes formatos.
Muestra solo el número de horas:
= TEXTO (B2-A2, "hh")
La fórmula anterior solo le dará el resultado que muestra la cantidad de horas transcurridas entre los valores de dos tiempos. Si su resultado es de 9 horas y 30 minutos, todavía le mostrará solo 9.
Muestra la cantidad de minutos totales
= TEXTO (B2-A2, "[mm]")
Muestra el número total de segundos
= TEXTO (B2-A2, "[ss]")
Mostrar horas y minutos
= TEXTO (B2-A2, "[hh]: mm")
Mostrar horas, minutos y segundos
= TEXTO (B2-A2, "hh: mm: ss")
Si se pregunta cuál es la diferencia entre hh y [hh] en el formato (o mm y [mm]), cuando utilice los corchetes, le dará el número total de horas entre las dos fechas, incluso si el El valor de hora es mayor que 24. Por lo tanto, si resta dos valores de fecha en los que la diferencia es más de 24 horas, el uso de [hh] le dará el número total de horas y hh solo le dará las horas transcurridas el día de la fecha de finalización. .
Obtenga la diferencia horaria en una unidad (horas / minutos) e ignore otras
Si desea calcular la diferencia de tiempo entre los dos valores de tiempo en solo el número de horas, minutos o segundos, puede usar la función dedicada HORA, MINUTO o SEGUNDO.
Cada una de estas funciones toma un solo argumento, que es el valor de tiempo, y devuelve la unidad de tiempo especificada.
Suponga que tiene un conjunto de datos como se muestra a continuación y desea calcular el número total de horas, minutos y segundos que han transcurrido entre estos dos tiempos.
A continuación se muestran las fórmulas para hacer esto:
Cálculo de horas transcurridas entre dos horas
= HORA (B2-A2)
Cálculo de minutos a partir del resultado del valor de tiempo (excluidas las horas completadas)
= MINUTO (B2-A2)
Cálculo de segundos a partir del resultado del valor de tiempo (excluyendo las horas y los minutos completados)
= SEGUNDO (B2-A2)
Un par de cosas que necesita saber al trabajar con estas fórmulas de HORAS, MINUTOS y SEGUNDOS:
- La diferencia entre la hora de finalización y la hora de inicio no puede ser negativa (que suele ser el caso cuando cambia la fecha). En tales casos, estas fórmulas devolverían un # ¡NUM! error
- Estas fórmulas solo usan la parte de tiempo del valor de tiempo resultante (e ignoran la parte del día). Entonces, si la diferencia entre la hora de finalización y la hora de inicio es 2 días, 10 horas, 32 minutos y 44 segundos, la fórmula de la HORA dará 10, la fórmula de MINUTOS dará 32 y la segunda fórmula dará 44
Calcular el tiempo transcurrido hasta ahora (desde la hora de inicio)
Si desea calcular el tiempo total que ha transcurrido entre la hora de inicio y la hora actual, puede utilizar la fórmula AHORA en lugar de la hora de finalización.
La función AHORA devuelve la fecha actual y la hora en la celda en la que se usa. Es una de esas funciones que no toma ningún argumento de entrada.
Por lo tanto, si desea calcular el tiempo total que ha transcurrido entre la hora de inicio y la hora actual, puede utilizar la siguiente fórmula:
= AHORA () - Hora de inicio
A continuación se muestra un ejemplo en el que tengo las horas de inicio en la columna A y el tiempo transcurrido hasta ahora en la columna B.
Si la diferencia de tiempo entre la fecha y la hora de inicio y la hora actual es superior a 24 horas, puede formatear el resultado para que muestre el día y la parte de la hora.
Puede hacerlo utilizando la siguiente fórmula de TEXTO:
= TEXTO (AHORA () - A2, "dd hh: ss: mm")
También puede lograr lo mismo cambiando el formato personalizado de la celda (como se cubrió anteriormente en este tutorial) para que muestre el día y la parte de la hora.
En caso de que su hora de inicio solo tenga la parte de la hora, Excel la consideraría como la hora del 1 de enero de 1990.
En este caso, si usa la función AHORA para calcular el tiempo transcurrido hasta ahora, le dará un resultado incorrecto (ya que el valor resultante también tendría el total de días transcurridos desde el 1 de enero de 1990).
En tal caso, puede utilizar la siguiente fórmula:
= AHORA () - INT (AHORA ()) - A2
La fórmula anterior usa la función INT para eliminar la porción del día del valor devuelto por la función ahora, y esto luego se usa para calcular la diferencia horaria.
Tenga en cuenta que NOW es una función volátil que se actualiza cada vez que hay un cambio en la hoja de trabajo, pero no se actualiza en tiempo real
Calcular el tiempo cuando cambia la fecha (calcular y mostrar tiempos negativos en Excel)
Los métodos cubiertos hasta ahora funcionan bien si su hora de finalización es posterior a la hora de inicio.
Pero el problema surge cuando la hora de finalización es menor que la hora de inicio. Esto sucede a menudo cuando está completando hojas de tiempo en las que solo ingresa la hora y no la fecha y la hora completas.
En tales casos, si está trabajando en un turno de una noche y la fecha cambia, existe la posibilidad de que su hora de finalización sea anterior a la hora de inicio.
Por ejemplo, si comienza su trabajo a las 6:00 p. M. De la tarde y completa su trabajo y tiempo fuera a las 9:00 a. M. De la mañana.
Si solo trabaja con valores de tiempo, restar la hora de inicio de la hora de finalización le dará un valor negativo de 9 horas (9 - 18).
Y Excel no puede manejar valores de tiempo negativos (y tampoco los humanos, a menos que pueda viajar en el tiempo)
En tales casos, necesita una forma de averiguar que el día ha cambiado y el cálculo debe realizarse en consecuencia.
Afortunadamente, hay una solución realmente fácil para esto.
Suponga que tiene un conjunto de datos como se muestra a continuación, donde tengo la hora de inicio y la hora de finalización.
Como observará, a veces la hora de inicio es por la tarde y la hora de finalización es por la mañana (lo que indica que se trataba de un turno nocturno y que el día ha cambiado).
Si utilizo la siguiente fórmula para calcular la diferencia de tiempo, me mostrará los signos de almohadilla en las celdas donde el resultado es un valor negativo (resaltado en amarillo en la imagen de abajo).
= B2-A2
Aquí hay una fórmula IF que si el valor de la diferencia de tiempo es negativo o no, y en caso de que sea negativo, devuelve el resultado correcto
= SI ((B2-A2) <0,1- (A2-B2), (B2-A2))
Si bien esto funciona bien en la mayoría de los casos, aún se queda corto en caso de que la hora de inicio y la hora de finalización tengan más de 24 horas de diferencia. Por ejemplo, alguien inicia sesión a las 9:00 a. M. El día 1 y cierra la sesión a las 11:00 a. M. El día 2.
Dado que esto es más de 24 horas, no hay forma de saber si la persona se ha desconectado después de 2 horas o después de 26 horas.
Si bien la mejor manera de abordar esto sería asegurarse de que las entradas incluyan la fecha y la hora, pero si es solo la hora con la que está trabajando, entonces la fórmula anterior debería resolver la mayoría de los problemas ( considerando que es poco probable que alguien trabaje durante más de 24 horas)
Sumar / restar tiempo en Excel
Hasta ahora, hemos visto ejemplos en los que teníamos la hora de inicio y finalización, y necesitábamos encontrar la diferencia horaria.
Excel también le permite sumar o restar fácilmente un valor de tiempo fijo del valor de fecha y hora existente.
Por ejemplo, supongamos que tiene una lista de tareas en cola en las que cada tarea toma el tiempo especificado y desea saber cuándo terminará cada tarea.
En tal caso, puede agregar fácilmente el tiempo que va a tomar cada tarea a la hora de inicio para saber a qué hora se espera que se complete la tarea.
Dado que Excel almacena los valores de fecha y hora como números, debe asegurarse de que la hora que está intentando agregar cumpla con el formato que Excel ya sigue.
Por ejemplo, si agrega 1 a una fecha en Excel, le dará la próxima fecha. Esto se debe a que 1 representa un día completo en Excel (que es igual a 24 horas).
Entonces, si desea agregar 1 hora a un valor de tiempo existente, no puede continuar y simplemente agregarle 1. debe asegurarse de convertir ese valor de hora en la parte decimal que representa una hora. y lo mismo ocurre con la adición de minutos y segundos.
Uso de la función TIME
La función de tiempo en Excel toma el valor de la hora, el valor de los minutos y el valor de los segundos y lo convierte en un número decimal que representa este tiempo.
Por ejemplo, si quiero agregar 4 horas a un tiempo existente, puedo usar la siguiente fórmula:
= Hora de inicio + HORA (4,0,0)
Esto es útil si sabe cuántas horas, minutos y segundos desea agregar a un tiempo existente y simplemente usa la función TIEMPO sin preocuparse por la conversión correcta del tiempo en un valor decimal.
Además, tenga en cuenta que la función TIEMPO solo considerará la parte entera del valor de la hora, los minutos y los segundos que ingrese. Por ejemplo, si utilizo 5.5 horas en la función HORA, solo agregaría cinco horas e ignoraría la parte decimal.
Además, tenga en cuenta que la función TIEMPO solo puede agregar valores que sean inferiores a 24 horas. Si su valor de hora es más de 24, esto le dará un resultado incorrecto.
Y lo mismo ocurre con los minutos y la segunda parte donde la función solo considerará valores menores a 60 minutos y 60 segundos.
Al igual que he añadido tiempo con la función HORA, también puede restar tiempo. Simplemente cambie el signo + a un signo negativo en las fórmulas anteriores
Usar aritmética básica
Cuando la función de hora es fácil y conveniente de usar, viene con algunas restricciones (como se mencionó anteriormente).
Si quieres más control, puedes usar el método aritmético que cubriré aquí.
El concepto es simple: convierta el valor de tiempo en un valor decimal que represente la parte del día, y luego puede agregarlo a cualquier valor de tiempo en Excel.
Por ejemplo, si desea agregar 24 horas a un valor de tiempo existente, puede usar la siguiente fórmula:
= Hora_inicio + 24/24
Esto solo significa que estoy agregando un día al valor de tiempo existente.
Ahora, adoptando el mismo concepto, digamos que desea agregar 30 horas a un valor de tiempo, puede usar la siguiente fórmula:
= Hora_inicio + 30/24
La fórmula anterior hace lo mismo, donde la parte entera de (30/24) representaría el número total de días en el tiempo que desea agregar, y la parte decimal representaría las horas / minutos / segundos
Del mismo modo, si tiene una cantidad específica de minutos que desea agregar a un valor de tiempo, puede usar la siguiente fórmula:
= Start_time + (Minutos para agregar) / 24 * 60
Y si tiene la cantidad de segundos que desea agregar, puede usar la siguiente fórmula:
= Start_time + (Minutos para agregar) / 24 * 60 * 60
Si bien este método no es tan fácil como usar la función de tiempo, lo encuentro mucho mejor porque funciona en todas las situaciones y sigue el mismo concepto. a diferencia de la función de hora, no tiene que preocuparse de si el tiempo que desea agregar es menos de 24 horas o más de 24 horas
Puede seguir el mismo concepto mientras resta tiempo también. Simplemente cambie el + a un signo negativo en las fórmulas de arriba
Cómo sumar tiempo en Excel
A veces, es posible que desee sumar rápidamente todos los valores de tiempo en Excel. Agregar varios valores de tiempo en Excel es bastante sencillo (todo lo que se necesita es una simple fórmula SUM)
Pero hay algunas cosas que necesita saber cuando agrega tiempo en Excel, específicamente el formato de celda que le mostrará el resultado.
Veamos un ejemplo.
A continuación, tengo una lista de tareas junto con el tiempo que tomará cada tarea en la columna B, y quiero agregar rápidamente estos tiempos y saber el tiempo total que tomará para todas estas tareas.
En la celda B9, he usado una fórmula SUMA simple para calcular el tiempo total que tomarán todas estas tareas, y me da el valor como 18:30 (lo que significa que me tomará 18 horas y 20 minutos completar todas estas tareas)
¡Todo bien hasta ahora!
Cómo SUMA durante 24 horas en Excel
Ahora vea lo que sucede cuando cambio a la hora, la Tarea 2 se completará de 1 hora a 10 horas.
El resultado ahora dice 03:20, lo que significa que debería tomar 3 horas y 20 minutos completar todas estas tareas.
Esto es incorrecto (obviamente)
El problema aquí no es que Excel se arruine. El problema aquí es que la celda está formateada de tal manera que solo le mostrará la parte de tiempo del valor resultante.
Y dado que el valor resultante aquí es más de 24 horas, Excel decidió convertir la parte de 24 horas en un día, eliminarla del valor que se muestra al usuario y solo mostrar las horas, minutos y segundos restantes.
Esto, afortunadamente, tiene una solución fácil.
Todo lo que necesita hacer es cambiar el formato de celda para forzar que muestre horas incluso si excede las 24 horas.
A continuación, se muestran algunos formatos que puede utilizar:
| Formato | Resultado Esperado |
| [mmm | 28:30 |
| [m]: ss | 1710:00 |
| d "D" hh: mm | 1 D 04:30 |
| d "D" hh "Mín" ss "Sec" | 1 D 04 Min 00 Seg |
| d "Día" hh "Minuto" ss "Segundos" | 1 día 04 minutos 00 segundos |
Puede cambiar el formato yendo al cuadro de diálogo de formato de celdas y aplicando el formato personalizado, o use la función TEXTO y use cualquiera de los formatos anteriores en la fórmula misma
Puede utilizar la siguiente fórmula de TEXTO para mostrar la hora, incluso cuando sea más de 24 horas:
= TEXTO (SUMA (B2: B7), "[h]: mm: ss")
o la siguiente fórmula si desea convertir las horas que superan las 24 horas en días:
= TEXTO (SUMA (B2: B7), "dd hh: mm: ss")
Resultados que muestran hash (###) en lugar de fecha / hora (Razones + corrección)
En algunos casos, puede encontrar que en lugar de mostrarle el valor de tiempo, Excel muestra los símbolos de almohadilla en la celda.
A continuación, se muestran algunas posibles razones y las formas de solucionarlas:
La columna no es lo suficientemente ancha
Cuando una celda no tiene suficiente espacio para mostrar la fecha completa, puede mostrar los símbolos de almohadilla.
Tiene una solución fácil: cambie el ancho de la columna y hágalo más ancho.
Valor de fecha negativa
Un valor de fecha u hora no puede ser negativo en Excel. en caso de que esté calculando la diferencia horaria y resulte ser negativa, Excel le mostrará los símbolos hash.
La forma de corregir el cambio de fórmula para obtener el resultado correcto. Por ejemplo, si está calculando la diferencia de tiempo entre las dos horas y la fecha cambia, debe ajustar la fórmula para tenerla en cuenta.
En otros casos, puede usar la función ABS para convertir el valor de tiempo negativo en un número positivo para que se muestre correctamente. Alternativamente, también puede una fórmula IF para verificar si el resultado es un valor negativo y devolver algo más significativo.
En este tutorial, cubrí temas sobre el cálculo del tiempo en Excel (donde puede calcular la diferencia de tiempo, sumar o restar tiempo, mostrar el tiempo en diferentes formatos y sumar valores de tiempo)
Espero que este tutorial te haya resultado útil.
