☰

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

tag-icon • tag-icon microsoft-excel date-time
Excel - Отслеживание времени +/- между последовательностью дат

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

... | Расчетное время прибытия | АТА | Разница | Всего | ...
     20.11.18 8:00 19.11.18 12:00 (ETA - ATA) (разница + последняя разница)

Проблема в том, что Excel не обрабатывает отрицательные даты и время. Поэтому, если что-то опережает schudle, оно становится #VALUE и вся серия умирает.

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

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

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

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

решение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столбец, он может выполнить промежуточный итог, чтобы включить положительные и отрицательные числа.

Связанный контент