Dieses Problem hat eine Vielzahl möglicher Kombinationen:
- wenn Anrufe in Bezug auf die Arbeitswoche, den Arbeitstag und das Mittagessen eingehen
- ob Teile benötigt werden oder nicht
- wenn die Teileanforderung relativ zur Arbeitswoche, zum Arbeitstag, zum Mittagessen und zur Anrufankunft erfolgt
- ob Teile Ankunft ein Wochenende oder sogar mehrere Wochenenden umfasst, wenn sie nicht verfügbar sind
- ob Tech-Versand gegen Anrufankunft und / oder Tech-Versand gegen Teilekunft ein Wochenende umfasst
- alle möglichen Kombinationen und Permutationen der oben genannten.
Ich konnte keinen Weg finden, eine generische Formel zu verwenden, um einfach Dinge zu zählen. Es sieht so aus, als müssten Sie für jedes Antwortszenario genau modellieren, wann jede Aktivität relativ zur Arbeitswoche, zum Arbeitstag und zum Mittagessen stattfindet, und relevante Zeiten hinzufügen und subtrahieren. Die Regeln werden sehr komplex und dies ist ohne Urlaub zu tun, was eine zusätzliche Dimension hinzufügen würde.
Es ist technisch möglich, dies mit einer "Formel" zu tun, aber es wäre so lang und komplex, dass Sie nie in der Lage wären, Fehler zu finden, zu modifizieren oder nachträglich zu verstehen. Diese Antwort verwendet also eine Reihe von Bausteinspalten (von denen einige einige lange Formeln enthalten), und diese werden gemischt, abgeglichen und wiederverwendet. Es wird eine Grundlage für die Änderung von Dingen bieten.
Ich habe das extremste verworrene Szenario erstellt, an das ich denken konnte, um es zu testen. Es ist möglich, dass diese Lösung zu kompliziert ist, weil es Situationen gibt, die im wirklichen Leben nicht vorkommen. Sie können es gerne vereinfachen.
Die Bausteinsäulen
Days & Times
Excel speichert Datum / Uhrzeit als eine einzige Zahl. Der ganzzahlige Teil ist eine Tageszählung und der gebrochene Teil ist die Zeit als Dezimalbruch eines Tages. Verschiedene Teile der Formeln müssen nur Tage oder Zeiten verwendet werden, sodass die ersten acht Bausteine diese aufteilen. Spalten Kdurch Ndie Tage für Spalten Cdurch Fund Spalten Odurch Rdie Zeiten. Es macht keinen großen Unterschied, wie Sie diese Spalten formatieren. Die Formeln ergeben Zahlen (die Tage werden knapp 42000 sein, die Zeiten sind Dezimalbruchteile - Mittag oder 12 Stunden sind 0,5). Angenommen, Ihre Daten beginnen in Zeile 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) Rohdauern und Bestellzeiten für Teile
Es gibt mehrere Möglichkeiten, die Bestellzeiten für Teile zu handhaben. Eine ist, die Dauer zu berechnen und herauszuziehen. In diesem Problem erhöht sich die Komplexität, da Sie die Ausschlüsse für das Mittagessen oder das Wochenende nicht doppelt zählen möchten. Ich habe einen anderen Ansatz gewählt und das Problem in zwei Teile zerlegt, wenn Teile bestellt werden - was passiert beim Bestellen der Teile und was passiert, nachdem die Teile erhalten wurden; alle Zeit dazwischen ignoriert werden.
Dies erfordert drei Zeiträume:
- Beleg für die Bestellung von Teilen anrufen
- Teilebestätigung an Tech-Versand
- Für den Fall, dass keine Teile erforderlich sind, rufen Sie den Technischen Versand an
Für einige Formeln ist die tatsächlich abgelaufene Zeit erforderlich, für andere ist die Tageszählung unabhängig von der Tageszeit erforderlich. Es gibt also sechs Bausteinspalten. Diese Zeiträume ziehen alle Wochenenden ab. Ich ging davon aus, dass ein Wochenende zwischen dem Empfang eines Anrufs und der Bestellung eines Teilauftrages (der Anruf kommt am Ende des Tages am Freitag) oder zwischen dem Empfang der Teile am Ende eines Freitags und dem Tech-Versand am Montag auftreten kann. Spalten Sbis Usind die tatsächlich verstrichenen Zeiten. Spalten Vdurch Xsind die Tageszählungen.
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 Beachten Sie, dass eine andere Anpassung für Wochenenden für den Fall "keine Teile" verwendet wird. Dies liegt daran, dass mein extremer Testfall ein Wochenende zwischen Anrufannahme und Bestellung von Teilen, eine lange Verspätung für die Ankunft von Teilen und ein weiteres Wochenende zwischen der Ankunft von Teilen und dem technischen Versand umfasst. Für das "No-Parts" -Szenario habe ich nur die gesamte Zeit verwendet, die zwei Wochenenden umfasste, sodass die Formel dafür ausgerechnet wurde. Wenn Sie einen Techniker feuern würden, der zwei Wochen für die Beantwortung eines Anrufs benötigte, können Sie ihn in dieselbe Art von Anpassung für ein Wochenende ändern, die in den anderen Formeln verwendet wird, wenn Sie Konsistenz wünschen.
Qualifying-Zeiten
Der nächste Schritt besteht darin, die Zeitrahmen für den Arbeitstag einzuhalten. Ich habe das Problem in drei Intervalle aufgeteilt.
- Erster Tag (relevante Zeiten beginnen bei Anrufankunft und können verschiedene Endpunkte haben)
- Letzter Tag (relevante Zeiten können verschiedene Startpunkte haben und mit dem Tech-Versand enden)
- Tage zwischen (dies sind alle Tage und müssen an Wochenenden angepasst werden)
Alle Aktivitäten können an einem einzigen Tag oder zwei Tagen ausgeführt werden. Daher müssen die Formeln prüfen, ob das bestimmte Intervall vorhanden ist und noch nicht abgerechnet wurde. Die Formeln unterscheiden sich je nachdem, ob die Reihenfolge der Teile erforderlich ist. Es gibt also zwei Formeln für jedes Intervall (erforderliche Teile sind die ersten in jedem Satz).
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) Beachten Sie, dass ich die lange Formel für die Lesbarkeit aufgeteilt habe. Wenn Sie kopieren und einfügen möchten, müssen Sie die Zeilenumbrüche und zusätzliche Leerzeichen löschen.
Mittagspause
Die Einstellungen für die Mittagspause legen fest, für welche Tage Mittagspausen gelten, zählt sie und multipliziert die Anzahl mit den gespeicherten Mittagspause-Spezifikationen. Wieder ist der von Teilen benötigte Fall zuerst:
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) Technische Antwortzeit
Die Antwortzeit ist dann die Auswahl der geeigneten Zahlen, die kombiniert werden sollen (unabhängig davon, ob Teile erforderlich sind oder nicht), und die Teile werden kombiniert. Zelle G2:
G2: =IF(ISBLANK(D2),Z2+AB2+AD2-AF2,Y2+AA2+AC2-AE2) Verwenden Sie ein benutzerdefiniertes Format. Wählen Sie unter Formate ein geschlossenes Zeitformat aus und passen Sie es im Anpassungsfenster an. Sie können etwas wie hh:mmoder verwenden hh"h "mm"m", das ein ähnliches Ergebnis erzeugen würde 03h 47m.
In Spalte H wollen Sie Stunden und Dezimalstunden. Ich mache nicht viel Zeit für die Formatierung in Excel, aber es scheint, als würden die eingebauten Formate nur ganze Stunden verarbeiten. Wenn ich falsch liege, kann vielleicht jemand anderes diese Antwort mit einem besseren Formatierungsansatz bearbeiten. Ich würde einfach die gespeicherte Zahl in Stunden umrechnen und sie dann als Zahl mit der gewünschten Anzahl von Dezimalstellen formatieren:
H2: =G2*24 Dies konvertiert einen Dezimalbruch eines Tages in Stunden.
Sie können diese Zeile mit Formeln nach Bedarf auf die Seite kopieren. Wenn Sie die Bausteinspalten nicht anzeigen möchten, blenden Sie diese Spalten aus.