44 freie Stunden für jede Periode von 7 Tagen

[Alooha]

Hallo,

leider habe ich keinen aussagekräftigeren Titel für den Thread gefunden.

Ich erläutere worum es geht.

Angestellte einer Dienststelle, in der auch nachts gearbeitet wird, haben laut Kollektivvertrag Recht auf einen zusätzlichen Urlaubstag wenn sie während 7 Perioden von 7 Tagen (wobei die Perioden nicht zusammenhängend sein müssen), nicht während 44 Stunden am Stück frei sind.
Das Zählen beginnt am 1.1. (oder am Tag an dem der Arbeitsvretrag beginnt, im Fall wo er erst im laufenden Jahr beginnt) und endet am 31.12. (oder am letzten Arbeitstag, falls der Vertrag im laufenden Jahr endet).
D.h.: die erste Periode geht vom 1.1. bis zum 7.1., die zweite beginnt am 8.1., usw. Falls der/die Angestellte zwischen dem 1. und 7.1. nicht 44 St. frei hat ist das also die Nummer 1 im Zähler der bis 7 geht.

Die Angestellten geben per Computer alle Stunden ein im Zusammenhang mit der Arbeit. Dazu hat jeder ein Blatt mit einem Formular in einem Workbook zur Verfügung, das für jede Stunde die der Monat hat ein Kästchen vorsieht, in das sie den treffenden Stundentyp eingeben: ein X für die Arbeitsstunden, ein U für die Urlaubsstunden, ein K für die Krankheitsstunden, usw.

Ich überspringe jetzt mal eine Etappe und setze voraus, dass ich es hingekriegt habe, die benötigten Daten in einer Tabelle zusammenzutragen. Um diese Etappe werde ich mich später kümmern müssen.
Zu diesem Zweck habe ich eine Situation simuliert: eine Tabelle, die die Daten von 3 Angestellten für Januar und Februar enthält:

der Aufbau der Tabelle:

A: der Name
B: das Datum
C: das Einstellungsdatum (leer ausser wenn das Datum im laufenden Jahr liegt)
D: das Datum an dem der Kontrakt endet (leer ausser wenn das Datum im laufenden Jahr liegt)
E: die Anzahl der im Stundenblatt ausgefüllten Kästchen für das Datum unter B
F: 24 Stunden die der Tag hat - die Anzahl der ausgefüllten Kästchen = die Stunden außerhalb der Arbeit
G: die Stunden aus F; in den Zeilen die die Tage betreffen, die außerhalb des Arbeitsvertrages liegen (davor oder danach) steht jetzt "AV"  für "außerhalb des Vertrags"
H: = G weniger die "AV"-Einträge, d.h die reinen zu zählenden Stunden

Ich hoffe, mein Anliegen verständlich gemacht zu haben.
Hat jemand von euch Experten eine Idee wie das zu bewerkstelligen wäre?
Am besten wäre natürlich -aber das geht nur per VBA- wenn aus meiner Tabelle eine Liste erstellt würde, die nur die betroffenen Angestellten enthalten würde, mit den 7 Perioden wo sie nicht während 44 Stunden frei hatten.
Mit freundlichen Grüßen
Alooha

[Ego]

Hallo A...,

hier noch einige Fragen und Bemerkungen zu deiner Anfrage:

A) Welcher Teil deiner Beschreibung gehört zum Vertrag und welcher Teil sind Erläuterungen von dir?

Bei den folgenden Bemerkungen gehe ich davon aus, dass dieser Text Vertragsbestandteil ist:

Angestellte einer Dienststelle, in der auch nachts gearbeitet wird, haben laut Kollektivvertrag Recht auf einen zusätzlichen Urlaubstag wenn sie während 7 Perioden von 7 Tagen (wobei die Perioden nicht zusammenhängend sein müssen), nicht während 44 Stunden am Stück frei sind.

Das Zählen beginnt am 1.1. (oder am Tag an dem der Arbeitsvretrag beginnt, im Fall wo er erst im laufenden Jahr beginnt) und endet am 31.12. (oder am letzten Arbeitstag, falls der Vertrag im laufenden Jahr endet).

B) Die Berechnung kann mit den von dir vorgegebenen Daten (hier Anzahl der leeren Kästchen pro Tag) nicht richtig erfolgen.
Notwendig ist eine Liste der Arbeitszeiten mit Zeitpunkten für Arbeitsbeginn und Arbeitsende.
Beispiel:
Wenn ein Mitarbeiter am Montag 12, am Dienstag 24 und am Mittwoch wieder 12 "leere Kästchen" hat, könnte er
1. Montags in den ersten 12 Stunden und Mittwoch in den letzten 12 Stunden gearbeitet haben und damit 48 (>44) Stunden frei gehabt haben oder
2. Montags in den letzten 12 Stunden und Mittwoch in den ersten 12 Stunden gearbeitet haben und damit 24 (<44) Stunden frei gehabt haben.

