Die letzten Wochen waren für mich beruflich sehr hektisch. Wochentage sind verrückt und die Wochenenden sind nie genug.
Anfang dieser Woche ging ich unseren jährlichen Urlaubskalender durch, um nach dem nächsten Feiertag zu suchen und wenn möglich einen Urlaub zu planen.
Und das brachte mich auf die Idee für diese Excel-Urlaubskalender-Vorlage.
In diesem Tutorial zeige ich Ihnen, wie Sie das Datum eines bestimmten Feiertags (z. B. Tag der Arbeit) berechnen.
Und du kannst auch ein tolles bekommen Excel-Vorlage für Urlaubslisten (kostenloser Download) am Ende dieses Blogs.
Klicken Sie hier, um die Vorlage herunterzuladen
Formel zur Berechnung von Feiertagen in Excel (Tag der Arbeit)
Hier ist die Formel, um das Datum für den Tag der Arbeit im Jahr 2021 zu erhalten (das ist der 1. Montag im September)
=DATUM(2021,9,1)+IF(WOCHENTAG(DATUM(2021,9,1),2)>1,7-WOCHENTAG(DATUM(2021,9,1),2)+1,1-WOCHENTAG( DATUM(2021,9,1),2))
Es gibt das Datum als 07. September 2021 zurück, was das Datum des Arbeitstages im Jahr 2021 ist.
Diese Formel kann leicht angepasst werden, um das Datum von Feiertagen zu erhalten, die auf einen bestimmten Wochentag des Monats fallen (z. B. Präsidententag oder Gedenktag).
So funktioniert die Formel
Da wir wissen, dass der Tag der Arbeit der 1. Montag im September ist, beginnen wir mit dem 1. September 2021.
Die folgende Formel würde uns die Seriennummer vom 1. September 2021 liefern:
=DATUM(2021,9,1)
Jetzt müssen wir prüfen, ob der erste Tag im September ein Montag ist oder nicht. Wir tun dies mit der folgenden Bedingung in der IF-Funktion:
WOCHENTAG(DATUM(2021,9,1),2)>1
Die Funktion WOCHENTAG gibt eine Zahl basierend auf dem Wochentag an. Da ich 2 als zweites Argument verwendet habe, würde es 1 für Montag, 2 für Dienstag, 3 für Mittwoch usw. zurückgeben.
Wenn der 1. September kein Montag ist, wäre die obige IF-Bedingung WAHR und würde den Wert aus dem folgenden Teil der Formel zurückgeben:
7-WOCHENTAG(DATUM(2021,9,1),2)+1
Der obige Teil subtrahiert einfach die Wochentagsnummer von 7 und addiert 1, wodurch wir den Wert erhalten, den wir zum 1. September 2021 addieren sollten, um den ersten Montag zu erhalten.
Und falls der 1. September 2021 ein Montag ist, stellt die folgende Formel sicher, dass das Ergebnis der IF-Funktion 0 ist:
1-WOCHENTAG(DATUM(2021,9,1)
Unten ist eine Infografik, die dieselbe Erklärung zeigt (erstellt mit 2014 statt 2021).
In ähnlichen Zeilen, wenn Sie andere Feiertage berechnen müssen. So lässt sich beispielsweise der Presidents’ Day, der am dritten Montag im Februar stattfindet, nach folgender Formel berechnen:
=DATUM(B2,2,1)+IF(WOCHENTAG(DATUM(B2,2,1),2)>1,7-WOCHENTAG(DATUM(B2,2,1),2)+1,1-WOCHENTAG( DATUM(B2,2,1),2))+((3-1)*7)
In der obigen Formel bezieht sich B2 auf den Jahreswert.
Hinweis: Ostern ist ein schwer zu berechnender Feiertag. Ich habe gegoogelt und eine Formel gefunden, die zu funktionieren scheint. Allerdings habe ich keine Ahnung wie das geht 🙂BONUS EXCEL URLAUBSKALENDER VORLAGE
Hier ist eine KOSTENLOSE Excel-Vorlage für den Feiertagskalender, die Ihnen Folgendes bietet:
- Die Liste der Feiertage (im ausgewählten Jahr).
- Die Anzahl der Tage bis zum nächsten Feiertag (Zahl im blauen Feld). Dies funktioniert nur für das laufende Jahr.
- Die Anzahl der Tage bis zum nächsten langen Wochenende (Zahl im grauen Feld). Dies funktioniert nur für das laufende Jahr.
Alles, was Sie tun müssen, ist das Jahr aus der Dropdown-Liste auszuwählen, und es wird Ihnen automatisch die Liste der Feiertage im ausgewählten Jahr angezeigt.
Ich habe dies für die Feiertage in den USA erstellt, da es an festen Terminen oder Tagen im Monat stattfindet.
In Indien (wo ich lebe) hängen viele Feiertage von der Mondposition (und vielen anderen Faktoren) ab, und daher ist es schwierig, eine solche Urlaubsvorlage zu finden.
Excel-Vorlage für Feiertagskalender herunterladen
Excel-Funktionen zum Erstellen dieser Feiertagskalendervorlage: -> WOCHENTAG | DATUM | WENN.
Wenn Sie wissen möchten, wie diese Feiertagskalendervorlage erstellt wird, können Sie die Spalten H-J im Vorlagenblatt einblenden und die Formeln anzeigen. Sie können auch Sheet1 einblenden, in dem ich die für die Jahres-Dropdown-Liste verwendeten Daten gespeichert habe.
Die folgenden Excel-Vorlagen/Artikel könnten Ihnen auch gefallen:
- KOSTENLOSE monatliche und jährliche Excel-Kalendervorlage.
- Vorlage für den Reiseplan und die Packliste.
- Vorlage für Mitarbeiterurlaub/Urlaubsverfolgung.
- Kalender integriert mit einer To-Do-Listen-Vorlage.
- So berechnen Sie die Anzahl der Tage zwischen zwei Daten in Excel
