Registriert seit: 23.05.2018
Version(en): 2016
DaherHallo!
Das Arbeitszeitgesetz sieht ja 30min Pause bei einer Arbeitszeit über 6h vor, 45min Pause ab iner Arbeitszeit von 9h.
Nun soll ich auf Arbeit unsere "Stundenabrechnungstabelle" ein wenig auf Vordermann bringen.
Enthalten sind bislang Spalten für Arbeitsbeginn und -ende und Pausendauer, jeweils als hh:mm formatiert. Zum Schluß wird einfach Arbeitsende-Arbeitsbeginn-Pausendauer berechnet und man erhält so die Arbeitszeit:
Code: =(Arbeitsende-Arbeitsbeginn-Pausendauer)*24
Als Standardzahl formatiert, gibt Excel dann hier die Arbeitszeit als dezimale Zahl aus.
Nun möchten unsere Büromiez...äh....unsere ehrenwerten Damen aus der Lohnbuchhaltung das gerne ändern und das Arbeitszeitgesetz ins Spiel bringen wollen. Das Arbeitzeitgesetz besagt ja,daß man ab 6h Arbeitsdauer 30min Pause hat, ab 9h 45min Pause. Nun, das mit einer Wenn-Formel auszurechnen,wäre nicht das Problem. Das Problem ist,daß längere Pausen als erlaubt in tatsächlicher Länge abgezogen werden sollen, bei kürzeren Pausen aber auf jeden Fall die gesetzlichen Pausenlängen.
Die einfachste Methode wäre es vielleicht, denjenigen, der die Tabelle ausfüllen soll, einfach statt der Pausenzeit die Zeit eintragen zu lassen, die er/sie "überzogen" hat und die von der Wenn-Formel abzuziehen. Aber ich fürchte, das würde wohl zu sehr dazu verleiten, die Pausenzeiten zu frisieren.
Daher bräuchte ich eine Formel,die im Grunde folgendes macht:
a) Arbeitsende-Arbeitsbeginn errechnen und daraus die zustehenden Pausenzeiten bestimmen (geht über eine Wenn-Formel)
b) Wenn die eingetragene Pausenzeit >erlaubtes Limit ist a),dann nimm nicht die gesetzlichen Zeiten,sondern zieh die tatsächlichen Zeiten ab.
Hat jemand eine Idee, wie man sowas optimalerweise in eine einzelne Formel unterbringen kann ? Im privaten Umfeld würde ich wohl einfach eine "unsichtbare Hilfszelle" nutzen, aber darauf möchte ich gerne verzichten.
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
23.05.2018, 15:20
(Dieser Beitrag wurde zuletzt bearbeitet: 23.05.2018, 15:20 von LCohen.)
Pause: =MAX(VERWEIS(Ende-Beginn;{0.6.9}/24;{0.30.45}/1440);manuellerPauseneintrag)
Falls bei 6:01 statt 30 nur 1 Minute(n) abgezogen werden soll (wofür ich immer plädiere), melde Dich. Die meisten anderen sehen das aber nicht so, wie ich.
Registriert seit: 10.04.2014
Version(en): Office 2019
Hallo L., bist du sicher, dass das so soll..?
Arbeitsblatt mit dem Namen 'Tabelle1 (2)' | | A | B | C | D | E | 1 | Tag | von | bis | Pause | man Eingabe | 2 | Mo 05.06.2017 | 7:00 | 16:00 | 0:30 | | 3 | Di 06.06.2017 | 9:00 | 18:00 | 0:45 | |
Zelle | Formel | D2 | =MAX(VERWEIS(C2-B2;{0.6.9}/24;{0.30.45}/1440);E2) | D3 | =MAX(VERWEIS(C3-B3;{0.6.9}/24;{0.30.45}/1440);E3) |
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010 | Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Ich würde eine lupo1-Lösung vorschlagen..:
Arbeitsblatt mit dem Namen 'Tabelle1 (2)' | | A | B | C | D | E | 1 | Tag | von | bis | Pause | man Eingabe | 2 | Mo 05.06.2017 | 7:00 | 16:00 | 0:30 | | 3 | Di 06.06.2017 | 9:00 | 18:00 | 0:30 | |
Zelle | Formel | D2 | =MAX(E2;SUMME((REST(C2-B2;1)>{2.3}/8)*{2.1}/96)) | D3 | =MAX(E3;SUMME((REST(C3-B3;1)>{2.3}/8)*{2.1}/96)) |
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010 | Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht
"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
Stimmt, die ist besser. Aber nicht so, wie ich es oben diskussionshalber zu bedenken gab.
Registriert seit: 10.04.2014
Version(en): Office 2019
An dieser Diskussion wollte ich mich nicht beteiligen. Es ging mir um die gleiche Zeit(dauer) aber unterschiedliche Ergebnisse bei der ersten Formel... (^_-) ... das wäre ja ungerecht...
Gruß Jörg
ich muss mich erst wieder ganz langsam heran robben. Also bitte ich um Nachsicht
"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
Registriert seit: 23.05.2018
Version(en): 2016
24.05.2018, 09:15
(Dieser Beitrag wurde zuletzt bearbeitet: 24.05.2018, 09:15 von Werniman.)
Hallo,
vielen Dank für Eure Antworten. Die Formel
Zitat:=MAX(E2;SUMME((REST(C2-B2;1)>{2.3}/8)*{2.1}/96))
scheint zwar zu funktionieren, aber irgendwie erst ab einer Pausendauer von 45min.
Wundert euch nicht,daß Spalte D fehlt, die habe ich für den Moment der Übersichtlichkeit wegen ausgeblendet. Da wird lediglich mit einer Wenn-Formel nochmal die gesetzlich zustehende Pausendauer angezeigt, sie spielt aber für die weitere Berechnung keine Rolle.
In Spalte F habe ich nochmal die Differenz aus Arbeitsende und Arbeitsbeginn berechnen lassen und dann das Ergebnis der o.g. Formel abgezogen,um so auf die Nettoarbeitszeit am betreffenden Tag zu kommen. Wie ihr
seht, zieht die Tabelle aus irgendeinem Grund immer sofort 45min Pause ab, auch wenn der Person bei dieser Anwesenheitsdauer nur 30min zustehen würde und sie tatsächlich nur 30min gemacht hat. Ist besonders gut in Zeile 13 zu sehen, wo ja eigentlich nur die gesetzlich zustehenden 30min abgezogen werden sollten. Interessanterweise stimmt es aber wieder, wenn die Ist-Pause die 45min-Marke überschreitet, wie man in den Zellen F15 und darunter sehen kann. Wieso zieht mir die Tabelle bei Pausenzeiten bis 45min immer generell 45min ab ? Wenn die Ist-Pause länger ist als 45min scheints ja wieder zu stimmen.
Arbeitsblatt mit dem Namen 'Mai_2018' | | A | B | C | E | F | 11 | | Anfang | Ende | Ist-Pause | IST | 12 | Mittwoch, 2. Mai 2018 | 8:00 | 16:00 | 00:00 | 7,25 | 13 | Donnerstag, 3. Mai 2018 | 08:00 | 16:00 | 00:30 | 7,25 | 14 | Freitag, 4. Mai 2018 | 08:00 | 16:00 | 00:40 | 7,25 | 15 | Montag, 7. Mai 2018 | 08:00 | 16:00 | 00:55 | 7,08 | 16 | Dienstag, 8. Mai 2018 | 08:00 | 16:00 | 01:00 | 7,00 | 17 | Mittwoch, 9. Mai 2018 | 08:00 | 16:00 | 01:15 | 6,75 | 18 | Donnerstag, 10. Mai 2018 | 08:00 | 16:00 | 01:30 | 6,50 |
Zelle | Formel | F12 | =(C12-B12-(MAX(E12;SUMME((REST(C12-13;1)>{2.3}/8)*{2.1}/96))))*24 | F13 | =(C13-B13-(MAX(E13;SUMME((REST(C13-13;1)>{2.3}/8)*{2.1}/96))))*24 | F14 | =(C14-B14-(MAX(E14;SUMME((REST(C14-13;1)>{2.3}/8)*{2.1}/96))))*24 | F15 | =(C15-B15-(MAX(E15;SUMME((REST(C15-13;1)>{2.3}/8)*{2.1}/96))))*24 | F16 | =(C16-B16-(MAX(E16;SUMME((REST(C16-13;1)>{2.3}/8)*{2.1}/96))))*24 | F17 | =(C17-B17-(MAX(E17;SUMME((REST(C17-13;1)>{2.3}/8)*{2.1}/96))))*24 | F18 | =(C18-B18-(MAX(E18;SUMME((REST(C18-13;1)>{2.3}/8)*{2.1}/96))))*24 |
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016 | Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Registriert seit: 13.04.2014
Version(en): 365, 2019
24.05.2018, 09:41
(Dieser Beitrag wurde zuletzt bearbeitet: 24.05.2018, 09:41 von BoskoBiati.)
Hi,
einfach so:
Arbeitsblatt mit dem Namen 'Tabelle1' | | B | C | D | E | 1 | Beginn | Ende | Pause | | 2 | 08:00 | 14:00 | 00:00 | 06:00 |
Zelle | Formel | E2 | =C2-B2-MAX(D2;((C2-B2>0,25)*0,5-(C2-B2>0,375)*0,25)/24) |
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010 | Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
oder so:
Arbeitsblatt mit dem Namen 'Tabelle1' | | B | C | D | E | 1 | Beginn | Ende | Pause | | 2 | 08:00 | 14:00 | 00:00 | 6,00 |
Zelle | Formel | E2 | =(C2-B2)*24-MAX(D2;((C2-B2>0,25)*0,5-(C2-B2>0,375)*0,25)) |
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010 | Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 23.05.2018
Version(en): 2016
Danke für deine Antwort. Ich hab gerade nochmal die Formel aus meinem letzten Posting reingehämmert,diesmal alles in Klammern gesetzt:
=(C2-B2-MAX(E2;SUMME((REST(C2-B2;1)>{2.3}/8)*{2.1}/96)))*24
Und aus irgendeinem unerklärlichen Grund scheint die Berechnung nun plötzlich zu funktionieren. Ich weiß nicht so recht,warum dem so ist, aber im Endeffekt zählt das Ergebnis. Danke für Eure Hilfe.
Registriert seit: 10.04.2014
Version(en): 2016 + 365
Hi,
(23.05.2018, 15:20)LCohen schrieb: Falls bei 6:01 statt 30 nur 1 Minute(n) abgezogen werden soll (wofür ich immer plädiere), melde Dich. Die meisten anderen sehen das aber nicht so, wie ich.
das halte ich auch für gerechter. Wie wäre das zu lösen?
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
05.06.2018, 06:46
(Dieser Beitrag wurde zuletzt bearbeitet: 05.06.2018, 06:56 von LCohen.)
=SUMMENPRODUKT((C2-B2>{0.24.26.36.37}/96)*(C2-B2-{0.24.26.36.37}/96)*{1.-1.1.-1.1})
oder
=MIN(C2-B2;MAX((C2-B2)*{0.1}+{24.-2}/96);MAX((C2-B2)*{0.1}+{34.-3}/96))
(natürlich jeweils mit
REST(C2-B2;1) statt C2-B2, falls gebraucht)
(von lupo1)
Das Gesetz verlangt alle 3 Stunden 1/12 Pause (mit der Abwandlung in den ersten 6 Stunden, bis wohin man das aufsparen kann). Gleichzeitig ist Mehrarbeit über 10 Stunden hinaus wohl verboten. Für diesen Konfliktfall hätte man aber noch
=SUMMENPRODUKT((B1-A1>{0.24.26.36.37.48.49}/96)*(B1-A1-{0.24.26.36.37.48.49}/96)*{1.-1.1.-1.1.-1.1})
=MIN(B1-A1;MAX((B1-A1)*{0.1}+{24.-2}/96);MAX((B1-A1)*{0.1}+{34.-3}/96);MAX((B1-A1)*{0.1}+{45.-4}/96))
|