C) zu:

D.h.: die erste Periode geht vom 1.1. bis zum 7.1., die zweite beginnt am 8.1., usw. Falls der/die Angestellte zwischen dem 1. und 7.1. nicht 44 St. frei hat ist das also die Nummer 1 im Zähler der bis 7 geht.

Das ist falsch und wäre, wenn es trotzdem Bestandteil des Vertrages wäre, sehr dumm.
Wenn die 7TagesPerioden wirklich so starr wären könnte
1. ein Mitarbeiter, der in 7 dieser starren Wochen zufällig keine arbeitsfreie Zeit>44 Stunden hatte einen zusätzlichen Urlaubstag bekommen
2. ein Mitarbeiter der in 25 Wochen immer von Mitte der einen starren 7TagesPerioden bis zur Mitte der nächsten 7TagesPeriode arbeitet keinen zusätzlichen Urlaubstag bekommen.

Richtiger (oder nicht so dumm) wäre, dass eine 7TagesPeriode beginnt:
1. zum Arbeitsanfang nach einer arbeitsfreien Zeit >= 44 Stunden  oder
2. nach einer 7TagesPeriode ohne Arbeitsfreier Zeit von 44 Stunden.

Eine Berücksichtigung von Arbeitsvertragsgrenzen wäre dann nicht mehr notwendig, da ein Mitarbeiter wohl kaum ausserhalb dieser Grenzen arbeitet.

[Alooha]

Hallo Helmut,

vielen Dank, dass du dich so ausführlich mit meiner komplizierten Sache beschäftigt hast.

ad A: der erste Teil des Zitats gibt die Bestimmung des Kollektivvertrags wieder, der zweite ist schon Interpretation.

ad B: deine Bemerkung ist richtig und hat mich schon mal gleich "erschlagen". Das Problem ist, dass die Stunden nun mal auf die beschriebene Art und Weise erhoben werden (siehe Screenshot im Anhang).
Die nächste Frage wäre dann: ist es möglich, Excel dazu zu bringen, die Kästchenmethode in Zeiten umzusetzen? Falls nicht, kann ich die Sache vergessen und es muß eine andere Methode, eine manuelle, angewandt werden.

ad C: schwieriger; alles Interpretation von mir; muß nicht unbedingt richtig sein
ad 1 unter "Falsch": das entspricht aber der Regel; während 7 Wochen keine 44 St. frei. Zu beachten ist ist, dass derjenige der den Arbeitsplan erstellt, diese Situation nach Möglichkeit zu verhindern sucht.,
ad 2 unter "Falsch": dieser Fall wird nicht eintreten; der Verantwortliche für den Arbeitsplan verteilt die Stunden nicht so
Uebrigens: ob eine Regelung dumm ist oder nicht hängt vom Betrachter ab: was für den Arbeitgeber dumm ist kann das Gegenteil für den Arbeitnehmer sein.

unter "Richtiger": es ist ja so, dass nach "Wochen" gerechnet wird, und ich hatte mir vorgestellt, so sei es am richtigsten. Allerdings ist es ein zusätzliches Problem, wenn die 7Tagesperioden nicht für alle Arbeitnehmer gleich sind und eine zusätzliche Herausforderung, um dem Rücksicht zu tragen. Einfacher wäre es, wenn die Perioden für alle gleich wären, also von Montags bis Sonntags (ich hatte zu erwähnen vergessen, dass 365 Tage gearbeitet wird).
D. h. wenn der 3.1. ein Montag ist, dann ist die erste Woche eben kürzer und sie wird eventuell überhaupt nicht berücksichtigt, sondern nur volle Wochen.

Eine verzwickte Sache.

Mit freundlichen Grüßen

Alooha

P.S.: das Anhängen einer JPEG Datei funktioniert nicht; ich kriege die unsinnige Fehlermeldung: "Die angehängte Datei ist zu groß. Die maximal zulässige Größe für diesen Dateityp beträgt 0 Kilobyte."

[Alooha]

HAllo,
was die wöchentliche Ruhezeit von 44 St. betrifft, setzt der Kollektivvertrag lediglich die gesetzlichen Bestimmungen des Arbeitsrechts um.

Und im Gesetz steht (frei aus dem franz. übersetzt; es handelt sich um das lux. Arbeitsrecht):

