Wie hebt man Wochenenden und Feiertage in Excel hervor?
Beim Arbeiten mit täglichen Aufzeichnungstabellen in Excel empfiehlt es sich oft, Wochenenden und gesetzliche Feiertage visuell von regulären Werktagen abzuheben – so wird die Übersichtlichkeit Ihrer Daten deutlich verbessert. Diese Hervorhebung ist besonders wertvoll bei Anwesenheitslisten, Projektzeitplänen oder Geschäftsprotokollen, da sie nicht nur das visuelle Management erleichtert, sondern auch spätere Analysen unterstützt. Excel bietet hierfür mehrere Ansätze: etwa die bedingte Formatierung, benutzerdefinierte Hilfsformelspalten oder fortgeschrittene Automatisierung per VBA. Die beste Lösung hängt dabei von Ihrem Tabellenaufbau, der Häufigkeit, mit der neue Daten hinzugefügt werden, sowie der Dynamik Ihrer Feiertagsliste ab.
- Wochenenden und Feiertage mit Bedingte Formatierung verwenden hervorheben
- Excel-Formel – Verwenden einer Hilfsspalte zur Kennzeichnung von Wochenenden und Feiertagen
- VBA-Code – Automatisches Schattieren von Wochenenden und Feiertagen
Wochenenden und Feiertage mit Bedingte Formatierung verwenden hervorheben
Die Funktion Bedingte Formatierung verwenden von Excel ermöglicht es Ihnen, Wochenenden (typischerweise Samstage und Sonntage) und Feiertage innerhalb Ihrer Datumsaufzeichnungen automatisch hervorzuheben – ohne Berechnungen direkt in Ihre Daten einzufügen. Diese Methode eignet sich besonders für Benutzer, die ihre Arbeitsblätter häufig überprüfen oder aktualisieren und sofortige Farbhinweise für arbeitsfreie Tage wünschen, die sich dynamisch an Änderungen der Daten oder Feiertage anpassen.
So richten Sie Bedingte Formatierung verwenden für Wochenenden und Feiertage ein:
1. Wählen Sie den Bereich mit den Daten aus, die Sie hervorheben möchten.
2. Wechseln Sie zur Registerkarte Start, klicken Sie auf Bedingte Formatierung verwenden und wählen Sie Neue Regel, um den Regel-Editor zu öffnen. Siehe Screenshot:
3. Im Dialogfeld Neue Formatierungsregel:
- Wählen Sie aus der Regeltyp-Liste „Formel zur Bestimmung der zu formatierenden Zellen verwenden“ aus.
- Geben Sie im Formeleingabefeld die folgende Formel ein:=NETWORKDAYS($A2;$A2;$F$2:$F$6)=0
- Klicken Sie auf die Schaltfläche Formatieren, um Ihren Hervorhebungsstil festzulegen.
4. Wechseln Sie im Fenster Zellenformat festlegen zur Registerkarte Füllung und wählen Sie eine Hintergrundfarbe für Wochenenden und Feiertage aus. Diese Farbe hebt arbeitsfreie Tage zur schnellen visuellen Orientierung klar von regulären Tagen ab.
5. Klicken Sie zweimal auf OK, um die Dialogfelder zu schließen. Ihre gewählte Hervorhebung wird automatisch auf alle Daten im ausgewählten Bereich angewendet, die auf Wochenenden oder aufgeführte Feiertage fallen. Feiertage können jederzeit durch Bearbeitung Ihres Feiertagsbereichs aktualisiert werden – die Formatierung passt sich dann automatisch an.
Tipps & Fehlerbehebung: Falls die Hervorhebung nicht erscheint, überprüfen Sie Ihre Datumsformatierung – die Formel funktioniert nur mit echten Excel-Daten. Abweichungen im Format können dazu führen, dass die Regeln fehlschlagen. Passen Sie außerdem die Zellreferenz an, wenn Sie die Regel auf mehrere Spalten anwenden. Aktualisieren Sie zudem Ihre Feiertagsliste, sobald sich Ihre Daten über mehrere Jahre erstrecken, um stets die korrekten arbeitsfreien Tage abzubilden.
Vorteile: Keine zusätzlichen Spalten oder manuelle Arbeit nötig – alles bleibt vollständig dynamisch, selbst wenn Ihre Tabelle wächst oder sich die Feiertagsliste ändert.
Mögliche Einschränkungen: Die Verwendung bedingter Formatierung unterliegt einer maximalen Anzahl von Regeln pro Arbeitsblatt (selten erreicht), und übermäßig komplexe Formeln können bei großen Datensätzen die Arbeitsmappenleistung beeinträchtigen.
Entfesseln Sie die Magie von Excel mit KUTOOLS AI
- Intelligente Ausführung: Führen Sie Zelloperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Sprachbefehl.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
- VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln spielend leicht.
- Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.
Excel-Formel – Verwenden einer Hilfsspalte zur Kennzeichnung von Wochenenden und Feiertagen
Für Benutzer, die mehr Kontrolle über die Farbkodierung wünschen oder ihre Daten nach Arbeitstagen und arbeitsfreien Tagen sortieren oder filtern möchten, ist das Hinzufügen einer Hilfsspalte mit Formeln eine zuverlässige Lösung. Diese Technik kennzeichnet jede Zeile eindeutig als Wochenende, Feiertag oder regulären Werktag und eignet sich ideal zum Filtern, manuellen Formatieren oder Aggregieren Ihrer Daten.
Diese Methode ist besonders nützlich, wenn reine Farbkennzeichnung nicht ausreicht oder wenn Sie Summen für Wochenenden und Feiertage direkt berechnen möchten.
Angenommen, Ihre Daten befinden sich in Spalte A, beginnend ab Zeile 2, und Feiertage sind in F2:F6 aufgelistet. Fügen Sie eine neue Spalte neben Ihrer Datums-Spalte ein und geben Sie eine Überschrift wie „Tagetyp“ ein.
1. Geben Sie in Zelle B2 (Hilfsspalte) die folgende Formel ein:
=IF(OR(WEEKDAY(A2,2)>=6,COUNTIF($F$2:$F$6,A2)>0),"Weekend/Holiday","Workday")Diese Formel führt für jedes Datum zwei Prüfungen durch:
–WEEKDAY(A2;2)>=6: Behandelt Samstag (6) und Sonntag (7) als Wochenende (passen Sie dies entsprechend an, falls Ihre Wochenenden anders ausfallen).
–COUNTIF($F$2:$F$6;A2)>0: Prüft, ob das Datum mit einem Eintrag in der Feiertagsliste übereinstimmt.
2. Ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf weitere Zeilen zu übertragen. Sobald die Spalte ausgefüllt ist, filtern Sie die Tabelle nach „Wochenende/Feiertag“, um diese Zeilen zu isolieren oder manuell hervorzuheben. Falls Sie eine farbliche Kennzeichnung wünschen, können Sie zudem die bedingte Formatierung basierend auf dieser Spalte anwenden.
Tipps: Falls sich Ihre Wochenenden unterscheiden, passen Sie die Logik der Funktion WEEKDAY an (z. B. verwenden Sie 1 für Sonntag oder regionsspezifische Werte). Halten Sie Ihre Feiertagsliste stets aktuell. Sollte sich Ihr Datenbereich ändern, wenden Sie die Füllung bzw. Kopie bei Bedarf erneut an.
Vorteile: Hilfsspalten ermöglichen es Ihnen, mehr Filterbedingungen festzulegen und umfassendere Berichte zu erstellen. Sie können problemlos identifizieren, zählen oder bei Bedarf gezielt einfärben.
Nachteile: Es wird eine zusätzliche Spalte in Ihren Daten benötigt sowie eine manuelle Farbfüllung – es sei denn, Sie kombinieren dies mit weiteren Regeln zur bedingten Formatierung.
VBA-Code – Automatisches Schattieren von Wochenenden und Feiertagen
Wenn Sie Bereiche mit Aktualisierungsdaten häufig anpassen oder die Hervorhebung von Wochenenden und Feiertagen noch weiter automatisieren möchten – über das hinaus, was mit Formeln und bedingter Formatierung möglich ist –, ist ein einfaches VBA-Makro äußerst effizient. Ideal für Anwender, die umfangreiche Listen verwalten oder regelmäßig Arbeitsblätter erstellen.
Mit einem Makro färbt Excel Wochenenden und Feiertage in Ihrem gewählten Datumsbereich automatisch entsprechend Ihrer Feiertagsliste ein. Passen Sie die Hervorhebungsfarben im Code ganz nach Bedarf an – und führen Sie das Makro einfach erneut aus, sobald sich Ihre Daten ändern.
1. Klicken Sie auf Entwickler > Visual Basic. Wählen Sie im Microsoft Visual Basic for Applications-Fenster Einfügen > Modul.
2. Kopieren Sie den folgenden Code, und fügen Sie ihn in das neu erstellte Modul ein:
Sub ShadeWeekendsAndHolidays()
Dim rngDates As Range
Dim rngHolidays As Range
Dim cell As Range
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set rngDates = Application.InputBox("Select the range with dates:", xTitleId, Selection.Address, Type:=8)
Set rngHolidays = Application.InputBox("Select the range with holiday dates:", xTitleId, , Type:=8)
On Error GoTo 0
If rngDates Is Nothing Then Exit Sub
If rngHolidays Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each cell In rngDates
If IsDate(cell.Value) Then
If Weekday(cell.Value, vbMonday) >= 6 Or Not IsError(Application.Match(CDbl(cell.Value), rngHolidays, 0)) Then
cell.Interior.Color = RGB(255, 199, 206) ' Light red fill; adjust as needed
Else
cell.Interior.ColorIndex = xlNone ' Remove fill from regular days
End If
End If
Next cell
Application.ScreenUpdating = True
End Sub3. Drücken Sie die F5-Taste, um diesen Code auszuführen. Eine Eingabeaufforderung fordert Sie auf, zunächst Ihre Datumszellen und anschließend Ihre Feiertagszellen auszuwählen. Folgen Sie einfach den Anweisungen auf dem Bildschirm.
Das Makro hebt Wochenenden (Samstag und Sonntag) sowie Feiertage (gemäß Ihrer Liste) farblich hervor. Passen Sie den RGB-Wert „RGB(255,199,206)“ im Code an, um bei Bedarf Ihre Wunschfarbe zu verwenden.
Tipps:
Falls Sie versehentlich den falschen Bereich ausgewählt haben, führen Sie das Makro einfach erneut aus. Alle vorhandenen Füllungen im ausgewählten Bereich werden für nicht übereinstimmende Zellen zurückgesetzt.
Stellen Sie sicher, dass Ihre Daten und Feiertage korrekte Excel-Datumsangaben (keine Textwerte) enthalten.
Vorteile: Spart Zeit bei wiederholten Aufgaben und bietet maximale Flexibilität – Sie müssen die Formatierung nicht manuell erneut anwenden.
Nachteile: Makros müssen für Ihre Arbeitsmappe aktiviert sein. Benutzer, die mit VBA nicht vertraut sind, sollten vor dem Ausführen unbedingt eine Sicherungskopie erstellen und das Makro zunächst darauf testen.
Weitere verwandte Artikel:
- Abwechselnde Zeilen einfärben für Zusammengeführt
- Es ist äußerst hilfreich, abwechselnde Zeilen in einem großen Datensatz farblich zu formatieren, um die Daten besser erfassen zu können. Manchmal enthalten Ihre Daten jedoch zusammengeführte Zellen. Wie heben Sie in Excel abwechselnde Zeilen – einschließlich der zusammengeführten Zellen – wie im folgenden Screenshot gezeigt farblich hervor?
- Ungefähre Übereinstimmung bei der Suche hervorheben
- In Excel können wir die VLOOKUP-Funktion verwenden, um schnell und einfach den ungefähr übereinstimmenden Wert zu erhalten. Haben Sie jedoch jemals versucht, eine ungefähre Übereinstimmung basierend auf Zeilen- und Spaltendaten zu ermitteln und diese ungefähre Übereinstimmung aus Ursprünglicher Datenbereich wie im folgenden Screenshot gezeigt hervorzuheben? Dieser Artikel beschreibt, wie Sie diese Aufgabe in Excel lösen können.
- Zelle hervorheben, wenn Wert ist größer als eine andere Zelle
- Um die Werte in zwei Spalten zu vergleichen – beispielsweise, wenn der Wert in Spalte B größer ist als der Wert in Spalte C derselben Zeile –, können Sie die Werte aus Spalte B wie im folgenden Screenshot gezeigt hervorheben. In diesem Artikel stelle ich einige Methoden vor, wie Sie eine Zelle in Excel hervorheben, sobald ihr Wert größer ist als der einer anderen Zelle.
- Hervorgehobener Zeilenbereich basierend auf Dropdown-Liste
- Dieser Artikel zeigt, wie Sie Zeilen basierend auf einer Dropdown-Liste farblich hervorheben. Nehmen Sie dazu den folgenden Screenshot als Beispiel: Wenn Sie in Spalte E „In Bearbeitung“ auswählen, wird die entsprechende Zeile rot hervorgehoben; bei der Auswahl von „Abgeschlossen“ erscheint sie blau; und sobald „Nicht begonnen“ gewählt wird, leuchtet die Zeile grün.
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 zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins