El valor de tiempo que debe ser 24:00 se muestra como 0:00

Tengo un problema extraño en Excel 2010.

La celda C8 tiene el formato Time y tiene la fórmula =E48/24 . La celda C9 tiene el formato Time y tiene la fórmula =24-C8 . La celda E48 está formateada como General .

Esto funciona perfectamente si puse 15.5 en la celda E48 ; Devuelve 15:30 y 8:30 en C8 y C9 , respectivamente.

Mi edición es cuando E48 es vacío, o contiene explícitamente 0, C8 y C9 leído como cero. Quiero que evalúen a 0:00 y 24:00 , respectivamente.

El propósito es que C8 muestre el tiempo de activación para el dispositivo en un período de 24 horas y C9 para mostrar el tiempo de inactividad durante ese mismo período.

Resultado Esperado:

  C | D | E || C | D | E 13:30 | | || 0:00 | | 8:30 | | || 24:00 | | | | 15.5 || | | (empty) 

¿Cómo puedo hacer esto?

Formato de su C8 y C9 como formato personalizado [h]:mm

A continuación, coloque en C9 esta fórmula =1-C8 lugar de =24-C8

Poner 24 en su C9 romperá su fórmula si hay un valor en E48

Lo que tiene ahora es trabajar por la razón equivocada. Obviamente, E48 es un número de horas, por lo que C8 , que contiene =E48/24 , es un número de días. Si E48 está entre 0 y 24, entonces C8 estará entre 0 y 1, expresando una fracción de un día. Esto corresponde a la forma en que funciona el almacenamiento de fecha / hora de Excel – una fecha / hora se almacena como el número de días (incluyendo una fracción de día, según sea necesario) desde medianoche, 1 de enero de 1900. Es por eso que C8 se muestra correctamente como el Número de horas y minutos correspondientes al número de horas en E48 .

Pero si E48 está entre 0 y 24 y C8 está entre 0 y 1, entonces 24-C8 es un número entre 23.0 y 24.0. Específicamente, si (como en su ejemplo) E48 es 15.5, entonces C8 es 0.645833333 y C9 es 23.35416667. Puesto que C8 expresa un número de días, el 24 se interpreta como un número de días. Si cambia el formato de C8 y C9 a un formato personalizado de d "days and" h:mm , verá que, para E48 = 15.5, C8 será 0 days and 15:30 y C9 será 23 days and 8:30 .

Por lo tanto, dos cosas:

  • Puesto que C8 es un número de días, usted quiere que C9 sea =1-C8 – computando el resto del día, y no mirando un período de 24 días.
  • En un formato de visualización de celdas de Excel para un valor de fecha / hora, las señales h (y hh ), mm , ss y AM/PM miran sólo la parte fraccional del valor, es decir, la fracción de un día, que corresponde a Una hora del día . (Observe la forma en que se muestra el valor 23.35416667 como 23 days and 8:30 , el 8:30 corresponde a la fracción 0.35416667).

    Un E48 blanco, por supuesto, se interpreta como un valor de 0, y hará que C8 evalúe a 0. Con C9 puesto a =1-C8 , se evaluará a 1. Si todavía tiene formato C9 como d "days and" h:mm , verá que se muestra como 1 days and 0:00 , que, por supuesto, es equivalente a 24 horas. Para que se muestre como 24:00 , es necesario utilizar [h] en el formato en lugar de simplemente h ; Es decir, utilice un formato de [h]:mm lugar de simplemente h:mm . El símbolo [h] examina el valor completo de la celda (no sólo la parte fraccionaria) y mostrará horas ≥24.