Office - Word, Excel und Co. 9.744 Themen, 41.408 Beiträge

Verfolgung starten Optionen

Excel und die Feiertage

incognito_23 / 12 Antworten / Baumansicht Nickles

Hallo zusammen,

aktuelle Problemstellung:

Habe ein Tabellenblatt mit Feiertagen angelegt. In B5 steht zum Beispiel der Feiertagsname und in den Zellen C5 bis G5 stehen die Datumsangaben (tt.mm.jjjj) für die jeweiligen Jahre. Also quasi von 2019 bis 2023. In C5 dann ein anderer Feiertag und so weiter.

In einem anderen Tabellenblatt stecken 5 verschiedene Einzeltabellen für die Tage Montag bis Freitag. Für die jeweilige Tabelle wird das Datum automatisch berechnet.

Ich möchte nun, dass die Liste mit den Feiertagen mit dem aktuellen Datum der Einzeltabellen abgeglichen wird und im Falle, dass es sich um ein Feiertag handelt in einer extra Zelle der Name des Feiertags ausgegeben wird.

Ich hab mich schon belesen und denke, dass es mit der Funktion SVERWEIS gehen sollte. Daraus jedoch eine passende Formel zu entwickeln brachte mich an mein Kompetenzlimit.

P.S. Das gleiche würde ich auch später gerne mit den Datumsangaben zu verschiedenen Schulferien (tt.mm.jjjj - tt.mm.jjjj) machen. Ich denke, dass lässt sich dann gut mit der Lösung der ersten Problemstellung lösen.

Schon mal Danke für jegliche Hilfe.

bei Antwort benachrichtigen
mawe2 incognito_23 „Excel und die Feiertage“
Optionen
In C5 dann ein anderer Feiertag und so weiter.

Meinst Du hier evtl. B6 statt C5?

Ich hab mich schon belesen und denke, dass es mit der Funktion SVERWEIS gehen sollte. Daraus jedoch eine passende Formel zu entwickeln brachte mich an mein Kompetenzlimit.

SVERWEIS sucht immer von links nach rechts.

Links müssen also die Datumsangaben stehen, der Name des Feiertags müsste als letzte Spalte rechts stehen, also z.B. in Spalte H.

http://www.excellernen.de/excel-sverweis/

Muss die Anordnung zwingend so bleiben, dass der Feiertagsname links (also in Spalte B) steht, müsstest Du statt SVERWEIS eine Kombination von INDEX und VERGLEICH nehmen. (Das gilt in Excel als die professionellere Alternative zu SVERWEIS). Damit kann man dann auch von rechts nach links suchen.

http://www.excellernen.de/excel-index-vergleich-funktion/

Ändere also zunächst (wenn möglich) die Anordnung der Inhalte und stelle dann mal ein paar Beispielscreenshots Deiner Tabellen hier ein, dann wird sich die passende SVERWEIS-Formel schon finden lassen.

Gruß, mawe2

bei Antwort benachrichtigen
incognito_23 mawe2 „Meinst Du hier evtl. B6 statt C5? SVERWEIS sucht immer von links nach rechts. Links müssen also die Datumsangaben stehen, ...“
Optionen

Danke für die schnelle Antwort.

Hier die angepasste Tabelle für die Feiertage.

Hier ein Teil der Tabelle für die einzelnen Wochentage.

In der blauen Zelle solle dann der Feiertag stehen insofern einer an dem bestimmten Tag ist.

bei Antwort benachrichtigen
mawe2 incognito_23 „Danke für die schnelle Antwort. Hier die angepasste Tabelle für die Feiertage. Hier ein Teil der Tabelle für die ...“
Optionen

Um im Tabellenblatt für die Wochentage den Feiertag anzuzeigen, müsste die folgende Formel für das Jahr 2019 genutzt werden:

=SVERWEIS(A7;Feiertage!$C$5:$Q$25;15;0)

Du musst außerdem noch eine Fehlerbehandlung mit einbauen.

Bei den Tagen, die kein Feiertag sind, würde die Fehlermeldung #NV erscheinen.

Mit der Funktion WENNFEHLER kannst Du stattdessen dort, wo kein Feiertag ist, einen Leerstring ausgeben.

=WENNFEHLER(SVERWEIS(A7;Feiertage!$C$5:$Q$25;15;0);"")

Die Anordnung der Inhalte auf dem Tabellenblatt "Feiertage" erschwert das Ganze etwas, weil man für jedes Jahr den Zellbereich "Feiertage!$C$5:$Q$25" neu definieren müsste (also für 2020 z.B. "Feiertage!$E$5:$Q$25") und auch der Spaltenindex (2019: 15) müsste geändert werden (2020: 13).

