Datumsberechnung Spezial (Teil 2) 2

Wie berechnet man den zweiten Mittwoch, den dritten Freitag oder den vierten Montag eines Monats?
 

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

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

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 wievielte Tag ist gesucht?

Der zweite Wert gibt den numerischen Wochentag des Monatsanfangs an. Dies erfolgt über die Funktion WOCHENTAG:

Der Wochentag des Monatsanfangs

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

Typ 11 der Funktion WOCHENTAG

Der dritte Wert gibt den Wochentag des gesuchten Datums wieder:

Der Wochentag des gesuchten Tags

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:

  1. Ist der Anfangswochentag gleich, größer oder kleiner dem gesuchten Wochentag
  2. 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

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

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

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.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

2 Gedanken zu “Datumsberechnung Spezial (Teil 2)

  • Hannes

    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?)

    • Martin Weiß Beitragsautor

      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