☰

Формула для расчета продолжительности времени, которое может охватывать два дня

tag-icon • tag-icon microsoft-excel worksheet-function
Формула для расчета продолжительности времени, которое может охватывать два дня

Столбец A: Время начала в 24-часовом формате (с использованием формулы =NOW()и фиксировано с помощью Ctrl+ SHIFT+ ;)
Столбец B: Время окончания в 24-часовом формате (вводится так же, как для столбца A)
Столбец C: Продолжительность, отформатированная как [mm], с использованием формулы=B1-A1

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

Фактический рабочий лист:

Скриншот реального рабочего процесса

решение1

Возможносамое элегантное и простое решение - это то, что вэтот ответупомянуто fixer1234 вэтот комментарий:

=MOD(B1-A1,1)

Обратите внимание: предполагается, что оба значения являются только значениями времени (или что часть даты должна быть усечена), т. е. каждое из них меньше 1, а продолжительность меньше 24 часов.

Объяснение:

Это работает, потому что для последовательного значения даты и времени 1 соответствует 24 часам, а значение только времени — это значение даты и времени меньше 1. Таким образом:

  • Если B1 >= A1то MOD(B1-A1,1)→ B1-A1как и ожидалось
    или
  • Если B1 < A1тогда MOD(B1-A1,1)эквивалентно 1-(A1-B1)(так как MOD(-n,m)=m-MOD(n,m)), что дает правильную «дополнительную» длительность


Альтернативное решение (судить вам, проще/элегантнее ли оно ;-) ):

=(B1<A1)+B1-A1

Объяснение:

Это работает, потому что TRUEзначения неявно приводятся к a 1, а FALSEзначения к a 0, когда используются в арифметических операциях. Таким образом:

  • Если B1 >= A1тогда (B1<A1)→ 0и формула становится B1-A1
    или
  • Если B1 < A1то (B1<A1)→ 1и формула эквивалентна1-(A1-B1)

Обратите внимание, что эта формула будет работать правильно.даже если оба значения являются датой и временемиразница более 24 часов!Этонетслучай первой формулы, поэтому вторая формула является более полезной/общей.

решение2

Ctrl++ просто помещает текущее время в ячейку без отметки даты, и, как указывает @Akina в своем ответе, это ничего не блокирует Shift.;

NOW()добавляет в ячейку дату и время. Чтобы увидеть это, щелкнитенеформатированная пустая ячейка или любая ячейка в Generalформатеи введите =NOW(). Во время набора этого текста я получаю 21/08/2018 11:25.

Если вы поместите =NOW()в ячейку A1и =NOW()+TIME(23,0,0)в ячейку B1, если вы затем поместите =B1-A1в ячейку C1и отформатируете ячейку , то Timeвы получите 23:00:00.

Если вы вводите время без отметок даты, вам нужно будет использовать формулу, IF()чтобы определить, меньше ли второе время, чем первое (временной интервал выходит за полночь). Следующая формула вычислит разницу во времени между временем в ячейке B1и ячейкой, A1независимо от того, больше ли полуночи время окончания. Она также будет работать с отметками даты или без них, поскольку отметки даты игнорируются.

=IF(B1<A1,MOD(B1-A1,1),B1-A1)

Пояснение формулы

=IF(B1<A1,...)

время в ячейке B1за полночь? Если да, то выполняется следующая часть

MOD(B1-A1,1)

Возвращает MOD(number,divisor)остаток после деления числа на делитель. Ячейка B1минус ячейка A1— отрицательное значение, так как время в ячейке B1уже за полночь, а Excel в большинстве случаев не любит отрицательные значения времени, поэтому он выдаст #####без MOD(). С MOD()этим разберется.

Альтернативой формуле MOD()было бы использование

TIME(23,59,59)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(0,0,1)+B1

Который 23:59:59 minus time in cell A1 plus 1 second plus time in cell B1.

Последний бит, который является ,B1-A1)последней частью, IF()где, если время в ячейке B1равнонетза полночь.

решение3

Спасибо @Akina, это единственная формула, которая у меня сработала... Я попробовал несколько из вышеперечисленного — с положительным подсчетом голосов, заметьте — и получил ужасный ###### или какое-то отрицательное число с несколькими десятичными знаками. Учитывал время, охватывающее два дня БЕЗ отметок о датах и ​​всей этой ерунды...

решение4

Чтобы получить разницу Minutesмежду двумя значениями, 24Hrs DateTime Stampsвы можете использовать любой из них:

=Int((A2-A1)*24*60)

Или

=int((A2-A1)*1440)

Или

=ROUNDUP((A2-A1)*1440,2)

Примечание,Ячейка формулы должна иметь Generalформат.

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