Как вычесть ВРЕМЯ ЕДИНИЦЫ (не время суток)? В прошлом году я пробежал марафон за 48 минут и 20 секунд. В этом году я пробежал марафон за 47 минут и 50 секунд. Когда я делаю это в Excel, я пробовал все мыслимое форматирование для времени (включая [h]mm:ss), и ничего не работает. Я ожидаю результат -00:00:30 (или минус 30 секунд).
4
3 ответа
5
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)
Третье, что нужно учитывать, - это форматирование ячеек. В пользовательском формате вы можете использовать [ч]: мм: сс или [м]: сс. Скобки сообщают Excel, что он должен считать количество часов ([ч]) или минут ([м]) и не возвращать их в ноль через 23 часа или через 59 минут. Итак, в вашем случае, убедитесь, что форматирование ячеек: [m]:ss
Короче говоря: нет готового способа сделать то, что вы хотите, потому что Excel не знает, как обрабатывать отрицательные значения времени.
Если вы действительно хотите рассчитать то, что вы спрашиваете, вы можете использовать следующую (немного более сложную) формулу, которая вычитает A1 из A2: =IF( A2-A1 < 0; "-"; "") & TEXT( ABS(A2-A1); "[m]:ss" ) . Что на самом деле делает, так это вычитает время, преобразует его в положительное число ( ABS ), форматирует его в виде строки ( TEXT ) и ставит перед ним «-», когда оно отрицательное. Обратите внимание, что результатом является текст, а не число (значение).
У Microsoft есть две статьи; один о сохранении значений даты и времени (пролистайте статью до половины) и один о сложении и вычитании времени.
3
Excel может обрабатывать отрицательные времена ...... но только если вы используете "систему дат 1904 года", а не систему дат 1900 по умолчанию в Excel
Чтобы установить это для конкретной рабочей книги, сделайте следующее:
Файл> Параметры> Дополнительно> Прокрутите вниз до раздела "При расчете этой книги" (внизу) и установите флажок "Система дат 1904".
Иногда вы можете получить нежелательные эффекты. Не переключайтесь на систему дат 1904 года в рабочей книге, где у вас уже есть даты - потому что базовая дата - 1/1/1904 а не 1/0/1900 даты будут меняться на 4 года и один день (1462 дня).
Смотрите скриншот:
-1
Это работает, функция времени не может принять 24 часа, а просто обмануть ее 23 часами, а затем исправить позже ....
=IF(A1<B1,TIME(23,0,0)-A1+B1+TIME(1,0,0),B1-A1)