Office-Blog

Excel 2010: Mit der »21« zur exakten Kalenderwoche

16.03.2011   Dieter Schiecke

Bis Excel 2007 bedurfte es längerer Formeln, um exakt zu ermitteln, in welche Kalenderwoche ein Datum fällt. Die Funktion KALENDERWOCHE lieferte zwar klaglos ein Ergebnis, doch sie rechnete nicht nach DIN. Das hat sich nach über 20 Jahren Excel auch bei den Entwicklern des Programms herumgesprochen.

Mit Excel 2010 wurde nun Typ 21 eingeführt.

Qual der Wahl beim zweiten Argument der Funktion KALENDERWOCHE

Klingt unverständlich? Oder sieht verwirrend aus? Nur auf den ersten Blick!

Zum Verständnis hier zwei Demos, wie KALENDERWOCHE vor Excel 2010 funktionierte und was es mit dem neuen Typ 21 in Excel 2010 auf sich hat.

Natürlich gibt es ganz unten noch einen Kompatibilitätstest mit früheren Excel-Versionen.

 

Rückblick: Wie funktionierte KALENDERWOCHE vor Excel 2010?

Im unten gezeigten Beispiel soll in Excel 2007 für das Datum in Zelle B2 die zugehörige Kalenderwoche ermittelt werden. Es geht um den 1.1.2011, der in diesem Jahr auf einen Samstag fiel.

Kalenderwoche berechnen in Excel 2007

  • Das erste Argument ist klar: der Bezug auf B2, denn dort steht das Datum.
  • Beim zweiten Argument kann ich zwischen 1 und 2 wählen. Bei 1 beginnt die Woche am Sonntag, bei 2 am Montag. Ich wähle natürlich die 2.
  • Excel 2007 ermittelt, der 1.1.2011 falle in die KW 1. Das aber ist nach der in Deutschland üblichen Zählweise falsch. Denn danach müssen in der ersten Kalenderwoche des Jahres mindestens vier Tage liegen. Andres gesagt: Wenn der 1.1.des Jahres auf einen Wochentag zwischen Montag und Donnerstag.fällt, dann ist dies die erste Kalenderwoche.Ist dies nicht der Fall, gehört der 1.1. zu Kalenderwoche 52 oder 53 des vorangegangenen Jahres.
  • Doch leider kann ich der Funktion KALENDERWOCHE diese Einschränkung nicht mit auf den Weg geben. Daher musste in der Vergangenheit noch zusätzlich geprüft werden, ob der 1.1. auf einen der Wochentage von Montag bis Donnerstag fällt.Eine längere Formel halt.

So ermittelt Excel 2010 die Kalenderwoche zu einem Datum exakt

In Excel 2010 geht es ohne lange Formel ab – vorausgesetzt beim zweiten Argument wird der passende Typ gewählt. Excel 2010 stellt zehn Möglichkeiten zur Wahl.

Bei KALENDERWOCHE in Excel 2010 den Typ 21 wählen

  • Typ 1 und 2 sind aus Kompatibilitätsgründen noch an Bord – mehr dazu weiter unten.
  • Typ 11 bis 17 gehören zum System 1, bei dem nur wichtig ist, mit welchem Tag die Woche beginnt.
  • Typ 21 gehört zum System 2. Was dies bedeutet, macht ein Blick in die Hilfe klar.

Auszug aus der Hilfe von Excel 2010

Zum Verständnis, was jede der zehn Typ-Varianten bewirkt, hier eine Übersicht:
Zum Vergrößern einfach auf das Bild klicken

Übersicht über die Wirkung aller zehn Typen für die Funktion KALENDERWOCHE in Excel 2010

Und wie steht es um die Kompatibilität mit früheren Excel-Versionen?

Natürlich finde ich es toll, dass die Funktion KALENDERWOCHE jetzt nach DIN rechnet. Doch wie sieht es aus, wenn Tabellen mit Verwendung der neuen Typen in älteren Excel-Versionen bearbeitet werden?

Versuch 1: Ich fange speichere die oben gezeigte Aufstellung als Datei im Format 97-2003 (*.xls). Zwar bekomme ich den unten gezeigten Kompatibilitätshinweis, aber der bezieht sich nur auf die von mir verwendeten Farben , die es so in 2003 noch nicht gab (da waren nur 56 statt 16 Mio. Farben verfügbar).

Die Kompatibilitätsmeldung in Excel 2010 verschweigt etwas Wichtiges

Dass ich mit der erweiterten Funktionalität von KALENDERWOCHE in 2007 und 2003 Probleme haben werde, darauf weist mich die Kompatibilitätsprüfung leider nicht hin.

Doch beim weiteren Testen stellt sich natürlich etwas anderes heraus. Ich beginne mit Excel 2007:

  • Nach dem Öffnen der XLSX-Datei in Excel 2007 sieht zunächst alles “normal” aus.
  • Verändere ich aber in der Spalte Datum eine Angabe – tippe ich beispielsweise 26.12.2010 bei 27.12.2010 ein – versucht Excel, in den nachfolgenden Spalten die KW neu zu berechnen. Dann  werden sofort die Gartenzäune angezeigt. Der Fehlerindikator weist auf einen Zahlenfehler hin.
  • Eine Ausnahme bilden die beiden Spalten, wo Typ 1 und 2 verwendet wurden, denn die gab es ja in Excel 2007 bereits.