"Während jeder Periode à 7 Tage hat jeder Arbeitnehmer Anrecht auf eine ununterbrochene Ruhezeit von wenigstens 44 Stunden. Nach jeder wöchentlichen Ruhezeit von wenigstens 44 Stunden hat er binnen der nächsten 7 Tagen Anrecht auf eine erneute Ruhepause von wenigstens 44 Stunden."

Die nächste Periode von 7 Tagen beginnt also ganz klar erst nach der Ruhepause.

Uebrigens hatte ich mich geirrt was die Anzahl der Wochen betrifft: es sind 8 und nicht 7.

MfG
Alooha

[Ego]

Hallo A...,

zu:

Die nächste Frage wäre dann: ist es möglich, Excel dazu zu bringen, die Kästchenmethode in Zeiten umzusetzen?

Ja. (siehe Anlage)
Da ich innerhalb des Tages keine 44 Stunden Pause haben kann, ist für die Berechnung nur der erste Arbeitsbeginn und das letzte Arbeitsende des Tages interessant.



zu:

Die nächste Periode von 7 Tagen beginnt also ganz klar erst nach der Ruhepause.

das entspricht meiner Interpretation: "eine 7TagesPeriode beginnt zum Arbeitsanfang nach einer arbeitsfreien Zeit >= 44 Stunden" und ist in der Anlage realisiert.

[Alooha]

Hallo,

vielen Dank. Eine beachtliche Leistung!

Nur:
* es gibt nicht nur "X"-Einträge, sondern eine ganze Reihe andere, sodass das einfachste wohl wäre, nach den leeren Kästchen zu suchen, anstatt nach den besetzten, oder aber das "X" in den Formeln durch "*" zu ersetzen, falls das funktioniert.
* meine Excel-Version kann nichts mit den Funktionen _xlfn.MINIFS() und _xlfn.MAXIFS() anfangen. Gibt es keine anderen, durch die man sie ersetzen kann?
Odser kann ich diese Formeln in meine Excel-Version (MS Office 365 ProPlus) integrieren?

Mit freundlichen Grüßen
Alooha

[mumpel]

Hallo!

"MAXIFS" und "MINIFS" (Deutsch: MINWENNS und MAXWENNS) sind normalerweise in Office 365 (entspricht bei mir Office 2016) enthalten. Wenn Du mit dem französischen Office arbeitest lauten die Funktionen MIN.SI und Max.SI. https://de.excel-translator.de/translator/

Gruß, René

[Ego]

Hallo A...,

A) Ersatz von MINWENNS/MAXWENNS
in älteren Excelversionen kann man zB MINWENNS durch eine Min(Wenn()) Konstruktion ersetzen (siehe Anlage).
Man muss die Formel dann nur bei jeder Änderung als Matrixfunktion mit Strg+Shift+Enter abschliessen.

B) Andere Einträge als "X"
Bisher hattest du nur die Einträge "K" und "U" erwähnt.
Da ich annehme, dass eure Mitarbeiter sowohl bei Krankheit als auch im Urlaub nicht arbeiten, war ich der Meinung, dass auf jeden Fall ein Urlaubseintrag aber auch ein Krankheitseintrag die arbeitsfreie Zeit nicht unterbrechen.

Ihr könnt ja intern klären, welche Einträge die arbeitsfreie Zeit unterbrechen.
1) Falls wirklich alle Einträge die arbeitsfreie Zeit unterbrechen kann man " ="X" " durch " <>"" " ersetzen.
2) Ansonsten können die Einträge, die die arbeitsfreie Zeit unterbrechen, mit der ODER-Funktion verbunden werden. Aber das solltest du selbst schaffen.

[Alooha]

Hallo,

@René: ich benutze ein deutsches Excel dem diese Funktionen unbekannt sind.

@Helmut: es ist so, dass alle Eintragstypen die arbeitsfreie Zeit unterbrechen. D.h. alle Einträge werden gezählt. Uebrigens gibt es in der Wirklichkeit 31 verschiedene!
Und "<>"" funktioniert nicht:
WENN(ZÄHLENWENNS(C14:Z14;"<>""")=0;"";MIN(WENN($C14:$Z14="<>""";$C$1:$Z$1))) ergibt #Wert.

WENN(ZÄHLENWENNS(C14:Z14;"*")=0;"";MIN(WENN($C14:$Z14="*";$C$1:$Z$1))) sowie
WENN(ZÄHLENWENNS(C14:Z14;""*"")=0;"";MIN(WENN($C14:$Z14=""*"";$C$1:$Z$1)))
funktionieren auch nicht
MfG
Aloha

[Alooha]

...ich hatte, vergessen, die Formel als Matrixformel einzugeben; das habe ich nachgeholt, aber als Resultat gibt es 0