= ЕСЛИ(начало <конец; МИН(конец; верхний) -МАКС(начало; нижний); МАКС(0; верхний старт) + МАКС(0; нижний конец))
У вас есть время начала и время окончания, и вы хотите знать, сколько часов между этими двумя временами перекрывают другой период времени в день. Например, вы хотите знать, сколько часов приходится на период с 8:00 до 17:00 или с 7:00 до 7:00 и т. д.
Эта информация может понадобиться для расчета различных почасовых ставок или разных сборов за обслуживание в зависимости от того, в какое время дня была выполнена работа. Например, вы можете заплатить одну ставку за часы, зарегистрированные в период с 7:00 вечера до 7:00 утра, а другую часовую ставку за пределами этого периода.
В этом примере мы сначала вычисляем общее количество часов между временем начала и временем окончания. Затем мы вычисляем общие часы, которые пересекаются с «периодом интереса». Используя эти значения, мы можем вычислить все оставшиеся часы.
Во всех формулах «нижний» представляет собой первый час (нижняя граница) в интересующем временном интервале, а верхний — второй час (верхняя граница).
Чтобы вычислить общее количество часов между начальным и конечным временем, формула в D5:
= ОСТАТ(C5-B5;1)
Формула в E5 работает в двух частях, используя ЕСЛИ для управления потоком. Если время начала меньше времени окончания, мы вычисляем перекрытие с интересующим периодом:
МИН(C5; верхний) -МАКС(B5; нижний)
Если время начала больше времени окончания (то есть время начала и время окончания пересекает полночь), мы используем:
МАКС(0; верхний B5) + МАКС(0; C5 — нижний)
Используя МАКС с нулем, мы предотвращаем использование отрицательных значений.
Наконец, чтобы вычислить «оставшиеся» часы (то есть часы, которые не попадают в интересующий нас период), мы просто вычтем E5 (включенный) из D5 (общая продолжительность).