Viel einfacher wäre das Verfahren, wenn die Datumsangaben für alle Jahre in einer Spalte stehen würden und in der rechten Nachbarspalte die Feiertage.

(Mein Beispiel enthält nur die Feiertage des ersten Halbjahres!)

=WENNFEHLER(SVERWEIS(A7;Feiertage!$A$2:$B$1000;2;0);"")

gilt dann für alle Jahre und nicht nur für 2019!

Probier' mal, ob Du damit weiterkommst.

bei Antwort benachrichtigen
incognito_23 mawe2 „Um im Tabellenblatt für die Wochentage den Feiertag anzuzeigen, müsste die folgende Formel für das Jahr 2019 genutzt ...“
Optionen

Danke für die ausführliche Erklärung.

Hab die Tabelle jetzt so umgebaut wie du es vorgeschlagen hast und den Namen "Feiertage" dafür definiert & deine letzte Formel benutzt. Bei einigen Tagen funktioniert es, bei anderen wiederum nicht.

Am Beispiel vom 22.04.2019 sieht es wie folgt aus:

Der 24. wird hier leider nicht als Ostermontag erkannt. 

Hingegen der 1. Weihnachtstag. Dieser wird erkannt. 

Der 10.06 Pfingsmontag wird leider auch wieder nicht erkannt.

Warum das so ist hat sich mir noch nicht erschlossen. Vielleicht hilft es wenn ich sage, dass ich das Datum aus A7 mit dieser Formel ausgeben lasse:

=TEXT(DATUM(B2;1;7*A2-3-WOCHENTAG(DATUM(B2;;);3));"TT.MM.JJJJ")

Diese Formel passt dann die Zellen für Dienstag - Freitag an:

=A7+WENN(WOCHENTAG(A7;2)=5;3;WENN(WOCHENTAG(A7;2)=6;2;1))

Den Zeitraum berechne ich via

=TEXT(DATUM(B2;1;7*A2-3-WOCHENTAG(DATUM(B2;;);3));"TT.MM.JJJJ")&" - " &TEXT(DATUM(B2;1; 7*A2-3-WOCHENTAG(DATUM(B2;;);3))+6;"TT.MM.JJJJ")

Ich hoffe, dass hilft uns weiter.

bei Antwort benachrichtigen
mawe2 incognito_23 „Danke für die ausführliche Erklärung. Hab die Tabelle jetzt so umgebaut wie du es vorgeschlagen hast und den Namen ...“
Optionen

Warum da einzelne Tage nicht korrekt zugeordnet werden, kann ich nicht erkennen.

Vielleicht solltest Du bei einer Zelle, wo die Zuordnung nicht funktioniert, die Fehlerbehandlung vorübergehend entfernen um zu sehen, welcher Fehler dort ausgegeben wird?

Irgendwie verstehe ich auch nicht, was Du da mit den TEXT-Funktionen vorhast. Datumsangaben sind doch Zahlen, ich würde die nicht in TEXT umwandeln sondern weiter als Zahlen benutzen.

Der 24. wird hier leider nicht als Ostermontag erkannt. 

Der 22.04.ist doch der Ostermontag. (OK, der wurde aber auch nicht richtig erkannt bzw. zugeordnet.)

Wo jetzt noch Fehler stecken, kann man an der Screenshots leider nicht erkennen.

bei Antwort benachrichtigen
incognito_23 mawe2 „Warum da einzelne Tage nicht korrekt zugeordnet werden, kann ich nicht erkennen. Vielleicht solltest Du bei einer Zelle, wo ...“
Optionen

Liegt wirklich daran, dass ich mit TEXT gearbeitet habe. Wenn ich das Datum händisch in A7 eintrage, funktioniert es. Dann muss ich mal schauen wie ich das mit den Datum besser mache. Hab dank. Wäre ich vermutlich nie drauf gekommen weil ich die Formel von einer anderen Excel-Tabelle Copy&Paste habe.

bei Antwort benachrichtigen
mawe2 incognito_23 „Liegt wirklich daran, dass ich mit TEXT gearbeitet habe. Wenn ich das Datum händisch in A7 eintrage, funktioniert es. Dann ...“
Optionen
Dann muss ich mal schauen wie ich das mit den Datum besser mache.

Ich würde den Zeitraum in B4 über drei Zellen "ausbreiten" (B4 bis D4) und somit Anfang und Ende der Woche als einzelnes Datum in einer Zelle eintragen (dazwischen das "-"). Optisch kannst Du das ja trotzdem so ähnlich gestalten wie jetzt (gemeinsamer Rahmen).

