Эта проблема имеет огромное количество возможных комбинаций:
- когда звонки поступают относительно рабочей недели, рабочего дня и обеда
- требуются ли запчасти
- когда запрос запчастей размещен относительно рабочей недели, рабочего дня, обеда и прибытия звонка
- будь то прибытие запчастей на выходные или даже на несколько выходных, если они недоступны
- будь то техническая отправка или прибытие звонка и / или техническая отправка или прибытие запчастей в выходные
- все возможные комбинации и перестановки вышеперечисленного.
Я не мог найти способ использовать общую формулу, чтобы просто считать вещи. Похоже, что для каждого сценария ответа вам необходимо точно моделировать, когда каждое действие происходит относительно рабочей недели, рабочего дня и обеда, а также складывать и вычитать релевантное время. Правила становятся очень сложными, и это без учета праздников, что добавило бы дополнительное измерение.
Технически возможно сделать это с помощью одной «формулы», но она будет настолько длинной и сложной, что вы никогда не сможете ее устранить, изменить или осмыслить. Поэтому в этом ответе будет использоваться набор столбцов строительных блоков (некоторые из которых содержат несколько длинных формул), и они смешиваются, сопоставляются и используются повторно. Это послужит основой для изменения вещей.
Я создал самый экстремальный, запутанный сценарий, который только мог придумать, чтобы проверить его. Возможно, что это решение слишком сложное, потому что есть некоторые ситуации, которые не встречаются в реальной жизни. Вы можете упростить это.
Строительный блок колонн
Days & Times
Excel хранит дату / время как одно число. Часть целых чисел - это число дней, а дробная часть - время как десятичная дробь дня. Различные части формул должны использовать только дни или просто времена, поэтому первые восемь строительных блоков разделяют их. Столбцы Kчерез Nдни для колонн Cчерез Fи колонны Oчерез Rте времена. Не имеет большого значения, как вы форматируете эти столбцы. Формулы приведут к числам (дни будут просто числа 42000, времена будут десятичные дроби - полдень или 12 часов будет 0,5). Предполагая, что ваши данные начинаются со строки 2:
K2: =INT(C2) L2: =INT(D2) M2: =INT(E2) N2: =INT(F2) O2: =MOD(C2,1) P2: =MOD(D2,1) Q2: =MOD(E2,1) R2: =MOD(F2,1) Необработанные сроки и время заказа деталей
Существует несколько способов обработки времени заказа деталей. Один из них - рассчитать продолжительность и вычесть ее. В этой проблеме это добавляет еще одну сложность, потому что вы не хотите удваивать количество исключений для обеда или выходных. Я использовал другой подход, разбивая проблему на две части в тех случаях, когда заказываются детали - что происходит до заказа деталей и что происходит после получения деталей; все время между игнорированием.
Это требует трех длительностей:
- получение звонка на заказ запчастей
- получение деталей в техническую отправку
- позвоните в службу технической поддержки в тех случаях, когда запчасти не требуются
Некоторые формулы требуют фактического истекшего времени, другие требуют количества дней независимо от времени суток, поэтому существует шесть столбцов строительных блоков. Эти длительности вычитают любые выходные. Я предположил, что выходные могут произойти между получением вызова и размещением заказа на запчасти (звонок поступает в конце дня в пятницу), или между получением части в конце пятницы и технической отправкой в понедельник. Столбцы Sчерез U- фактическое прошедшее время. Столбцы Vчерез Xэто количество дней.
S2: =D2-C2-IF(WEEKDAY(C2,2)+L2-K2>5,2,0) T2: =F2-E2-IF(N2-M2+WEEKDAY(E2,2)>5,2,0) U2: =F2-C2-INT((N2-K2+WEEKDAY(C2,2))/7)*2 V2: =L2-K2-IF(WEEKDAY(C2,2)+L2-K2>5,2,0) W2: =N2-M2-IF(N2-M2+WEEKDAY(E2,2)>5,2,0) X2: =N2-K2-INT((N2-K2+WEEKDAY(C2,2))/7)*2 Обратите внимание, что для случая отсутствия деталей используется другая настройка для выходных. Это связано с тем, что мой экстремальный тестовый случай включал выходные между прибытием звонка и заказом запасных частей, большую задержку с доставкой деталей и еще один выходной между прибытием запасных частей и технической доставкой. Для сценария «без запчастей» я просто использовал все время, включая два выходных, поэтому формула должна была справиться с этим. Если вы задействуете любого специалиста, которому для ответа на вызов потребовалось две недели, вы можете изменить его на тот же тип корректировки на выходные дни, который используется в других формулах, если вы хотите согласованности.
Отборочные времена
Следующим шагом является работа с временными рамками рабочего дня. Я разбил проблему на три интервала.
- Первый день (соответствующие времена начинаются по прибытии звонка и могут иметь различные конечные точки)
- Последний день (соответствующие времена могут иметь различные начальные точки и заканчиваться технической отправкой)
- Days Between (это все целые дни и нуждаются в корректировке на выходные)
Все действия могут выполняться в течение одного дня или двух дней, поэтому в формулах необходимо проверить, существует ли определенный интервал и еще не учтен. Формулы различаются в зависимости от того, требуется ли упорядочение деталей, поэтому для каждого интервала есть две формулы (требуемые детали являются первыми в каждом наборе).
First Day Y2: =IF(L2=K2,IF(M2=K2,IF(N2=K2,R2-Q2+P2,$J$3-Q2+P2),P2),$J$3)-O2 Z2: =IF(N2=K2,R2,$J$3)-O2 Last Day AA2: =IF(M2=N2,IF(L2=N2,IF(K2=N2,0,P2-$J$2+R2-Q2),R2-Q2),R2-$J$2) AB2: =IF(K2=N2,0,R2-$J$2) Days Between AC2: =IF(L2=K2,0,IF(M2=L2,IF(N2=L2,0,$J$3-Q2+P2-$J$2+(L2-K2-1-INT((L2-K2+WEEKDAY(C2,2))/7)*2)* ($J$3-$J$2)),P2-$J$2+(L2-K2-1-INT((L2-K2+WEEKDAY(C2,2))/7)*2)*($J$3-$J$2)))+ IF(M2=L2,0,IF(N2=M2,0,$J$3-Q2+(N2-M2-1-INT((N2-M2+WEEKDAY(E2,2))/7)*2)*($J$3-$J$2))) AD2: =IF(N2-K2>1,N2-K2-1-INT((N2-K2+WEEKDAY(C2,2))/7)*2,0)*($J$3-$J$2) Обратите внимание, что я разделил длинную формулу для удобства чтения. Если вы хотите скопировать и вставить, вам нужно будет удалить возврат каретки и лишние пробелы.
Обеденные перерывы
Корректировки обеденных перерывов определяют, в какие дни имеются применимые перерывы на обед, подсчитывают их и умножают количество на сохраненную спецификацию перерывов на обед. Опять же, требуемый для деталей случай первый:
AE2: =(IF(S2>$J$5-O2,1,0)+IF(S2>P2-$J$6,1,0)+IF(V2>=2,V2-1,0)+IF(T2>$J$5-Q2,1,0)+IF(T2>R2-$J$6,1,0)+IF(W2>=2,W2-1,0))*($J$6-$J$5) AF2: =(IF(U2>$J$5-O2,1,0)+IF(U2>R2-$J$6,1,0)+IF(X2>=2,X2-1,0))*($J$6-$J$5) Время технического отклика Время
отклика - это выбор подходящего набора чисел для объединения (требуются ли детали) и объединение элементов. Ячейка G2:
G2: =IF(ISBLANK(D2),Z2+AB2+AD2-AF2,Y2+AA2+AC2-AE2) Используйте пользовательский формат. Под форматами выберите формат времени, который близок, а затем настройте его в окне настройки. Вы можете использовать что-то вроде hh:mmили hh"h "mm"m", что даст результат, как 03h 47m.
В столбце H вы хотите часы и десятичные часы. Я не делаю много времени форматирования в Excel, но кажется, что встроенные форматы любят работать только с целыми часами. Если я не прав, возможно, кто-то еще сможет отредактировать этот ответ с лучшим подходом к форматированию. Я просто преобразовал бы сохраненное число в часы и затем отформатировал бы его как число с желаемым количеством десятичных знаков:
H2: =G2*24 Это преобразует десятичную дробь дня в часы.
При необходимости вы можете скопировать этот ряд формул вниз по странице. Если вы не хотите смотреть на столбцы строительного блока, скройте эти столбцы.