Office: (Office 2013) Frühestes Lieferdatum berechnen

Hallo Kollegen,

ich habe ein Problem, das weit über meine extrem grundlegenden Excel-Kenntnisse hinausgeht, und vielleicht findet sich hier ja jemand, der es mir erklären und mich auf dem Weg zur richtigen Formel leiten kann.

Im Moment habe ich eine Tabelle vor mir, in der ich die frühesten Lieferdaten für bestimmte Aufträge eintragen muss. Ich kalkuliere sie alle von Hand. So wie ich Excel einschätze, ist der Vorgang aber komplett in eine Formel umsetz- und automatisierbar. Problem ist, dass diese Formel OR-Variablen, die Unterscheidung zwischen Tagen und Monaten sowie die Auslassung von Feiertagen umfassen würde. Wenn sich also einer zutraut, mir Datumsberechnung beizubringen: Ich würde mich sehr freuen! Die (editierte) Excel-Datei mit einer detaillierten Erklärung der nötigen Formel befindet sich im Anhang!

:)

 

Hallo,

die Mischung von Monaten und Tagen ist nicht sonderlich praktisch.. könnte man Monate nicht auch als 30 Tage verallgemeinern?

Und was ist bei dem Sonderfall Ordner und Scout: Vorausbestellbarkeit ist 30 Tage und 1 Monat. Muss ich nun abhängig vom Stichtagsmonat erkennen, was länger ist? (Feb: 28 Tage, März: 31 Tage)

VG
Santa

 

In der Tat, die Unterscheidung nervt. Letzten Endes wäre es besser, wenn sie drin bleiben würde, aber wir können uns der Lösung ja erstmal so nähern, als ob 1 Monat = 30 Tage und 2 Monate = 60 Tage wären. (Die Tabellen, die ich ausfülle, sind ellenlang, deswegen bin ich über jeden noch so kleinen Automatisierungsschritt glücklich.)

 

Hallo,

ich habe deine Tabelle Vorbedingungen so angepasst:

Vorbedingungen

	B	C	D	E	F	G	H	I	J	K	L
3	Bedingung für Artikel:	Vorausbestellbarkeit in Zeiteinheiten:			Bedingung für Firma:	Vorausbestellbarkeit in Zeiteinheiten:			Bedingung für Lieferschema:	Verzögerung in Zeiteinheiten:
4	Papier	0	Tage		Herlitz	0	Tage		Liefert sofort	0	Tage
5	Stifte	7	Tage		Pelikan	30	Tage		Liefert nach Regel A	1	Tage
6	Ordner	30	Tage		Scout	1	Monate		Liefert nach Regel B	4	Tage
7	Mappen	2	Monate		Lamy	1	Monate		Liefert nach Regel C	44	Tage

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.01 einschl. 64 Bit

Und kann damit nun das Zwischenergebnis X ausrechnen:

Lieferdaten

	B	C	D	E	F	G
2	Auftragsnummer	Artikel	Firma	Lieferschema	Stichtag	zwischenergebnis X
3	'00001	Papier	Herlitz	Liefert sofort	20.02.2015	Freitag, 20. Februar 2015
4	'00002	Papier	Pelikan	Liefert sofort	20.02.2015	Mittwoch, 21. Januar 2015
5	'00003	Papier	Scout	Liefert sofort	20.02.2015	Dienstag, 20. Januar 2015
6	'00004	Papier	Lamy	Liefert sofort	20.02.2015	Dienstag, 20. Januar 2015
7	'00005	Stifte	Herlitz	Liefert sofort	20.02.2015	Freitag, 13. Februar 2015

