Office: (Office 365) Formel für Berrchnung Pause zwischen >6h und <06,5hAnwesenheit

Hallo zusammen,

ich berechne aktuell die Pausenzeit wenn die Anwesenheit >6h ist -30min.

Die Berechnung ist so leider nicht ganz korrekt. Wenn zB. jemand nach 6:30 geht, sollen nur 0,30 abgezogen werden. Wenn jemand nach 6:02 geht sollen nur 0:02 abgezogen werden.
Wenn die Anwesenheit >6,5h ist werden 0,5 bzw. 30 min abgezogen. Wenn die Anwesenheit <6h ist, wird nichts abgezogen.

Berechnen tu ich das heute so (ist soweit korreckt, nur nicht wenn jemand zwischen 6,00 und 6,50 geht dann soll nur die Differenz abgezogen werden.

Wie können wir das umstellen? Evtl. wie weiter unten?
betroffener Formelteil:

WENN(ODER(REST($L7-$K7;1)>=ZEIT(6;;);REST($J7-$I7;1)>=ZEIT(6;;);REST($H7-$G7;1)>=ZEIT(6;;));ZEIT(;30;);0))


Komplette Formel falls für Euch wichtig?
Der erste Teil sucht nach der Schicht F, FSEVZ, usw.
=WENN(ODER($C7="F";$C7="FSEVZ";$C7="SLF";$C7="SLT";$C7="S";$C7="SLS";$C7="X");

Hier der betroffene Teil oben beschrieben:
WENN(ODER(REST($L7-$K7;1)>=ZEIT(6;;);REST($J7-$I7;1)>=ZEIT(6;;);REST($H7-$G7;1)>=ZEIT(6;;));ZEIT(;30;);0))

Hier werden die Zeiten berechnet nur wenn anwesend zwischen 21:30 und 22:00
ist eine andere Bedingung wie oben beschrieben:
+WENN(ODER($C7="SLSN");MAX(;MIN($I$3+($H$3>$I$3);$H7+($G7>$H7))-MAX($H$3;$G7))+MAX(;(MIN($I$3;$H7+($G7>$H7))-$G7)*($H$3>$I$3))+MAX(;MIN($I$3+($H$3>$I$3);$H7+0)-$H$3)*($G7>$H7)+MAX(;MIN($I$3+($H$3>$I$3);$J7+($I7>$J7))-MAX($H$3;$I7))+MAX(;(MIN($I$3;$J7+($I7>$J7))-$I7)*($H$3>$I$3))+MAX(;MIN($I$3+($H$3>$I$3);$J7+0)-$H$3)*($I7>$J7)+MAX(;MIN($I$3+($H$3>$I$3);$L7+($K7>$L7))-MAX($H$3;$K7))+MAX(;(MIN($I$3;$L7+($K7>$L7))-$K7)*($H$3>$I$3))+MAX(;MIN($I$3+($H$3>$I$3);$L7+0)-$H$3)*($K7>$L7);0)

Hier werden die Zeiten berechnet nur wenn anwesend zwischen 03:30 und 04:00
ist eine andere Bedingung wie oben beschrieben:
+WENN(ODER($C7="N";$C7="NSEVZ";$C7="SLN");MAX(;MIN($I$4+($H$4>$I$4);$H7+($G7>$H7))-MAX($H$4;$G7))+MAX(;(MIN($I$4;$H7+($G7>$H7))-$G7)*($H$4>$I$4))+MAX(;MIN($I$4+($H$4>$I$4);$H7+0)-$H$4)*($G7>$H7)+MAX(;MIN($I$4+($H$4>$I$4);$J7+($I7>$J7))-MAX($H$4;$I7))+MAX(;(MIN($I$4;$J7+($I7>$J7))-$I7)*($H$4>$I$4))+MAX(;MIN($I$4+($H$4>$I$4);$J7+0)-$H$4)*($I7>$J7)+MAX(;MIN($I$4+($H$4>$I$4);$L7+($K7>$L7))-MAX($H$4;$K7))+MAX(;(MIN($I$4;$L7+($K7>$L7))-$K7)*($H$4>$I$4))+MAX(;MIN($I$4+($H$4>$I$4);$L7+0)-$H$4)*($K7>$L7);0)

