Excel und „die Feiertage“

• 28. Februar 2017
• 10

• Categories:

•  
  • Allgemein

• Tags:

•  Bewegliche Feiertage, Datums-Funktionen, Feiertage, Feste Feiertage, Funktionen, Ostersonntags-Formel

Feste und bewegliche Feiertage berechnen, letztere mit Hilfe einer Formel zum Berechnen des Ostersonntags:

Und ich fange wieder an mit ein paar schlauen Sprüchen zum Thema „Feiertag“: Feiertage waren ursprünglich „der religiösen Feier gewidmete, arbeitsfreie Tage“. Sie kommen in allen Kulturen und Völkern vor. Wenn ein Feiertag „durch die Rechtsordnung der einzelnen Staaten besonders geschützt“ wird, spricht man von gesetzlichen Feiertagen.

Feiertage in Deutschland

In Deutschland bestimmen die einzelnen Bundesländer die gesetzlichen Feiertage. Der einzige Feiertag, der durch den Bund (Staatsvertrag) festgelegt wurde, ist der Tag der Deutschen Einheit. Neben diesem Feiertag gibt es noch acht weitere Feiertag, über die sich alle 16 Bundesländer einig sind. Insgesamt also:

Neujahr / Karfreitag / Ostermontag / Tag der Arbeit / Christi Himmelfahrt / Pfingstmontag / Tag der Deutschen Einheit / 1. Weihnachtsfeiertag / 2. Weihnachtsfeiertag

Natürlich gilt das auch für den Ostersonntag und den Pfingstsonntag. Aber da sie als Sonntage ohnehin schon frei sind, werden sie hier nicht mitgezählt.

Feiertage, die nur in bestimmten Bundesländern oder Gebieten gefeiert werden, sind:

Heilige Drei Könige / Gründonnerstag / Fronleichnam / Augsburger Hohes Friedensfest / Mariä Himmelfahrt / Reformationstag / Allerheiligen / Buß- und Bettag

Feste – nicht bewegliche – Feiertage

Die nicht beweglichen Feiertag fallen in jedem Jahr auf das gleiche Datum. Es handelt sich dabei um:

• Neujahr                                          1. Januar
• Heilige Drei Könige                        6. Januar
• Tag der Arbeit                                1. Mai
• Augsburger Friedensfest               8. August
• Mariä Himmelfahrt                         15. August
• Tag der Deutschen Einheit            3. Oktober
• Reformationstag                            31. Oktober (Halloween ist kein deutscher Feiertag!)
• Allerheiligen                                   1. November
• 1. Weihnachtsfeiertag                   25. Dezember
• 2. Weihnachtsfeiertag                   26. Dezember

Die „Berechnung“ dieser Feiertage ist entsprechend einfach: Wenn Sie der Funktion DATUM (siehe Blog Excel und „Rechnen mit Datum“) die entsprechenden Werte für Jahr, Monat und Tag liefern, ermitteln Sie z.B. den Tag der Arbeit für das aktuelle Jahr:

               =DATUM(JAHR(HEUTE());5;1)

Bewegliche Feiertage

Hier wird es schon spannender, denn diese Feiertag fallen nicht immer auf das gleiche Datum. Sie können in unterschiedlichen Jahren sogar um über einen Monat „wandern“.

Der entscheidende Dreh- und Angelpunkt, an dem sich diese Feiertage orientieren, ist der Ostersonntag. Ausnahme ist der Buß- und Bettag (siehe unten).

Hier die Liste der beweglichen Feiertage und ihre „Lage“ im Verhältnis zum Ostersonntag

• Gründonnerstag               Donnerstag vor Ostersonntag                    = OS – 3
• Karfreitag                          Freitag vor Ostersonntag                           = OS – 2
• Ostersonntag                   Berechnung siehe unten
• Ostermontag                    Montag nach Ostersonntag                        = OS + 1
• Christi Himmelfahrt          39 Tage nach Ostersonntag                       = OS + 39
• Pfingstsonntag                49 Tage nach Ostersonntag                        = OS + 49
• Pfingstmontag                 50 Tage nach Ostersonntag                        = OS + 50
• Fronleichnam                   60 Tage nach Ostersonntag                       = OS + 60
• Buß- und Bettag              Mittwoch vor dem 23. November                 eigene Formel

Berechnung des Ostersonntags

Johann Carl Friedrich Gauß war ein bedeutender deutscher Mathematiker. 1807 entwickelte er eine Formel zur Berechnung des Ostersonntags, die er 1816 nochmal erweiterte.

Die Kurzform dieser Formel lautet – mit Excel-Funktionen ausgedrückt:

               =RUNDEN((TAG(MINUTE(A1/38)/2+55)&“.4.“&A1)/7;)*7-6

Dabei wird davon ausgegangen, daß die Jahreszahl, für die der Ostersonntag errechnet werden soll, in Feld A1 steht.

Der Nachteil dieser Formel ist, daß sie bei bestimmten Jahren nicht und ab dem Jahr 2079 (oder auch ab 2204) gar nicht mehr funktioniert. Wenn Sie, so wie ich, vorhaben, diese Formel in einem dieser Jahre in Jetzt-Zeit zu testen, dann verwenden Sie lieber diese Version:

                 =DATUM(A1;3;28)+REST(24-REST(A1;19)*10,63;29)-REST(KÜRZEN(A1*5/4)+REST(24-REST(A1;19)*10,63;29)+1;7)

Auch diese Formeln stammen nicht von mir, sondern wieder aus meinen Excel-Büchern.

Liste der berechneten Feiertage

Mit den dargestellten Formeln können Sie die für das aktuelle Jahr relevanten Feiertage ermitteln.

Für die Berechnung des Ostersonntags (oranges Feld) habe ich hier die einfache Formel verwendet.

Wie oben versprochen, finden Sie im grünen Feld die Formel zur Berechnung des Buß- und Bettags:

               =DATUM(A1;12;25)-WOCHENTAG(DATUM(A1;12;25);2)-32

Ich habe den absoluten Bezug auf Feld A1 verwendet. Sie können natürlich auch einen relativen Bezug verwenden oder auch den Bezug auf jedes beliebige Feld legen.

Und für die Faschings-Narrischen oder Karnevals-Jecken habe ich auch noch den Rosenmontag (Zeile 6) aufgenommen, obwohl er im eigentlichen Sinne kein Feiertag ist. Und das gleiche gilt für Silvester.

Praktisches Beispiel

Ich möchte Sie auf die Blogs Excel und „die Arbeitstage“ – Teil 1 und Excel und „die Arbeitstage“ – Teil 2 hinweisen. Hier habe ich Ihnen die Funktionen ARBEITSTAG, ARBEITSTAG.INTL, NETTOARBEITSTAGE und NETTOARBEITSTAGE.INTL vorgestellt. Sie können diese Funktionen mit einem Argument „Freie_Tage“ befüllen. Wenn Sie nun für dieses Argument den Bereich mit den errechneten Feiertagen (C4:C24) einfüllen, können Sie alle Feiertage für das Jahr 2017 berücksichtigen und so die Arbeitstage für dieses Jahr errechnen: