Это лучшая стратегия для отображения отрицательного значения времени в Excel?

Question

Я пытаюсь отправить вычисленную ячейку как duedate - now() для количественного определения просроченного и оставшегося времени (отрицательные значения = просроченные, положительные значения = оставшееся время). Рассматривать:

      Col A DueDate    Col B  NOW         Col C remaining time
Row1  3/7/2013  9:00   3/8/2013   12:00   -1:03:00:00
Row1  3/8/2013 15:00   3/8/2013   12:00    0:03:00:00
Row3  3/9/2013  9:00   3/8/2013   12:00    1:03:00:00

Тем не мение,

• Этот лист ссылается на множество других листов, использующих систему дат 1900 года, поэтому я опасаюсь перехода на систему 1904 года из-за непредсказуемости того, как даты будут интерпретироваться между двумя системами дат.
• A1-B1 дает ################## , как мы и ожидаем, когда дата возвращает отрицательную дату.
• Я нашел преобразование текста, TEXT(ABS(A1-B1),-h:mm:ss , но поскольку он просто добавляет отрицательное значение к абсолютному значению, КАЖДОЕ значение даты возвращается как отрицательное. ABS правильно, только не знак.

Используя это в качестве подсказки, я попробовал TEXT((A1-B1),h:mm:ss но получил VALUE!# ; Я предполагаю, что это не любит отрицательный результат математики времени.

Это: IF(B1>A1,(TEXT(ABS(A1-B1),-h:mm:ss,A1-B1) имеет несколько проблем.

• Все, что ожидается сегодня, возвращается как 0:00:00:00 ; кажется, что он игнорирует часы, минуты и секунды.
• Сортировка столбца по времени, кажется, сортирует по ABS поэтому мои значения «0:00:00:00» находятся вверху списка, а самые большие отрицательные числа - внизу. Я ожидаю, что нормальная сортировка будет иметь самый большой отрицательный результат, затем ноль, а затем возрастание до самого высокого значения.

РЕДАКТИРОВАТЬ:

Предложение Мартино приблизило меня, но все, что должно быть на следующий день или два, очень короткое. Я изменил h на [h] чтобы учесть время больше 24 часов.

DueDate         NOW()           
3/1/2013 17:00  3/8/2013 12:16  -163:16:11
3/7/2013 17:00  3/8/2013 12:16  -19:16:11
3/8/2013 12:16  3/8/2013 12:16  992196:16:11    <-- ???
3/15/2013 17:00 3/8/2013 12:16  992369:00:00   <--  ???
3/31/2013 17:00 3/8/2013 12:16  992753:00:00   <--  ???
5/8/1944 12:00  3/8/2013 12:16  -603384:16:11
6/14/2238 7:00  3/8/2013 12:16  2966839:00:00
3/8/2013 9:00   3/8/2013 12:16  -3:16:11
3/8/2013 17:00  3/8/2013 12:16  992201:00:00   <--  ???

Однако, с h last как h , те же строки возвращают квадратные значения:

DueDate         NOW()       3/1/2013 17:00  3/8/2013 12:23  -19:23:45
3/7/2013 17:00  3/8/2013 12:23  -19:23:45
3/8/2013 12:23  3/8/2013 12:23  12:23:45     <-- ???
3/15/2013 17:00 3/8/2013 12:23  17:00:00     <-- ???
3/31/2013 17:00 3/8/2013 12:23  17:00:00     <-- ???
5/8/1944 12:00  3/8/2013 12:23  -0:23:45
6/14/2238 7:00  3/8/2013 12:23  7:00:00
3/8/2013 9:00   3/8/2013 12:23  -3:23:45
3/8/2013 17:00  3/8/2013 12:23  17:00:00     <-- ???

Есть ли более элегантный способ форматирования столбца C так, как я хотел бы его видеть?

Answer 1

Я думаю, что эта формула будет работать:

=IF(B1>A1, TEXT(B1-A1,"-h:mm:ss"), TEXT(A1-B1,"h:mm"))

Но только на разницу во времени менее 24 часов. Для более длительных периодов вы можете использовать:

=IF(B1>A1, "-",) & TEXT(INT(ABS(B1-A1)), "#,##0") & "d " & TEXT(MOD(ABS(B1-A1), 1), "hh:mm")

для такого результата (я правильно оправдал оставшееся время):

DueDate             Now                 Remaining Time
03/01/2013 17:00    03/08/2013 12:16         -6d 19:16
03/07/2013 17:00    03/08/2013 12:16         -0d 19:16
03/08/2013 12:16    03/08/2013 12:16          0d 00:00
03/15/2013 17:00    03/08/2013 12:16          7d 04:44
03/31/2013 17:00    03/08/2013 12:16         23d 04:44
05/08/1944 12:00    03/08/2013 12:16    -25,141d 00:16
06/14/2238 07:00    03/08/2013 12:16     82,276d 18:44
03/08/2013 09:00    03/08/2013 12:16         -0d 03:16
03/08/2013 17:00    03/08/2013 12:16          0d 04:44

Вероятно, он может быть расширен и на годы, если это необходимо. Делать это с полной точностью было бы немного сложнее, так как количество дней в году варьируется ¹ - так что это останется в качестве упражнения для читателя. ;-)

[1] Согласно Википедии, средний календарный год длится 365,2425 дней.

Answer 2

Если вы хотите, чтобы в Excel отображалось отрицательное время, даже когда вы выполняете с ним вычисления, вы можете изменить способ создания времени. Вы можете сделать это, перейдя в инструменты, Параметры, вкладка «Расчеты» и выбрав «Система дат 1904 года». Это теперь будет отображать отрицательное время как -xx:xx:xx