Wie lautet die Excel-Formel, um den 1. und 3. Donnerstag eines Monats aufzuführen - Vielen Dank im Voraus, Excel-Freunde.?
4 Antworten
Ich habe jetzt mal eine Formel gebastelt, die unter Garantie viel zu kompliziert geraten ist, aber um dies Uhrzeit habe ich keine Lust mehr sie zu optimieren.
Klappen sollte sie aber, und zwar so:
In A1 steht ein Datum aus dem Monat, mit dem Du beginnen möchtest, egal welches. Dann diese Formel und runterkopieren soweit Du es brauchst:
=DATUM(JAHR($A$1);MONAT($A$1)+(ZEILE(A1)-1)/2;1)+VERWEIS(WOCHENTAG(DATUM(JAHR($A$1);MONAT($A$1)+(ZEILE(A1)-1)/2;1);14)-1;{0;1;2;3;4;5;6};{0;6;5;4;3;2;1})+REST(ZEILE(A2);2)*14
Damit werden Dir alle 1. und 3. Donnerstage angezeigt, beginnend mit dem Monat in A1. SO hatte ich Deine Frage verstanden. Nur die beide Donnerstage aus dem aktuellen Monat zu zeigen ist natürlich einfacher.
Aber sicherlich gibt es auch für die Formel oben eine elegantere Lösung.
Vielleicht schaue ich mir das morgen nochmal an, falls keiner der anderen Experten mir zuvorkommt.
Wenn du das Datum des 1. Donnerstag meinst:
=DATUM(Jahr;Monat;1)+REST(5-WOCHENTAG(DATUM(Jahr;Monat;1));7)
Wobei Jahr und Monat das relevante Jahr und der relevante Monat sind.
das Datum des 3. Donnerstag erhältst du, indem du zum Datum vom 1. Donnerstag 14 hinzuzählst.
zur Analyse eines Datums habe ich mir folgendes einfallen lassen: Datum in A1 zB 6.10.16
Auswertung durch die Formel:
=GANZZAHL(TAG(A1)/7)+1&"."&TEXT(A1;"TTT")&" im "&TEXT(A1;"MMM.JJ")
in Text: 1.Do im Okt.16
Dasgeht für jedes Datum, also auch zB 24.12.17 -> 4.So im Dez.17 oder der -vllt völlig irrelevante- Geburtstag eines verstorbenen Verwandten:
23.04.1908 -> 4.Do im Apr.08
Willst Du aber umgekehrt das Datum kreieren,
brauchst du die entsprechenden Eingaben. ich beginne in
J1=Jahr=2016, K1=Monat=Okt; L1=Wochentag=Do; M1=wievielter=1.
Die Formel hab ich nicht einfacher hingekriegt, sie ist:
=REST(WOCHENTAG("1."&K1&"."&J1)+VERGLEICH(L1;{"So"."Mo"."Di"."Mi"."Do"."Fr"."Sa"};0);7)+(M1-1)*7+("1."&K1&"."&J1)
das ist eine Datumszahl, die sicherheitshalber im Bendef. Format TTT, T.M.JJ formatiert werden sollte (vllt sogar noch besser TTT, T.MMM.JJ)
Achtung:die Formel merkt nicht, wenn in den anderen Monat gewechselt wird, wenn zB nach dem 5.Do im Okt.16 gefragt wird, sie liefert einfach dann den Do, 3.11.16. Die Ausschaltung des Fehlers ist problemlos möglich, führt aber zu mehr als einer Verdoppelung der Formellänge (mach ich morgen bei Bedarf nach Rückmeldung, für heute 1:52 nachts reicht das erst mal)
Die Formel von Oubyi funktioniert auf Anhieb wie gewünscht: Ein Startdatum eingeben und drag + dropen. Ergebnis: in jedem Folgemonat sind 2 Donnerstage (1. und 3.). Vielen Dank für die Bemühungen. Alle anderen werde ich bei Gelegenheit ausprobieren.
da erfahrungsgemäß die Ansprüche sofort steigen, wenn es geht, habe ich die Formel so verallgemeinert, dass Du jeden n-ten beliebigen Wochentag im Monat damit finden kannst. Also zB den Buß-und Bettag (3.Mi im Nov).
Ich selbst brauch zB jeweils den 1. u,. 3. Di im Monat, daneben für was anderes den 2. Di im Monat sowie den 1. Mi im Monat. Da ich die Tage in einer Kalender-artigen Tagesliste stehen hab, nehme ich die oberste Formel (Analyse:wenn WAHR, zeige Grund). Wobei ich meine bisherige Formel, die etwas komplizierter war, bei der Gelegenheit vereinfacht hab.
Nun will ich hier nicht Werbung für meine Formel machen, wenn Du mit der Formel von Oubyi zufrieden bist, bleib dabei: die beste Formel ist immer die, die man gut kennt!
irgendwas stimmt da noch nicht, wenn ich in einen anderen Monat wechsle, wirds falsch ===>morgen! (ich denke, wenn man in der Fo von gfnTom noch meinen Vergleich reinmacht, könnts gehen
jetzt hoffentlich richtig, Vorgaben: J1=2016;K1=Aug; L1=Do:M1=3
=("1."&K1&"."&J1)-(WOCHENTAG("1."&K1&"."&J1)-VERGLEICH(L1;{"So"."Mo"."Di"."Mi"."Do"."Fr"."Sa"};0))+WENN(WOCHENTAG("1."&K1&"."&J1)>VERGLEICH(L1;{"So"."Mo"."Di"."Mi"."Do"."Fr"."Sa"};0);M1;M1-1)*7
liefert (Bendef formatiert als TTT, T.M.JJJJ): Do, 18.8.2016
Auch hier: ein Wechsel in den nächsten Monat wird nicht bemerkt!
Anm: ich hab hier der Einfachheit halber das amerikanische Wochentag verwendet, ist im Prinzip egal, man muss es bei Änderung nur anpassen.
In A1 steht der Monat. Beispiel: 1.10.2016 Monatsanfang ist immer der erste!
In einer Zelle steht
=A1-REST(A1-2-4;7)+6
14 Tage später
=A1-REST(A1-2-4;7)+20
Die 4 in der Formel steht für Donnerstag.Montag ist 1, Sonntag ist 7
Vielen Dank für die Bemühungen. Ich möchte ein Startdatum eingeben und drag + dropen. Das funktioniert mit der Formel von Oubyi richtig. Diese werde ich bei Gelegenheit auch ausprobieren.
Ich verstehe nicht, was du meinst. Wenn ich dich dich richtig verstanden haben sollte, so wolltest du den 1. und 3. Donnerstag im Monat wissen.
Dann gibst du zum Beispiel in A1 1.1.2016 ein und in A2 .1.1.2016 ein. Beide Zellen markieren und nach unten ziehen. Schon bildet Excel, OpenOffice und LibreOffice das Increment.
Siehe Bild
Meine Formel musst du nur nach unten kopieren, bzw. kopieren.
DH! das ist viel kürzer als meine Formel und ich hab dann noch damit rumgespielt und sie verallgemeinert:
A1=1.des Monats/Jahr, Eingabe ganz kurz als 9/16 (M/JJ oder zB 11/1914)
B1: gewünschter Wochentag in Kurzform als Text: Do
C1: wievielter als Zahl (ohne Punkt)
Formel:
=A1-REST(A1-VERGLEICH(B1;{"Sa"."So"."Mo"."Di"."Mi"."Do"."Fr"};0);7)-1+7*C1
Ausgabezelle vorsichtshalber als TTT, T.MMM.JJ formatieren (oder .JJJJ)
Die Idee, den Wochentag direkt über REST zu berechnen merke ich mir.
Die Ausgabezelle muss als Datum formatiert werden.
Die Formel funktioniert auch bei LibreOffice / OpenOffice. Beide Programme sind kostenlos!
Diese Formel funktioniert auf Anhieb wie gewünscht: Ein Startdatum eingeben und drag + dropen. Ergebnis: in jedem Folgemonat sind 2 Donnerstage (1. und 3.). Danke Oubyi. Wenn jetzt aber jemand denkt, ich würde durchblicken warum - Nein.