Nachdem ich vor zwei Wochen im ersten Teil dieser Kurzserie gezeigt habe, wie man den letzten bestimmten Wochentag (z.B. den letzten Montag) in einem Monat findet, werde ich heute die Flexibilität noch etwas erhöhen.
In diesem Artikel zeige ich, wie man bestimmt, auf welches Datum z.B. der erste Montag, der zweite Mittwoch oder der dritte Donnerstag eines Monats fällt.
Wenn du dich also in dem kommenden Jahr konsequent von jeder am zweiten Dienstag im Monat stattfindenden Abteilungsbesprechung abseilen willst, dann hilft dieser Beitrag vielleicht bei deiner Urlaubsplanung…
Der Fairness halber eine kleine Warnung vorweg:
Heute musst du ganz tapfer sein. Auch wenn sich die Aufgabenstellung ganz einfach anhört, ist es keine triviale Lösung, die ich heute vorstelle. Also Zähne zusammenbeißen und durch (am besten, du lädst dir gleich die Beispieldatei herunter)!
Eingabemaske
Um die Eingabe so einfach wie möglich zu gestalten und vor allem, um Fehleingaben zu vermeiden, habe ich die ersten drei Felder von B2:D2 als Dropdown-Listen gestaltet (ich traue dir zu, im vierten Feld eine vierstellige Jahreszahl einzutippen).
Eingabe und Ausgabe
Im Feld C6 wird aus der Eingabe der jeweilige Monatsanfang zusammengesetzt und im Feld E6 wird das gesuchte Datum angezeigt. Außerdem habe ich rechts daneben noch einen Kalender erstellt, der das Datum ebenfalls farblich hervorhebt. So sieht man auf einen Blick, ob das Ergebnis wirklich stimmt. So weit, so gut.
Vorbereitungen Teil 1
Zur Berechnung des gesuchten Datums benötige ich neben dem Datum des Monatsanfangs drei Werte, die ich in den Zellen B11, C11 und E11 ermittle.
3 benötigte Zwischenwerte
Der erste Wert ist lediglich die numerische Darstellung des in Zelle B2 eingegebenen Parameters: Aus „zweite“ wird „2“, aus „dritte“ wird „3“ usw. Dies ist über eine kleine Hilfstabelle umgesetzt, auf die über eine SVERWEIS-Funktion zugegriffen wird:
Der wievielte Tag ist gesucht?
Der zweite Wert gibt den numerischen Wochentag des Monatsanfangs an. Dies erfolgt über die Funktion WOCHENTAG:
Der Wochentag des Monatsanfangs
Wichtig:
Für den zweiten Parameter verwende ich in der Funktion den Typ 11, was bedeutet, dass Montag dem Wert 1 entspricht und Sonntag dem Wert 7:
Typ 11 der Funktion WOCHENTAG
Der dritte Wert gibt den Wochentag des gesuchten Datums wieder:
Der Wochentag des gesuchten Tags
Da wir das Datum ja noch nicht kennen, habe ich den Tag aus Zelle C2 mit einem einfachen SVERWEIS in den numerischen Wochentag umgewandelt.
Bis hierher ist also noch alles ziemlich überschaubar.
Vorbereitungen Teil 2
Jetzt kommt der komplizierte Teil. Hier habe ich mir ziemlich lang das Hirn zermartert, bis ich auf eine brauchbare Lösung gekommen bin. Die Berechnung des gesuchten Datums hängt nämlich von zwei Dingen ab:
- Ist der Anfangswochentag gleich, größer oder kleiner dem gesuchten Wochentag
- Ist das erste Vorkommen gesucht (x-te = 1) oder ein höheres (x-te = 2, 3, 4)
Somit ergeben sich 6 verschiedene Regeln, die bei der Berechnung des gesuchten Datums berücksichtigt werden müssen:
Die 6 Regeln
Abhängig von diesen 6 Regeln müssen zum Anfangsdatum unterschiedliche Werte addiert werden, um auf das gesuchte Datum zu kommen. Für jede dieser Regeln habe ich im Bereich M15:M20 das entsprechende Datum berechnet, das je nach Regel natürlich völlig unterschiedlich ausfällt.
Der Trick ist nun, die richtige Regel zu verwenden. Dies habe ich in Zelle E6 mit Hilfe der INDEX-Funktion und ein paar WENN-Verschachtelungen umgesetzt:
Die eigentliche Berechnung mit INDEX
Hier sieht man, dass jede WENN-Funktion einer der 6 Regeln entspricht. Trifft die erste WENN-Funktion zu, wird der Wert 1 zugeordnet, bei der zweiten WENN-Funktion der Wert 2 usw.
Dieser ermittelt Wert dient dann der INDEX-Funktion dazu, aus dem Bereich M15:M20 das gesuchte Datum zu übernehmen.
Ich gebe zu, das ist eine umständliche Angelegenheit, aber mir ist keine bessere Lösung eingefallen. Zumindest scheint sie zu funktionieren:
Ein anderes Beispiel
Vielleicht sehe ich ja den Wald vor lauter Bäumen nicht und du hast eine viel einfachere Lösung für dieses Problem. Dann würde ich mich freuen, wenn du sie unten in den Kommentaren mit uns teilen würdest.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Das könnte einfacher sein:
a das Datum des 1. des Monats
b den dazugehörigen Wochentag
c die Differenz dieses und des gewünschten Wochentages
dann: a + c + ((Anzahl der Woche-1) * 7)
Hab es probiert, klappt. (Wie poste ich die Beispielmappe?)
Hallo Hannes,
ich wusste, es gibt eine einfachere Lösung als meine 🙂
Das möchte ich den anderen Lesern natürlich nicht vorenthalten. Hier ist der Link für die Beispielmappe von Hannes:
Beispielmappe herunterladen
Vielen Dank dafür!
Schöne Grüße,
Martin
Hallo Martin,
habe gerade diese Seite gelesen und mir deine Datei heruntergeladen. Zuerst einmal meinen Respekt und Anerkennung für deine Homepage und vor allem für die sehr guten Erklärungen zu den einzelnen Excel – Funktionen.
Die Erläuterungen in dieser Datei sind auch für Excel – Anfänger verständlich.
Habe allerdings deine Tabelle im Bereich x-te Zahl (D24:E27) um die Zahl Fünf erhöht, da manche Monate ( z. B. Dezember 2016) 5 Wochen haben. In Zelle B11 dann noch die Formel anpassen und schon werden auch die Tage der 5. Woche angezeigt. Allerdings habe dabei festgestellt, dass die Auswertung 5. Tag eines Monats zu einem „falschen“ Ergebnis kommt, wenn es den ausgesuchten Tag in der fünften Woche gar nicht gibt (Beispiel: 5. Montag im Dezember 2016; Ergebnis 02.01.2017).
Schöne Grüße und ein ruhiges und friedvolles Jahr 2017
Michael
Hallo Michael,
Du hast Recht, dieser Fall ist tatsächlich eine Schwachstelle in der vorgestellten Lösung. Hier müssten die Formeln noch dahingehend erweitert werden, dass eine Prüfung auf den Monat stattfindet (d.h. Ergebnismonat = Anfangsmonat) und im Fehlerfall ein Hinweis kommt. Das wäre noch eine kleine Fleißaufgabe 🙂
Dir auch schöne Grüße und einen guten Rutsch!
Martin
Hallo Martin,
habe die Datei in der Form geändert, dass in Zelle E5 (Ergebnis) ein Hinweis erfolgt, wenn das Datum der Abfrage sich im darauffolgenden Monat befindet. Gibt mit Sicherheit elegantere Möglichkeiten, aber so geht es auch .
Außerdem habe ich in der Zelle E2 eine Jahresliste hinterlegt.
Falls du an der geänderten Datei Interesse haben solltest, schicke ich dir diese natürlich gerne zu.
Gruß
Michael
Hallo Michael,
sehr gerne. Dann würde ich die neue Datei im Artikel zum Download für die anderen Leser verlinken.
Schöne Grüße,
Martin
Hallo,
ich suche nach einer bestimmten Formel, die ich bisher nicht finden konnte. Es geht um die Ausgabe eines bestimmten Datums in Zusammenhang mit einem Feiertag. Also: In Bulgarien gibt es einen Feiertag am 6.5. Fällt der 6.5. auf ein Wochenende, gibt es am folgenden Montag einen extra freien Tag (feine Sache …). Ich möchte das Datum dieses Montags ermitteln. Kannst du mir dabei helfen?
Danke!
Hallo Martina,
eine einfache Lösung wäre z.B. diese:
=WENN(WOCHENTAG(A1;11)=6;A1+2;WENN(WOCHENTAG(A1;11)=7;A1+1;A1))
Damit wird der Wochentag des Datums in A1 geprüft. Wenn A1 = 6 (also Samstag), dann addiere 2 Tage. Wenn A1 = 7 (also Sonntag), dann addiere 1 Tag. In allen anderen Fällen nimm das Datum aus A1 unverändert.
Schöne Grüße,
Martin
Wunderbar aufwendig. Genial diese Berechnungen mit @index, sehe ich erstmalig.
Habe aber eine Tabelle angelegt, vom ersten bis letzten Tag des Monats dynamisch natürlich, habe Wochentag und Häufigkeit jeden Wochentags berechnen lassen und dann per Summewenns() Ergebnis ermittelt. Danke für Deine tolle Vorarbeit
Maximales Vorkommen eines wird dynamisch ermittelt
Hallo Jürgen,
ja, eine dynamische Datumstabelle ist natürlich auch eine sehr gute Idee. Vielen Dank für die Anregung.
Schöne Grüße,
Martin