Heute ist wieder der besondere Tag, den es nur alle vier Jahre gibt: Der 29. Februar. Wenn auf Deinem Handy (Kalender, Armbanduhr…) also schon der erste März angezeigt, dann hat hier offensichtlich irgendwer ignoriert, dass 2016 ein Schaltjahr ist.
Wie landläufig bekannt ist, finden Schaltjahre nur alle vier Jahre statt. Aber das ist nicht die ganze Wahrheit. Es sind genau genommen 3 Regeln, die bei der Beurteilung eines Schaltjahres zu berücksichtigen sind.
Welche genau das sind und vor allem wie man das in eine schicke Excel-Formel verpackt, erklärt der heutige Artikel.
Und so geht’s:
Wann spricht man von einem Schaltjahr?
Wer es ganz genau wissen möchte, dem sei Wikipedia ans Herz gelegt. Auf den Punkt gebracht, müssen die folgenden Regeln geprüft werden:
Ist die Jahreszahl…
- …ohne Rest durch 4 teilbar? –> Schaltjahr
- …ohne Rest durch 100 teilbar? –> kein Schaltjahr
- …ohne Rest durch 400 teilbar? –> doch wieder ein Schaltjahr
Oder etwas anders formuliert:
Glatte Jahrhunderte (1800, 1900, 2000…) sind demnach Schaltjahre, wenn Sie durch 400 ohne Rest teilbar sind. Somit war 1600 ein Schaltjahr, 1700 jedoch nicht. Alle anderen Jahre sind dann Schaltjahre, wenn sie sich ohne Rest durch 4 teilen lassen.
Und wie berechnet man das jetzt mit Excel? Dazu gibt es die unterschiedlichsten Ansätze. Die Datei mit allen gezeigten Beispielen kannst du bei Bedarf hier herunterladen.
Wenn eine Jahreszahl gegeben ist
Es gibt sicherlich eine Vielzahl an Lösungen für das Schaltjahrproblem. Hier sind drei davon, wenn in einer Zelle lediglich eine Jahreszahl angegeben ist.
Methode 1:
Nehmen wir an, in Zelle B4 befindet sich die Jahreszahl, die wir untersuchen wollen. Mit Hilfe der REST-Funktion und einer WENN-Abfrage lässt sich mit der folgenden Formel bestimmen, ob das angegebene Jahr ein Schaltjahr ist oder nicht:
=WENN((REST(B4;4)=0)-(REST(B4;100)=0)+(REST(B4;400)=0)=0;"kein Schaltjahr";"Schaltjahr")
Methode 1: Jahreszahl + WENN-Funktion
Methode 2:
Anstelle der WENN-Funktion kann man auch die WAHL-Funktion nutzen. Außerdem wird zur Abbildung der Regeln eine Kombination aus UND-Funktion und ODER-Funktion verwendet:
=WAHL(ODER(UND(REST(B4;4)=0;REST(B4;100)<>0);REST(B4;400)=0)+1;"kein Schaltjahr";"Schaltjahr")
Methode 2: Jahreszahl + WAHL-Funktion
Methode 3:
Und schließlich noch eine dritte Variante, bei der wir auf die Datumsfunktionen TAG und DATUM zurückgreifen. Hier prüfe ich, ob der 29. ein gültiges Datum ergibt:
=WENN(UND(TAG(DATUM(B4;3;0))=29;B4<>1900);"Schaltjahr";"kein Schaltjahr")
Dabei nutzen wir einen kleinen Trick in der DATUM-Funktion:
DATUM(B4;3;0)
Das wäre also der 0. Tag im März, was dem letzten Tag des Februars gleichkommt.
Außerdem schließe ich im zweiten Teil der UND-Bedingung das Jahr 1900 noch aus, denn für dieses Jahr berechnet die DATUM-Funktion kein korrektes Ergebnis: Excel erlaubt den 29.02.1900 als gültiges Datum, obwohl dieser Tag nicht existiert hat!
Methode 3: Jahreszahl + WENN-Funktion
Wichtig: Die letzte Methode funktioniert nur bei einer Jahresangabe ab 1900, denn die Excel-Zeitrechnung – und damit die Datumsfunktionen – beginnt erst am 01.01.1900.
Wenn ein Datum gegeben ist
Liegt hingegen in der Zelle eine beliebige Datumsangabe vor, dann lässt sich mit den folgenden Methoden prüfen, ob es sich um ein Schaltjahr handelt. Es sind vom Ansatz her die gleichen Methoden wie oben, lediglich erweitert um die JAHR-Funktion.
Wichtig: Alle angegebenen Methoden funktionieren nur bei echten Datumsangaben ab dem 01.01.1900, dem Beginn der Excel-Zeitrechnung.
Methode 1:
=WENN((REST(JAHR(B4);4)=0)-(REST(JAHR(B4);100)=0)+(REST(JAHR(B4);400)=0)=0;"kein Schaltjahr";"Schaltjahr")
Methode 1: Datum + WENN-Funktion
Methode 2:
=WAHL(ODER(UND(REST(JAHR(B4);4)=0;REST(JAHR(B4);100)<>0);REST(JAHR(B4);400)=0)+1;"kein Schaltjahr";"Schaltjahr")
Methode 2: Datum + WAHL-Funktion
Methode 3:
=WENN(UND(TAG(DATUM(JAHR(B4);3;0))=29;JAHR(B4)<>1900);"Schaltjahr";"kein Schaltjahr")
Methode 3: Datum + WENN-Funktion
So, und in 4 Jahren frage ich diese Formeln hier ab!
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.
Hallo Martin,
eine schoene Zusammenstellung hast du uns zum Schaltjahr offeriert.
Ich moechte noch eine weitere Moeglichkeit drauflegen, ist mir spontan eingefallen:
Ab A1 schreibe ich die Jahreszahlen, z.B. ab 1985 in die Spalte. In B1 schreibe ich die Formel
=WENN(DATEDIF(„01.01.“&A1;“31.12.“&A1;“D“)+1=366;“Schaltjahr“;““)
Die Formel ziehe ich bis unten durch.
Das sollte so auch gehen.
Viele Grueße
Gerhard
Hallo Gerhard,
vielen Dank für diese schöne und wirklich clevere Alternative!
Für alle Leser, denen die DATEDIF-Funktion bisher unbekannt war, habe ich hier noch einen kleinen Artikel geschrieben:
https://www.tabellenexperte.de/datedif-sage-mir-wie-alt-du-bist/
Schöne Grüße,
Martin
Hallo Martin und Gerhard,
eine schöne Formelzusammenstellung von euch beiden zum heutigen „besonderen“ Tag. Besonders die DATEDIF ist ein interessanter Tipp!
Viele Grüße
Detlev
Hallo Martin,
alle deine Formeln (und auch die Formel von Gerhard Pundt) gelten für den gregorianischen Kalender. Der wurde bei uns am 15.10.1582 eingeführt. In GB und USA gilt der gregorianische Kalender erst seit 14.9.1752 und in Russland erst seit 14.2.1918.
Leider funktionieren alle Excel-Datumsfunktionen erst ab dem 1.01.1900. Zu allem Überdruss wird in Excel das Jahr 1900 fälschlicherweise als Schaltjahr angenommen!
Mit einer zusätzlichen WENN-Funktion kann die Formel angepasst werden. Mit einer weiteren, kleinen Modifikation kann auch die Methode 3 so angepasst werden, dass sie auch vor 1900 funktioniert:
=WENN( WENN($B$4; WENN($B$4<1582; REST($B$4;4)=0; TAG(DATUM($B$4+400;3;0))=29); NV()); "Schaltjahr"; "kein Schaltjahr")
Viele Grüße,
Reimund
Hallo Reimund,
du hast vollkommen recht, vielen Dank für die Hinweise und die tolle Formel!
Schöne Grüße,
Martin
Leider erkennt excel2003 bei der TAGE360-Funktion nicht, dass der 29.02.2024 der letzte Tag des Monats ist und der Zeitraum vom 01.12.2023 – 29.02.2024 (=Tage360(A1;B1;WAHR)+1) nicht 89, sondern 90 ergibt… Der Februar 2024 wird nur mit 29 Tagen addiert statt mit 30. Ist bei Zinsberechnung leider falsch.
Da müsste ich dann für diese Differenz noch eine Bedingung einbauen oder die Formel (Zinsberechnung seit 2000) für diesen Zeitraum manuell ändern. Schade, die Formeln für die Ermittlung der Schaltjahre sind sicher ausgefeilt, plustern die eigentlich kleine Tabelle aber unnötig auf. Manchmal ist dann doch noch der Mensch gefragt für individuelle Problemlösung.
– Oder gibt es doch noch eine elegante Lösung, die ich bei Datedif in den verschiedensten Varianten zur „Verbesserung“ der eigentlich passenden Tage360-Funktion nicht gefunden habe? Hab ja schon alle Zeiträume in Jahre, Monate, Tage zerlegt und dann wieder (J*360+M*30+T+1) addiert, erhalte aber jedes Mal irgendwo eine Differenz zu den tatsächlichen Zinstagen. – Und irgendwann ist natürlich Schluss mit Rumbasteln, sonst ist es schneller am Tischrechner erledigt.
Frohes Schaltjahr!
Hallo Lilli,
mir ist jetzt keine Excel-Funktion bekannt, in der Schaltjahre explizit berücksichtigt werden. Das ist ja überhaupt erst der Grund, warum man hier so wilde Verrenkungen anstellen muss, um eine relativ banale Tatsache berechnen zu können.
Daher kann ich dir leider keinen Tipp geben, wie man das elegant beispielsweise in Zinsberechnungen einfließen lassen kann. Vielleicht hat ja ein anderer Leser dafür eine Lösung.
Schöne Grüße,
Martin