Fehler in Excel 2007 für Typ 11 bis 21

Gleiche Situation bei Excel 2003. Dort teste ich die oben bereits erwähnte XLS-Datei.

  • Nach dem Öffnen der Datei sehe ich zunächst nur, dass die Farben anders als im Original sind. Die Werte für die Kalenderwochen sind auch hier zunächst noch “in Ordnung”.
  • Beim Eintippen des neuen Datums 26.12.2010 in der linken Spalte erfolgt auch hier die Neuberechnung und prompt die Anzeige der Fehler ab der Spalte mit Typ 11.
  • In den Spalten, wo die Formeln Typ 1 und 2 enthalten, erfolgt die Neuberechnung ohne Fehler.

Fehler in Excel 2003 für Typ 11 bis 21

Über den Autor

Dieter Schiecke

- Zertifizierter Office-Trainer mit den Schwerpunkten Excel, PowerPoint und Word
- Chefredakteur von »PowerPoint aktuell«
- Projekterfahrener Berater beim Umstieg auf neue Office-Versionen
- Routinierter Dienstleister beim Einführen firmengerechter Office-Vorlagen
- Autor für Microsoft Press, dpunkt.verlag und das »Projekt Magazin«

23 Comments so far

WolfgangPosted on  4:45 pm - Sep 19, 2016

TOP!
Sehr gute Erklärung und hilfreich visualisiert! Vielen Dank!

AdrianPosted on  12:57 pm - Feb 9, 2016

TOP!
Sehr gute Erklärung und hilfreich visualisiert! Vielen Dank!

ChrisPosted on  10:36 pm - Feb 4, 2016

Und wieder was dazu gelernt. Vielen Dank für diese tolle Ausführungen.

ThomasPosted on  4:48 pm - Dez 14, 2015

Hallo zusammen,

ich habe nun das Problem, die Zelle mit der KW sich nicht automatisch aktualisiert.
Datum steht in C2 als Bsp. 13. März 2016. in der Zelle F2 soll dann die richtige Kalenderwoche stehen als Zahl. Das Jahr 2015 klappte wunderbar mit der Formel =KALENDERWOCHE(C2;21). Nun mit einmal klappt diese Formel nur manuell, d.h. ich klicke in Zelle F2 doppel und dann drücke ich Enter, dann geht es, aber wieso nur? Bitte um Hilfe.

    ThomasPosted on  10:16 am - Dez 16, 2015

    Ich hatte einen allgemeinen Fehler gehabt.

    „Lösung:

    Das Verfahren, nach dem Excel Formeln berechnet, hängt von den Einstellungen ab, die Sie unter „Extras, Optionen“ auf der Registerkarte „Berechnung“ vorgenommen haben. Ist dort die Option „Automatisch“ aktiviert, berechnet Excel bei jeder Zelleingabe von sich aus alle Formeln der ganzen Arbeitsmappe neu.“

    LG

RolandPosted on  9:07 pm - Sep 9, 2015

Wie kann ich denn aus einer kw ein automatisches Wochen datum machen .
Also in Spalte D1 generiere ich immer aktuell die kw . nun will ich in D2 das Datum zum Bsp. Von Montag bis Freitag stehen haben also D1 Kw2 Und in D2 05.Jan – 09.Jan 2015 .

    HorstPosted on  11:34 am - Sep 11, 2015

    Die Kalenderwoche und das Jahr stehe in A1 und B1.

    Dann ist =DATUM(B1;1;1)+7-WOCHENTAG(DATUM(B1;1;1);15) der erste Donnerstag des Jahres.

    =DATUM(B1;1;1)-WOCHENTAG(DATUM(B1;1;1);15)-4+A1*7 ist der Montag der KW A1.
    =DATUM(B1;1;1)-WOCHENTAG(DATUM(B1;1;1);15)+1+A1*7 ist der entsprechende Freitag.
    =DATUM(B1;1;1)-WOCHENTAG(DATUM(B1;1;1);15)+3+A1*7 der entsprechende Sonntag.

    Die genannten Ergebnis-Zellen müssen natürlich als Datum formatiert sein. Die Formeln gelten für KW1 bis KW52 bzw. KW53. Ob KW53 jeweils in dem geforderten Kalenderjahr gehört, muss man noch getrennt prüfen.

HorstPosted on  3:22 pm - Sep 9, 2015

Die Lösung vom 8.Sept. 2015 enthielt leider nur die Fälle, in denen am Jahresanfang der Jahrgang des letzten Jahres gebraucht wird (z.B. 1.1.2016 liegt in kw53/2015). Es gibt aber auch Fälle, in denen am Jahresende der Jahrgang des nächsten Jahres gebraucht wird (z.B. 31.12.2018 liegt in kw01/2019).

