Ingresar tiempos negativos (Microsoft Excel)

Guido necesita ingresar tiempos negativos en las celdas. Si ingresa un tiempo positivo (2:00), Excel lo reconoce muy bien. Si intenta escribir -2: 00, Excel le dice que hay un error en su fórmula. Guido se pregunta cuál es la forma más fácil de ingresar un valor de tiempo negativo en una celda y hacer que Excel lo reconozca como un valor de tiempo.

Antes de proporcionar un par de posibles respuestas a la pregunta, es bueno comprender la forma en que Excel ve las fechas y horas. Internamente, las fechas y horas se almacenan como números de serie, que no son más que números reales. Supongamos que ingresa una fecha en la celda B4, como «17 de marzo de 2020» (sin las comillas. Excel entiende que está ingresando una fecha y la convierte en un número de serie. Sin embargo, no puede saber que es un número de serie , porque Excel le muestra de manera útil la fecha representada por el número de serie. (Consulte la Figura 1.)

Figura 1. Ingresando una fecha en Excel.

Tenga en cuenta que la fecha se muestra en la celda como 17-Mar-20 y en la barra de fórmulas como 3/17/2020. (Exactamente cómo Excel muestra la fecha depende de cómo la ingresó y cuál es su configuración regional dentro Windows.) La fecha que se muestra en la celda (17-Mar-20) es el resultado de cómo se formatea la celda. Cuando ingresó la fecha en la celda y debido a que la celda anteriormente tenía un formato «General», Excel modificó el formato. de la celda para mostrar la fecha como se sintió mejor.

Debido a la fecha que se muestra en la barra de fórmulas (17/3/2020), podría pensar que esto es t La forma en que Excel almacena las fechas internamente. No tan; puede ver esto ingresando una fórmula en una celda adyacente (= B4) y luego formateando esa celda como «General». (Ver figura 2)

Figura 2. Ver cómo Excel almacena una fecha.

La barra de fórmulas muestra la fórmula (= B4), como debería, pero la celda muestra el valor 43907. Si formateó la fecha en la celda B4 como general, también mostraría el mismo valor. Este es el número de serie; es la forma en que Excel almacena las fechas internamente.

Pero, ¿qué significa el número de serie? Es un «recuento» del número de días desde una fecha base. Normalmente, esta fecha base es el 1 de enero de 1900, siendo ese día el día 1. El 2 de enero de 1900 sería el día 2, y así sucesivamente. Por lo tanto, 43907 es el día 43,907 que comienza con el 1 de enero de 1900.

Sin embargo, es posible que la fecha base de un libro sea diferente. Excel, para fines históricos, comprende dos posibles fechas base. El otro se conoce como el sistema de fechas de 1904. Comienza a contar con el 1 de enero de 1904. A diferencia del sistema de fechas de 1900, el sistema de fechas de 1904 comienza a contar con 0. Por lo tanto, el 1 de enero de 1904 tiene un número de serie de 0, el 2 de enero de 1904 tiene un número de serie de 1, etc.

Puede ver qué sistema de fechas se utiliza en un libro siguiendo estos pasos:

(Vea la figura 3.)

Si la casilla de verificación está seleccionada en el paso 4, su libro de trabajo usa el sistema de fechas 1904. Si no está seleccionado, entonces está utilizando el sistema de fechas 1900.

¿Qué tiene esto que ver con la pregunta original de Guido sobre entrar en tiempos negativos? Mucho, en realidad. Verá, un valor de tiempo, para Excel, no es más que una parte de un día. Tenga en cuenta que los ejemplos proporcionados hasta ahora involucran fechas que se almacenan como números de serie completos, en este caso 43907.

Si ingresa una fecha con una hora en una celda, como «17/03/20 6:00 pm»

(de nuevo sin las comillas, entonces se almacena internamente como la parte del número de serie a la derecha del punto decimal. (Ver Figura 4)

Figura 4 . Ver cómo Excel almacena una fecha y una hora.

La parte fraccionaria almacenada con el número de serie (.75) significa que la hora que ingresó (6:00 pm) es el 75% del día . Los tiempos, la parte decimal, pueden oscilar entre .0 (12:00:00 am) y .999988426 (11:59:59 pm).

Debido a este esquema de almacenamiento de fechas y veces internamente como números de serie, no existe tal cosa en el mundo de Excel como un «tiempo negativo».

No hay forma de analizar y almacenar ese valor, por lo que Excel muestra un mensaje de error cuando lo intenta. puede hacer «cálculos de fecha» y restar una hora de un valor de hora ya almacenado en una celda, pero todo lo que está haciendo es ajustar el número de serie de fecha y hora. Por ejemplo, si tiene el número de serie 43907.75 almacenado en una celda y s Si extrae un «tiempo» que daría un resultado negativo, todo lo que ha hecho es pasar al día anterior. (Vea la figura 5.)

Figura 5. Restar de una fecha y hora.

Tenga en cuenta que en la celda B6 se ingresó una hora, sin una fecha que la acompañe (10:00 pm). Esto fue analizado por Excel como un número de serie con una «fecha cero» como se muestra en la celda C6. Si intenta restar una hora de ese número de serie que arroja un resultado negativo, Excel no puede mostrar el resultado como una hora. (Ver figura 6)

Figura 6. No se puede restar demasiado con tiempos.

Si pasa el puntero del mouse sobre las marcas de almohadilla que se muestran en la celda B9, verá el mensaje de error: «Las fechas y horas que son negativas o demasiado grandes se muestran como «. (La redacción exacta del mensaje de error puede variar de una versión a otra de Excel).

Obtendrá el mismo resultado si también intenta llegar a momentos negativos de otras maneras. Por ejemplo, si ingresa un valor de tiempo en una celda y luego, en una celda diferente, usa una fórmula para multiplicar el tiempo por -1.

Cuando intenta formatear el resultado como una hora, vuelve a obtener las marcas de almohadilla. Excel simplemente no puede mostrar tiempos negativos porque no pueden existir en lo que respecta al programa.

Entonces, ¿qué va a hacer Guido? Si realmente necesita trabajar con tiempos negativos, es muy poco lo que puede hacer por las razones ya explicadas.

(Más sobre esto en un momento, sin embargo.) Si simplemente quiere restar tiempos, esa es una historia diferente. El hecho de que Guido esté intentando ingresar algo como -02: 00 indica que está trabajando con duraciones de eventos, no con «horas de reloj» reales. (Recuerde que Excel trabaja con tiempos de reloj para realizar su análisis, y -02: 00 no es un tiempo de reloj; no puede decirle a alguien que lo encontrará a las -02: 00.)

Para manejar la duración de los eventos, simplemente puede dejar que Excel haga los cálculos por usted. Por ejemplo, se podría colocar una hora de inicio en la celda B4 y una cantidad de horas que Guido quiere restar de ella (2) se podría colocar en la celda C4. En la celda D4, podría usar una fórmula para calcular el resultado:

Dividir el número de horas entre 24 da el valor decimal correcto que Excel utiliza internamente en sus números de serie. Por supuesto, si el resultado de la fórmula da como resultado un número de serie negativo, obtendrá las marcas de almohadilla cuando intente formatear el resultado como una hora. La única solución a esto es asegurarse de que lo que se almacena en la celda B4 incluya una fecha. Puede formatear la celda B4 para mostrar solo la hora, pero la inclusión de la fecha evitará que el resultado sea un error. Puede obtener más información sobre cómo trabajar con tiempos transcurridos negativos echando un vistazo a este ExcelTip:

Después de que todo esté dicho y hecho, hay una forma en que puede trabajar con tiempos negativos reales: cambie al sistema de fechas de 1904, como se discutió anteriormente.

Por ejemplo, observe los resultados de las restas cuando utilice el sistema de fechas de 1904. (Consulte la figura 7.)

Figura 7. El sistema de fechas de 1904 manejará tiempos negativos.

Este enfoque funcionará muy bien para libros de trabajo individuales en su propio sistema, pero puede no ser tan bueno cuando otros usen su libro de trabajo. Depender de libros de trabajo que utilizan sistemas de fechas diferentes (algunos 1900 y otros 1904)

puede ser una causa de problemas potenciales a medida que usted (u otros) copian información de un libro de trabajo a otro. Este problema se discutió en un ExcelTip diferente:

Al usar el sistema de fechas 1904, todavía tendrá contratiempos al ingresar tiempos negativos, pero los resultados de las fórmulas pueden mostrar tiempos negativos sin ningún problema.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13356) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.