Danke Euch.

grüße
Marcel

 

Hallo Marcel,

derartige Berechnungen wie Du sie anstrebst, wurde schon in allen möglichen Varianten mehrfach mit Excel in verschiedensten Foren behandelt. Google mal danach. Und wenn Du dazu konkrete Nachfragen hast, dann solltest sinnvollerweise nicht (nur) Deine Formel aufzeigen, denn wer wird schon Deine spez. Datenstruktur nachstellen. (D)eine (Beispiel-) solltest Du dann schon auch mit einstellen.

 

Hallo Werner,

ich habe das Netz bereits mehrfach durchsucht, finde zahlreiche Sachen zum Thema Arbeitszeit. Allerdings nicht
Wenn zB die Anwesenheitszeit >6:00 bis <6:30 bzw. die Zeit dazwischen berechnet werden soll bzw. die Differenz, finde ich leider nichts.

Ich habe eine Beispieldatei angehängt. Gleich mit einem Beispiel was die Formel ausspucken müsste ;-)
Vielleicht kannst Du oder sonst jemand mich da ja unterstützen - wäre super ;-)

Grüße
Marcel

 

Hallo Marcel,

Deine bisherige Beispieldaten sind sämtlich Zeiten innerhalb eines Tages.
Dafür (und nur dafür) würde ich z.B. folgende Formel in M7 einsetzen:
=MIN(MAX((SUMMENPRODUKT((LINKS(G$6:I$6;1)="K")*(H7:J7-G7:I7))*(K7=0)-1/4)*(K7=0);0);1/48)
und dies Formel nach unten kopieren.

Für Zeiten die über Mitternacht hinausgehen solltest Du noch Soll-Ergebnisdaten für weitere Beispieldaten angeben.

 

Ich spiele jetzt mal <scheißarrogant>

Mich wundert, dass meine Formel hier immer noch nicht als Standard akzepiert wird. Gerade Du, neopa, solltest sie eigentlich mittlerweile inhaliert haben (so oft, wie Du Dich zu dem Thema äußerst):

</scheißarrogant>

B1: =SUMMENPRODUKT((A1>{0.24.26.36.37}/96)*(A1-{0.24.26.36.37}/96)*{1.-1.1.-1.1}) für die geminderte Zeit
B1: =A1-SUMMENPRODUKT((A1>{0.24.26.36.37}/96)*(A1-{0.24.26.36.37}/96)*{1.-1.1.-1.1}) für die Pausensumme

Im Grunde ist sie aber betragsmäßig Bullshit. Denn das Gesetz wird bestimmt 1/12 statt 1/13 anteilig Pause an der Gesamtzeit beabsichtigt haben. Das bedeutet, dass man eigentlich die Pause inkludieren müsste, statt anzuhängen. Ist jedoch bei keiner Zeiterfassung so. Hier also mein Plädoyer für eine arbeitnehmerfreundliche Gesetzesinterpretation (obwohl ich selbst aus der Unternehmersphäre bin):

B1: =SUMMENPRODUKT((A1>{0.22.24.35.36}/96)*(A1-{0.22.24.35.36}/96)*{1.-1.1.-1.1})

Noch eine andere Deutung will auch nach der 1. Pause volle 3 Stunden arbeiten lassen, so wie man vor der 1. Pause schon volle 6 Stunden arbeiten musste. Das wäre dann zwar doof, aber zumindest folgerichtig für 1/13 statt 1/12:

B1: =SUMMENPRODUKT((A1>{0.24.26.38.39}/96)*(A1-{0.24.26.38.39}/96)*{1.-1.1.-1.1})

 

Hallo Werner, hallo Lupo,

vielen Dank für Eure Ideen!!
@neopa C:
Zu dem Lösungsvorschlag: Die Formel funktioniert leider nur teilweise. Ich habe sie in der Beispieldatei (lade ich nochmals aktuell hoch) in der Spalte N bzw ab N7 getestet.
In manchen Beispielen gibt sie leider eine Pause aus, wo keine sein soll. (siehe Beispieldatei)

