Urlaubstage ohne Wochenende zählen!

[dreckfrosch]

Hallo liebe Helferlein,

anbei findet Ihr einen Entwurf für einen Urlaubsplan, hier exemplarisch April 2017.

Urlaubsplan.ods

(17.67 KiB) 389-mal heruntergeladen

Die Wochenenden sind mittels bedingter Formatierung markiert.

1. Frage:
Wie kriege ich in Spalte AF die Urlaubstage addiert, ohne das die eingetragenen "U" an Wochenenden und Feiertagen mitgezählt werden?
An den Wochenenden einfach keine "U" einzutragen, ist keine Alternative!

2. Frage:
Besteht die Möglichkeit auch die Feiertage (für Niedersachsen) automatisch erkennen, und einfärben zu lassen?
(hier im April habe ich es händisch gemacht und für die Zellen die gleiche Formatvorlage, wie für Wochenenden genutzt.)

Ich danke schon jetzt für die Unterstützung.


Viele Grüße

[mikele]

Hallo,

zu Frage1: Die Zählung der Urlaubstage (ohne Wochenenden) kannst du mit folgender Formel lösen:

Code: Alles auswählen

=SUMMENPRODUKT(B5:AE5="U";WOCHENTAG($B$3:$AE$3;2)<6)

zu Frage2: Dazu müssten zunächst auf einem Extrablatt die Feiertage hinterlegt werden. Einige Forenbeiträge haben sich bereits diesem Problem angenommen.

[paljass]

Hi,
dafür gibts die Funktion =Nettoarbeitstage() - näheres dazu in der Hilfe.
Die extra Liste mit Feiertagen brauchts dafür natürlich auch.

Gruß
paljass

[balu]

Hallo dreckfrosch,

deine Wünsche lassen sich eigentlich schon erledigen, ABER da Du 2 Bundesländer aufgeführt hast, wird die ganze Sache schon komplizierter. 2 mal unterschiedliche Feiertage, und 2 mal unterschiedliche Ferien.

-> Wie willst Du da differenzieren?
-> Welches Bundesland spielt die Hauptrolle?
-> Wie hast Du dir das ganze genau vorgestellt?
-> Hast Du eine seperate Liste wo die Mitarbeiter nach ihren Bundesländern aufgeteilt sind?

Fragen über Fragen, die aber geklärt werden müssen da sich das alles auf die Urlaubstageberechnung niederschlägt. Bei nur einem Bundesland wäre das zumindest für mich eine Sache die ich Ratz-Fatz erledigt hätte, aber bei 2 Bundesländern sieht die Sache schon ganz anders aus.



Gruß
balu

[dreckfrosch]

Hallo,

sorry, die Ferienzeilen dienen nur als Hinweis für die Mitarbeiter, da werde ich die Ferien einfach händisch einfärben, damit sie bei der Urlaubspalnung parat sind.
Es geht ausschließlich um die Berücksichtigung der Feiertage in Niedersachsen!!!

Gruß

[balu]

sorry, ....

Brauchst dich doch nicht zu Entschuldigen. Selbst wenn Du 2 Bundesländer verwalten müsstest, so würde man dies hinbekommen, man muss dann nur im Vorfeld alles abklären.

die Ferienzeilen dienen nur als Hinweis für die Mitarbeiter

Gut zu wissen.

da werde ich die Ferien einfach händisch einfärben, damit sie bei der Urlaubspalnung parat sind.

Brauchst Du doch nicht. Es müssen lediglich 2 seperate Datenbereiche für die Ferien angelegt werden damit eine Formel darauf zugreifen kann. Der Rest geschiet automatisch.

Es geht ausschließlich um die Berücksichtigung der Feiertage in Niedersachsen!!!

Auch gut zu wissen.
Da wird für die Feiertage auch eine seperate Tabelle angelegt, worauf eine Formel zugreift. Rest geht dann auch automatisch.


So, und nun zu der angehängten Datei von mir.

Eins vorweg, ich habe unterhalb der "Kalenderzeile" noch ein paar weitere Zeilen eingefügt damit meine kommenden Formeln dort untergebracht werden können. Diese zusätzlichen Zeilen kannst Du bei bedarf Ausblenden.

Da ja selbst bei nur einem Bundesland die Bedingung vorgegeben ist: *Urlaubstage am Wochenende und an Feiertagen nicht mit aufsummieren*, habe ich folgende Formel eingesetzt.
Und zwar in der Zeile 4

Code: Alles auswählen

=WENN(ZÄHLENWENN(Feiertage.$B$5:$B$9;B$2);1;WENN(WOCHENTAG(B$2;2)>5;1;""))
 

