Formelfrage

[Thomas78]

Hallo zusammen,

ich hab da mal wieder eine kleine Frage an die Experten:

Ich habe ein Archiv aus dem ich mir mit folgender Formel erfolgreich die Urlaubstage (u) in einen Kalender eintragen lasse :

=WENN(SUMMENPRODUKT((Mitarbeiter=$D8)*(Von<=P$3)*(Bis>=P$3)*(Status="u"));"u";"")

Nun kann aber im Namensbereich Status ein u für Urlaub (funktioniert) oder ein k für Krank oder eine 2 für eine 2 Tagestour stehen.
Die letzten beiden Punkte möchte ich noch in meine obenstehende Formel integrieren. Das heisst die Formel soll erkennen wenn in Status ein u steht soll es mir ein "u" in den Kalender schreiben, bei einem k ein "k" und bei einer 2 eben eine "2".

Kann mir jemand helfen wie ich die beiden anderen Kriterien einfügen kann?

Vielen Dank und schöne Grüße

Thomas

[neopa]

Hallo Thomas,

Deine Aussagen sind ziemlich ja spartanisch. Unklar ist zumindest ob in Status auch andere Angaben als "u", "k" und "2" oder eben "" vorkommen können.

Nachfolgend geh ich davon aus dass nur die die erwähnten Angaben vorliegen und dass Mitarbeiter, Von, Bis und Status definierte Bereichsnamen sind. 
Dann sollte folgende Formel

=WENNFEHLER(VERWEIS(9;1/(Mitarbeiter=$D8)*(Von<=P$3)*(Bis>=P$3);Status)&"";"")

Sollten noch andere Angaben in Status vorliegen, kann man diese durch eine Formelerweiterung z.B. WECHSELN() oder durch eine Zusatzprüfung eliminieren.

[Thomas78]

Hallo Werner,

danke für deine Hilfe und Entschuldigung für die unverständlichen Angaben.

Deine Annahmen sind korrekt: Es können nur u,k,2 oder nichts in Status stehen. Ausserdem sind die Namensbereiche definiert.

Deine Formel funktioniert leider noch nicht richtig:

Egal welches Datum in "von" "bis" steht es trägt mir immer den Status ein wenn der Name zutrifft. Ich habe zum Beispiel von 02.07.2017 bis 15.07.2017 "u" eingegeben und im Kalender erscheint z.B. am 01.07.2017 auch ein "u".

Was mache ich falsch? Bzw. was bedeutet bei Vergleich das 9;1?

Vielen Dank und schöne Grüße

Thomas

[neopa]

Hallo Thomas,

sorry ein Kopierfehler meinerseits. Richtig muss die Formel natürlich so lauten:

=WENNFEHLER(VERWEIS(9;1/(Mitarbeiter=$D8)/(Von<=P$3)/(Bis>=P$3);Status)&"";"")

Anstelle der 9 kann jede beliebige Zahl größer 1 stehen.

[Thomas78]

Hallo Werner,

super jetzt funktioniert alles perfekt!

Vielen Dank für deine Hilfe! 
Auf die "/" statt "*" wäre ich nicht gekommen...

Vielen Dank und schöne Grüße

Thomas

[Thomas78]

Upps eine Frage hätte ich noch:

Kann man in die Formel einbauen, dass er nur Arbeitstage (Montag-Freitag) und keine Feiertage (Namensbereich: Feiertag) in den Kalender einträgt?

Sorry für die Nachfrage, hätte ich gleich sagen sollen.

Thomas

[neopa]

Hallo Thomas,

.... ich hab inzwischen mal nach meinen letzten Erläuterungen zu Deiner Frage:

was bedeutet bei Vergleich das 9;1/?

gesucht. Hier http://www.online-excel.de/fom/fo_read.php?f=1&bzh=91077&h=91069&ao=1#a123x hatte ich zuletzt etwas ausführlicher zu diesem Thema geschrieben. Die Aufgabe war dort zwar eine andere aber das Prinzip ist das Gleiche.

[neopa]

Hallo Thomas,

Deine Fragestellung nach Nichtberücksichtigung von Feiertagen sowie Sa/So hatte ich vorhin übersehen.

Die Formel dafür müsste dann wie folgt:

=WENNFEHLER(VERWEIS(9;1/(Mitarbeiter=$D8)/(Von<=P$3)/(Bis>=P$3)/(ZÄHLENWENN(Feiertage;P3)=0)/(REST(P3-2;7)<5);Status)&"";"")

erweitert werden.

[Thomas78]

Hallo Werner,

absolut perfekt. Ich kann dir nur tausendmal Danke sagen!

Auch danke für deinen Link, wieder was zum lernen!

Schöne Grüße 

Thomas