Es muss also heißen:
US-Kalenderwoche:
=“wk“&TEXT(KALENDERWOCHE(A1);“00″)&“/“&JAHR(A1)

EUR-Kalenderwoche:
=“kw“&TEXT(KALENDERWOCHE(A1;21);“00″)&“/“&JAHR(A1)-UND(KALENDERWOCHE(A1;21)>50;MONAT(A1)=1)+UND(KALENDERWOCHE(A1;21)=1;MONAT(A1)=12)

oder kürzer:

=“kw“&TEXT(KALENDERWOCHE(A1;21);“00″)&“/“&JAHR(A1)+WAHL((KALENDERWOCHE(A1;21)-MONAT(A1))/50+2;1;0;-1)

    HorstPosted on  2:48 pm - Sep 14, 2015

    Die meisten Einträge auf dieser Seite (auch meiner hier drüber) führen beim Kopieren nach Excel übrigens zum Fehler #NAME?, da die Anführungszeichen (Code 34; straight quote marks) offenbar automatisch in „left curly quotemarks“ oder „right curly quotemarks“ umgesetzt worden sind. Also bitte in Excel-Formeln als Anführungszeichen nur die geraden Anführungszeichen benutzen (Code 34).

PeterPosted on  8:41 am - Aug 27, 2015

Kalenderwoche „21“ scheint für 2016 nicht korrekt zu funktionieren. Der 1. Januar wird immer als KW 1 ausgegeben, der 4. Januar entsprechend bereits als KW 2.
Lösung „Kürzen“ hingegen liefert korrekte Werte.

AndreasPosted on  9:38 pm - Jul 21, 2015

So geht es in allen Versionen

=KÜRZEN((D72-DATUM(JAHR(D72+3-REST(D72-2;7));1;REST(D72-2;7)-9))/7)

Hier steht das Datum in D72

Aus einem anderen Forum.

DanielPosted on  1:09 pm - Jul 14, 2015

Danke für den Tipp. Hat fürs 2015 jetzt ganz toll geholfen!

JeannettePosted on  8:52 pm - Feb 15, 2015

Hmm tolles Problem und keine Lösung die überall funktioniert.

Hier ein Lösungsansatz:
Die offizielle Berechnung gibt es uns doch fast schon vor. Die Woche die mehr 4 und mehr Tage hat ist die Woche 1

=WENN((WOCHENTAG(A1;2))<=4;1;"53")

    MarioPosted on  9:59 am - Mai 19, 2015

    Diese Lösung funktioniert aber nur, wenn in A1 der 01.01.xxxx steht.
    Es ist also maximal eine Hilfsformel dessen Ergebnis dann weiter in Bedingungen eingebunden werden müsste.
    Oder hast Du dafür eine Lösung?

MikePosted on  11:59 am - Jun 13, 2014

Die Kommentare zum Thema Woche + Jahr sind zwar schön, aber leider nicht für alle Fälle korrekt. Um den Jahreswechsel kann das Vorgehen leider zu falschen Ergebnissen führen.

So ist z.B. der 29.12.2014 in der KW01/15 (zumindest bei Verwendung von KALENDERWOCHE(x;21) ergibt sich KW 1). Obige Formeln würden aber KW01/14 liefern, da JAHR(29.12.2014)=2014.

Eine richtig einfache Lösung für diese Problematik suche ich noch, insofern bin ich für Tipps dankbar ;-)

Karsten MichelPosted on  4:34 pm - Aug 2, 2013

Ich hatte das Problem, dass ich die Kalenderwoche bezogen auf ein Datum im Format Woche / Jahr darstellen wollte.
Zusammen mit Kollegen habe ich dieses nun gelöst und möchte Euch die Lösung zur Veröffentlichung vorstellen.
1. unter der Funktion =KALENDERWOCHE(A1;21) wird nur die Wochennummer angezeigt.
2. durch folgende Erweiterung dieser Funktion in:
=WENN(Q5=““;““;(KALENDERWOCHE(A1;21)&“ / „&JAHR(A1)-2000))
erscheint nun für das Datum 10.10.2012
41 / 12

    Karin R.Posted on  5:47 pm - Aug 2, 2013

    Hallo,
    das Konstrukt mit -2000 ist etwas umständlich.
    Wie wäre es mit der folgenden Kombination von KALENDERWOCHE, JAHR und TEXT?
    =KALENDERWOCHE(A1;21)&“/“&TEXT(JAHR(A1);“JJ“)

    Ich persönlich würde allerdings eine vierstellige Jahreszahl vorziehen, also:
    =KALENDERWOCHE(A1;21)&“/“&TEXT(JAHR(A1);“JJJJ“)

    Gruß Karin

SaschaPosted on  1:18 pm - Jan 31, 2012

Sehr schöne Aufbereitung!

Danke

JörnPosted on  9:41 am - Apr 15, 2011

Das finde ich total schick – danke für diese Info. Die Microsoft-Hilfe zu Excel ist nämlich nicht hilfreich in dieser Beziehung…