Есть ли способ правильно отобразить отрицательные промежутки времени в Excel?

Question

Есть ли способ заставить Excel показывать отрицательный промежуток времени? Если я вычту два значения времени (скажем, при вычитании фактического количества времени, потраченного на что-то из количества времени, запланированного для этого), и результат будет отрицательным, Excel просто заполняет ячейку результата хешами, чтобы уведомить меня, что результат не может быть отображается как значение времени. Даже OpenOffice.org Calc и Google Spreadsheets могут отображать отрицательные значения времени.

Есть ли способ обойти эту проблему с помощью условного форматирования? Я действительно не хочу создавать обходной путь, рассчитывая часы и минуты сам или что-то в этом роде.

Answer 1

От http://spreadsheetpage.com/index.php/tip/C17/P10/ Джона Уокенбаха:

По умолчанию Excel использует систему дат, которая начинается с 1 января 1900 года. Отрицательное значение времени генерирует комбинацию даты и времени, которая выпадает до этой даты, что недопустимо.

Решение состоит в том, чтобы использовать систему дат 1904 года. Выберите Инструменты, Параметры. В диалоговом окне «Параметры» перейдите на вкладку «Расчет» и выберите опцию системы дат 1904 года, чтобы изменить дату начала на 2 января 1904 года. Ваши отрицательные времена теперь будут отображаться правильно.

Если вы используете систему дат 1904 года, будьте осторожны при связывании ячеек дат в других книгах. Если в связанной книге используется система дат 1900 года, даты, полученные по ссылкам, будут неверными.

Answer 2

Если ответы появляются в столбце ячеек, начинающихся с "А1", скрыть этот столбец из поля зрения. Затем "вставьте" другой столбец клеток рядом со скрытым столбцом.

Перейдите в ячейку вверху нового столбца и введите формулу

=IF((A1<0,-A1,A1)

Это всегда даст читабельный ответ. Однако неясно, будет ли правильный ответ изначально положительным или отрицательным. Вам нужен механизм, чтобы показать это. Идеальный способ - включить в формулу еще одно условие, меняющее цвет на красный, если A1 меньше нуля.

Answer 3

Я решил проблему следующим образом:

Предположим, что

E2 - время начала, а F2 - время окончания (отформатируйте эти ячейки в формуле TIME, это не имеет значения, 24 часа или Am/Pm)

тогда формула будет

=TEXT((24-E2)+(F2),"H::MM")

формат ячейки должен быть (НОМЕР)

эта формула поможет вам, если время начала будет вечером D, а время окончания - утром следующего дня. Удачи

Answer 4

Рассматривали ли вы использование условного форматирования для выделения отрицательных промежутков времени?

Я знаю, что это, вероятно, не является технически правильным ответом, но он позволит пользователю / читателю сразу увидеть, какой диапазон отрицателен.

Answer 5

= ЕСЛИ (В4-А4 <0, (ТЕКСТ ((24-B4)+(А4), "- Н: ММ: СС")), В4-А4)

Это формула, которую я использовал, чтобы решить отрицательное время.