Общая формула
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Резюме
Чтобы рассчитать общее количество рабочих часов между двумя датами и временем, вы можете использовать формулу, основанную на функции ЧИСТРАБДНИ. В показанном примере E5 содержит эту формулу:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
где «нижний» - именованный диапазон H5, а «верхний» - именованный диапазон H6.
Примечание: этот пример был вдохновлен вызовом формулы на Chandoo и более полным решением, предоставленным мастером формул Барри Гудини на форуме MrExcel.
Объяснение
Эта формула вычисляет общее количество рабочих часов между двумя датами и временем, которые происходят между «нижним» и «верхним» временем. В показанном примере нижнее время - 9:00, а верхнее - 17:00. Они появляются в формуле как названные диапазоны «нижний» и «верхний».
Логика формулы состоит в том, чтобы вычислить все возможные рабочие часы между датами начала и окончания включительно, а затем отбросить любые часы в дату начала, которые встречаются между временем начала и более низким временем, и любые часы в дату окончания, которые происходят между время окончания и время окончания.
Функция ЧИСТРАБДНИ обрабатывает исключение выходных и праздничных дней (если они указаны как диапазон дат). Вы можете перейти на NETWORKDAYS.INTL, если в вашем расписании нестандартные рабочие дни.
Форматирование вывода
Результат - число, которое представляет общее количество часов. Как и во все времена Excel, вам нужно будет отформатировать вывод с помощью подходящего числового формата. В показанном примере мы используем:
(h):mm
Квадратные скобки предотвращают перенос Excel, когда часы больше 24. Другими словами, они позволяют отображать часы больше 24. Если вам нужно десятичное значение для часов, вы можете умножить результат на 24 и отформатировать его как обычный номер.
Простая версия
Если время начала и окончания всегда будет находиться между нижним и верхним временем, вы можете использовать более простую версию этой формулы:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Нет времени начала и времени окончания
Чтобы рассчитать общее количество рабочих часов между двумя датами, предполагая, что все дни являются полными рабочими днями, вы можете использовать еще более простую формулу:
=NETWORKDAYS(start,end,holidays)*hours
См. Объяснение здесь для подробностей.
