Столбец A: время начала в 24-часовом формате (по формуле =NOW() и заблокировано с помощью Ctrl+SHIFT+;)
Столбец B: время окончания в 24-часовом формате (вводится так же, как для столбца A)
Колонка C: продолжительность, отформатированная как [mm] , с использованием формулы =B1-A1
Как рассчитать продолжительность, если данные в столбце B включают время, которое происходит на следующий день?
Актуальный рабочий лист:
Ctrl + Shift + ; просто помещает текущее время в ячейку без метки даты, и, как указывает @Akina в своем ответе, оно ничего не блокирует.
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 не превышает полуночи.
Возможно, самое изящное и простое решение - это решение в этом ответе, упомянутое 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 неявно приводятся к 1 , а значения FALSE к 0 , когда используются в арифметических операциях. Таким образом:
B1 >= A1 то (B1<A1) → 0 и формула становится B1-A1B1 < A1 то (B1<A1) → 1 и формула эквивалентна 1-(A1-B1)Обратите внимание, что эта формула будет работать правильно, даже если оба значения являются datetime и с интервалом более 24 часов! Это не относится к первой формуле, поэтому эта вторая формула является более полезной / общей.
Как рассчитать продолжительность, если данные в столбце B включают время, приходящееся на следующий день?
=1 + B1 - A1
отформатированный как время покажет правильный результат. Если B1> A1, пересчет, конечно, содержит ненулевую часть даты, но она не отображается из-за формата ячейки.
Если вы хотите использовать это значение времени в некоторых вычислениях времени, используйте более сложную формулу
=B1-A1-INT(B1-A1)
PS.
и заблокируйте клавишей CTRL+SHIFT+;
Эта комбинация ничего не блокирует. Он полностью заменяет содержимое ячейки текущим значением времени.
Чтобы получить разницу в Minutes между двумя 24Hrs DateTime Stamps вы можете использовать любую из них:
=Int((A2-A1)*24*60)
Или же
=int((A2-A1)*1440)
Или же
=ROUNDUP((A2-A1)*1440,2)
Обратите внимание, что ячейка формулы должна иметь General формат.