Я пытаюсь найти формулу, которая работает в рабочее время между двумя датами в Excel.
Рабочие часы начинаются в 09:00 и заканчиваются в 17:00 и не включают выходные.
В настоящее время таблица составлена следующим образом;
A1: Start Time - B1: End Time - C1: Total Business hours
A2: 2016-01-04 10:31:17 - B2: 2016-01-06 10:02:14
Можно ли использовать формулу, которая будет рассчитывать разницу во времени (рабочие часы) и отображать ее в C2?
Предполагая, что даты в одном и том же году и пренебрегая небольшой ошибкой округления, я нашел жизнеспособное решение:
Сначала я объясню это, а затем добавлю формулу ниже.
Я разделил решение в нескольких случаях, если это
ЕСЛИ 1: ДАТЫ В ТО ЖЕ НЕДЕЛЕ
IF(WEEKNUMBER(A2)=WEEKNUMBER(B2);
ЕСЛИ 1.1: ДАТЫ В ТО ЖЕ ДЕНЬ
IF(DAY(A2)=DAY(B2));
Возьмите часы конечной даты минус часы начальной даты = результат
(B2-A2)*24;
ЕСЛИ 1.2: ДАТЫ НЕ В ТО ЖЕ ДЕНЬ
Возьмите день конечной даты минус день начальной даты (= дни между обеими датами), умножьте на 8 (= 17-9) и добавьте разницу в часах. Функция MOD используется, чтобы отрезать дни дат и оставаться только со временем.
(DAY(B2)-DAY(A2))*(17-9)+(MOD(B2,1)-MOD(A2,1))*24);
ЕСЛИ 2: ДАТЫ НЕ В ТО ЖЕ НЕДЕЛЕ
Я вычисляю количество часов между этими неделями, затем добавляю часы даты начала к концу начальной недели (к счастью, год начался в пятницу, поэтому я мог просто сделать номер недели (startdate)* 7 и, наконец, добавьте количество часов с начала недели до даты окончания.
(WEEKNUMBER(B2)-WEEKNUMMER(A2)-1)*5*(17-9)+(WEEKNUMBER(A2)*7-QUOTIENT(A2;1))*(17-9)+17+(QUOTIENT(B2;1)-(WEEKNUMBER(B2)*7-4))*(17-9)+(MOD(B2;1)-MOD(A2;1))*24-9)
В конце вам просто нужно собрать все эти куски кода вместе, и вы можете рассчитать рабочие часы между датами.
Вот еще одна формула, в которой используется функция NETWORKDAYS которая позволит вам указывать праздники или, если у вас более поздняя версия Excel, вы можете заменить NETWORKDAYS.INTL чтобы указать, какие дни являются вашими выходными.
=NETWORKDAYS([@[Start Time]],[@[End Time]])*8/24-
([@[Start Time]]-(INT([@[Start Time]])+9/24))-
(INT([@[End Time]])+17/24-[@[End Time]])
Поскольку мы настраиваем представление времени в виде долей дня, результаты будут в формате даты / времени Excel и могут быть отформатированы для отображения секунд, если вы хотите.
