Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Datum/Zeit

Beitrag: Ermittlung des x-ten Wochentages eines Jahres *

Aufgabe
Es soll das Datum in einem Jahr bestimmt werden, wann der soundsovielte Wochentag ist.

Beispiel:
       A               B               C               D       
1 3  Montag  2001  1  
2       1  
3 15.01.01       

Der 3. Montag im Jahr 2001 ist der 15.01.01
Lösung
in A1 steht die Woche (bzw. der wie vielte Tag gesucht wird)
in B1 der Wochentag (als Text)
in C1 das Jahr

in D1 (Hilfszelle - ausblenden) steht:
=VERGLEICH(B1;{"Montag";"Dienstag";"Mittwoch";"Donnerstag";"Freitag";"Samstag";"Sonntag"};0)

in D2 (Hilfszelle - ausblenden) steht:
=WOCHENTAG(DATUM(C1;1;1);2)

Das Ergebnis
=DATUM(C1;1;1+WENN(D2 > D1;D1+7-D2;D1-D2)+(A1-1)*7)

Es geht natürlich auch ohne Hilfsspalten, allerdings wird das dann eine "Bombenlegerformel", in D1 steht:
=DATUM(C1;1;1+WENN(WOCHENTAG(DATUM(C1;1;1);2)>VERGLEICH(B1;{"Montag";"Dienstag";"Mittwoch";"Donnerstag";"Freitag";"Samstag";"Sonntag"};0) ;VERGLEICH(B1;{"Montag";"Dienstag";"Mittwoch";"Donnerstag";"Freitag";"Samstag";"Sonntag"};0)+7-WOCHENTAG(DATUM(C1;1;1);2) ;VERGLEICH(B1;{"Montag";"Dienstag";"Mittwoch";"Donnerstag";"Freitag";"Samstag";"Sonntag"};0)-WOCHENTAG(DATUM(C1;1;1);2))+(A1-1)*7)

oder etwas kürzer als "Minenlegerformel":
=DATUM(C1;1;1+WENN(WOCHENTAG(DATUM(C1;1;1);2)>VERGLEICH(B1;TEXT({2;3;4;5;6;7;8};"TTTT");0);VERGLEICH(B1;TEXT({2;3;4;5;6;7;8};"TTTT");0)+7 -WOCHENTAG(DATUM(C1;1;1);2);VERGLEICH(B1;TEXT({2;3;4;5;6;7;8};"TTTT");0)-WOCHENTAG(DATUM(C1;1;1);2))+(A1-1)*7)

Und unser Datums-Freak Peter Dorigo macht daraus einen Knallfrosch:
=DATUM(C1;1;A1*7-WOCHENTAG(("1/"&C1)-VERGLEICH(B1;TEXT({2;3;4;5;6;7;8};"TTTT"););3))

Der soundsovielte Wochentag ab irgendeinem Datum
In A1 steht das Ausgangsdatum; in A2 der gewünschte Wochentag (1=Mo bis 7=So); in A3 der wievielte Tag
ab (also inklusive) A1:
{=A1-1+VERGLEICH(A2;WOCHENTAG(A1+ZEILE(1:7)-1;2);0)+7*(A3-1)} - bzw. {ohne}

=A1-1+VERGLEICH(A2;WOCHENTAG(A1+{0;1;2;3;4;5;6};2);0)+7*(A3-1)
nach A1:
{=A1+VERGLEICH(A2;WOCHENTAG(A1+ZEILE(1:7);2);0)+7*(A3-1)} - bzw.

=A1+VERGLEICH(A2;WOCHENTAG(A1+{1;2;3;4;5;6;7};2);0)+7*(A3-1)

Erläuterung
Ergänzung 08.12.2007 von Manfred Flohr:
Zu einem Datum will man wissen, der wievielte Montag, Dienstag ... Sonntag im Jahr das ist („Knallfrosch im Rückwärtsgang“).

In A1 steht das Datum. In C1 steht der Wochentag (= Datum im Format TTTT). In B1 steht die Formel

=(A1-DATUM(JAHR(A1);1;)+WOCHENTAG(("1/"&JAHR(A1))-REST(A1-1;7);3))/7&". " & TEXT(A1;"TTTT")

Ergebnis: 3.
Der 15.01.2001 ist also der 3. Montag in 2001.