Excel - время отслеживания +/- между последовательностями дат

Question

Я делаю запланированные события в Excel 2016, которые отслеживают разницу между сериями отметок даты / времени и сохраняют промежуточные итоги.

... |    ETA       |     ATA       |     Diff     |     Total          |  ...
     11/20/18 8:00   11/19/18 12:00  (ETA - ATA )   (diff + last diff)

Проблема в том, что Excel не обрабатывает отрицательные даты. Так что, если что-то впереди щудле, оно становится # ЗНАЧЕНИЕМ, и вся серия умирает.

Я пытался использовать формат даты 1904 года, но это всегда не менее 1 дня, плюс нулевая дата отсутствует, и даже равные даты будут равняться +1 дням.

Затем используется TEXT() для преобразования в текст, но на самом деле невозможно использовать его для следующего события, так как оно не является датой, а отрицательные даты по-прежнему будут проблемой.

Единственный другой вариант - разбить дни / часы / минуты на отдельные скрытые столбцы и преобразовать / добавить к каждой дате для каждой даты-времени или чего-то глупого.

Есть ли какой-то другой способ хранения промежуточных итогов положительных или отрицательных дат, которые я пропускаю?

Answer 1

Поскольку Excel представляет значения даты / времени как Double , это, конечно, простая математика. Я подозреваю, что ваша проблема связана с форматированием вашей колонки Diff . В этом примере:

|         A        |         B        |      C       |    D     | ... |   AA   |
|        ETA       |        ATA       |     Diff     |  Total   | ... | Helper |
|------------------|:----------------:|:------------:|:--------:| ... |:------:|
| 11/20/2018 8:00  | 11/19/2018 12:00 |   00:20:00   |          | ... | =A2-B2 |
| 11/19/2018 12:00 | 11/20/2018 8:00  |  -00:20:00   | =AA2+AA3 | ... | =A3-B3 |

Столбец Diff форматируется не как Дата, а как Общий. Формула в столбце Diff

=TEXT((A2-B2)*24,"#0.00")&" hours"

Но, конечно, вы можете приспособить это к тому, что вам нужно.

Расширяя ответ выше, используйте столбец "помощник" (даже если он направо)

Поэтому вместо этого в столбце Diff используйте формулу, например

=IF(A2>=B2,TEXT(A2-B2,"dd:hh:mm"),"-"&TEXT(B2-A2,"dd:hh:mm"))

это даст вам приятно отображаемое значение. Но затем в крайнем правом столбце (может быть, AA?), Используйте простую формулу =A2-B2 . Затем, вернувшись в ваш столбец Total , он может выполнить промежуточный итог, включив в него положительные и отрицательные числа.