Clever-Excel-Forum - Anteilige Berechnung von Pausenzeiten

Seiten: 1 2

Hallo zusammen,

ich erstelle gerade eine Übersicht, in der Arbeitszeitdaten gespeichert werden sollen. Nun gibt es ja die gesetzlichen Pausen, d.h. nach 6 Stunden 30 Minuten und nach 9 Stunden nochmals 15 Minuten. Diese Zeiten am Ende des Tages abzuziehen, wenn 6 bzw. 9 Stunden überschritten wurden, ist ja kein Problem. Dieses Procedere machen auch alle im Netz auffindbaren Tabellen.
Nun folgendes Problem:
Wenn jemand 06:12 Minuten anwesend war, darf er natürlich nicht 30 Minuten Pause abgezogen bekommen sondern anteilig nur 12 Minuten, wenn er 06:01 anwesend war, nur 1 Minute usw. Alles andere wäre rechtswidrig. Ich bekomme natürlich nur hin, dass Excel 30 min abzieht, sobald 6 Stunden erreicht werden. Das Gleiche gilt bei den 15 Minuten nach 9 Stunden. Wie bekomme ich hin, dass er nur anteilig abzieht?

Ich habe folgende Formel "entworfen", die aber nicht funktioniert. In ihr habe ich für jede Minute von 06:00 bis 06:30 eine Funktion hinterlegt- klappt aber wie gesagt nicht. Er soll in der Zelle nur die Zeit der Pause (0:30 bzw. 0:24 usw. ausgeben. Die Zelle F37 ist in dem Fall die geleistete Arbeitszeit. Wer kann mir helfen?

:22: :22: :22: :22:

=WENN(F37>=ZEIT(6;1;0)<=ZEIT(6;2;0);"0:01";WENN(F37>=ZEIT(6;2;0)<=ZEIT(6;3;0);"0:02";WENN(F37>=ZEIT(6;3;0)<=ZEIT(6;4;0);"0:03";WENN(F37>=ZEIT(6;4;0)<=ZEIT(6;5;0);"0:04";WENN(F37>=ZEIT(6;5;0)<=ZEIT(6;6;0);"0:05";WENN(F37>=ZEIT(6;6;0)<=ZEIT(6;7;0);"0:06";WENN(F37>=ZEIT(6;7;0)<=ZEIT(6;8;0);"0:07";WENN(F37>=ZEIT(6;8;0)<=ZEIT(6;9;0);"0:08";WENN(F37>=ZEIT(6;9;0)<=ZEIT(6;10;0);"0:09";WENN(F37>=ZEIT(6;10;0)<=ZEIT(6;11;0);"0:10";WENN(F37>=ZEIT(6;11;0)<=ZEIT(6;12;0);"0:11";WENN(F37>=ZEIT(6;12;0)<=ZEIT(6;13;0);"0:12";WENN(F37>=ZEIT(6;13;0)<=ZEIT(6;14;0);"0:13";WENN(F37>=ZEIT(6;14;0)<=ZEIT(6;15;0);"0:14";WENN(F37>=ZEIT(6;15;0)<=ZEIT(6;16;0);"0:15";WENN(F37>=ZEIT(6;16;0)<=ZEIT(6;17;0);"0:16";WENN(F37>=ZEIT(6;17;0)<=ZEIT(6;18;0);"0:17";WENN(F37>=ZEIT(6;18;0)<=ZEIT(6;19;0);"0:18";WENN(F37>=ZEIT(6;19;0)<=ZEIT(6;20;0);"0:19";WENN(F37>=ZEIT(6;20;0)<=ZEIT(6;21;0);"0:20";WENN(F37>=ZEIT(6;21;0)<=ZEIT(6;22;0);"0:21";WENN(F37>=ZEIT(6;22;0)<=ZEIT(6;23;0);"0:22";WENN(F37>=ZEIT(6;23;0)<=ZEIT(6;24;0);"0:23";WENN(F37>=ZEIT(6;24;0)<=ZEIT(6;25;0);"0:24";WENN(F37>=ZEIT(6;25;0)<=ZEIT(6;26;0);"0:25";WENN(F37>=ZEIT(6;26;0)<=ZEIT(6;27;0);"0:26";WENN(F37>=ZEIT(6;27;0)<=ZEIT(6;28;0);"0:27";WENN(F37>=ZEIT(6;28;0)<=ZEIT(6;29;0);"0:28";WENN(F37>=ZEIT(6;29;0)<=ZEIT(6;30;0);"0:29";WENN(F37>=ZEIT(6;30;0);"0:30";"0:00"))))))))))))))))))))))))))))))

=SUMMENPRODUKT((F37>{0.24.26.36.37}/96)*(F37-{0.24.26.36.37}/96)*{1.-1.1.-1.1})

... endlich einer, der das wie ich als rechtswidrig ansieht!

Hallo

=MAX(0;MIN(F37-6/24;1/48))+MAX(0;MIN(F37-9/24;1/96))

Gruss Sepp

@LCohen

Hast du deine Formel getestet, z.B.Arbeitszeit 05:00 ?

Hi Sepp,

	F	G	H
35	Arbeitszeit	Arbeitszeit	Pausenzeit
36	ohne Pausen	LCohen	Josef B
37	05:00	05:00	00:00
38	05:30	05:30	00:00
39	06:00	06:00	00:00
40	06:15	06:00	00:15
41	06:30	06:00	00:30
42	06:45	06:15	00:30
43	08:45	08:15	00:30
44	09:00	08:30	00:30
45	09:15	08:30	00:45
46	09:30	08:45	00:45

Formeln der Tabelle

Zelle	Formel
G37	=SUMMENPRODUKT((F37>{0.24.26.36.37}/96)(F37-{0.24.26.36.37}/96){1.-1.1.-1.1})
H37	=MAX(0;MIN(F37-6/24;1/48))+MAX(0;MIN(F37-9/24;1/96))

passt doch. Wink

Gruß Uwe

1. wurde das Thema schon oft, z.B. hier, erschöpfend behandelt

2. Sepp hätte bei seiner Bemängelung freundlicherweise sagen können: "LCohen ermittelte das Komplement zur Pause, was aber nicht gefragt war", statt einen erst mal wie der Hund die Wurst suchen zu lassen. Übrigens wird seine Formel schneller länger, wenn noch mehr Pausen ins Spiel kommen:

=SUMMENPRODUKT((F37>{0.24.26.36.37}/96)*(F37-{0.24.26.36.37}/96)*{1.-1.1.-1.1})
=SUMMENPRODUKT((F37>{0.24.26.36.37.48.49}/96)*(F37-{0.24.26.36.37.48.49}/96)*{1.-1.1.-1.1.-1.1})
=SUMMENPRODUKT((F37>{0.24.26.36.37.48.49.60.61}/96)*(F37-{0.24.26.36.37.48.49.60.61}/96)*{1.-1.1.-1.1.-1.1.-1.1})

=MAX(0;MIN(F37-6/24;1/48))+MAX(0;MIN(F37-9/24;1/96))
=MAX(0;MIN(F37-6/24;1/48))+MAX(0;MIN(F37-9/24;1/96))+MAX(0;MIN(F37-12/24;1/96))
=MAX(0;MIN(F37-6/24;1/48))+MAX(0;MIN(F37-9/24;1/96))+MAX(0;MIN(F37-12/24;1/96))+MAX(0;MIN(F37-15/24;1/96))

3. haben wir vermutlich alle unrecht, auch die Zeiterfassungssysteme selbst. Denn der Gesetzgeber wollte ganz bestimmt einen gleichmäßigen 8,33%-Pausenanteil an der Anwesenheit erreichen (Ausnahme ausdrücklich im ersten 3-Std-Abschnitt), nicht etwa 7,69%, die dann auch noch schwanken bei weiteren Pausen. Die korrekte pauschalierte Pausenannahme wäre also (allerdings zu Ungunsten der Arbeitnehmer bei der Lohnabrechnung):

=SUMMENPRODUKT((F37>{0.22.24.35.36.47.48.59.60}/96)*(F37-{0.22.24.35.36.47.48.59.60}/96)*{1.-1.1.-1.1.-1.1.-1.1})
oder auch
=SUMMENPRODUKT(WENNFEHLER(EXP(LN(F37-{0.22.24.35.36.47.48.59.60}/96));)*{1.-1.1.-1.1.-1.1.-1.1})

was bedeutet, dass es nach 5:30 schon 0:30 Pause geben müsste, damit dann 6:00 als Gesamtzeit passt. Und nach 8:45 dann 0:15 für zusammen 9:00. Immer 1/12 vom, nicht 1/12 oben drauf.

Ich habe aber schon (tolerierte) "Besitzstände" der Arbeitnehmer erlebt der folgenden Art: Erst nach 6:30 gibt es 0:30 (zusammen 7:00), nach 9:00 dann 0:15 (zusammen 9:15). Das hat folgenden Charme: Der Arbeitnehmer sticht die Stechuhr bei 3mal 6:30 und bei 2mal 9:00 in der Woche. Das ergibt dann 6:30+6:30+6:30+8:30+8:30=38:30 Wochenarbeitszeit. Nur durch diese Manipulation spart der AN damit 1:30 für ihn unnötige Pausen-Anwesenheit pro Woche. Hinzu kommt, dass (die meisten?) Stechuhren tatsächlich "rechtswidrig" die Pause als Block abziehen. Daher sticht er vorsichtshalber bei 6:29+6:29+6:29+8:59+8:59=39:25-1:00=38:25, so dass er jede Woche 0:05 weniger als das Soll arbeitet, was er dann durch einen längeren Tag irgendwann ausgleicht.

Hallo Uwe

Stimmt, da habe ich LCohen Unrecht getan. Seine Formel berechnet die Nettoarbeitszeit korrekt. Da ist mir beim Übertragen in meine Tabelle ein Fehler unterlaufen.

@ LCohen Wenn du grossen Wert auf die Kürze der Formel legst, kannst du in deinem Fall auf Summenprodukt verzichten. Da genügt eine einfache Summe.

Gruss Sepp

Hallo thunderball81,

das ist so nicht richtig:

Zitat:Nun gibt es ja die gesetzlichen Pausen, d.h. nach 6 Stunden 30 Minuten und nach 9 Stunden nochmals 15 Minuten.

Zitat:In § 4 ArbZG finden sich Vorgaben, nach wie vielen Stunden Arbeit eine Pause einzulegen ist (Ruhepausen). Bei einer Arbeitszeit von sechs bis neun Stunden muss eine [b]Arbeitspause von mindestens 30 Minuten[/b] genommen werden. Bei einer Arbeitszeit von mehr als neun Stunden ist eine Pause von 45 Minuten vorgeschrieben.
Quelle: https://www.kanzlei-hasselbach.de/2015/g...pausen/12/

wobei zu berücksichtigen ist, dass die tägliche Arbeitszeit acht Stunden nicht überschreiten darf.

Da hast Du zwar recht, aber nur, weil in der Formel eine {}-Konstante verwendet wird.

a) Die um WENNFEHLER verkürzte Fassung tut es jedoch auch mit {}-Konstante nicht. Getestet mit 365.
b) Würde die Formel noch länger, verwendet man statt {1.-1.1.-1.1.-1.1.-1.1} irgendwann REST(SPALTE(A1:I1);2)*2-1. Auch dann fkt. SUMME nicht.

(06.04.2019, 10:56)Klaus-Dieter schrieb: [ -> ]... wobei zu berücksichtigen ist, dass die tägliche Arbeitszeit acht Stunden nicht überschreiten darf.

Sie darf acht Stunden nicht regelmäßig überschreiten (sonst wären ja gar keine Überstunden möglich, bei einer 40-Stunden-Woche). Bei 10 Stunden ist Schluss, wiederum mit Ausnahme von Tätigkeiten oder Situationen, die es nicht anders erlauben.

Hallo LCohen,

um das zu präzisieren: in einem beliebigen Zeitraum von zwei Wochen innerhalb der Gesamtarbeitszeit darf die durchschnittliche Arbeitszeit pro Tag acht Stunden nicht überschreiten. Wobei, wie du schon angemerkt hast, die tägliche Maximalzeit bei zehn Stunden liegt.

Seiten: 1 2