Excel: Wenn ein Auftrag länger als 8 Stunden dauert am nächsten Tag weiter rechnen
Hallo ich hoffe mir kann jeman helfen,
Ich will eine Excel erstellen die als Zeitplanungstabelle dient. Heißt ich habe ein Projekte, welches 3 Stunden geht. Arbeitsbeginn ist um 07:00 also geht das Projekt bis 10:00 Uhr. Jetzt kommt ein Auftrag welcher 10:00 Stunden geht, also 10 Uhr abgeschlossenes Projekt + 10:00 Stunden neues Projekt wären 20 Uhr. Arbeitszeit geht aber nur bis 16 Uhr. Also sollte die Formel ab 07:00 am nächsten Tag weiter rechnen.
Bisher habe ich Spalte M Spalte N Spalte P Beginn Projekt Dauer Fertigstellung 07:00 10:04 20:04
(Eigentlich bin ich schon ein schritt weiter, da ich hier meine Excel nicht einfügen kann umschreibe ich es im kleinen Rahmen)
Ich möchte das Excel Weiß er kann nur in Zeiten zwischen 7 und 16 Uhr rechnen
Ich hoffe mir kann jemand helfen ich danke im Voraus
3 Antworten
Ich würde das schon so wie du machen, nur das ich in Spalte L das Datum Beginn, in Spalte O die errechnete Endzeit ausgeben würde und dann in Spalte P Tatsächliche Fertigstellung und folgende Formelm: In Spalte P für das Enddatum:
=WENN(O2>ZEIT(16;0;0);L2+1;L2)
Und in Spalte Q für die Uhrzeit
=WENN(O2>ZEIT(16;0;0);ZEIT(7;0;0)+(O2-ZEIT(16;0;0));O2)
Habe das mach nachgebaut und meine Tabelle sieht aus wie im Bild unten dagestellt.
Ich habe Wert datrauf gelegt, dass auch Projekte, die mehrere Schichten dauern (Schicht = Arbeitstag 9h, aber nur einschichtige Tage hier verarbeitbar), richtig berechnet werden.
Wochenenden/Feiertage können manuell korrigiert werden (weiß nicht, ob ichs automatisch noch hinkriege)
Hab lange knobeln müssen, aber so sollte es gehen:
Prinzip1: ich verschiebe die tägliche Arbeitszeit auf 0:00 - 9:00 (=fiktiv), das erlaubt wesentlich übersichtlichere Formeln. (wird am Ende in Spalte G wieder korrigiert)
Prinzip2: Für jedes Projekt wird eine neue Zeile angefangen, so dass die Datumsliste variabel wirden muss.
Prinzip3: längere Projekte kereieren neue Tageszeilen, für jeden Tag also mindestens eine Zeile in der Liste.
Prinzip4: Ein längerer Formelblock wird vorgegeben, die Formeln sind nach Beendigung eines Projekts durch neue Projekte samt Pj-Dauer zu überschreiben.
Von Zeit zu Zeit ist Kopieren des erledigten Teils und Einfügen als Werte sinnvoll.
Meine vllt nicht optimale Lösung benötigt 7 Spalten (bei mir E:K, Du kannst sie beliebig umordnen oder Blöcke verschieben, aber vollzieh das erst mal auf einem neuen Blatt nach):
E: Pj-Name, F: Pj-Dauer, G: Pj-Wechsel Echtzeit, H: Schichtbeginn fiktiv, I: Pj-Wechsel, fiktiv J: fiktives Pj-Ende bzw bei Unterbrechung Restdauer; K: Arbeitsende fiktiv.
Tatsächlichen Schichtbeginn und -Ende weiß man ja.
Trage nun Folgendes ein:
E4: Pj1 ;
F4: Projektdauer in h (Tipp: gib für 5h nur 5: ein, xl versteht das als Stunden. Formatiere die Zelle benutzerdefiniert als [h] "h" oder, wenns genauer sein soll, als [h]:mm
G4: 5.1.15 7:00 als Startpunkt ;
H4: 5.1.15 0:00 als fiktiven Startpunkt (beides nur in der ersten Zeile.
i4: =H4 ;
J4: =+I4+WENN(ISTTEXT(F4);0;F4) (Ergebnis bei 5 h ist 5.1.15 5:00) und schließlich
K4: =H4+9/24
Zeile 5:
E5: =WENN(I5<=K4;"nächstes Pj";"Forts. "&WECHSELN(E4;"Forts";""))
F5: =WENN(LINKS(E5;5)="Forts";"Leer lassen!";"PJ-Dauer") ; (Formate wie Zeile 4)
G5: =I5+7/24 (Spalte vorgezogen, damit neben Pj-und Dauer-Spalte)
H5: =WENN(J5<K5;H5;H5+1) (ditto)
i5: =WENN(J4<K4;J4;H5+J4-K4) (ditto)
J5: =+I5+WENN(ISTTEXT(F5);0;F5)
K5: =H5+9/24
Zieh den Formelblock runter über zunächst mal so 10-20 Zeilen.
Jetzt kannst Du E5 überschreiben mit zB Pj2, F5: 6: , das reicht schon.
Du siehst jetzt in E6 den Eintrag "Forts. Pj2" (ein Punkt=erste Fortsetzung) und in F5 den Eintrag "Leer lassen!"
Der Pj-Wechsel ist jetzt auf 6.1. gesprungen
In dieser Zeile darfst du "wegen Forts." nichts überschreiben, ABER TROTZDEM:
der 6.1. ist ein Feiertag! Nur deshalb schreibst Du da in Sp.F rein: 9: (Stunden der ausgefallenen Schicht)
Du siehst, das Datum springt einen Tag weiter auf den 7.1.15, der Text Forts.Pj2 bekommt einen weiteren Punkt (gleiches bei Sa/So)
erst in der nächsten: "nächstes Pj" mit Pj3 (oder sonstigem Pj-Kürzel, beliebig) und neuer Stundenplanzahl überschreiben.
Zeiten, die in den übernächsten oder folgende Tage reichen, kreieren weitere Tageszeilen nach Erfordernis. Man kann also sehen, in welchem Pj HEUTE oder MORGEN oder wann sonst gearbeiter werden soll..
Schau Dir das einfach mal näher an und frag ggf zurück!
noch eine Erinnerung, weiöl ich gerade selbst drauf reingefallen bin:
Die Stunden immer als zB
5:
eingeben, sonst werden aus Stunden Tage und damit die 24-fache Pj-Dauer!
Wenn also plötzlich eine unerklärlich hohe Zahl bei den Pj-Stunden drinsteht:
Eingabe wiederholen mit Doppelpunkt!
Ok ich habe es versucht nachzubauen wahrscheinlich habe ich ein paar Sachen nicht richtig übernommen es funktioniert nicht ganz. Ich habe jetzt auch noch das Problem das eine Fortlaufende übersichtliche Liste haben muss welche für "Leien" einfach zu bedienen ist. Meine Idee ist eigentlich das die Liste (wie natürlich alle Listen) vollautomatisch funktioniert. Heißt ich gebe ein es werden 4000 Produkte hergestellt in einer Stunde kann man beispielsweise 150 Produkte abarbeiten. Heißt 26,66 Stunden benötigt dieses Projekt.
Also der Mitarbeiter schreibt rein 4000 Produkte die Tabelle errechnet den Zeitwert dieser wiederum errechnet vom ausgehenden Startdatum das Enddatum und die Enduhrzeit innerhalb des 8 stunden Arbeitsrahmen. Ich hoffe das ist verständlich soweit.
Also Spalte K Menge 4000 Spalte O Produktionsdauer 26:40 (HH:MM) Spalte Q Startdatum 16.01.2015 Spalte R Beginn um 07:00 (HH:MM)
Spalte S Fertigstellung am (da happert es jetzt schon) Ansatz von mir (Da ich davon ausgehe das ein Projekt nicht über 40 Stunden geht) eine Oder Funktion zu machen welche einfach durch Wenn O>8 dann 1 (1 Tag), wenn O>16 dann 2 (2 Tage) wenn O>24 Dann 3 Tage und so weiter) [dies mal um es erstmal für mich simpel zu gestalten)
Spalte U Endzeit mit Berücksichtigung zwischen 7 und 16 uhr
Ich hätte nicht gedacht das es doch so kompliziert ist mit dem ersten Ansatz konnte ich ganz gut arbeiten jedoch komm ich dann nicht auf das richtige Datum wenn die Bearbeitungszeit über 16 Stunden ist.
Vielleicht übersehe ich nur eine kleinigkeit oder ich denke zu kompliziert.
Danke auf jedenfall für die Antworten, vor allem gut mal andere Ansätze zu sehen
Jetzt hab ich doch noch eine Formel hingekriegt (habe mich aber nur dem Problem der Schichtübergreifenden Zeit gewidmet, das andere kannst du ja selbst) Aber zunächst noch ein Tipp: formatiere die Zelle mit den Produktionsdauer-Stunden als [h]:mm, das ist leichter les- und vergleichbar (Fehler würden leichter auffallen)
Noch eine Unklarheit blieb: die Schichtdauer ist 9h, Du sprichst aber auch von 8h. Die 1 h Differenz (Mittagspause?) erforderte nochmals einen erheblichen Denkprozess für die Korrektur. Aber schau Dir erst mal die Formel an.
Produktionsdauer: O2 (als Zeit, wie Du ja auch angibst, nicht StundenAnzahl)
Startzeit (als Datum+Zeit): Q2
Formel: Q3 (ich hab die Erklärungen in die Formel gepackt, N("xxx")=0 und beeinflusst das Ergebnis nicht, die +N(...)-Zeilen sind demnach auch folgenlos rauszulöschen:
Q3=+GANZZAHL(Q2)
+N("Tag. im Folgenden sind 7/24=Schichtbeginn, 9/24=Schichtdauer")
+VERGLEICH(O2 * 24;{0;9;18;27;36;45;54;63;70};1)-1
+N("ganze Projekt(-Schicht-)Tage")
+WENN(REST(O2;9/24)+REST(Q2;1)-7/24>9/24;1;0)+7/24
+N("wenn: Stunden aus angebrochenem Start-Tag
+ Stundenanteil aus Projekt > 1Schicht, dann:1 zusätzlicher Schicht-Tag")
+REST(REST(O2;9/24)+REST(Q2;1)-7/24;9/24)
+N("StundenAnteile der nicht zu ganzer Schicht umwandelbaren Zeit + Schichtbeginn")
Diese Formel in O3 liefert die Endzeit für die Pj-Dauer in O2 und ist gleichzeitig die Startzeit für ein neues Pj in O3.
Von Zeit zu Zeit sollten die Formeln oberhalb zu Wert umgesetzt werden.
Ich hoffe, das klappt bei Dir jetzt (hat mich eine (fast) schlaflose Nacht gekostet)
Das Problem Sonn-/Feiertage ist auch noch nicht bearbeitet.
so, jetzt noch die Erweiterung übers Wochenende sowie Feiertagskorrekturen (oder Urlaub oder Reinigungs-/Reparaturtage, sofern nicht als Stundenprojekt).
Erfordert 2-3 Hilfsspalten (kannst Du nach Q einfügen und danach nach rechts ausserhalb verschieben).
Proj-Dauer wieder in O2 (maximal: 45 h).
Startzeit wieder in Q2.
Wieder obige Formel, diesmal in R2 .
die Matrix im Teil Vergleich() ist hier auf max. 45 zu kürzen, also
**. . .
+VERGLEICH(O2*24;{0;9;18;27;36;45};1)-1
. . .**
Diese Formel kann aber auch Sa oder So liefern. deshalb:
S2: =WENN(REST(R2;7)<REST(Q2;7);R2+2;R2)
in N2 (oder sonstwo in der Zeile) muss der Bearbeiter erkennen, ob im Zeitraum Q2..S2und ggf anschließend Feiertage liegen und zB fff eintragen für 3 Feiertage (wenn zB der Hl.Abend auf Mo fällt, anschließend 2 Tage Betriebsferien). (bis zu 5 Zeichen, zB fffuu ergeben richtige Ergebnisse).
Als Pj-Abschluss in
T2: =WENN(REST(S2+LÄNGE(N2);7)<REST(S2;7);S2+2+LÄNGE(N2);S2+LÄNGE(N2))
und noch
Q3: =T2 (oder T2 direkt nach Q3 schieben, Geschmackssache)
noch zum 8 h -Arbeitstag: Wenn nur 8h gearbeitet wird, folgende Veränderungen:
in Zeile 2 suchen/ersetzen 9 durch 8 und den Vektor {0;9;18;27;36;45} (in Vergleich()
ersetzen durch {0;8;16;24;32;40}
Manko:
die Nachmittagszeiten erscheinen so, als wäre die Mittagspause um
15:00-16:00, die Nachmittagszeiten sind also um 1 h verschoben.
Das Einarbeiten der Pause in die Formel wäre aber sehr aufwändig, das mach ich jetzt nicht mehr!
Müsste sowas sein wie
=wenn(Rest(Gesamtformel);1)>0,5; Gesamtformel +1/24)
wobei Gesamtformel derBandwurm in R2 ist und möglicherweise Dopplungen der Aktion durch Bezüge auf schon behandelte Zellen entstehen könnten, müsste ziemlich aufwändig nochmal durchgetestet werden.
Danke für die letzte Antwort
ich habe es versucht nachzubauen wahrscheinlich habe ich ein paar Sachen nicht richtig übernommen es funktioniert nicht ganz. Ich habe jetzt auch noch das Problem das eine Fortlaufende übersichtliche Liste haben muss welche für "Leien" einfach zu bedienen ist. Meine Idee ist eigentlich das die Liste (wie natürlich alle Listen) vollautomatisch funktioniert. Heißt ich gebe ein es werden 4000 Produkte hergestellt in einer Stunde kann man beispielsweise 150 Produkte abarbeiten. Heißt 26,66 Stunden benötigt dieses Projekt.
Also der Mitarbeiter schreibt rein 4000 Produkte die Tabelle errechnet den Zeitwert dieser wiederum errechnet vom ausgehenden Startdatum das Enddatum und die Enduhrzeit innerhalb des 8 stunden Arbeitsrahmen. Ich hoffe das ist verständlich soweit.
Also Spalte K Menge 4000 Spalte O Produktionsdauer 26:40 (HH:MM) Spalte Q Startdatum 16.01.2015 Spalte R Beginn um 07:00 (HH:MM)
Spalte S Fertigstellung am (da happert es jetzt schon) Ansatz von mir (Da ich davon ausgehe das ein Projekt nicht über 40 Stunden geht) eine Oder Funktion zu machen welche einfach durch Wenn O>8 dann 1 (1 Tag), wenn O>16 dann 2 (2 Tage) wenn O>24 Dann 3 Tage und so weiter) [dies mal um es erstmal für mich simpel zu gestalten)
Spalte U Endzeit mit Berücksichtigung zwischen 7 und 16 uhr
Ich hätte nicht gedacht das es doch so kompliziert ist mit dem ersten Ansatz konnte ich ganz gut arbeiten jedoch komm ich dann nicht auf das richtige Datum wenn die Bearbeitungszeit über 16 Stunden ist.
Vielleicht übersehe ich nur eine kleinigkeit oder ich denke zu kompliziert.
Danke auf jedenfall für die Antworten, vor allem gut mal andere Ansätze zu sehen
automatisiertes Einfügen von 8h bei Sa, So, F und U-Tagen
(Eintrag F bzw U in Sp.E):
F5: =WENN(ODER(REST(GANZZAHL(G5);7)<2;D5="F";D5="U");9/24;WENN(LINKS(E5;5)="Forts";"Leer lassen!";"PJ-Dauer"))
..
Darüberhinaus empfehle ich (hier ausnahmsweise) eine mehrfach
bedingte Formatierung:
Standardformat dieser Zelle F5 : Normalschrift, Farbe automat. (od.schwarz)
F5 Neue Regeln:
1.: auf Basis Zellinhalt: Bestimmter Text: Inhalt: Leer lassen!
Format zB: Schrift lila (=eins rechts von magenta in der Farbwabe)
2.: Formel verwenden:
F5 =ODER(REST(GANZZAHL(G5);7)<2;D5="F";D5="U")
Format zB: Schriftfarbe dunkelgrün , kursiv
Dieses Format auf alle gefüllten Zellen der Spalte übertragen
E4 1.: Zellen enthalten-bestimmter Text- beginnen mit: nächst
Format zB: Schrift blau, fett (=links unterhalb von du-blau in der Farbwabe)
E4 2.: Zellen enthalten-bestimmter Text- beginnen mit: Forts
Format zB: Schrift lila (=eins rechts von magenta in der Farbwabe)