verwendete Formeln
Zelle	Formel	Bereich	N/A
G3:G7	=MIN(DATUM(JAHR(F3);MONAT(F3)-SVERWEIS(C3;Vorbedingungen!$B:$D;2;0)*N(SVERWE IS(C3;Vorbedingungen!$B:$D;3;0)="Monate");TAG(F3)-SVERWEIS(C3;Vorbedingungen!$B:$D;2;0)*N(SVERWEIS(C3;Vorbedingungen !$B:$D;3;0)="Tage"));DATUM(JAHR(F3);MONAT(F3)-SVERWEIS(D3;Vorbedingungen!$F:$H;2;0)*N(SVERWEIS (D3;Vorbedingungen!$F:$H;3;0)="Monate");TAG(F3)-SVERWEIS(D3;Vorbedingungen!$F:$H;2;0)*N(SVERWEIS(D3;Vorbedingungen !$F:$H;3;0)="Tage")))+SVERWEIS(E3;Vorbedingungen!$J:$K;2;0)

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.01 einschl. 64 Bit

Feiertage, sowie ob Sa oder So als Ergebnis rauskommt, wird noch nicht berücksichtigt.

VG
Santa

 

Super! Sehr interessant, Mc Santa, vielen Dank. Ich versuche hinter die Formel zu steigen, die du da zusammengebaut hast.

Ich habe extrem wenig praktische Erfahrung mit Excel, deswegen versage ich noch dabei, die Formel in die Tabelle einzubauen (siehe Anhang). Kannst du sehen, wo ich den Fehler mache?

Von X auf Y müsste man relativ einfach mit der Funktion ARBEITSTAG() kommen, vermute ich.

 

=MIN(DATUM(JAHR(F3);MONAT(F3)-
SVERWEIS(C3;Vorbedingungen!$B$4:$D$7;2;0)*N(SVERWEIS(C3;Vorbedingungen!$B$4:$D$7 ;3;0)="Monate");TAG(F3)-SVERWEIS(C3;Vorbedingungen!$B$4:$D$7;2;0)*N(SVERWEIS(C3;Vorbedingungen!$B$4:$D$7 ;3;0)="Tage"));DATUM(JAHR(F3);MONAT(F3)-SVERWEIS(D3;Vorbedingungen!$F$4:$H$7;2;0)*N(SVERWEIS(D3;Vorbedingungen!$F$4:$H$7 ;3;0)="Monate");TAG(F3)-
SVERWEIS(D3;Vorbedingungen!$F$4:$H$7;2;0)*N(SVERWEIS(D3;Vorbedingungen!$F$4:$H$7 ;3;0)="Tage")))+
SVERWEIS(E3;Vorbedingungen!$J$4:$K$7;2;0)

9 SVERWEISe, die man auf 3 reduzieren könnte. Damit würde das Modell 3mal so schnell kalkulieren. Und die Formel würde mit Hilfszellen etwas lesbarer.

 

Hallo,

klicke nochmal in die Formel und drücke Enter. Microsoft liefert dir dann einen Korrekturvorschlag der Formel, den du annimmst.

Jetzt funktioniert die Formel richtig und du musst nur noch das Ergebnis als Datum formatieren.

Leider habe ich die Funktion ARBEITSTAGS() hier nicht zur Verfügung, das musst du also alleine probieren..

Klappt meine Formel nun?
Viel Erfolg *Smilie
VG
Santa

 

Hallo Lupo

Ich sehe spontan mit Hilfszellen nur eine Reduktion auf 5 Sverweise, aber vielleicht habe ich eine Möglichkeit übersehen.
Wie würdest du es lösen?

VG
Santa

 

1-4 geben den gleichen Datensatz zurück (VERGLEICH). Weiter mit INDEX.
5-8 geben den gleichen Datensatz zurück (VERGLEICH). Weiter mit INDEX.
9 kann so bleiben (also dafür keine Hilfszelle; somit bleiben 2 Hilfszellen).

 

Mc Santa, nope, das hatte ich versucht, aber ich kriege den Klassiker #WERT!

 

Ah, hab es. Das Problem lag in der Spalte "Stichtag". Excel scheint die Eingabe "20.02.2015" bei mir nicht korrekt als Datum verarbeitet zu haben, obwohl die Zelle als Datum formatiert war. Der Wert ist komplett als "Jahr" verarbeitet gewesen. Erst als ich 2015/02/20 eingegeben habe, hat die Formel funktioniert. Tausend Dank für deine große Hilfe!

