Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1644to1648
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Umsetzung der Ferien und Feiertage

Umsetzung der Ferien und Feiertage
19.09.2018 13:57:49
Romy
Hallo liebe Excel Gemeinde,
Zur Zeit bemühe ich mich für das nächste Jahr eine Excel Übersicht zu gestalten in denen der Urlaub der Mitarbeiter eingetragen wird. Hierzu habe ich mich der Vorlage, die bereits bei Excel hinterlegt ist, bedient und diese auf meine Bedürfnisse zugeschnitten.
Für die Spalte AH auf den Arbeitsblättern Jan-Dez habe ich bereits eine Formel gefunden, die die Wochenenden bei der Eintragung nicht berücksichtigt.
Meine 2 Probleme sind folgende:
  • Die Formel in der Spalte AH wollte ich gerne so weit erweitern, dass sie auch die Feiertage (hinterlegt auf dem Arbeitsblatt Sonstiges) nicht berücksichtigt.

  • Je nach Jahr sollen die Ferientermine farblich markiert werden. Problem dabei ist, dass diese sich anpassen sollen, wenn ich in der Zelle AH4 auf dem Januar Arbeitsblatt das Jahr ändere.

  • Datei: https://www.herber.de/bbs/user/124073.xlsx
    Vielen Dank für eure Unterstützung schon mal im Voraus.

    26
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    http://xxcl.de/0016.htm
    19.09.2018 14:31:43
    lupo1
    http://xxcl.de/0016.htm
    ... auch wenn die Formel oben durch ARBEITSTAG ersetzt werden sollte.
    AW: Umsetzung der Ferien und Feiertage
    19.09.2018 14:32:58
    Rudi
    Hallo,
    zuerst aus Mo, Di, Mi, .... ein richtiges Datum machen
    Januar!C5: =DATUM(CalendarYear;1;1); andere Monate entsprechend
    D5: =C5+1; nach rechts kopieren; Zellformat: "TTT"
    AH7: =SUMMENPRODUKT((C7:AG7="u")*((WOCHENTAG($C$5:$AG$5;2)&lt6)*(ZÄHLENWENN(Sonstiges!$F$12:$F$25;$C$5:$AG$5)=0)))
    Gruß
    Rudi
    AW: ne Addition von 1 könnte Probleme bereiten ...
    19.09.2018 15:22:54
    1
    Hallo Rudi,
    ... denn damit könnten in den Monaten ohne 31 Tage schon in Zeile 5 die Folgemonatstage gelistet werden, ohne dass dies bei dem gewählten Zahlenformat gleich erkennbar ist. Und im Februar müsste alle 4 Jahre die Formel bis zum 29. weiter gezogen werden.
    Gruß Werner
    .. , - ...
    Anzeige
    ja, ja, ...
    19.09.2018 21:31:22
    Rudi
    ...Werner, du alter Knötterkopp ;-)
    Weiß ich doch alles selbst.
    Gruß (auch an dein holdes Weib)
    Rudi
    AW: na sicher doch, ...
    20.09.2018 11:20:50
    neopa
    Hallo Rudi,
    ... aber ein Nutzer(in), der seine Excel-Kenntnisse mit "Basiskenntnisse" einstuft, sehr wahrscheinlich nicht. Nur deswegen hatte ich diesen Beitrag eingestellt. Hätte ihn aber vielleicht gleich anders adressieren sollen, da geb ich Dir völlig Recht.
    Gruß (auch für die Deine) Werner
    .. , - ...
    AW: dazu würde ich zuvor einiges ändern ...
    19.09.2018 14:49:07
    neopa
    Hallo Romy,
    ... so in Zeile 5 Deiner Monatsblätter die Formel und das Zahlenformat.
    Wenn Du die Monatsblätter dazu zuvor gruppierst, kannst Du das in reinem "Ritt" tun.
    In C5 folgende Formel: =WENNFEHLER((SPALTE(A1)&"."&Monatsname&"."&CalendarYear)+0;0) mit dem benutzerdefinierten Zahlenformat: TTT. Blattgruppierung danach wieder aufheben.
    Für C5:AG6 würde ich noch eine bedingte Formatierung vorsehen, damit nur die wirklichen Monatstage sichtbar sind und nicht z.B. 29 bis 31 Februar. Die bed. Formatierung erstelle in einem Blatt und übertrage diese dann auf die anderen Blätter.
    Deine Feiertagsdefinition weise noch einen Bereichnamen zu. Z.B.: Ft
    Dann wird die Auswertungsformel einfach und übersichtlicher. =SUMMENPRODUKT((REST(C$5:AG$5;7)>1)*((C7:AG7="U")-ZÄHLENWENN(Ft;C$5:AG$5)*(C7:AG7="U")))
    Gruß Werner
    .. , - ...
    Anzeige
    AW: dazu würde ich zuvor einiges ändern ...
    19.09.2018 16:05:15
    Romy
    Den Abschnitt versteh ich leider nicht, inwiefern ich diesen umsetzen kann:
    "Für C5:AG6 würde ich noch eine bedingte Formatierung vorsehen, damit nur die wirklichen Monatstage sichtbar sind und nicht z.B. 29 bis 31 Februar. Die bed. Formatierung erstelle in einem Blatt und übertrage diese dann auf die anderen Blätter."
    Gruß Romy
    AW: nun, die bedingte Formatierung ...
    20.09.2018 11:32:04
    neopa
    Hallo Romy,
    ... zeig ich Dir nachfolgend am Beispiel Deines Februar-Tabellenblattes auf. Mehr zur bedingten Formatierung sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=74
    Als Geltungsbereich definiert ist für nachfolgender bedingter Formatierung: $C$5:$AG$6
    (anstelle weißen Hintergrundes und weißer Schrift hab ich hier der besseren Darstellung wegen gewählt).
    Die Zellformel C5 ist zuvor bis AG5 ziehend kopiert.
     BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
    4FebruarAbwesenheitstage2019
    5 FrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoSaSaSa 
    6Mitarbeitername1234567891011121314151617181920212223242526272829  Tage

    Formeln der Tabelle
    ZelleFormel
    C5=WENNFEHLER((SPALTE(A1)&"."&Monatsname&"."&CalendarYear)+0;0)
    Namen in Formeln
    ZelleNameBezieht sich auf
    C5CalendarYear=Januar!$AH$4
    Namen verstehen

    Bedingte Formatierungen der Tabelle
    ZelleNr.: / BedingungFormat
    C51. / Formel ist =C$5=0Abc


    Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
    Gruß Werner
    .. , - ...
    Anzeige
    Umsetzung der bedingten Formatierung
    21.09.2018 08:52:59
    Romy
    Vielen Dank für deine Ausführungen. Deine Formel Wennfehler hatte ich zuvor durch deinen ersten Beitrag bereits umgesetzt. Deine Erläuterungen zur bedingten Formatierung haben mir dann den entscheidenden Hinweis gegeben, wie (Gehirnknoten ;) ) ich ansetzen muss.
    Leider hat das nicht so geklappt, wie erwünscht. Könnte jedoch daran liegen, dass bereits eine hinterlegte Bedingung (Vorlage von Excel) dem im Wege stand:
    =MONAT(DATUM(CalendarYear;2;29))<>2 --> Wenn die Bedingung für Zelle AE6 zutrifft, wird die Schrift weiß
    daher habe ich dann die Bedingung kopiert und mit der Formel die Bedingung geknüpft, dass dann für die Zelle AE5 die Schrift schwarz wird.
    Vielen Dank für die Hilfe zur Beseitigung meines ersten Problems. Könnt ihr auch eine Hilfestellung bei dem 2. Problem (Anfangsthread) zur Umsetzung der farblichen Markierung der Ferientage geben?
    Grüße Romy
    Anzeige
    AW: hierzu ...
    21.09.2018 09:02:37
    neopa
    Hallo Romy,
    ... Deiner Bedingungsformel bedarf es nicht (mehr), wenn Du meine Formelvorschläge umgesetzt hast.
    Zu den Feiertagskennzeichnung hatte ich Dir in meinem ersten Beitreag im thread schon einen Vorschlag unterbreitet. Bist Du damit nicht klargekommen?
    Gruß Werner
    .. , - ...
    Farbliche Markierung der Ferien
    21.09.2018 10:47:10
    Romy
    Da ich nicht wusste, ob ich nicht richtig gelesen hab, habe ich mir den ganzen Thread nochmal durchgelesen.
    Deinen folgenden Vorschlag habe ich umgesetzt:
    "Deine Feiertagsdefinition weise noch einen Bereichnamen zu. Z.B.: Ft. Dann wird die Auswertungsformel einfach und übersichtlicher.
    =SUMMENPRODUKT((REST($C$5:$AG$5;7)>1)*((C7:AG7="U")-ZÄHLENWENN(Feiertage_MV;$C$5:$AG$5) *(C7:AG7="U"))) "
    Ich denke du meintest diesen Beitrag. Aber in meiner 2. Bitte ging es nicht um die Feiertage, sondern um die farbliche Markierung der sich von Jahr zu Jahr ändernden Ferien in den einzelnen Arbeitsblättern.
    Gruß Romy
    Anzeige
    AW: nachgefragt ...
    21.09.2018 11:01:13
    neopa
    Hallo Romy,
    ... zunächst aber sorry, da hatte ich mich total verlesen, Du meintest nicht Feiertage sondern die Ferientage.
    Könnten die Ferientage anders angeordnet werden, als das momentan der Fall ist?
    Dieses lässt sich zwar auch auswerten, aber einfacher wäre es wenn je Jahr Beginn und in in zwei Spalten unter einander stehen würden. Deine Daten also quasi um 90° Grad transformiert. Das wäre etwas einfacher.
    Gruß Werner
    .. , - ...
    AW: nachgefragt ...
    21.09.2018 13:16:03
    Romy
    Passiert alles und ist menschlich :)
    Ich habe das fast schon voraus geahnt und hab die Daten schon umgestellt. Der Bereich D1:P10 (2018-2023) ist für Excel als Ferienzeiten_MV definiert.
    Userbild
    Gruß Romy
    Anzeige
    AW: dann ...
    21.09.2018 13:36:54
    neopa
    Hallo Romy,
    ... stell doch bitte Deine aktualisierte Excel-Datei (kein Bild) ein, damit ich dies nicht selbst tun muss. Ich weiß allerdings auch nicht, ob ich heute noch dazu komme, weil ich gleich wieder offline gehe.
    Gruß Werner
    .. , - ...
    AW: bedingte Formatierung ...
    24.09.2018 10:47:07
    neopa
    Hallo Romy,
    ... lösche Deine bedingten Formatierungen in C5:AG6 und definiere neu bei markierten C5:AG6:
    folgende Formel: =C$5=0 und als Format: weiße Schrift auf weißen Hintergrund oder benutzerdefiniertes Zahlenformat: ;;; auf weißen Hintergrund.
    Gruß Werner
    .. , - ...
    Anzeige
    AW: bedingte Formatierung geändert
    24.09.2018 11:38:51
    Romy
    Gemäß deinen Vorgaben hab ich die Formatierung geändert.
    https://www.herber.de/bbs/user/124183.xlsx
    Nachfrage: Die farbliche Markierung der sich von Jahr zu Jahr ändernden Ferien in den einzelnen Arbeitsblättern ist das auch umsetzbar?
    Gruß Romy
    AW: ach ja, die Ferientage ...
    24.09.2018 14:05:01
    neopa
    Sorry Rommy,
    ... an die hatte ich mich heute Vormittag gar nicht mehr erinnert.
    Markiere C5:AG6 und dafür folgende bedingte Formatierungbedingungsformel:
    =SUMME((C$5>=FerienFeiertage!$E$2:$O$10)*(C$5
    Gruß Werner
    .. , - ...
    Anzeige
    Ferientage
    24.09.2018 15:20:37
    Romy
    Ich hab die bedingte Formatierung, wie von dir beschrieben, umgesetzt:
    C5:AG6 markiert und die Formel eingetragen
    =SUMME((C$5>=FerienFeiertage!$E$2:$O$10)*(C$5<=FerienFeiertage!$F$2:$P$10)*(FerienFeiertage!$E$1:$O$1=$AH$4))
    Als Bedingung hab ich angegeben, dass der Hintergrund rot formatiert werden soll, wenn die Formel zutrifft.
    Jedoch rührt sich nix. Wo könnte bei mir der Fehler liegen?
    Gruß Romy
    AW: nachgefragt ...
    24.09.2018 15:48:00
    neopa
    Hallo Romy,
    ... hab momentan Deine Daten nicht vor Augen, hast Du die Formatierung auch für einen Monat vorgenommen wo auch Ferien sind? Ich hatte die Formatierung nur im Februar getestet, weil da auf jeden Fall Ferien sind/waren.
    Falls Du es nicht nachvollziehen kannst, stell noch mal Deine Datei mit der von Dir vorgenommenen Formatierung ein, damit ich erkennen kann, wo der Fehler liegt.
    Gruß Werner
    .. , - ...
    Anzeige
    Gelöst
    24.09.2018 16:10:20
    Romy
    Ich hab den Fehler selbst entdeckt. Die Weihnachtsferien überspringen die Jahre. Unter dem Jahr 2018 war also das Datum bis 05.01.2019 aufgelistet. Das hab ich nun getrennt. In 2018 hab ich die Daten bis 31.12. aufgelistet und bei 2019 die ab dem 01.01. . Ebenso für die Folgejahre.
    Somit funktiert es, wie es soll. Die Formel hab ich entsprechend erweitert.
    Vielen Dank für deine Unterstützung Werner.
    Gruß Romy
    AW: bitteschön owT
    24.09.2018 16:13:03
    neopa
    Gruß Werner
    .. , - ...
    Oh weh .. doch noch nicht fertig
    25.09.2018 09:58:57
    Romy
    Ich hab eine weitere Liste angelegt mit dem Namen Urlaubssperre und auch dafür eine bedingte Formatierung auf den Arbeitsblättern hinterlegt.
    1. Formatierung Ferien:
    =SUMME((C$5>=FerienFeiertage!$E$2:$O$11)*(C$5<=FerienFeiertage!$F$2:$P$11) *(FerienFeiertage!$E$1:$O$1=$AH$4))
    2. Formatierung Urlaubssperre:
    =SUMME((C$5>=FerienFeiertage!$E$15:$O$17)*(C$5<=FerienFeiertage!$F$15:$P$17) *(FerienFeiertage!$E$14:$O$14=$AH$4))
    Nun wollte ich eine dritte bedingte Formatierung hinterlegen, wenn sich die Ferien und die Urlaubssperre überschneiden. Jedoch funktioniert die Formel nicht so wie ich möchte.
    3. Formatierung Überschneidung Ferien/Urlaubssperre:
    =WENN((SUMME((C$5>=FerienFeiertage!$E$2:$O$11)*(C$5<=FerienFeiertage!$F$2:$P$11) *(FerienFeiertage!$E$1:$O$1=$AH$4)))=(SUMME((C$5>=FerienFeiertage!$E$15:$O$17)*(C$5< =FerienFeiertage!$F$15:$P$17)*(FerienFeiertage!$E$14:$O$14=$AH$4)));WAHR;FALSCH)
    Im Januar Arbeitsblatt markiert er mir bspw. den 11.01.2019 bis 31.01.2019, obwohl für diesen Zeitraum keine Ferien oder Urlaubssperren hinterlegt sind.
    Gruß Romy
    AW: stell dazu wieder Deine Datei ein owT
    25.09.2018 12:40:35
    neopa
    Gruß Werner
    .. , - ...
    AW: aber diesemal als xlsm, warum? ...
    25.09.2018 18:30:31
    neopa
    Hallo Romy,
    ... ich lade mir jedenfalls immer nur xlsx-Dateien aus Netz herunter.
    Gruß Werner
    .. , - ...

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige