Wie prüft man in Excel, ob ein Datum auf einen gesetzlichen Feiertag fällt, und wie zählt man Tage unter Ausschluss dieser Feiertage?

Bevor Sie prüfen, ob ein Datum ein gesetzlicher Feiertag ist, müssen Sie zunächst alle Feiertage innerhalb der vorgegebenen Jahre in Excel auflisten. Eine Tabelle wie im folgenden Screenshot dargestellt vereinfacht Ihnen diese Aufgabe erheblich.

Es gibt drei Arten amerikanischer gesetzlicher Feiertage:

• (1) Die erste Art ist ein gesetzlicher Feiertag an einem festen Datum, wie beispielsweise der Neujahrstag am 1. Januar. Den Neujahrstag können Sie ganz einfach mit der Formel =DATUM(Gegebenes Jahr;1;1) berechnen;
• (2) Die zweite Art ist ein gesetzlicher Feiertag, der stets auf einen bestimmten Wochentag fällt – wie beispielsweise der Präsidententag. Diesen können Sie ganz einfach mit folgender Formel berechnen:=DATUM(Gegebenes Jahr;1;1)+14+WAHL(WOCHENTAG(DATUM(Gegebenes Jahr;1;1)));1;0;6;5;4;3;2);
• (3) Und schließlich der Memorial Day: Berechnen Sie ihn ganz einfach mit der Formel =DATUM(Gegebenes Jahr;6;1)-WOCHENTAG(DATUM(Gegebenes Jahr;6;6)).

Hier liste ich Formeln auf, mit denen Sie sämtliche gesetzlichen Feiertage in der folgenden Tabelle berechnen können. Geben Sie einfach die jeweilige Formel in die entsprechende Zelle ein und drücken Sie anschließend die Enter-Taste.

Hinweis: In den Formeln der obigen Tabelle ist C1 die Bezugszelle für das angegebene Jahr. In unserem Beispiel steht sie für das Jahr 2015 – passen Sie sie einfach je nach Bedarf an.

Mit diesen Formeln berechnen Sie mühelos die Daten der gesetzlichen Feiertage für vorgegebene Jahre – siehe folgender Screenshot:

Nachdem Sie alle Daten der gesetzlichen Feiertage für bestimmte Jahre aufgelistet haben, können Sie mit Excel-Formeln ganz einfach prüfen, ob ein Datum ein gesetzlicher Feiertag ist. Angenommen, Sie haben eine Datumsliste wie im folgenden Screenshot gezeigt – ich zeige Ihnen jetzt, wie Sie das mühelos umsetzen können.

Wählen Sie eine leere Zelle neben der Datumsliste aus – beispielsweise Zelle B18 –, geben Sie die Formel =IF(COUNTIF($C$2:$D$11,A18),"Holiday",„No") ein und ziehen Sie anschließend den Ausfüllknauf auf den gewünschten Bereich. Siehe obiger Screenshot:

Hinweise:

(1) In der Formel =IF(COUNTIF($C[[PH_20]]:$D[[PH_19]],A18),"Holiday",„No") steht $C$2:$D$11 für den Bereich, in dem die gesetzlichen Feiertage bestimmter Jahre aufgelistet sind, und A18 enthält das zu prüfende Datum. Sie können diese Bezüge ganz einfach an Ihre Anforderungen anpassen. Die Formel liefert „Feiertag“, wenn das betreffende Datum ein gesetzlicher Feiertag ist – andernfalls „Nein“.

(2) Alternativ können Sie auch diese Matrixformel verwenden: =IF(OR($C$2:$D$11=A18),"Holiday",„NO"), um zu prüfen, ob das entsprechende Datum ein Feiertag ist oder nicht.

In Teil 1 haben wir bereits sämtliche Feiertage eines vorgegebenen Jahres aufgelistet. Nun führt Sie diese Methode Schritt für Schritt durch die Berechnung der Anzahl der Tage zwischen zwei Daten – unter Ausschluss aller Wochenenden und Feiertage innerhalb des angegebenen Datumsbereichs.

Wählen Sie eine leere Zelle aus, in der die Anzahl der Arbeitstage angezeigt werden soll, und geben Sie die Formel =NETTOARBEITSTAGE(E1;E2;B2:B10) ein. Drücken Sie anschließend die Enter-Taste.

Hinweis: In den obigen Zellen ist E1 das Startdatum des festgelegten Datumsbereichs, E2 das Enddatum und B2:B10 die Feiertagsliste, die wir in Teil 1 erstellt haben.

Sie erhalten nun die Anzahl der Tage zwischen den beiden Daten – unter Ausschluss von Wochenenden und Feiertagen im festgelegten Datumsbereich.