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

435
E.F.

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

... | ЭТА | ATA | Diff | Всего | ... 20.11.08 8:00 19.11.18 12:00 (ETA - ATA) (diff + последний diff) 

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

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

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

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

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

0

1 ответ на вопрос

0
PeterT

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

Я форматировал разницу и итоги в формате dd: hh: mm, чтобы показать время в формате date-y. Уловка с введением в ТЕКСТ промежуточных итогов не может быть добавлена ​​с указанием «часов». E.F. 5 лет назад 0
Я обновил свой ответ некоторыми дополнительными идеями, которые должны помочь. PeterT 5 лет назад 0
Спасибо за обновление. Я также упустил возможность не использовать формат date-y и просто держать его +/- дней в числовом формате. Затем в конце серии добавьте его к абсолютной дате и оно никогда не приведет к отрицательной дате. E.F. 5 лет назад 0