☰

Вычитание ЕДИНИЦ ВРЕМЕНИ (не времени суток)

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Вычитание ЕДИНИЦ ВРЕМЕНИ (не времени суток)

Как вычесть ЕДИНИЦУ ВРЕМЕНИ (не время суток)? В прошлом году я пробежал марафон за 48 минут и 20 секунд. В этом году я пробежал марафон за 47 минут и 50 секунд. Когда я делаю это в Excel, я перепробовал все мыслимые форматы для времени (включая [h]mm:ss), но ничего не работает. Я ожидаю результат -00:00:30(или отрицательные 30 секунд).

решение1

Excel использует доли дня для хранения времени. Таким образом, 3 часа хранятся как 0,125 (3/24). Время хранится как значение даты/времени. Excel начинает отсчет дат с 1 января 1900 года 12:00 утра (1/2/1904 12:00 утра в Excel для Mac). Ввод времени 0:48:20 на самом деле означает 1 января 1900 года 0:48:20 часов. Excel не работает с отрицательными значениями даты/времени, поэтому вычитание до отрицательного значения приводит к ошибке (ячейка с "#######").

Другая проблема — заставить Excel понять, что под 48:20 вы подразумеваете 48 минут и 20 секунд, а не 48 часов и 20 минут. Есть два способа сделать это:

  • Используйте часовую запись. 48 минут и 20 секунд становятся:0:48:20
  • Используйте TIMEфункцию:=TIME(0;48;20)

Третье, что следует учесть, — это форматирование ячеек. В пользовательском формате можно использовать [h]:mm:ss или [m]:ss. Скобки сообщают Excel, что нужно подсчитать количество часов ([h]) или минут ([m]) и не сбрасывать их до нуля через 23 часа или через 59 минут. Поэтому в вашем случае убедитесь, что форматирование ячеек следующее:[m]:ss

Короче говоря: не существует «готового» способа сделать то, что вам нужно, поскольку Excel не умеет обрабатывать отрицательные значения времени.

Если выДействительнохотите вычислить то, что вы спрашиваете, вы можете использовать следующую (немного более сложную) формулу, которая вычитает A1 из A2: =IF( A2-A1 < 0; "-"; "") & TEXT( ABS(A2-A1); "[m]:ss" ). На самом деле она вычитает время, преобразует его в положительное число ( ABS), форматирует его как строку ( TEXT) и добавляет "-" перед ним, если оно отрицательное. Обратите внимание, что результатом является текст, а не число (значение).

У Microsoft есть две статьи: одна осохранение значений даты и времени(прокрутите вниз примерно до середины статьи) и еще одну осложение и вычитание времени.

решение2

Эксельможетобрабатывать отрицательные значения времени... но только если вы используете «систему дат 1904», а не систему дат Excel по умолчанию 1900

Чтобы установить это для конкретной книги, выполните следующие действия:

Файл > Параметры > Дополнительно > Прокрутите страницу вниз до раздела «При расчете этой книги» и установите флажок «Система дат 1904».

Иногда вы можете получить нежелательные эффекты. Не переключайтесь на систему дат 1904 года в рабочей книге, где у вас уже есть даты, потому что базовая дата, а 1/1/1904не 1/0/1900даты изменятся на 4 года и один день (1462 дня).

Смотрите скриншот:

введите описание изображения здесь

решение3

Это работает, функция времени не может принять 24 часа, поэтому просто обманите ее, указав 23 часа, а затем исправьте позже...

=IF(A1<B1,TIME(23,0,0)-A1+B1+TIME(1,0,0),B1-A1)

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