Mit ZÄHLENWENN wird überprüft ob das zu vergleichende Datum in der Feiertagsliste vorhanden ist, siehe Tabellenblatt *Feiertage*. Wenn ja, dann wird eine 1 ausgegeben.
Sollte das zu vergleichende Datum kein Feiertag sein, dann wird überprüft ob es sich um ein Wochenende handelt, WOCHENTAG(B$2;2). Die 2 in WOCHENTAG ztählt die Tage von Montag mit 1 bis Sonntag mit 7. Dadurch ist es einfacher ein Wochenende zu bestimmen, >5. Und wenn es ein Wochende ist, wird auch eine 1 ausgegeben.

Jetzt müssen nur noch die zutreffenden Bedingungen summiert werden, und das geschieht mit dieser Formel.

Code: Alles auswählen

=SUMMENPRODUKT(B7:AE7="u";B$4:AE$4="")
 

SUMMENPRODUKT zählt jetzt nur die Zellen zusammen die;

1.
ein "u" beinhalten.

und 2.
in der Zeile 4 Leer sind "".

ACHTUNG! SEHR WICHTIG!
In den Zellen dürfen nur kleine u eingetragen werden, bei großen U zählt SUMMENPRODUKT Falsch.

Die farbliche Markierung für Wochende und Feiertag habe ich nicht geändert.

Damit wäre das Thema Urlaub erledigt.


Kommen wir zu den Ferien.
In der Zeile 5 habe ich folgende Formel.

Code: Alles auswählen

=SUMMENPRODUKT(B$2>=$Ferien.$B$5:$B$10;B$2<=$Ferien.$C$5:$C$10)
 

Im Tabellenblatt *Ferien* gibt es 2 Bereiche für die Ferien in Niedersachsen und in NRW. Beide sind aber vom Aufbau identisch. Das heißt, das der Ferienbeginn in der Spalte mit der Überschrift VON rein kommt, und das Ferienende in die Spalte BIS.

Anders ausgedrückt.
In Spalte VON kommt der erste Ferientag, und in Spalte BIS der letzte Ferientag.

Und wenn es sich nur um einen einzigen Ferientag handelt, sie NRW, dann wird dieser einzelne Ferientag bei VON als auch bei BIS eingetragen. Das musst du dir unbedingt merken!

SUMMENPRODUKT durchsucht also in der Ferienliste ob das zu vergleichende Datum zwischen VON und BIS liegt, und wenn ja, dann wird eine 1 ausgegeben.

Und diese Zeile 5 fragt die Bedingte Formatierung ab, und wenn dort zu dem vergleichendem Datum eine 1 steht, dann wird das Datum als Feriendatum markiert.

Das war im groben die Beschreibung für die Ferien in Niedersachsen. Und genau so verhällt es sich mit den Ferien für NRW, einzig der Unterschied ein anderer Feriendatenbereich und die Formel dafür steht in der Zeile 6.

Du kannst natürlich die Farben für die Ferien nach Lust und Laune ändern, gar so wie es dir gefällt. Ich habe da mal nur 2 farben willkürich eingestellt.


Die Daten in den Tabellenblättern *Ferien* und *Feiertage* sind momentan für das Jahr 2016 eingestellt. Für das Jahr 2017 musst Du dort die Daten ändern.
Und ich habe dort die Bereiche farblich markiert wo Du die Daten eingeben kannst ohne die Formeln ändern zu müssen.


Jetzt noch etwas allgemeines.
In der Zelle A1, hattest Du von Hand ja eingegeben: *Urlaub April 2017*. Und im Anschluß musstest Du das Startdatum in der Zelle B4 von Hand eintragen. Das habe ich jetzt geändert, da einfacher.

In A1 gibst Du das gewünschte Datum ein, z.B. 1.8.2016, und schon wird dein Titeltext dort angezeigt und die Kalenderzeilen passen sich automatisch diesem Datu an. Und Fäädddiscccch


Sollte aus einem mir unerklärlichen Grunde die Datei bei dir nicht korrekt funktionieren, so sag bescheid, aber nenne dann auch deine LO Version und zwar die ganz genaue Versionsnummer.



Gruß
balu

[dreckfrosch]

...was soll ich sagen. ich bin mal wieder absolut überwältigt, wieviel mühe ihr euch hier alle für wildfremde noobs gebt!

ich habe nichts hinzuzufügen...balu...die datei funktioniert und zwar zu 100%!

vielen dank, ihr habt mir sehr geholfen!



bis zum nächsten mal!