Fehlt nur noch die Verarbeitung von X nach Y. Ich werde da ein bisschen rumprobieren. Wenn irgendwer sonst Zeit hat, mir unter die Arme zu greifen, würde ich mich sehr freuen *Smilie

Edit: Ich glaub, ich hab's. Mc Santa, falls dich interessiert, wie das mit der Funktion Arbeitstag() abläuft, wirf einen Blick in die angehängte Datei. Ich werd jetzt noch versuchen, beide Formeln zu einer zusammenzupacken *Smilie

Diese beiden Formeln müssen jetzt noch in eine einzige Zeile gepackt werden, dann ist das schlimmste geschafft:

=MIN(DATUM(JAHR(F3);MONAT(F3)-SVERWEIS(C3;Vorbedingungen!$B$4:$D$7;2;0)*N(SVERWEIS(C3;Vorbedingungen!$B$4:$D$7 ;3;0)="Monate");TAG(F3)-SVERWEIS(C3;Vorbedingungen!$B$4:$D$7;2;0)*N(SVERWEIS(C3;Vorbedingungen!$B$4:$D$7 ;3;0)="Tage"));DATUM(JAHR(F3);MONAT(F3)-SVERWEIS(D3;Vorbedingungen!$F$4:$H$7;2;0)*N(SVERWEIS(D3;Vorbedingungen!$F$4:$H$7 ;3;0)="Monate");TAG(F3)-SVERWEIS(D3;Vorbedingungen!$F$4:$H$7;2;0)*N(SVERWEIS(D3;Vorbedingungen!$F$4:$H$7 ;3;0)="Tage")))+SVERWEIS(E3;Vorbedingungen!$J$4:$K$7;2;0)

=ARBEITSTAG(G3; 3)

 

Bin bislang daran gescheitert, beide Formeln zu vereinigen. Irgendwer noch eine Idee?

 

Hallo,

das sollte doch jetzt einfach gehen:
Klicke in Zelle G3 und kopiere die gesamte Formel.
Klicke nun die Formel mit den Arbeitstagen an und markiere "G3", füge dort die kopierte Formel ein.
lösche das zweite = (Gleichheitszeichen) aus der Formel und bestätige.

In deiner Datei hat diese Vorgehen geklappt, kannst du das nachstellen?

VG
Santa

 

Super, Mc Santa, wieder was gelernt!

Ich habe ein paar Checkläufe durchgeführt, und dabei ist mir aufgefallen, dass es einen Sonderfall gibt, den die Formel noch nicht abdeckt. Ein Blick auf die Seite "Vorbedingungen" der angehängten Datei macht es wahrscheinlich am deutlichsten: Für die beiden Vorausbestellbarkeit-Vorbedingungen gibt es Fälle, in denen kein Wert eingetragen ist. Das bedeutet, es gibt dort schlichtweg keine Verzögerung. Das ist in der Regel kein Problem für die Formel, weil sie ja automatisch den höheren Wert annimmt, aber in manchen Fällen (und es sind genug, um eine Überarbeitung der Formel sinnvoll zu machen) ist es bei BEIDEN Vorbedingungen der Fall.

Und das wiederum würde bedeuten, der Artikel ist quasi augenblicklich lieferbar, abhängig vom Lieferschema plus den klassischen zwei Werktagen. Gibt es eine Möglichkeit, die Formel so zu modifizieren, dass sie diesen Sonderfall quasi folgendermaßen berücksichtigt:

IF Vorbedingung 1 = "nicht" AND Vorbedingung 2 = "nicht" THEN frühestes Lieferdatum = heute + Bedingung für Lieferschema + 2 Werktage

 

Hallo,

kannst du nicht in den Vorbedingungen überall eine 0 eintragen?
Strg + H
"Nicht" ersetzen durch "0"

VG
Santa