Общая формула
=IF(end>start, end-start, 1-start+end)
Резюме
Чтобы рассчитать количество часов между двумя временами, вы можете использовать формулу, которая вычитает время начала из времени окончания. Если время начала и окончания охватывает полночь, вам нужно будет изменить формулу, как описано ниже. В показанном примере формула в D5:
=IF(C5>B5,C5-B5,1-B5+C5)
Объяснение
Чтобы рассчитать часы между временами, когда оба времени находятся в одном и том же календарном дне, вы можете просто вычесть время начала из времени окончания:
=end-start
И примените числовое форматирование для отображения часов.
Однако, когда время пересекает границу дня (полночь), все может стать сложнее. Прочтите ниже, чтобы увидеть несколько способов справиться с этой проблемой.
Как Excel отслеживает время
В Excel один день равен 1, что соответствует 24 часам. Это означает, что время и часы являются дробными значениями от 1, как показано в таблице ниже:
| Часы | Время | Дробная часть | Ценность |
|---|---|---|---|
| 3 | 3:00 УТРА | 3/24 | 0,125 |
| 6 | 6:00 УТРА | 6/24 | 0,25 |
| 4 | 4:00 УТРА | 4/24 | 0,167 |
| 8 | 8:00 УТРА | 24 августа | 0,333 |
| 12 | 12:00 PM | 24 декабря | 0,5 |
| 18 | 18:00 | 18/24 | 0,75 |
| 21 год | 9:00 ВЕЧЕРА | 21/24 | 0,875 |
Простой расчет продолжительности
Когда время начала и время окончания совпадают с одним днем, вычислить продолжительность в часах несложно. Например, с временем начала 9:00 и временем окончания 17:00 вы можете просто использовать эту формулу:
=end-start =5:00PM-8:00AM =0.375-0.708=.333 // 8hours
Когда времена переходят полночь
Вычислить прошедшее время сложнее, если время пересекает границу дня (полночь). Например, если время начала - 22:00 в один день, а время окончания - 5:00 утра следующего дня, время окончания на самом деле меньше времени начала, и приведенная выше формула вернет отрицательное значение, а Excel будет отображать строку хеш-символов (########).
Чтобы исправить эту проблему, вы можете использовать эту формулу для времени, которое пересекает границу дня:
=1-start+end
Вычитая время начала из 1, вы получаете количество времени в первый день, которое вы можете просто добавить к количеству времени во 2-й день, которое совпадает со временем окончания .
Эта формула не будет работать в течение одного дня, поэтому мы можем обобщить и объединить обе формулы внутри оператора IF следующим образом:
=IF(end>start, end-start, 1-start+end)
Теперь, когда оба времени относятся к одному и тому же дню, время окончания больше, чем время начала , поэтому используется простая формула. Но когда время пересекает границу дня, используется вторая формула.
Альтернатива функции MOD
Используя функцию MOD с делителем 1, мы можем упростить приведенную выше формулу до следующего:
=MOD(end-start,1)
Здесь функция MOD решает отрицательную проблему, используя функцию MOD для «переворота» отрицательных значений на требуемое положительное значение. Эта версия формулы будет обрабатывать оба случая, поэтому мы можем исключить условный оператор IF.
Примечание: ни одна из приведенных выше формул не будет обрабатывать длительность более 24 часов. Если вам это нужно, см. Параметр «Дата + время» ниже.
Чтобы узнать больше о модуле, вот хорошая ссылка на Khan Academy.
Форматирование продолжительности времени
По умолчанию Excel может отображать время, даже время, представляющее продолжительность, с использованием AM / PM. Например, если у вас есть расчетное время 6 часов, Excel может отобразить это как 6:00 утра. Чтобы удалить AM / PM, примените собственный числовой формат, например ч: мм.
В случаях, когда рассчитанное время превышает 24 часа, вы можете использовать собственный формат, например (ч): мм. Синтаксис квадратной скобки (h) указывает Excel отображать продолжительность более 24 часов. Если вы не используете скобки, Excel просто «перевернется», когда продолжительность достигнет 24 часов (как часы).
Упрощение проблемы с датой + временем
Вы можете просто решить задачу вычисления прошедшего времени, работая со значениями, которые содержат как дату, так и время. Чтобы ввести дату и время вместе, используйте единый интервал между временем и датой: 01.09.2016, 10:00.
Затем вы можете использовать базовую формулу для расчета прошедшего времени:
=end-start
В приведенном ниже примере начальное и конечное значения содержат как дату, так и время:
Формула:
=C5-B5
Отформатирован в произвольном числовом формате (ч): мм для отображения прошедших часов.
