Feiertage farbig markieren (Google Tabellen)
In diesem Tutorial zeige ich Euch wie Ihr mit den GOOGLE TABELLEN in Eurem Kalender die Feiertage farbig markieren könnt. Hierfür benutze ich in diesem Video die bedingte Formatierung.
Wer sich mit der bedingten Formatierung noch gar nicht auskennt sollte sich vielleicht auch erst einmal dieses Tutorial anschauen:
Bedingte Formatierung ganz einfach (Google Tabellen / Spreadsheets). Das Tutorial zeigt wie eine bedingte Formatierung für die entsprechenden Zellen angewendet werden kann.
Wer das erste mal mit Google Tabellen oder überhaupt mit einem Tabellenkalkulationsprogramm arbeitet sollte sich vielleicht auch erst einmal dieses Tutorial anschauen:
Einfache Berechnungen für Anfänger (Google Tabellen)
Das Tutorial zeigt ganz einfache Berechnungen und sollte den Einstieg wesentlich leichter machen.
Dieses Tutorial ist extra für Anfänger die sich mit dem Tabellenkalkulationsprogramm GOOGLE TABELLEN / SPREADSHEETS noch nicht so viel beschäftigt haben. Es zeigt die Funktionen und Möglichkeiten Schritt für Schritt.
Das Video-Tutorial "Feiertage im Kalender farbig markieren (Google Tabellen)"
Das könnte Dich auch interessieren
- Dynamischen Wochenkalender erstellen (Google Tabellen)
- Zellen neben einem Suchwort markieren (Google Tabellen)
- Kalender Datum Zeit (Google Tabellen)
- Tutorials über Google Tabellen Script - Programmierung
- Tutorials über Google Tabellen
- Senkrechter Jahreskalender + Download (Google Tabellen Spreadsheets)
- Dynamischer Kalender mit Google Tabellen
- Dynamischen Monatskalender erstellen + Download (Google Tabellen Spreadsheets)
- Übersichts-Liste der Google Tabellen Tutorials
24 Comments
Hallo,
vielen Dank für das Tutorial über die Feiertage bei Google Tabellen; ich habe hierzu noch eine Frage.
Gibt es sowas ähnliches auch um Ferien im Kalender automatisch einzutragen. Die Vorgabe währe z.B.
Osterferien Startdatum Enddatum
Sommerferien Startdatum Enddatum
Herbstferien Startdatum Enddatum
Winterferien Startdatum Enddatum
Google Tabellen sollte dann automatisch vom Start- bis zum Enddatum die Felder mit einer bestimmten Farbe markieren.
Das kann man mit der bedingten Formatierung machen. Wenn Du Dir das Tutorial angeschaut hast, wirst Du gesehen haben wie man die bedingte Formatierung anwendet.
Hier musst Du nur eine oder mehrere neue bedingte Formatierung erstellen die dann im Eingabebereich für Formeln in etwa so aussieht (Zellen formatieren wenn … "Benutzerdefinierte Formel ist").
=und(A4>=$AB$4;A4<=$AC$4) Der Kalender muss dazu natürlich wieder komplett ausgewählt werden, so wie im Video gezeigt.Darauf achtend dass Du auch in der bedingten Formatierung eine Farbe ausgewählt hast. Standardmäßig ist diese eigentlich schon mit einer Standardfarbe vorbelegt. Die Zellen AB4 und AC4 enthalten bei der Formel das Datum für Ferienbeginn und Ferienende. Das kannst Du Dir ja entsprechend anpassen. Du musst das für alle Ferien separat machen, also mehrere bedingte Formatierungen. Allerdings könntest Du das natürlich auch in einer einzigen Formatierung unterbringen allerdings dann mit sehr vielen UND sowie ODER Funktionen. Ich denke die separate Eingabe ist vermutlich einfacher und überschaubarer 😉
Hallo Chris,
vielen Dank für die schnelle Info. Ich habe den ersten Vorschlag, die 4 einzelnen Bedingungen, in meine Tabelle erfolgreich eingebaut.
Gestern habe ich zufällig (durch Google) auch so eine Formatierung als Einzeiler gefunden und zwar bei Tipps und Tricks auf einer Excel Seite.
Die Formel für die bedingte Formatierung lautet (schon auf meine Kalender-Tabelle angepasst):
=UND(A4>=SVERWEIS(A4;$D$39:$D$42;1;1);A4<=VERWEIS(A4;$D$39:$E$42;$E$39:$E$42))
wobei A4 der Beginn von meinem Kalender ist und in den Zeilen D39 bis E42 die Ferientermine stehen. Mit dieser Formel habe ich es geschafft mehrere Termine in ein Kalender automatisch eintragen lassen wenn mann die Parameter D39 bis E42 anpasst.
(Das mit dem Anpassen der Werte war Zufall, ich versuch jetzt erst mal mehr Informationen zu den Funktionen SVERWEIS und VERWEIS zu bekommen damit ich diese Formel verstehe 🙂 )
Das mit dem SVerweis ist eigentlich nicht so kompliziert. Hier wird ein Suchbegriff in senkrechter Richtung gesucht. Ich habe dazu auch schon Tutorials gemacht.
Das erste Tutorial ist zwar für Excel, ist meiner Meinung nach aber wesentlich besser verständlich und auch für Google Tabellen anwendbar.
https://toptorials.com/sverweis-super-simpel-anfaenger-excel/
Das zweite Tutorial ist für Google Tabellen geht aber schon ein kleines Stückchen weiter.
https://toptorials.com/sverweis-google-tabellen/
Es gibt auch noch WVerweis, der den Suchbegriff in waagerechter Richtung sucht. Hier gibts auch schon Tutorials von mir. Für den normalen Verweis müsste ich wohl vielleicht noch mal ein Tutorial erstellen.
Das ist aber genau was ich meinte das man auch alles in einer Formel unterbringen kann 😉 Aber super dass Du das hier gepostet hast, können sicher noch mehr Leute gebrauchen 😉
Du kannst natürlich jetzt selbst entscheiden ob Du die fette Formel nimmst oder lieber die kleinen Formeln. Der Vorteil der kleinen Formeln wäre natürlich dass Du die Sommerferien in Geld machen kannst und den Rest halt etwas dunkler 😉
Hallo Chris,
ich bedanke mich für die zahl- und hilreichen Tutorials und kann nur sagen "weiter so!". Hier eine kurze Frage:
Ich möchte ebenfalls, wie in diesem Tutorial, Feiertage in gezeigten Kalender durch die bedingte Formatierung farblich markieren. Bei mir soll dies jedoch auf einem anderen Tabellenblatt erfolgen. Hierzu habe ich auch schon den Bereich mit den jeweiligen Feiertagen benannt und wollte mich mit "SVERWEIS" hierauf beziehen (=SVERWEIS(A1;Feiertage;1;0). Dies scheint bisher, auch nach einigen Versuchen mit anderen Befehlen (=ZÄHLENWENN(Feiertage;A1)0) noch nicht so ganz zuklappen. Ich komme einfach nicht weiter. Alle Zellen sind entsprechend mit Datum formatiert.
PS: Auf dem gleichen Tabellenblatt funktioniert im Übrigen die Markierung wie von dir beschrieben einwandfrei.
Hi Tom,
versuche mal
=SVERWEIS(A1;INDIREKT("Feiertage");1;0)
Danke Dir übrigens für das positive Feedback über meine Tutorials 😉
Viele Grüße
Chris
Danke Chris für die Antwort. Leider hat das auch nicht funktioniert. Irgendwie erkennt mir "SVERWEIS" in einer bedingten Formatierung den benannten Zellbereich nicht (Feiertage). Es liegt definitiv kein Schreibfehler vor. Im selben Tabellenblatt funktioniert dies wunderbar. Gibt es die Möglichkeit trotzdem über andere Tabellenblätter auf Zellebereiche zu zugreifen? Vielleicht (=SVERWEIS(A1;'Tabellenblat1!B2:C10';1;0) oder so ähnlich?
Danke vorab
Tom
Hi Tom,
mich wundert das es bei Dir nicht funktioniert, denn ich habe das bei mir erst getestet bevor ich Dir das gesendet habe.
Eine andere Möglichkeit wie Indirekt gibt es meiner Meinung nach nicht.
Schau Dir mal die Info an. Hier ist der Teil "Erweiterte Optionen für die bedingte Formatierung verwenden" -> "Benutzerdefinierte Formeln zur bedingten Formatierung verwenden" und hier nach Punkt 6 der Hinweis der das bestätigt.
Hinweis zur bedingten Formatierung
Ich vermute dass es mit dem Bereich zusammen hängt den Du einen Namen gegeben hast. Wenn Du nur die Datumseinträge markierst und nicht die Namen der Feiertage (also nur eine Spalte) dann sollte es eigentlich funktionieren.
In der Formel die Du oben zu stehen hast greifst Du auch auf Spalte B zu wo auch der Suchbegriff stehen müsste. Ich nehme aber an das der Suchbegriff in Zeile C zu finden ist.
Eine Tabelle wo Du siehst das es funktioniert findest Du hier. Die Datei kannst Du auch bearbeiten allerdings auch jeder andere. Wenn die Datei beschädigt ist, sag bescheid dann erstelle ich noch einmal eine Kopie. Da kannst Du aber auch sehen wie ich die Formel in die bedingte Formatierung eingetragen habe.
https://docs.google.com/spreadsheets/d/1stpFdTwCtoXwKPovEnYXJVwteZZtQBwRBZBDY4MuhDI/edit?usp=sharing
Hier ist in Tabellenblatt2 die bedingte Formatierung eingebaut und greift auf Tabellenblatt1 zu um die Datumseinträge zu vergleichen.
Viele Grüße
Chris
Hallo Chris,
ich habe genau das nochmal probiert und dein Muster und die Hinweise gelesen. Beim ersten eingeben hat es nicht geklappt. Nach nochmaligem aufrufen waren alle Markierungen am richtigen Platz. Somit funktioniert alles. Besten Dank dafür. Einen Fehler hatte ich dann aber doch gefunden. Bei deiner Antwort vom 25.09. hattest du die Anführungszeichen oben/unten gesetzt und ich habe diesen Text einfach kopiert und bei mir eingefügt. Sicher lag es d
an den Anführungszeichen welche ja oben/oben gesetzt werden sollen?
Jedenfall ist alles besten und ein großes Lob nochmal an dieser Stelle für die ausgezeichnete und ebenfalls schnelle Hilfe!!!
Beste Grüße
Tom
Hi Tom, da hast Du natürlich recht. Das mit den Anführungszeichen habe ich nicht gesehen und wurde hier beim Einfügen in die Kommentare so eingefügt. Das Original hatte natürlich beide Anführungszeichen oben. Da muss ich nächstes mal beim Einfügen per Copy & Paste doch nochmal genauer hinschauen 😉
Trotzdem schön dass Du es jetzt noch hinbekommen hast.
Danke Dir auch für das positive Feedback 😉
Hallo Chris, ich hatte dich bezüglich dieses Beitrags schon einmal erfolgreich um Hilfe gebeten. Nun möchte ich den Kalender etwas anpassen.
Ich habe den Kalender bzw. die einzelnen Tage für ganze Jahr in einer Spalte C dynamisch aufgebaut. Nun möchte ich automatisch in Spalte F Buchstaben erzeugen und zwar genau in der Zeile in welcher in Spalte C ein Feiertag ist und dieser =7 also ein Sonntag ist.
Du musst wissen, der eigentliche Kalender wird schon, dank dir, über ein anderes Tabellenblatt erfolgreich dynamisch mit den jeweiligen Feiertagen erzeugt. Somit geht es nur noch um die genannte Automatk.
Spalte C = Dateumseinträge mit Feiertagen (wird über "indirekt" abgefragt und zeilenweise farblich markiert)
Spalte F = hier sollen alle Feiertage, nur Sonntag, mit "FT" markiert bzw. beschrieben werden
Ich habe es schon mehrfach probiert. Leider bisher ohne Erfolg. Hatte immer
=wenn(und(SVERWEIS($C5;INDIREKT("Feiertage");1;0);WOCHENTAG($C5;2)=7);F5:F500="FT";"")
und entsprechend variiert. Wie gesagt, hat leider noch nicht geklappt. Ich komme hier nicht weiter.
Für deine Hilfe danke ich schonmal im Voraus
Tom
Hi Tom, soweit ich sehen kann sieht es gar nicht so schlecht aus. Ich habe das jetzt nur überflogen weil es schon recht spät ist. Der Fehler liegt vermutlich nur an F5:F500= was Du da komplett weg nehmen kannst. Die Wenn Funktion gibt im letzten Parameter "" aus wenn die Bedingung nicht erfüllt wird und soll ja im vorletzten Parameter auch nur "FT" ausgeben wenn beide Bedingungen der UND Funktion erfüllt werden und nichts mit F5:F500 etc. Check mal ob das dann schon funktioniert 😉
Hallo Chris, GANZ GROSSEN DANK AN DICH trotz der späten Stunde noch zu antworten!!!
Ich habe den Tipp umgesetz in Form von
=WENN(UND(SVERWEIS($C$5;INDIREKT("Feiertage");1;0);WOCHENTAG($C5;2)=7);"FT";"")
Diese habe ich in F5 geschrieben und wollte dann auf alle weiteren Felder nach unten kopieren. Bei dem ersten Feld (01.01.2017) welcher ein Feiertag und Sonntag war hat alles gut geklappt. FT wurde ordnungsgemäß gesetzt. Beim kopieren der Formel nach unten gab es leider Probleme bei den nächsten Sonntagen im Januar die ja keine Feiertage waren. Hier wurden ebenfall FT gesetzt. Die anderen Wochentage waren hingegen wieder ordnungsgemäß leer. Ich bin mir nicht sicher welche Werte beim kopieren angepasst werden sollen damit alles richtig klappt. Hier nochmals der jetzige Stand:
F5 – =WENN(UND(SVERWEIS($C$5;INDIREKT("Feiertage");1;0);WOCHENTAG($C5;2)=7);"FT";"") – Datum 01.01.2017/Feiertag/Sonntag – korrekt gesetzt
F6 – =WENN(UND(SVERWEIS($C$5;INDIREKT("Feiertage");1;0);WOCHENTAG($C6;2)=7);"FT";"") – Datum 02.01.2017/Wochentag/Montag – korrekt gesetzt
…
…
F12 – =WENN(UND(SVERWEIS($C$5;INDIREKT("Feiertage");1;0);WOCHENTAG($C12;2)=7);"FT";"") – Datum 08.01.2017/Wochentag/Sonntag – FT gesetz
F13 – =WENN(UND(SVERWEIS($C$5;INDIREKT("Feiertage");1;0);WOCHENTAG($C13;2)=7);"FT";"") – Datum 08.01.2017/Wochentag/Montag – FT korrekt gesetzt
…
…
Bei F12 sollte das Feld durch die WENN/UND Funktion eigentlich leer bleiben.
Darauf hin habe ich nochmal die Bedingung in WOCHENTAG auf <7 geändert. Hierbei werden alle Tage auch Feiertage mit FT besetzt außer Sonntage alos genau umgekehrt. Irgenwie funktionieren die WENN/UND-Bedingungen noch nicht in Kombination (vermutlich bei SVERWEIS).
Ich weiß nun nicht genau wie die Formel je nach Feld beim kopieren geändert werden muss, dass immernoch alles funkioniert.
Bei kopieren auf F6, F7, F8… wird bei SVERWEIS($C5 nicht weitergezählt und bleibt auf $C$5 da ja hier auch die Datumsfelder untereinander liegen. Bei WOCHENTAG wird weiter gezählt von $c5 auf $c6, $c7, $c8…. Nun bin ich mir nicht sicher ob beim kopieren/SVERWEIS weiter gezählt werden sollte oder nicht und bei kopieren/WOCHENTAG ebenfalls oder nicht.
Leider bin ich mit meinem Latein am ende. Du brauchst dich nicht zu so später Stunde noch hinsetzen und antworten. Wenn du mehr Zeit hast und entsprechend ausgeschlafen bist, reicht das allemal! Ich bin schon froh, dass du dich meiner überhaupt angenommen hast.
Gruß Tom
Ich denke Du hast da die Probleme mit den absoluten und relativen Bezügen. Schau Dir wenn möglich mal das Tutorial an: https://toptorials.com/relative-absolute-bezuege-google-tabellen/
Im ersten Abschnitt der UND Funktion also dem SVERWEIS ändert sich bei Deiner Formel die Zelle nie. Das heißt beim Kopieren greifst Du immer auf die gleiche Zelle zu und bekommst immer das gleiche Ergebnis.
Im zweiten Teil der UND Funktion bleibst Du beim Kopieren immer auf der Spalte. Da hättest Du dann Probleme wenn Du die Formel nach rechts kopierst. Wenn Du das alles manuell anpasst nachdem Du die Formel auch rechts irgendwo einfügst könnte es natürlich funktionieren aber ich vermute Du möchtest die Formel nur einmal schreiben und überall hin kopieren können. Schau Dir also mal die Sache mit den Dollarzeichen nochmal genauer an 😉
Guten morgen,
das mit den relativen Bezügen habe ich soweit verstanden und habe nun die Formel in
=WENN(UND(SVERWEIS($C5;INDIREKT("Feiertage");1;0);WOCHENTAG(C5;2)<7);"FT";"") – Die Formel steht in F5
Nun passen zumindest die Feiertage/Sonntage mit der gewünschten FT-Markierung!!! Jedoch kommen bei den anderen Feldern folgende Fehler:
Bei der Auswertung von VLOOKUP wurde der Wert "42762" nicht gefunden. (in den Zellen steht #NV)
Leider finde ich zu diesem Fehler keine Hilfe.
Tom
Schau Dir mal die Funktion WENNFEHLER an.
Die Zahl die Du dort siehst ist die interne Zahl für ein Datum. SVerweis hat nach dem Datum (hier als Zahl dargestellt) gesucht und über SVerweis kein Eintrag in Deiner Matrix Feiertage gefunden. Deshalb bekommst Du diesen Fehler angezeigt. Hier musst Du jetzt die WENNFEHLER Funktion mit einbauen.
Hallo Chris,
nach deinem letzten Hinweis mit WENNFEHLER hat es eine Weile gedauert bis bei mir der Groschen gefallen ist. Ich habe erst verschiedene Möglichkeiten durchprobieren müssen bis es dann aber doch noch funktioniert hat. Ich muss dir ein großes Lob und meinen herzlichen Dank sagen. Deine schnellen und hilfreichen Antworten lassen keine Wünsche offen.
Eine tolle Webseite mit vielen nützlichen und hilfreichen Videos für Anfänger und Fortgeschrittene. Echt empfehelenswert!
Hallo Tom, Danke für die netten Worte über meine Webseite. Klar hätte ich Dir das vermutlich noch ein bisschen einfacher machen können aber man soll natürlich hier auch ein bisschen was lernen. Super dass Du das mit ein bisschen Hilfestellung von mir hin bekommen hast. Solche Nachrichten wie Du die jetzt geschrieben hast lese ich natürlich immer sehr gerne 😉
Danke Dir jedenfalls für das super Feedback 😉
Hallo Chris!
Vielen Dank für die super Anleitungen!
hab noch eine Frage zu den Feiertagen
ich habe einen Stundenzettel für meine Mitarbeiter gemacht. sieht folgender maßen aus.
Datum Beginn Ende Beginn Ende Gesamt
1.1.18 8:00 10:00 10:30 11:30 3:00
Die Feiertage werden farblich markiert und im folge Jahr automatisch geändert.
Meine Frage: ist es möglich die Gesamtstunden an einem Feiertag automatisch zu multiplizieren, z.B. mit 2, damit an diesen tag die stunden Doppelt gerechnet werden.
Vielen Dank im vorau,
Johannes Kreindl
Hallo Johannes, DANKE für das nette Kompliment 🙂
Die Stunden kannst Du verdoppeln indem Du zum Beispiel eine WENN Funktion in Kombination mit dem SVerweis benutzt. In der WENN Funktion hast Du ja die Möglichkeit eine Bedingung festzulegen.
Als Bedingung benutzt Du den SVerweis um zu überprüfen ob das Datum ein Feiertag ist (genau wie bei der bedingten Formatierung). Wenn es ein Feiertag ist, benutzt Du Deine Formel und multiplizierst das ganze. Wenn das kein Feiertag ist, dann kannst Du die Formel ganz normal rein schreiben.
Also für die WENN Funktion:
1. Parameter die Abfrage ob es sich um einen Feiertag handelt mit dem SVerweis
2. Parameter die Formel mit Multiplikator
3. Parameter die Formel ohne Multiplikator.
Im Grunde musst Du die Formel aus der bedingten Formatierung entsprechend für den ersten Parameter nur anpassen.
Viele Grüße
Chris
Hallo Chris,
erst mal herzlichen Dank für deine suuuper Tutorials!!!
In deinen Kalender mit farbiger Markierung der Feiertage wollte ich eine zusätzliche bedingte Formatierung einfügen, nämlich solche Tage fett darstellen, an denen ich arbeite. Dazu habe ich in einem anderen Tabellenblatt einen Bereich "Arbeitstage" benannt. Jetzt wollte ich die bedingte Formatierung so anweisen: =VLOOKUP(A4;Arbeitstage;1;false). Da bekomme ich aber immer einen Fehler, obwohl es funktioniert, wenn ich mit dieser Formel nur eine einzelne Zelle abfrage.
Kannst du mir helfen? Wäre suuuper.
Vielen Dank schon mal
Bernhard
Hallo Chris, wieder mal ein sehr schönes Video von dir. Ich möchte die Feiertage gerne auf die Zeilen A36 und A37 eintragen. Habe auch schon alles mögliche versucht die Bedingte Formatierung zu bearbeiten aber leider ohne Erfolg. Kannst du mir da die Formel zu zeigen
Hallo,
vielen Dank für die tolle Anleitung, ich habe mir schon den dynamischen Kalender nachgebaut und möchte jetzt auch Feiertage farblich markieren. Mit dem Sverweis klappt das nur für den Januar. Ich vermute, dass durch die Verwendung von =edate von Feb-Dez die Werte im Kalender nicht mehr als 2022 erkannt werden. Kann das sein? Gibt es dafür einen Workaround?
hatte gerade einen Kommentar geschickt, kannst ignorieren. Ich hatte einen Fehler im dynamischen Kalender, jetzt klappt alles. 🙂 Danke nochmal für die detaillierten Anleitungen!