Excel Feiertage einfaerben, bedingte Formatierung?
Hallo,
ich habe eine Tabelle "2022" (koennte ich auch "Jahr" benennen oder so), in der in Spalte "A" das Datum berechnet wird.
Ab Spalte "B" werden Zahlen eingetragen und berechnet.
In "A6" steht der 01.01.2022 und die Spalte ist mit Datum TT.MM.JJJJ formatiert.
In "A7" ist die Formel =A6+1 drin, in "A8" =A7+1, usw. bis "A36", dem 31. Januar des Jahres.
In Zeile 37 werden die Zahlen ab Spalte "B" (von "A6" bis "A36") summiert.
Zeile 38 ist leer und dient nur zur optischen Trennung zwischen Januar und Februar.
In "A39" ist dann die Formel =DATUM(JAHR(A6);MONAT(A6)+1;1) die den 01. Februar des Jahres berechnet.
Ab "A40" geht es dann wie in Zeile 7 analog bis Zeile 66 fuer den 28. Februar weiter.
In "A41" ist dann die Formel =WENN(TAG(A66+1)=1;"";A66+1) fuer die Berechnung des 29. Februar drin.
In Zeile 68 werden die Zahlen ab Spalte "B" summiert.
Zeile 69 ist leer und dient nur zur optischen Trennung zwischen Februar und Maerz.
In "A70" ist dann die Formel =DATUM(JAHR(A6);MONAT(A6)+2;1) die den 01. Maerz des Jahres berechnet.
Das funktioniert alles bis zum 31. Dezember in "A393".
Mit bedingter Formatierung werden auch die Samstage und Sonntage entsprechend in der Spalte "A" eingefaerbt.
Jetzt habe ich versucht, dass sich auch die Feiertage, vor allem auch die variablen, per bedingter Formatierung einfaerben sollen.
Dazu habe ich ein weiteres Tabellenblatt "Feiertage" wie folgt angelegt.
In Zelle "A1" steht die Ueberschrift "Feiertag" drin, und in den Zeilen darunter, welcher Feiertag.
In Zelle "B1" wir Jahreszahl des entsprechenden Jahres als Zahl z.B. 2022 eingetragen, Zelle ist als zahl formatiert.
In Zelle "C1" steht die Ueberschrift "Anmerkungen" drin, welcher Feiertag oder sonstiges, in den Zeilen darunter.
In Zelle "D1" steht die Ueberschrift "Formel" drin, und darunter habe ich mir die entsprechenden Formel von Spalte "B" sichtbar dargestellt.
Ich habe noch keine funktionierende Formel gefunden, um in Blatt "2022" die Feiertage in Spalte "A" aus dem Blatt "Feiertage" zu vergleichen und per bedingter Formatierung einzufaerben. Zum Beispiel mit "SVERWEIS".
Vielen Dank schonmal, fuer hilfreiche Antworten.
p.s.: Schnellposter ohne hilfreiche Antworten melde ich.
Hier ein Ausschnitt der Tabelle "Feiertage"
Hier ein Ausschnitt der Tabelle "2022" vom Januar
Hier ein Ausschnitt der Tabelle "2022" vom Februar 2022
Und hier ein Ausschnitt der Tabelle "2022" vom Februar 2024 (29. Februar erscheint)
3 Antworten
Irgendwie sehe ich nicht, in WELCHER Spalte von "Feiertage" denn jetzt das in 2022 zu suchende DATUM steht.
Auf jeden Fall brauchst Du für die Bedingte Formatierung in 2022 keinen SVERWEIS, sondern nur ein ZÄHLENWENN.
In etwa (angenommen Datum ist in Feiertage!B:B):
2022!A:A markieren:
Bedingte Formatierung/ Neue Regel/ Formel zur Ermittlung der zu formatierenden Zellen verwenden/ Werte formatieren, die für diese Formel wahr sind:
=ZÄHLENWENN(Feiertage!B:B;A1)>0
Formatieren/ OK/ OK.
Und denke dran, diese BF ggf. VOR die für die Wochenenden zu setzen.
(Kannst Du mit den Pfeilen in der Kopfzeile der BF anpassen).
Hilft Dir das schon weiter?
Hallo Fleischtester, danke fuer deineAntwort. Das SVERWEIS den Fehler #NV liefert, habe ich schon gelesen und auch eine Loesung gefunden, dass dieser nicht angezeigt wird / werden soll.
Hallo Oubyi, meine Antwort steht weiter unten unter Fleischtester. Mal sehen, wohin diese geschoben wird.
Hallo Oubyi, danke fuer deine Antwort. Ich habe jetzt noch ein paar Screenshots hinzugefuegt, vielleicht ist meine Beschreibung dann besser zu verstehen. Aber ich teste mal deinen Vorschlag.
Moin,
hier kann man meinen Schichtkalender runterladen. Schaue wie ich das gemacht habe ... Es kann sogar sein, das ich es mit SVerweis gemacht hatte ...
(Einfach Ausgabespalte = Eingabespalte ...) Zählenwenn geht natürlich auch (Wie von Oubyi beschrieben!)
Man kann auch sehen, wie ich Ostern berechnet habe und das ich mithilfe der Bedingten Formatierung einfach vergleiche ...
Grüße
Ostern bzw. Ostersonntag habe ich, ausgehend vom Jahr in "B1" (B$1) so berechnet:
DATUM(B$1;3;28)+REST(24-REST(B$1;19)*10,63;29)-REST(KÜRZEN(B$1*5/4)+REST(24-REST(B$1;19)*10,63;29)+1;7)
Hallo Fleischtester, kann die Datei dort leider nicht runterladen, muesste mich dazu registrieren. Habe noch ein paar Bilder hinzugefuegt, vielleicht sind diese hilfreich bei meiner Beschreibung. Ich probiere erstmal mit dem Vorschlag von Oubyi. Eine Loesung mit SVERWEIS waere aber auch interessant, vielleicht klaert sich dann ja mein Denkfehler.
Moin,
probiere die Lösung von Oubyi, diese vLösung verwende ich auch schon eine ganze Weile. ...
Grüße
Hallo Fleischtester, habe die Formel von Oubyi so angepasst und unter den bedingten Formatierungen der Wochenenden gelassen:
=ZÄHLENWENN(Feiertage!$B$2:$B$17;$A6)>0
Wenn ein Feiertag auf ein Wochenende faellt, ist das irrelevant.
Moin,
wenn du die Wochenenden über bed. Formatierung bestimmen lässt, kannst du das über die Reihenfolge beeinflussen.
Grüße
z.B. so (und mit dem Pinsel übertragen)... siehe auch
Antwort von Oubyi, UserMod Light 18.08.22, 18:38:35
Für die Wochenenden eine weitere Bedingte Formatierung anlegen.
Hallo GutenTag2003, ich habe die Formel von Oubyi so angepasst und unter den bedingten Formatierungen der Wochenenden gelassen:
=ZÄHLENWENN(Feiertage!$B$2:$B$17;$A6)>0
Wenn ein Feiertag auf ein Wochenende faellt, ist das fuer mich irrelevant.
Aber ich werde auch mal deine Formel testen, die waere angepasst dann wohl:
=ZÄHLENWENN(Feiertage!B:B;A6)=1
Wenn Du die Wochenenden "aussparen" willst, kannst Du das mit einer Kombination "=Wochentag" erreichen.
Möglicherweise erübrigt sich das auch, wenn Du die Wochenende per Bedingter Formatierung färbst.
Die Wochenenden (Samstag und Sonntag) sollen entsprechend farblich dargestellt werden. Wenn ein Feiertag auf ein Wochenende faellt, dann soll er als Wochenende dargestellt werden. Faellt ein Feiertag auf einen Wochentag (Montag bis Freitag), dann soll entsprechend farblich gekennzeichnet werden. Fuer einen Feiertag auf einen Samstag kann ich ja die Reienfolge der bedingten Formatierungen in der Reihenfolge entsprechend aendern, dass dieser als Feiertag gekennzeichnet wird.
Moin,
Sverweis geht auch. Das liefert einen Fehler, wenn das Datum nicht gefunden wird. Den Fehler verarbeitet Bed.Format. nicht! ...
Grüße