Nicht-Wochenarbeitstage ausgrauen

[tine239]

Hello zusammen,

ich erstelle derzeit einen Urlaubskalender für mein Team - so gut es geht dynamisch anhand der Jahreszahl; Wochenenden und Feiertage automatisch farblich hervorheben sowie Ferien nach manueller Eingabe ebenfalls.

Wir haben mehrere Teilzeitkräfte, die an unterschiedlichen Tagen in der Woche arbeiten. Wenn möglich würde ich dies ebenfalls farblich kennzeichnen - sodass deutlich wird, dass bspw. Freitag kein Arbeitstag ist und die Person diesen Tag bei der Urlaubsplanung nicht vergessen hat; sondern grundsätzlich nicht da ist. Dabei müssen ungerade und gerade Wochen ebenfalls berücksichtigt werden, wenn bspw. nur Mittwoch in der ungeraden Wochen nicht gearbeitet wird - in der geraden Woche aber schon. 

Ich hoffe, ihr könnt mir folgen und ein paar Tipps geben. Mein Hirn bekommt das nicht auf die Reihe. 


Liebe Grüße

[slowboarder]

Hi

du könntet als Formel für die Bedingte Formatierung die Funktion NettoArbeitstage.INTL verwenden.
diese zählt die Arbeitstage in einem Zeitraum (kann auch ein Tag sein), wobei Wochenenden bzw freie Tage in der Woche angegeben und Feiertage berücksichtigt werden können.

die freien Tage gibt man als Text aus 0 (Arbeitstag) und 1 (freier Tag) für die sieben Wochentage beginnend mit Montag an.

also wäre die Formel in etwa so, hier als Beispiel für eine Person, die wechselweise Mo-Mi und Mi-Fr arbeitet.

=NETTOARBEITSTAGE.INTL(I$5;I$5;WENN(ISTGERADE(ISOKALENDERWOCHE(I$5));"0001111";"1100011");'Feiertage NRW'!$B$4:$B$20)

diese Texte kannst du dir ja in deiner Liste "Wochenarbeitstage" erstellen und dann über den SVerweis bzw einfach die Reihenfolge der Zeilen dem jeweiligen Mitarbeiter zuspielen.

die Formel gibt dir dann 1 (Arbeitstag) oder 0 (freier Tag) aus, was du auch in der Bedingten Formatierung nutzen kannst.

Gruß Daniel

[Sigi.21]

Hallo Tine,

anbei eine Lösung mit VBA-Funktion. (Mit Formeln war mir zu kompliziert.)

Um die Verarbeitung mit VBA zu ermöglichen, musste ich einige Änderungen vornehmen:
- Leerzeilen zwischen den Daten gelöscht
- verbundene Zellen gelöscht
- unnütze Doppelangaben (Datum und WoTag) 1x gelöscht
- gerade / ungerade KW als 0 und 1
- WochenarbeitsTage und freie Tage als 1100111 (Mo-So)
- Feiertage zu arbeitsfreie Tage umfunktioniert
- etc.

Gruß Sigi

[EA1950]

Hallo,

da du Excel365 hast, habe ich dir eine Formellösung gebaut.

Änderungen/Ergänzungen:
1) Im Arbeitsblatt "Urlaubsübersicht" habe in Zeile 2 eine Kalenderwochenzeile (mit bed.Formatierungen) eingefügt. Diese dient nur zur Information und wird nicht zum Funktionieren der übrigen Funktionen benötigt.

2) In den Zellen I2, I3 und I4 befinden sich jeweils Array-Funktionen für die Datierungen.

3) Die bedingten Formatierungen für die Feiertage, Ferientage, Rosenmontag habe ich auf benutzerdef. Funktionen umgestellt. Diese sind jetzt im Namensmanager zu finden.

4) Die Kennzeichnung der Arbeitstage für die einzelnen Mitarbeiter habe ich bedingte Formatierung umgestellt (grün) mit Hilfe folgender benutzerdefinierten Funktion "Ist_MA_Arbeitstag" (im Namensmanager zu finden):

PHP-Code:

/**
Funktion gibt WAHR zurück falls Arbeitstag; sonst FALSCH.
Parameter 'TeamNr' = Nummer des Teams (1 bzw. 2)
Parameter 'MA' = Mitarbeitername (zB.in A7:A13)
Parameter 'Dat' = Tagesdatum (zb. in J4:NJ4)
Parameter 'Kz' = Kennzeichen (Optional; steht für "x" wenn nicht angegeben)
*/
Ist_MA_Arbeitstag = LAMBDA(TeamNr; MA; Dat; [Kz];
    WENN(
        ODER(IstFeiertag(Dat); IstWochenende(Dat); IstRosenmontag(Dat));
        FALSCH;
        LET(
            rngTeam; WAHL(TeamNr; Team_1; Team_2);
            MaZl; XVERGLEICH(MA; INDEX(rngTeam; 0; 1); 0);
            ArbWo; WENN(ISTUNGERADE(ISOKALENDERWOCHE(Dat)); 1; 6);
            INDEX(rngTeam; MaZl; ArbWo + WOCHENTAG(Dat; 2)) = WENN(WURDEAUSGELASSEN(Kz); "x"; Kz)
        )
    )
);; 


Dabei habe ich als arbeitsfreie Tage die Feiertage, das Wochenende und den Rosenmontag berücksichtigt - die Ferientage habe ich nicht berücksichtigt. Ansonsten müsste man die Ferien mit der Funktion "IstFerientag(Dat)" noch hineinnehmen.

5) Im Arbeitsblatt "Wochenarbeitstage" habe ich 2 benannte Bereiche erstellt (im Namensmanager):
Bereich "Team_1" = Wochenarbeitstage!$A$6:$K$12  (blau umrandet)
Bereich "Team_2" = Wochenarbeitstage!$A$16:$K$21 (blau umrandet)

6) Im Arbeitsblatt "Feiertage NRW" habe ich aus der Feiertagstabelle die intelligente Tabelle "tbFeiertage" erstellt.

7) Im Arbeitsblatt "Ferien NRW" habe ich aus der Ferientabelle die intelligente Tabelle "tbFerien" erstellt.

8) Die Ermittlung der Urlaubstage in Urlaubsübersicht!$C:$C dürfte ja kein Problem sein. An den gewünschten Tagen, sofern sie grün gefärbt sind, ein "U" einfügen und in Spalte C mit =ZÄHLENWENN(Bereich;"U") zusammenzählen.