Формула Excel: отримайте графік роботи між датами та часом -

Загальна формула

=(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)

Немає часу початку та часу закінчення

Для розрахунку загальної кількості робочих годин між двома датами, припускаючи, що всі дні є повними робочими днями, ви можете використовувати ще простішу формулу: