Excel 2010: Mit der »21« zur exakten Kalenderwoche

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

7 Kommentare zu „Excel 2010: Mit der »21« zur exakten Kalenderwoche“

  • Mike:

    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 Michel:

    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.:

      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

  • Sascha:

    Sehr schöne Aufbereitung!

    Danke

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

Kommentieren