Wie überprüfe ich, ob ein Datum ein Feiertag ist, und zähle Tage außer Feiertagen in Excel?
Wie überprüfe ich, ob ein Datum ein Feiertag ist, und zähle Tage außer Feiertagen in Excel?
Sie haben beispielsweise eine Liste mit Daten und möchten überprüfen, ob in der Liste Feiertage vorhanden sind. Wie können Sie dies schnell erledigen? In diesem Artikel zeige ich Ihnen, wie Sie alle Daten amerikanischer Feiertage innerhalb bestimmter Jahre berechnen und dann in Excel leicht überprüfen können, ob ein Datum ein amerikanischer Feiertag ist.
- Teil 1: Berechnen Sie Feiertage innerhalb bestimmter Jahre in Excel
- Teil 2: Überprüfen Sie in Excel, ob ein Datum ein Feiertag ist
- Teil 3: Zählen Sie Tage zwischen zwei Tagen außer Wochenenden und Feiertagen in Excel
Teil 1: Berechnen Sie Feiertage mit einem bestimmten Jahr in Excel
Bevor Sie überprüfen, ob ein Datum ein Feiertag ist, müssen Sie alle Feiertage innerhalb bestimmter Jahre in Excel auflisten. Daher erleichtert die Vorbereitung einer Tabelle wie im folgenden Screenshot Ihre Arbeit.
Es gibt drei Arten von amerikanischen Feiertagen:
(1) Die erste Art ist ein Feiertag an einem festen Datum, beispielsweise am Neujahrstag, dem 1. Januar. Mit der Formel können wir den Neujahrstag leicht berechnen = DATUM (gegebenes Jahr, 1,1);
(2) Die zweite Art ist ein Feiertag an einem festen Wochentag wie dem Präsidententag. Mit der Formel können wir den Präsidententag leicht berechnen = DATUM (gegebenes Jahr, 1,1) + 14 + WÄHLEN (WOCHENTAG (DATUM (gegebenes Jahr, 1,1)), 1,0,6,5,4,3,2);
(3) Und die letzte Art ist der Gedenktag, wir können den Gedenktag leicht mit der Formel berechnen = DATUM (gegebenes Jahr, 6,1) - WOCHENTAG (DATUM (gegebenes Jahr, 6,6)).
Hier liste ich Formeln zur Berechnung aller Feiertage in der folgenden Tabelle auf. Geben Sie einfach die Formeln in die entsprechende Zelle ein und drücken Sie Enter Schlüssel eins nach dem anderen.
| Urlaub | Zelle | Formeln |
| Neujahrstag | C2 | = DATUM (C1,1,1) |
| Martin Luther King Jr. Tag | C3 | = DATUM (C1,1,1) + 14 + WÄHLEN (WOCHENTAG (DATUM (C1,1,1)), 1,0,6,5,4,3,2) |
| Präsidententag | C4 | = DATUM (C1,2,1) + 14 + WÄHLEN (WOCHENTAG (DATUM (C1,2,1)), 1,0,6,5,4,3,2) |
| Volkstrauertag | C5 | = DATUM (C1,6,1) - WOCHENTAG (DATUM (C1,6,6)) |
| Independence Day | C6 | = DATUM (C1,7,4) |
| Tag der Arbeit | C7 | = DATUM (C1,9,1) + WÄHLEN (WOCHENTAG (DATUM (C1,9,1)), 1,0,6,5,4,3,2) |
| Columbus Day | C8 | = DATUM (C1,10,1) + 7 + WÄHLEN (WOCHENTAG (DATUM (C1,10,1)), 1,0,6,5,4,3,2) |
| Veterans Day | C9 | = DATUM (C1,11,11) |
| Thanksgiving Day | C10 | = DATUM (C1,11,1) + 21 + WÄHLEN (WOCHENTAG (DATUM (C1,11,1)), 4,3,2,1,0,6,5) |
| Christmas Day | C11 | = DATUM (C1,12,25) |
Hinweis: In den Formeln in der obigen Tabelle ist C1 die Referenzzelle, die das angegebene Jahr lokalisiert. In unserem Beispiel bedeutet dies das Jahr 2015, und Sie können es je nach Ihren Anforderungen ändern.
Mit diesen Formeln können Sie auf einfache Weise Daten der Feiertage mit bestimmten Jahren berechnen. Siehe Screenshot unten:
Speichern Sie einen Bereich als AutoText-Eintrag (verbleibende Zellenformate und Formeln) für die zukünftige Wiederverwendung
Es muss sehr mühsam sein, Zellen zu verweisen und Formeln für die Berechnung jedes Feiertags anzuwenden. Kutools für Excel bietet eine nette Problemumgehung von Autotext Dienstprogramm zum Speichern des Bereichs als AutoText-Eintrag, der die Zellenformate und -formeln im Bereich beibehalten kann. Und dann werden Sie diesen Bereich mit nur einem Klick wiederverwenden. Die Arbeit wird einfach mit nur einem Klick, um diese Tabelle einzufügen und das Jahr in dieser Tabelle zu ändern!
Kutools for Excel - Laden Sie Excel mit über 300 wichtigen Tools auf. Genießen Sie eine 30-tägige KOSTENLOSE Testversion mit vollem Funktionsumfang, ohne dass eine Kreditkarte erforderlich ist! Hol es dir jetzt
Teil 2: Überprüfen Sie in Excel, ob ein Datum ein Feiertag ist
Nachdem wir alle Daten von Feiertagen bestimmter Jahre aufgelistet haben, können wir mit Formeln in Excel leicht überprüfen, ob ein Datum ein Feiertag ist oder nicht. Angenommen, Sie haben eine Datumsliste wie im folgenden Screenshot gezeigt, und ich werde die Möglichkeiten vorstellen, wie dies einfach durchgeführt werden kann.
Wählen Sie eine leere Zelle neben der Datumsliste aus, sagt Zelle B18, und geben Sie die Formel ein = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No") Ziehen Sie dann den Füllgriff in den gewünschten Bereich. Siehe Screenshot oben:
Notizen:
(1) In der Formel = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No") ist $ C $ 2: $ D $ 11 der Bereich der Feiertage in bestimmten Jahren und A18 ist die Zelle mit dem Datum, an dem Sie überprüfen möchten, ob es sich um einen Feiertag handelt, und Sie können sie je nach Bedarf ändern. Und diese Formel gibt "Feiertag" zurück, wenn das bestimmte Datum ein Feiertag ist, und "Nein", wenn dies nicht der Fall ist.
(2) Sie können diese Array-Formel auch anwenden = IF (OR ($ C $ 2: $ D $ 11 = A18), "Urlaub", "NEIN") um zu überprüfen, ob das entsprechende Datum ein Feiertag ist oder nicht.
Teil 3: Zählen Sie Tage zwischen zwei Tagen außer Wochenenden und Feiertagen in Excel
In Teil 1 haben wir alle Feiertage in einem bestimmten Jahr aufgelistet. Mit dieser Methode können Sie nun die Anzahl der Tage außer allen Wochenenden und Feiertagen in einem Datumsbereich zählen.
Wählen Sie eine leere Zelle aus, in der Sie die Anzahl der Tage zurückgeben möchten, und geben Sie die Formel ein = NETZWERKSTAGE (E1, E2, B2: B10) hinein und drücken Sie die Enter Key.
Note: In den obigen Zellen ist E1 das Startdatum des angegebenen Datumsbereichs, E2 das Enddatum und B2: B10 die Feiertagsliste, die wir in Teil 1 berechnen.
Jetzt erhalten Sie die Anzahl der Tage außer Wochenenden und Feiertagen im angegebenen Zeitraum.
Kopieren Sie Formeln genau / statisch, ohne die Zellreferenzen in Excel zu ändern
Kutools for Excel Exakte Kopie Mit dem Dienstprogramm können Sie problemlos mehrere Formeln exakt kopieren, ohne die Zellreferenzen in Excel zu ändern, und verhindern, dass relative Zellreferenzen automatisch aktualisiert werden.
Kutools for Excel - Laden Sie Excel mit über 300 wichtigen Tools auf. Genießen Sie eine 30-tägige KOSTENLOSE Testversion mit vollem Funktionsumfang, ohne dass eine Kreditkarte erforderlich ist! Hol es dir jetzt
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über 300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...
Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
- Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
- Zuletzt aktualisiert am .