@lupo1:
Bei uns ist es mit der Pausenregelung nicht ganz so einfach, auch ich steig da erst langsam durch.
Wenn tagsüber >6h gearbeitet wird, erfolgt eine "unbezahlte" Pause von 30 Min. Wenn >6h und <6,5h gearbeitet wird, wird nur die Differenz abgezogen. Arbeitet man also 6,25 dann wird nur 0,25h abgezogen.

Arbeitet man SpätNachts, ist die Pause immer fest zwischen 21:30 und 22:00 Uhr egal wann der Beginn war oder das Ende ist.
Arbeitet man Nachts ist die Pause immer zwischen 3:30 und 4:00 Uhr. egal wann der Beginn war oder das Ende ist.

Zum Verständnis. Wir haben je Schicht ca. 3x30 Min Pause, ich möchte allerdings nur die "unbezahlte" Pause berechnen. Wir müssen also keine weiteren Regeln wie >9h oder sowas berechnen. Wie gesagt ich steig da auch erst jetzt richtig durch. Ursprünglich dachte ich das >6h -30min Pause....wär ja auch zu einfach gewesen ;-)
Wenn man grundsätzlich eine Pause ab/anstempelt ist diese unbezahlt auch wenn <0,5h wird nur diese abgezogen. Nicht ganz logisch, aber so ist es ;-)

Grüße
Marcel

 

Ach so, das ist der Thread von neulich. Warum bist Du nicht darin geblieben?

Hier habe ich mich an Deinen Text vor Deinen Formeln gehalten, und damit liege ich richtig. Die Pause nach 9 Stunden muss Dich ja nicht interessieren, wenn Ihr sie nicht habt. Sie ist aber Zwang!

Weil kein Lob für meine Formel kommt, hast Du sie nicht ausprobiert.

 

Ich hatte das Gefüht, als hättest Du genug von meinen Extrawünschen. ;-)
Außerdem ist die Aufgabenstellung ja jetzt ne andere, da ich damals noch gedacht habe wenn >6h dann -30. Das hatte ja soweit auch funktioniert.
Als ich nun das ein oder andere nachgerechnet hatte, ist mir leider erst da die neue Berechnungsgrundlage aufgefallen...

 

Hallo MHS1002,

ja, da hatte ich Deine Zielstellung falsch interpretiert. Dann für Deine bisherigen Beispieldaten (die Mitternacht nicht überschreiten) in M7 so:
{=MIN(MAX(MAX((LINKS(G$6:I$6;1)="K")*(H7:J7-G7:I7))-1/4;0)*(K7=0);1/48)}

 

Hallo Werner,

vielen Dank! Nun funktioniert die Formel fast richtig;-) bzw. ist nur eine Anwesenheit gestempelt, passt es super.

Wir haben allerdings noch ein kleines Problem bei der Berechnung der zweiten oder dritten Anwesenheitszeit. Wenn hier die Anwesenheit >6h (in der Beispieldatei i14 = 13:00 Uhr und J14 = 23:00 Uhr)
wird noch eine Pause abgezogen, was ja eigl. nicht der Fall sein sollte.

Evtl. könnten wir das so umgehen =Wenn(i14>0;0;{=MIN(MAX(MAX((LINKS(G$6:I$6;1)="K")*(H7:J7-G7:I7))-1/4;0)*(K7=0);1/48)})
oder gibt es noch eine bessere Variante?

Grüße
Marcel

 

Hallo Marcel,

bisher sollte nur jedes 3. Kommen nicht berücksichtigt werden. Wenn nur die Zeit der ersten Anwesenheit maßgebend ist, dann wird die Formel kürzer und bedarf auch eines Matrixformelabschluss mehr. Dann reicht doch einfach:
=MIN(MAX(MAX((H7-G7))-1/4;0);1/48)

 

Super vielen lieben Dank!
Grüße
Marcel