Die Kalenderwoche ist gerade im Geschäftsbereich eine gängige Zeitangabe. Vor allem bei Projekten arbeitet man häufig mit Kalenderwochen, um den Beginn oder die Dauer von Projektschritten zu definieren.
Excel bietet hier mit der KALENDERWOCHE-Funktion ein praktisches Mittel, um aus einem Datum die zugehörige Kalenderwoche zu ermitteln. Was mache ich aber für den umgekehrten Fall: Wenn ich nur eine Kalenderwoche gegeben habe und dazu z.B. das Datum des ersten Tages wissen möchte?
Kann doch gar nicht so schwer sein, habe ich mir gedacht. Bis mir nach einiger Zeit ziemlich der Kopf geraucht hat, denn die Tücke steckt hier im Detail.
Schließlich habe ich doch noch eine Lösung gefunden, welche sogar die in unseren Breiten übliche ISO-Kalenderwochenregelung berücksichtigt.
Und so geht’s:
Im Internet schwirren einige Lösungen zu dieser Aufgabe herum. Allerdings haben sie alle (zumindest diejenigen, die ich finden konnte) einen großen Haken: Sie berücksichtigen nicht die ISO-konforme Kalenderwochenberechnung.
Die Tücken der Kalenderwoche
Auch wenn es dem Laien meist nicht bewußt ist: Die Berechnung einer Kalenderwoche ist nicht ganz ohne. Im Artikel „Wie Dir Excel zu 2 Wochen mehr Urlaub verhilft“ habe ich sehr plastisch gezeigt, dass die Excel-Funktion KALENDERWOCHE etwas mit Vorsicht zu genießen ist.
Laut ISO 8601 ist die erste Kalenderwoche eines Jahres diejenige, in der der erste Januar-Donnerstag liegt. Infolgedessen kann es passieren, dass der 01.01. nicht in der KW 1 liegt, sondern in der KW 52 oder 53 des Vorjahres (so der Fall für den 01.01.2012 oder den 01.01.2016).
Um das zu berücksichtigen, sollte man in der Funktion KALENDERWOCHE grundsätzlich für den zweiten Parameter den Zahl-Typ 21 angeben. Im folgenden Bild sieht man ganz gut, wann der 1. Januar auch tatsächlich in KW 1 liegt. Nämlich immer dann, wenn er vor oder auf den ersten Donnerstag des Jahres fällt:
KALENDERWOCHE-Funktion
Auf diese Hintergrundinformation greifen wir im Verlauf des Artikels noch zurück. Kommen wir zunächst zu den Wochentagen.
Die Funktion WOCHENTAG
Mit dieser Funktion lässt sich die fortlaufende Nummer des Wochentages eines bestimmten Datums ausgeben.
=WOCHENTAG(Datum; Typ)
Für den Typ stehen folgende Optionen zur Verfügung:
Zahl-Typen
In unseren weiteren Berechnungen verwende ich den Typ 11 und erhalte damit die Werte 1 für Montag bis 7 für Sonntag.
Die Funktion DATUM
Als letzte Funktion brauchen wir noch die DATUM-Funktion. Diese liefert den Datumswert zurück, wenn man als Parameter Jahr, Monat und Tag übergibt:
=DATUM(Jahr;Monat;Tag)
Somit haben wir alle Hilfsmittel beisammen, um uns langsam der Ursprungsaufgabe zu nähern: Welche Tage gehören zu einer gegebenen Kalenderwoche?
Die Aufgabe
Wir geben also das gewünschte Jahr und die Kalenderwoche an und wollen dazu die zugehörigen Tage berechnen lassen, wie im folgenden Bild dargestellt:
Die Aufgabe
Bevor wir uns an die eigentliche Formel machen, stellen wir noch ein paar generelle Überlegungen an:
- Eine Kalenderwoche beginnt am Montag, was also Tag 1 der jeweiligen Woche sein soll
- Jede Woche hat 7 Tage
- Das Jahr beginnt mit KW 1
- Der 1. Januar kann, muss aber nicht zwingend in KW 1 liegen
Wenn wir also den ersten Montag des Jahres bestimmen könnten, dann bräuchten wir nur noch die Anzahl der Wochen multipliziert mit 7 Tagen zu addieren, um auf den Montag in unserer gesuchten Kalenderwochen zu kommen. In einem Flußdiagramm sehen diese Überlegungen folgendermaßen aus:
Der Ablauf als Diagramm
Und jetzt müssen wir das Ganze nur noch in die entsprechende Excel-Formel gießen. Dazu kombinieren wir die zu Beginn erläuterten Funktionen KALENDERWOCHE, DATUM und WOCHENTAG. Heraus kommt dabei folgendes „Monster“:
Die fertige Formel
Keine Angst, wir zerlegen die Formel nochmal in die Bestandteile. Dann wird das Bild hoffentlich ein wenig klarer:
Die Monster-Formel erläutert
Damit haben wir den ersten Tag unserer vorgegebenen Kalenderwoche. Bei Bedarf erhalten wir die restlichen Tage, indem wir einfach noch den Wert 1 zum jeweiligen Vortag addieren:
Die restlichen Tage bestimmen
Zur Kontrolle lassen wir uns mit der KALENDERWOCHE-Funktion noch bestätigen, dass das ermittelte Datum auch wirklich in der angegebenen Woche liegt:
Kontrolle ist besser
Passt! Und funktioniert ganz ISO-konform auch für Jahre, bei denen der 1. Januar nicht auf die KW 1 fällt:
Alles ISO-konform
Ich gebe zu, dass das eine etwas schwerere Geburt war. Aber wenn’s leicht wäre, könnte es ja jeder 🙂
Kennst Du vielleicht doch noch einen einfacheren Weg, um dieses Problem zu lösen? Dann lass es uns unten in den Kommentaren wissen.
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.
Klasse!!
Hab ich mich immer mit rumgeägert. Danke für die Lösung.
Beim Abtippen der „Monster“-Formel passieren leicht Fehler wegen den Klammern und Semikolons. Hier für diejenigen die sich das Abtippen ersparen wollen (oder habe ich nur den Download übersehen?):
=WENN(KALENDERWOCHE(DATUM(A3;1;1);21)=1;WENN(WOCHENTAG(DATUM(A3;1;1);11)=1;DATUM(A3;1;1)+7*(B3-1);DATUM(A3;1;1)-WOCHENTAG(DATUM(A3;1;1);11)+1+7*(B3-1));WENN(WOCHENTAG(DATUM(A3;1;1);11)=1;DATUM(A3;1;1)+7+7*(B3-1);DATUM(A3;1;1)+7-WOCHENTAG(DATUM(A3;1;1);11)+1+7*(B3-1)))
Weiter so!
Freue mich schon auf die nächsten Tipps und Tricks.
Timo
Hallo Timo,
nein, Du hast nichts übersehen, es gibt tatsächlich keinen Download. Aber Du hast Recht: Bei so einer Formel sind Tippfehler vorprogrammiert. Danke für’s Reinstellen.
Schöne Grüße,
Martin
Hallo Martin,
auf: http://www.excelformeln.de/formeln.html?welcher=186
gibt es noch andere Lösungen.
Gruß von Luschi
aus klein-Paris
Hallo Luschi,
vielen Dank für den Link. Ich hätte mich auch gewundert, wenn es nicht noch andere Lösungen gibt. Wenngleich ich auch zugeben muss, die genannten Formeln noch nicht alle ganz durchdrungen zu haben 🙂
Aber sie funktionieren, und das ist die Hauptsache!
Schöne Grüße,
Martin
Pingback: Excel-Quickies im Januar | Der Tabellen-Experte
Frage: Gibt es einen Unterschied zwischen Wochentag Typ2 und Typ11? Ich hab dazu gegoogelt und nichts gefunden.
Hallo Stephen,
meines Wissens gibt es keinen Unterschied. Ebenso bei Typ 1 und Typ 17, auch die scheinen identisch zu sein.
Schöne Grüße,
Martin
Das Kann Excel auch automatisch:
=KALENDERWOCHE(B4;21)
https://support.office.com/de-DE/article/KALENDERWOCHE-Funktion-E5C43A03-B4AB-426C-B411-B18C13C75340
Auch beim Wochentag muss man hier in Europa den richtigen Typ mit angeben, dann wird Montag =1 gesetzt…
Nur als Info für weitere Suchenden.
Hallo Hilfsscherrif,
die Funktion KALENDERWOCHE liefert die Kalenderwoche des Datums, nicht jedoch das Datum des ersten Tags in der betreffenden Woche. Oder habe ich irgendetwas überlesen?
Schöne Grüße,
Martin
Hallo Martin,
danke, für die tolle Formel.
Ich möchte gerade einen Wochenkalender (Mo. bis So.) damit erstellen. nun habe ich das Problem, das z.B.im Januar 2017 der erste an einem Sonntag ist und nicht angezeigt wird.
Wie kann ich in Excel dieses Problem umsetzen, dass alle Tage vor dem ersten Montag angezeigt werden oder bin ich total auf dem Holzweg, dass ich den Wochenkalender mit dieser Formel nicht erstellen kann.
Viele Grüße
Markus
Hallo Markus,
ich denke, hier musst Du ein wenig mit der WENN-Funktion herumspielen, um solche Fälle abzufangen. Pauschal lässt sich das nicht sagen, da es vom genauen Aufbau der Tabelle und von Deinen Formeln abhängt.
Schöne Grüße,
Martin
Hallo Martin,
deine Excel-Beiträge finde ich großartig. Was hältst du von folgender Variante (mit OpenOffice erstellt, sollte mit Excel aber auch funktionieren)?:
=(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7
oder mit Fehlermeldung:
=WENN(KALENDERWOCHE((DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7;21)B3;“Fehler“;(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7)
Grüße von Ansgar
hier noch eine Ergänzung: vor dem: …B3;“Fehler“;… muss das Ungleichzeichen „“ (ohne Anführungsstriche) eingefügt werden (das wurde leider beim Veröffentlichen rausgelöscht.
„=WENN(KALENDERWOCHE((DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7;21)<>B3;“Fehler“;(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7)“
Ansgar.
Hallo Ansgar,
vielen Dank für diese schöne Formel! Die funktioniert auch mit Excel. Kleine Änderung: Man muss zum Schluss den Wert 1 addieren, damit der Montag dabei herauskommt. Ansonsten wird immer der Sonntag ausgespuckt. Hier also nochmal die beiden angepassten Versionen:
=(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7+1
bzw.
=WENN(KALENDERWOCHE((DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7+1;21)<>B3;“Fehler“;(DATUM(A3;1;4)-WOCHENTAG(DATUM(A3;1;4);11))+(B3-1)*7+1)
In jedem Fall um Längen eleganter als das von mir im Artikel vorgestellte Monster 😉
Danke dafür,
Martin
=(MONATSENDE(A3;-1)+1)-(WOCHENTAG((MONATSENDE(A3;-1)+1);2))+1+(B3-1)*7
=DATUM(N1;1;7*M1-3-WOCHENTAG(DATUM(N1;;);3))
N1 = das JAHR
M1 = Kalenderwoche
Hi Rene,
vielen Dank für diese extrem kurze und elegante Lösung!
Schöne Grüße,
Martin
Auch ich bedanke mich herzlich für diese Lösung. Ich bedanke mich aber auch bei allen, die zu diesem Thema geschrieben haben!
Bernard
Hallo Bernard,
sehr gerne!
Schöne Grüße,
Martin
Hallo Rene,
wirklich super kurz und funktioniert wie ich’s brauche. Aber verstanden hab ichs nicht.
Du suchst ein Datum. Dieses Datum ist im Jahr N1 im ersten Monat und im x-ten Tag (Bei mir wars die KW 28 in 2019 und damit der 189. Tag in 2019). Auf so eine Idee wär ich gar nicht gekommen, ist aber genial.
Dieser 189. Tag berechnet sich aus 7 * die Anzahl der Kalenderwochen (im Beispiel also 7 * 28 = 196) abzüglich etwas.
Und genau dieses Etwas verstehe ich jetzt nicht. Was wird hier abgezogen? -3 (warum?) und dann ein bestimmter Wochentag???
Könntest du das mir zum Verständnis erklären?
Wie gesagt die Lösung ist super kurz und funktioniert tadellos, dafür schon mal meinen Respekt und Dank.
Grüße,
Kevin
Lieber Rene,
kannst du die Formel vielleicht nochmal genauer erklären? Oder gerne auch du @Martin Weiß 🙂
Ich häng mich ein wenig dran auf, warum -3 und ein unbestimmter Wochentag (DATUM(N1;;);3) subtrahiert werden.
LG
Karas
Hallo Karas,
da kann ich leider nicht viel beitragen, denn mir erschließt sich die Formel auch nur teilweise.
Der unbestimmte Wochentag (DATUM(N1;;);3) ergibt immer den Wochentag des 30.11. des jeweiligen Vorjahres. Man könnte auch schreiben DATUM(N1;0;0). Der nullte Monat ist in der Excel-Logik der Dezember (da der erste Monat der Januar ist). Der nullte Tag ist immer der letzte Tag des Vormonats. Und daraus ergibt sich der 30.11., also der letzte Tag vor dem Dezember.
Die WOCHENTAG-Funktion mit dem Parameter 3 liefert den laufenden Wochentag beginnend mit Montag = 0, Dienstag = 1, … Sonntag = 6.
Unter der Annahme, dass das gegebene Jahr in Zelle N1 = 2015, dann liefert die WOCHENTAG-Funktion Folgendes:
=WOCHENTAG(DATUM(N1;;);3) =
=WOCHENTAG(„30.11.2014“);3) = 6
da der 30.11.2014 auf einen Sonntag fällt.
Aber dann beißt’s ehrlich gesagt auch bei mir aus…
Lieber Martin,
vielen Dank schon einmal für die Erklärung! Ich bezweifle, dass Rene hier nochmal reinschaut und seine Formel auflöst :/ Die funktioniert ja auch eigentlich sehr gut, leider gibt es für die KW01 immer falsche Werte an. Hätte daher gerne mal gewusst was er sich dabei gedacht hat.
LG
Karas
Hallo Martin,
Hallo Karas,
Der =DATUM(N1;1;7*1-3) ergibt immer den 04. Januar des laufenden Jahres.
Die erste Kalenderwoche definiert sich dadurch, das der erste Donnerstag eines Jahres immer in der ersten Kalenderwoche liegt. Dadurch ergibt sich auch, dass der 04. Januar immer in der ersten KW liegen muss.
Davon ausgehend wird dann die KW multipliziert. Also KW 1 = 7*1-3 = 04 | KW 2 = 7*2-3 = 11 (siehe 11 = 04 + 7).
Allerdings wird mir nicht ganz klar wieso der letzte (Wochen-)Tag, 2 Monate zuvor abgezogen wird, könnte mir aber vorstellen, dass evtl. das Schaltjahr des Vorjahres (wenn es eins ist) eine Rolle spielen könnte.
Wenn es das ist, ist es ein kleiner Denkanstoß 🙂
Gruß Marcel
Hallo Marcel,
danke schon mal für die Ergänzungen, die Regel mit dem 4. Januar bzw. dem Donnerstag ist soweit grundsätzlich auch klar. Auch wenn sich die gesamte Logik hinter dieser schönen kurzen Formel von Rene sich mir immer noch nicht erschließt…
Schöne Grüße,
Martin
@Karas
[„Die funktioniert ja auch eigentlich sehr gut, leider gibt es für die KW01 immer falsche Werte an.“]
Bei mir passt das mit der ersten KW und dem ersten Tag. Zumindest vom Jahr 1901 an.
Das Jahr 1900 gibt mir einen #ZAHL! Fehler. Und von Jahr 1 bis 1899 sind die Werte „Falsch“, was allerdings daran liegt, das Excel einfach die Jahre nicht kennt. Der Excel Kalender fängt am 01.01.1900 mit der Zahl 1 an zu rechnen.
Gruß M.T-S,
Hallo Martin,
hallo Karas,
ich habe ein wenig mit der Formel herumgespielt, und hab diese soweit (denke ich) aufgedröselt.
=DATUM(N1;1;7*M1-3) ergibt für die erste KW immer den 04.01. und pro KW werden 7 Tage addiert.
Der 04.01. liegt IMMER in der ersten KW und darüber lässt sich der Wochentag über gleichnamige Funktion bestimmen.
Der ist eigentlich auch wichtig um den ersten Tag der ersten KW zu bestimmen und darüber dann alle weiteren Tage der jeweiligen KW. Einfach immer 7 Tage addieren also + (7 * KW) oder 7*M1.
Der Wochentag für den 04.01. ist tatsächlich auch in der Formel enthalten und zwar hier.
WOCHENTAG(DATUM(N1;;);3)
Das ergibt, wie du schon erläutert hast Martin, den letzten Tag vom Vorletzten Monat.
Wenn man nun den 30.11. des Vorjahres, vom 04.01. des aktuellen Jahres abzieht, ergibt das 35 Tage.
35 ist glatt durch 7 Teilbar. Das würde 5 ergeben, interessiert hier aber überhaupt nicht. Wichtig ist, das es durch 7 Teilbar ist ohne Rest, denn dadurch ist der Wochentag vom 04.01. genau der selbe wie der vom 30.11. des Vorjahres.
Das bedeutet dann, man könnte die Formel auch so schreiben:
=DATUM(N1;1;7*M1-3-WOCHENTAG(DATUM(N1;1;4);3))
Also Zusammenfassend:
Der erste Teil berechnet den 04.01. und addiert 7 Tage pro Kalenderwoche dazu (verschiebt also das Datum zur gesuchten Kalenderwoche).
Der zweite Teil zieht den Wochentag des 04.01. (bzw. des 30.11.) vom ermittelten Datum ab.
Dadurch erhält man das Datum des Montags der gesuchten KW.
So ich hoffe, dass ich das nun nicht zu kompliziert erklärt habe. 🙂
Gruß Marcel
=MAX(
KALENDERWOCHE(DATUM($D$43;12;26);21);
KALENDERWOCHE(DATUM($D$43;12;27);21);
KALENDERWOCHE(DATUM($D$43;12;28);21);
KALENDERWOCHE(DATUM($D$43;12;29);21);
KALENDERWOCHE(DATUM($D$43;12;30);21);
KALENDERWOCHE(DATUM($D$43;12;31);21))
Wie siehts damit aus?
Hallo Yannick,
die Formel liefert auf jeden Fall immer die letzte Kalenderwoche des angegebenen Jahres, was schon mal ein guter Ansatz ist. Aber leider nicht den gesuchten ersten Tag zu einer gegebenen Kalenderwoche.
Schöne Grüße,
Martin
Formel: Neujahr – (WOCHENTAG(Neujahr;11)-1)
Damit ermittel ich den Montag der Neujahrswoche. Alles weitere ist reine Addition/Multiplikation.
Grüße aus Berlin
Harald
Hallo Harald,
eine sehr elegante Lösung, vielen Dank dafür!
Schöne Grüße,
Martin
Hallo zusammen, sehr viele gute Inputs.
Ich möchte zur KW eine Datums aber auch gerne das korrekte Jahr in der Form [JJJJ]/KW[KW] ausgeben (zb 2004/KW53).
Dazu verwende ich folgende Formel (Datum ist in A2):
=JAHR((„4.1.“&JAHR(A2))+KALENDERWOCHE(A2;21)*7-4-REST(„2.1.“&JAHR(A2);7))&“/KW“&TEXT(KALENDERWOCHE(A2;21);“00“)
Funktioniert auch prima, allerdings hat die Formel anscheinend ein Problem bei Jahren mit KW53 (zB 2004)
Di, 28.12.2004: 2004/KW53
Mi, 29.12.2004: 2004/KW53
Do, 30.12.2004: 2004/KW53
Fr, 31.12.2004: 2004/KW53
Sa, 01.01.2005: 2006/KW53 => Jahr sollte 2004 sein
So, 02.01.2005: 2006/KW53 => Jahr sollte 2004 sein
Mo, 03.01.2005: 2005/KW01
Di, 04.01.2005: 2005/KW01
Ich komm leider auf meinen Denkfehler nicht drauf.
lg
Martin
Hallo Martin,
tut mir leid, da muss ich auch passen. Vielleicht hat ein anderer Leser eine Idee.
Schöne Grüße,
Martin
Hallo Martin,
Habs hinbekommen (Tips aus Herbers Excel Forum waren ausschlaggebend)
HIer die Formel (Datum in A2):
=VERKETTEN(„KW „;MIN(JAHR(A2-1-REST(A2-2;7)+4);JAHR(A2-REST(A2-1;7)+4));“_“;TEXT(KÜRZEN((A2-DATUM(JAHR(A2+3-REST(A2-2;7));1;REST(A2-2;7)-9))/7);“00″))
Wobei der Term
==> MIN(JAHR(A2-1-REST(A2-2;7)+4);JAHR(A2-REST(A2-1;7)+4))
das korrekte Jahr berechnet und der Term
==> TEXT(KÜRZEN((A2-DATUM(JAHR(A2+3-REST(A2-2;7));1;REST(A2-2;7)-9))/7);“00″)
die korrekte KW berechnet.
Ergebnis zB
Datum JJJJ+KW
Do, 30.12.2004 KW 2004_53
Fr, 31.12.2004 KW 2004_53
Sa, 01.01.2005 KW 2004_53
So, 02.01.2005 KW 2004_53
Mo, 03.01.2005 KW 2005_01
Di, 04.01.2005 KW 2005_01
Hallo Martin,
wow, diese Formel ist ein echtes „Schmankerl“! Danke fürs Teilen.
Schöne Grüße,
Martin
Das finde ich wirklich beeindruckend. Allerdings denke ich, dass die Wahl der Kalenderwoche System 2 doch bereits die Prüfung auf die Länge der 1. KW im Jahr enthält. Deshalb prüfe ich einfacher:
=WENN(WOCHENTAG($A4;2)=1;KALENDERWOCHE($A4;21);““)
m.E. stimmt das so.
GEHT VIEL EINFACHER
Formel für Wochenanfang Montag
=DATUM-REST(DATUM;7)+2
Formel für Wochenende Sonntag
=DATUM -REST(DATUM;7)+8
Liebe Grüße
H.-J.
Hallo Hans-Joachim,
du bist mein Held, diese Lösung ist einfach genial!
Vielen Dank dafür,
Martin
hallo Martin,
ist eigentlich bekannt, dass microsoft schon über 120 Jahre mit falschem Kalender arbeitet. Die Burschen haben der Menschheit
den 29.02.1900 untergejubelt, den es überhauptnicht gibt. Alle 4 Jahre gibt es ein Schaltjahr, das bei jedem 100. jedoch ausfällt,
aber bei jedem 400. doch dazukommt.
War es ein Glücksfehler, dass microsoft den 29.02.2000 im Kalender gelassen hat?
Güße H.-J.
Hallo Hans-Joachim,
dieser Fehler wird auch von Microsoft nicht gänzlich unter den Tisch gekehrt, aber sicherlich ist der in der breiten Anwenderbasis eher kaum bekannt. Vermutlich weil in vielen Fällen auch nicht relevant. Hier ein von Microsoft veröffentlichter Artikel über diese Thematik:
Excel nimmt fälschlicherweise an, dass das Jahr 1900 ein Schaltjahr ist
Schöne Grüße,
Martin
meine vor kurzem zugeschickte Formel für Wochenende und Wochenanfang hatte leider noch Macken.
Die korrekte Formel für Wochenanfang bezogen auf ein Datum muss lauten:
=[Datum]-WENN(REST([Datum];7)=0;5;WENN(REST([Datum];7)=1;6;REST([Datum];7)-2))
Die korrekte Formel für Wochenende bezogen auf ein Datum muss lauten:
=[Datum]-WENN(REST([Datum];7)=0;5;WENN(REST([Datum];7)=1;6;REST([Datum];7)-2))+6
viel Spass beim Benutzen
RO
Hallo Martin,
ich stoße gerade auf Deine Seite, da ich das Datum des Sonntags einer Vorjahres-(Vergleichs-)Woche auf Basis des heutigen Datums errechnen möchte. Dabei bin ich auf Deinen ursprünglichen Ansatz gestoßen und habe zwei Fragen zu Deinem Flussdiagramm. Ich komme bei folgenden Punkten auf andere Ergebnisse:
1.) Der Pfad 01.01. liegt NICHT in KW 1 des Jahres UND 01.01. IST ein Montag dürfte nicht möglich sein?!
Durch die Definition KW 1 = Woche mit erstem Donnerstag des Jahres folgt: Wenn 01.01. ein Montag, dann ist der darauf folgende Donnerstag (in der gleichen Woche) der erste Donnerstag des Jahres => damit MUSS der 01.01. in der KW 1 liegen
2.) Der Rechenweg unten links (01.01. ist Montag und liegt in KW 1) scheint mir falsch, da in dieser Konstellation der 01.01. ja schon in der KW 1 liegt. Der erste Rechenschritt „Nimm den 01.01. des Jahres und addiere 7 Tage (damit bist du in KW 1)“ bringt mich damit tatsächlich auf den 08.01. und damit KW 2.
Interpretiere ich hier irgendwas falsch?
Viele Grüße
Georg
Ergänzung: in der abgebildeten Formel direkt darunter scheinen mir auch die beiden „Montagsformeln“ gegenüber der Darstellung im Flussdiagramm genau getauscht zu sein (mein Punkt 2).
Der in der Formel enthaltene Pfad erstes Wenn falsch, drittes Wenn wahr wird meines Erachtens einfach in keiner Konstellation zutreffen (mein Punkt 1).
Hallo an Martin und an alle Kommentatoren,
vielen Dank für den Artikel und die wirklich guten Beiträge in den Kommentaren. Ich konnte daraus wirklich gute Inputs bekommen. Meine Problemstellung zum Thema Kalenderwoche ist folgende:
Ich möchte durch EIngabe eines Jahres das Datum des ersten Mittwochs in einer ungeraden KW ermitteln. Wobei dieser Mittwoche schon im neuen Jahr liegen muss. Dazu nutze ich
=DATUM(A1;1;7*1-1-WOCHENTAG(DATUM(A1;;)*3))
wobei in A1 das jeweilige Jahr eingeben wird. Das Ganze soweit wie gplant, funktioniert allerdings nur, solange der 01.01 eines Jahres kein Donnerstag ist. Das ist in 2026 das nächste Mal der Fall. Dort wäre der erste Mittwoch in einer ungeraden KW im neuen Jahr dann in der KW3. Da sbekomme ich mit meiner Formel oben nciht abgebildet. Hat jemand eine Idee wie man dies umsetzen könnte?
Vielen Dank
kleine Korrektur: *3 am Ende der Formel muss ;3 heißen.
Nach etwas Nachdenken über das Problem konnte ich die Fornmel zumindest auf
=DATUM(A4;1;7-WOCHENTAG(DATUM(A4;;);2))
reduzieren. Bin bei meinem Problem mit dem 01.01. eines Jahres an einem Donnerstag aber noch nicht weiter gekommen.
Hallo Christian,
hilft dir die Formel weiter?
=DATUM(A1;1;7*((ABRUNDEN((WOCHENTAG(DATUM(A1;1;1); 15))/7;0)*2)+1)-1-WOCHENTAG(DATUM(A1;1;4);3))
A1: Ist das jeweilige Jahr.
7*((ABRUNDEN((WOCHENTAG(DATUM(A1;1;1); 15))/7;0)*2)+1) : Berechnet ob der Mittwoch in KW 1 oder 3 liegt.
Genauer:
=> WOCHENTAG(DATUM(A1;1;1); 15) : Holt den Wochentag des 01.01. (Option 15 steht für „von Freitag = 1 bis Donnerstag = 7)
=> ABRUNDEN(‚Wochentag’/7;0) : Ergibt für Donnerstag 1 und alle anderen Tage 0. (Wenn der 01.01. ein Donnerstag ist, dann fällt der Mittwoch nicht in die erste KW).
=> *2)+1 : Ergibt KW 3 (01.01. = Donnerstag) bzw. KW 1. (01.01. Nicht Donnerstag)
Den Rest habe ich weiter oben genauer erklärt. Die Formel enspricht im Grunde der, mit dem sich der Montag einer Kalenderwoche ermitteln lässt mit dem Unterschied, dass hier nach Mittwoch gesucht wird und die KW auf 1 und 3 beschränkt wird:
=DATUM(N1;1;7*M1-3-WOCHENTAG(DATUM(N1;1;4);3))
Geändert/Ergänzt an der Stelle „M1-3“ durch
((ABRUNDEN((WOCHENTAG(DATUM(A1;1;1); 15))/7;0)*2)+1)-1
Gruß Marcel
Danke für die Antwort. Noch kann ich die Formel nicht ganz nachvollziehen, aber das kommt schon noch.
Beim ersten Test gibt die Formel jedenfalls immer den 04.01.xxxx zurück. Egal welches Jahr man in A1 einträgt. Eine Idee wieso? Oder mache ich was falsch? Ich suche mal…
Kommando zurück, das ging schnell. Hatte einen Übertragsungsfehler drinnen. Es funktioniert, auch für 2026. Dort wird korrekt der 14.01.2026 ausgegeben.
Wahnsinn, vielen Dank für die Hilfe!!!
Gruß
Christian
Hallo Martin, danke für die tolle Erklärung und die Formel. Sie funktioniert bestens!
Schöne Grüße
Gern geschehen, freut mich, wenn die Formel weiterhilft.
Schöne Grüße,
Martin
Hallo,
ich befasse mich auch gerade mit der Thematik und möchte das hier mal aufgreifen. Wenn ich euch frage, welches sind die Tage der 52. KW im Jahre 2022, was wäre eure Antwort. Zum Hintergrund: die 52. KW im Jahre 2022 gibt es zweimal; am Anfang und am Ende.
a) 01.01.2022 – 02.01.2022 (Sa – So)
b) 01.12.2022 – 31.12.2022 (Mo – Sa)
Wenn ich die Aufgabe dieser Seite zitieren darf: „Die Aufgabe: Wir geben also das gewünschte Jahr und die Kalenderwoche an und wollen dazu die zugehörigen Tage berechnen lassen, wie im folgenden Bild dargestellt“.
So gibt es mehr als einen Bereich; werden beide Bereiche berechnet?
Gruß
Stefan
Hallo Stefan,
ich glaube, das ist jetzt eher eine philosophische Frage. Wenn man nur die Kalenderwoche isoliert betrachtet stimmt das natürlich, dass sowohl der 01.01.2022 als auch 31.12.2022 jeweils einer KW 52 angehören. Aber für den 01.01.2022 ist es eben die KW 52 aus dem Vorjahr 2021, für den 31.12.2022 jedoch die KW 52 aus dem aktuellen Jahr 2022. Eine Kalenderwochenbetrachtung macht nur Sinn, wenn man auch das Jahr berücksichtigt, das der betreffenden KW zugeordnet ist. Und damit ergibt sich natürlich die irritierende Erkenntnis, dass ein Datum aus dem aktuellen Jahr einer KW des Vorjahres zugeordnet ist – oder bereits einer KW des Folgejahres, wie das beispielsweise beim Jahreswechsel 2024/2025 sein wird.
Schöne Grüße,
Martin
Ich wähle folgende Formel für dieses Problem:
F1 =Jahr
J1= gegebene KW
= Datum($F$1;1;1)+1-Wochentag(Datum($F$1;1;1);11)))+(7*J$1)= Montag der gegebenen KW
Irgendwie muss mensch wohl abfangen, dass eine KW vorgegeben wird, die nicht existiert.
z.B. so:
G1 Jahr; =WENN(KALENDERWOCHE(DATUM(G1;12;31);21)>KALENDERWOCHE(DATUM(G1;12;24);21);KALENDERWOCHE(DATUM(G1;12;31);21);KALENDERWOCHE(DATUM(G1;12;24);21))
Meine Formel greift bei Vorgabe einer nicht existenten KW dann automatisch auf die maximale KW zurück
Hallo Jürgen,
vielen Dank für die Formel. Sie liefert jedoch in manchen Fällen nicht das korrekte Datum.
Beispiel:
Für KW 53/2015 wird der 04.01.2016 geliefert, es müsste aber der 28.12.2015 sein. Es ist einfach eine kniffelige Angelegenheit…
Schöne Grüße,
Martin
Hallo Jürgen
Beschäftige mich auch damit eine nicht so einfache Geschichte
Diese Formel ist perfekt liefert genau den richtigen Montag für die erste Kalenderwoche egal welches Jahr man eingibt
Danke lg
F1 =Jahr
J1= gegebene KW
= Datum($F$1;1;1)+1-Wochentag(Datum($F$1;1;1);11)))+(7*J$1)= Montag der gegebenen KW