Значение времени, которое должно быть 24:00, отображается как 0:00.

Question

У меня странная проблема в Excel 2010.

Ячейка C8 отформатирована как время и имеет формулу =E48/24 .  Ячейка C9 отформатирована как время и имеет формулу =24-C8 .  Ячейка E48 отформатирована как General .

Это прекрасно работает, если я положил 15.5 в ячейку E48 ; он возвращает 15:30 и 8:30 в C8 и C9 , соответственно.

Моя проблема, когда E48 пуст или явно содержит 0, C8 и C9 читаются как ноль.  Я хочу, чтобы они оценили до 0:00 и 24:00 соответственно.

Цель состоит в том, чтобы C8 показывал время работы устройства в течение 24 часов, а C9 - время простоя в течение того же периода.

Ожидаемый результат:

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

Как я могу это сделать?

Answer 1

Отформатируйте ваши C8 и C9 как пользовательский формат [h]:mm

Затем положите на C9 эту формулу =1-C8 вместо =24-C8

Если поставить 24 на ваш C9 вы нарушите формулу, если есть значение на E48

Answer 2

То, что у вас сейчас, работает не по той причине.  Очевидно, что E48 - это количество часов, поэтому C8 , который содержит =E48/24 , - это количество дней.  Если E48 находится между 0 и 24, то C8 будет между 0 и 1, выражая часть дня.  Это соответствует способу хранения даты / времени в Excel - дата / время сохраняются как количество дней (включая часть дня, если необходимо) с полуночи 1 января 1900 года.  Вот почему C8 правильно отображается как количество часов и минут, соответствующее количеству часов в E48 .

Но если E48 находится в диапазоне от 0 до 24, а C8 находится в диапазоне от 0 до 1, то 24-C8 - это число от 23,0 до 24,0.  В частности, если (как в вашем примере) E48 - 15,5, то C8 - 0,645833333, а C9 - 23,35416667.  Поскольку C8 выражает количество дней, 24 интерпретируется как количество дней.  Если вы измените формат C8 и C9 на пользовательский формат d "days and" h:mm , вы увидите, что для E48 = 15,5 C8 будет 0 days and 15:30 а C9 будет 23 days and 8:30

Итак, две вещи:

• Поскольку C8 - это количество дней, вы хотите, чтобы C9 был =1-C8 - вычисляя остаток дня, а не смотря на 24-дневный период. 

• В формате отображения ячейки Excel для значения даты / времени токены h (и hh), mm , ss и AM/PM смотрят только на дробную часть значения, т. Е. На долю дня, которая соответствует время суток.  (Обратите внимание, как значение 23.35416667 отображается как 23 days and 8:30 ; 8:30 соответствует дроби 0.35416667.)

  Пустой E48 , конечно, интерпретируется как значение 0, и будет приводить к тому, что C8 будет иметь значение 0.  С C9 установленным на =1-C8 , он будет равен 1.  Если у вас все еще есть C9 отформатированный как d "days and" h:mm , вы увидите, что он отображается как 1 days and 0:00 , что, конечно, эквивалентно 24 часам.  Чтобы он отображался как 24:00 , вам нужно использовать [h] в формате вместо простого h ; т.е. используйте формат [h]:mm вместо простого h:mm .  Токен [h] просматривает все значение в ячейке (не только дробную часть) и отображает часы ≥24.