Direkt zum Inhalt

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

doc prüfen, ob Feiertag 1

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:

doc prüfen, ob Feiertag 2

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!


ad autoamerikanische Feiertage 1

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.

doc prüfen, ob Feiertag 6

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.


ad genaue Kopierformeln 3

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

Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate   |  Leere Zeilen löschen   |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren   |   Runde ohne Formel ...
Super-Lookup: VLookup mit mehreren Kriterien    VLookup mit mehreren Werten  |   VLookup über mehrere Blätter hinweg   |   Unscharfe Suche ....
Erweiterte Dropdown-Liste: Erstellen Sie schnell eine Dropdown-Liste   |  Abhängige Dropdown-Liste   |  Mehrfachauswahl Dropdown-Liste ....
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  |  Vergleichen Sie Bereiche und Spalten ...
Ausgewählte Funktionen: Rasterfokus   |  Designansicht   |   Große Formelleiste    Arbeitsmappen- und Blattmanager   |  Ressourcen (Autotext)   |  Datumsauswahl   |  Arbeitsblätter kombinieren   |  Zellen verschlüsseln/entschlüsseln    Senden Sie E-Mails nach Liste   |  Superfilter   |   Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets12 Text Tools (Text hinzufügen, Zeichen entfernen, ...)   |   50+ Chart Typen (Gantt-Diagramm, ...)   |   40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag, ...)   |   19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen, ...)   |   12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion, ...)   |   7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Zellen teilen, ...)   |   ... und mehr

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

kte tab 201905


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!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Like the Observed Holiday, I need to also recognize additional days off outside of the Holiday or observed holidays. Example: If Christmas is on a Friday, I need to calculate the days before and after (until New Years) off.
This comment was minimized by the moderator on the site
How would you calculate 15 calendar days from a given date including weekends but excluding holidays using a list/table of holiday dates?
This comment was minimized by the moderator on the site
How to make it show the actual name of the holiday instead of just "holiday"?
This comment was minimized by the moderator on the site
Hi KC,
You can change the text “holiday” to INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)) in the formula, and the whole formula will be changed to
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Please note that the dates you will check should be placed in one column.
This comment was minimized by the moderator on the site
Thanks, I have worked out a system to determine whether a public holiday is a weekday, but this also gives an alternative. The problem is that if one does it per month, then there are gaps between days where public holidays occur during weekdays. An example as below taking part of December 2017. the figures to immediate right of dates (Col B) are the WEEKDAY values. If date falls on a Saturday or Sunday (value 6 or 7) then the C Column reflects a blank cell ("") if a weekday the Cell has a "1", if a Public Holiday during a weekday then a "0" 21/12/2017 4 1 22/12/2017 5 1 23/12/2017 6 24/12/2017 7 25/12/2017 1 0 26/12/2017 2 0 27/12/2017 3 1 28/12/2017 4 1 29/12/2017 5 1 30/12/2017 6 31/12/2017 7 I can then sort manually using the Filter approach to get the 1's in one continuous column of rows without the blanks or 0's. Copy and paste to a worksheet where I can import the data into the temperature charts. I am trying to get the filter section automated either via formula by deleting all the 0's and blank cells with the resultant shifting up of cells containing the 1's, or via VBA. The ultimate prize would be combining the steps in Column A and Column C into one formula. The end game is to populate a temperature chart with the workday name and in the next corresponding row the day of the required month Mon Tue Thu Fri 7 8 10 11 Using August as an example where the 9th is a public holiday that falls during a work day, resulting in the data relating to the Wed being removed and the rest of the column shifting up one (or more) places. Then transposed into the above cells. I hope I am explaining with sufficient clarity :-)
This comment was minimized by the moderator on the site
How could I make this work for Federal Holiday? Meaning if the date of a holiday happens to fall on a weekend then the Federal holiday would either be Friday or Monday.
This comment was minimized by the moderator on the site
I used the formulas above to calculate the actual day of the holiday and made a second column for Observed holiday. I made this formula to accomplish this: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15))=7,B15-1,B15)). The cell reference B15 is referring to the holiday which is in the actual holiday column, in this case New Years Day. When the actual holiday falls on a Saturday, the Observed holiday will be listed as Friday and for actual holidays falling on Sunday, the observed holiday will be listed as Monday. Hope this helps.
This comment was minimized by the moderator on the site
This is an accurate function which will work for New Years Day that would fall on a weekend (years 2022 and 2023): =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1),2)>5))
This comment was minimized by the moderator on the site
trying to make a formula for subtracting CALENDAR DAYS and holidays. I have been able to figure out for WORKDAYS and HOLIDAY, but I cannot figure out how to do CALENDAR days and holidays. here is what I am currently using for WORKDAYS AND HOLIDAYS. Help! So I need this to be CALENDAR days instead of WORKDAYS.] =WORKDAY(B28-5,1,HOLIDAYS)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations