Вычислять время между двумя датами, исключая выходные, когда ввод происходит в выходные дни?

Question

Я пытаюсь вычислить разницу во времени между двумя датами, чтобы исключить выходные и праздничные дни в Excel. Проблема, с которой я сталкиваюсь, заключается в том, что иногда событие происходит в выходные дни.

Например, я имею дело с системными входами, чтобы вычислить время между тем, когда что-то было добавлено в систему, и когда кто-то работал над проблемой.

У меня есть время ввода в ячейке A1 и законченное время в ячейке B1. В настоящее время я использую следующую формулу NETWORKDAYS, но мне не нравится, как это возвращает полные дни, вместо этого я хотел бы знать часы. При использовании формулы NETWORKDAYS вместе с MOD я получаю сообщение об ошибке в любое время, когда что-то происходит в выходные дни.

Я имею дело с системой, в которой каждый может что-то ввести в любое время, но мы не отвечаем в нерабочее время, если система автоматически не отвечает за нас.

Answer 1

Нет встроенной функции NETWORKHOURS. Вам придется реализовать это самостоятельно или найти примеры в Интернете.

Answer 2

Это работает?

1) Введите начальный день / время в ячейку A1 и конечный день / время в ячейку B1

2) Создайте именованный диапазон под названием Праздники, который содержит любые праздники. Пример создания именованного диапазона.

3) Скопировать формулу ниже, вставьте в панель формул для ячейки D1, и нажмите Enter.

=IFERROR(MROUND(NETWORKDAYS(
IF(OR(
          WEEKDAY(A1,2)>=6,
          COUNTIF(Holidays,INT(A1))>0),WORKDAY(A1,1),
     A1),
IF(OR(
          WEEKDAY(B1,2)>=6,
          COUNTIF(Holidays,INT(B1))>0),WORKDAY(B1,-1)+TIME(23,59,59),
     B1))-1-MOD(
IF(OR(
          WEEKDAY(A1,2)>=6,
          COUNTIF(Holidays,INT(A1))>0),WORKDAY(A1,1),
     A1),1)+MOD(
IF(OR(
          WEEKDAY(B1,2)>=6,
          COUNTIF(Holidays,INT(B1))>0),WORKDAY(B1,-1)+TIME(23,59,59),
     B1),1),"1:00"),0)

Пример:

4) Отформатируйте ячейку D1 как время с типом 37:30:55.

5) Пример результата с началом дня / времени в субботу.

Примечание. Выше показан только один час, поскольку рабочий день начинается в полночь.

==============================================

Ожидаемые результаты

1) Вернуть рабочее время между указанной датой / временем.

2) Вернуть 0, если между указанной датой / временем <= 0 рабочих часов (т. Е. Начало / конец в те же выходные).

3) Округление до ближайшего часа.

4) Рабочий день считается 24 часа. Возможно, вы можете изменить формулу для конкретного рабочего времени, но это не было указано в вопросе.

==============================================

Используемые источники

Как рассчитать часы в Excel, исключая выходные

Извлечение даты из числа даты и времени в Excel

Как я могу проверить, является ли дата рабочим днем?

Как округлить время до ближайшего часа в Excel?