So kannst Du das Datum des Montags direkt von dort übernehmen, am Dienstag musst Du nur 1 addieren, Mittwoch 2 usw.

Gruß, mawe2

bei Antwort benachrichtigen
incognito_23 mawe2 „Ich würde den Zeitraum in B4 über drei Zellen ausbreiten B4 bis D4 und somit Anfang und Ende der Woche als einzelnes ...“
Optionen

Genau so hab ich es gemacht und es läuft endlich fehlerfrei. Ein Problem weniger. 

Btw: Ist es auch möglich SVERWEIS für Zeiträume zu nutzen anstatt nur für ein einzelnes Datum, siehe Ferienzeiträume? Prinzipiell könnte ich ja wieder jedes Datum einer Ferienzeit zuordnen. Die Tabelle wird dabei nur extrem groß.

bei Antwort benachrichtigen
mawe2 incognito_23 „Genau so hab ich es gemacht und es läuft endlich fehlerfrei. Ein Problem weniger. Btw: Ist es auch möglich SVERWEIS für ...“
Optionen
Ist es auch möglich SVERWEIS für Zeiträume zu nutzen anstatt nur für ein einzelnes Datum, siehe Ferienzeiträume?

Ja, das geht.

Beispiel:

In der Tabelle links werden (hier nur für April) die Zeiträume definiert, in dem nur das Datum des Beginns eine neuen Zeitraums eingetragen wird.

Am 01.04. beginnt ein Zeitraum ohne Ferien, am 15.04. beginnen die Ferien, sie enden am 26.04., also beginnt am 27.04. wieder ein Zeitraum ohne Ferien.

Mit der Formel

=SVERWEIS(D1;$A$1:$B$3;2;0)

in Zelle E1 ff

sieht das so aus:

(Die Fehlebehandlung habe ich bewusst weggelassen.)

Änderst Du das letzte Argument in der SVERWEIS-Funktion von 0 auf 1, erhältst Du diese Formel::

=SVERWEIS(D1;$A$1:$B$3;2;1)

und die Ausgabe in der Spalte E sieht so aus:

Statt für 30 Tage im April muss man nur für 3 Tage eine Vorgabe machen. (Eigentlich sogar nur für 2, wenn man vorher schon die anderen Monate (Jan, Feb, Mrz) in der Tabelle hat und dort schon Zeiträume ohne Ferien definiert waren.

Gruß, mawe2

bei Antwort benachrichtigen
incognito_23 mawe2 „Ja, das geht. Beispiel: In der Tabelle links werden hier nur für April die Zeiträume definiert, in dem nur das Datum des ...“
Optionen

Überragend. Hat alles geklappt. Danke für die Erklärung. Bin meinem Ziel wieder ein Stück näher gekommen und ein paar Excel-Skills sind auch hängen geblieben.

Eine letzte Frage zu SVERWEIS:

Ist es möglich diese Funktion in Kombination mit "Datenüberprüfung" zu benutzen? 

Ich möchte die Auswahl in meinen Dropdownlisten einschränken lassen. Darin befinden sich Namen. Die auswählbaren Namen sollen automatisch für den Tag angepasst werden. Wenn also jemand in der dazugehörigen SVERWEIS-Tabelle gefunden wurde, fliegt er oder sie aus der Dropdownliste.

Kurz gesagt: Mir soll die tagesaktuelle Auswahl eingeschränkt werden, wenn jemand krank oder im Urlaub ist. Und das i-Tüpfelchen wäre, wenn dann noch der/die Name/n mit Zeitraum bzw. einzelner Tag in einer extra Zelle ausgegeben werden.

bei Antwort benachrichtigen
mawe2 incognito_23 „Überragend. Hat alles geklappt. Danke für die Erklärung. Bin meinem Ziel wieder ein Stück näher gekommen und ein paar ...“
Optionen

Ich denke, mit etwas Probieren könnte das von Dir genannte Szenario möglich sein.

Ich selbst habe damit (in Excel) keine Erfahrungen, weil ich in solchen Fällen dann schon eher auf Datenbanken mit entsprechenden Abfragemöglichkeiten ausweiche (meist in Access).

Excel bietet ja auch einige Datenbankfunktionen, evtl. wirst Du damit zum Ziel kommen?

Möglicherweise wirst Du aber auch an VBA nicht vorbeikommen.

Viel Erfolg!

bei Antwort benachrichtigen
incognito_23 mawe2 „Ich denke, mit etwas Probieren könnte das von Dir genannte Szenario möglich sein. Ich selbst habe damit in Excel keine ...“
Optionen

Alles klar. Danke für die Hinweise. Ich probier mein Glück.

bei Antwort benachrichtigen