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)"
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!