Обща формула
=(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)
Обобщение
За да изчислите общото работно време между две дати и часове, можете да използвате формула, базирана на функцията NETWORKDAYS. В показания пример 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 обработва изключването на уикендите и празниците (когато е предоставена като диапазон от дати). Можете да преминете към 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
Вижте обяснението тук за подробности.
