Live-Forum - Die aktuellen Beiträge
Datum
Titel
12.10.2025 02:58:05
Kalenderwoche zählen
10.10.2025 15:11:39
Werte aus Zellen in Punktdiagramm, Beschriftung
10.10.2025 11:07:34
"Text in Spalten" ändert die Zahl von 21.1 in 45678
10.10.2025 10:11:05
geöffnete Excel-Datei mit variablen Namen finden
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1740to1744
1740to1744
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Verschachtelte Formel für Pausenregelung

Forumthread: Verschachtelte Formel für Pausenregelung

Verschachtelte Formel für Pausenregelung
28.02.2020 09:54:26
Oli
Hallo,
ich benötige Hilfe bei der Zusammenfassung von Formeln zu einer Formel.
Es soll die Arbeitszeit als Nettoarbeitszeit mit dem Abzug der Pausen in bestimmten „Zeitfenstern“ ausgegeben werden.
Es gibt z.B. besondere Regeln das die Pause über 6 Std. nur anteilig abgezogen werden darf usw.
Ich habe versucht die Pausenregelung in der angehängten Tabelle darzustellen und wie es endgültig aussehen soll. https://www.herber.de/bbs/user/135510.xlsx
Es sind auch schon Formeln hinterlegt die die Nettozeiten richtig ausgeben aber nur einzeln in den vorgegebenen Zeitfenstern.
Ich dachte ich kann die einzelnen Formeln in z.B. in einer WENN Formel zusammenfassen aber das ist mir nicht gelungen.
Ich hoffe ich habe mich verständlich ausgedrückt was das Problem ist und hoffe das Ihr mir bei der Lösung des Problems helfen könnt.
Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 11:17:09
Joachim
Hallo,
probiert mal 
=wenns(Anwesenheit6;anwesenheit9, Anteil über 30 oder 45 abziehen) .

Achte auf =wenns() nicht =wenn()
Du kannst für Dein Problem ausreichend viele Bedingungen in Wenns() eintragen und bspw. auch
zwischen 
=6 und =6,5 und =9 und 9

unterscheiden.
hilft das?
Anzeige
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 11:27:26
onur
Wo ist das Problem? DU beschreibst es nur kompliziert.
Bei einer Anwesenheitszeit unter 6 Stunden - 0 Pause
Bei einer Anwesenheitszeit ab 6 Stunden - 30 min Pause
Bei einer Anwesenheitszeit ab Stunden - 45 Pause
Also: 
=WENN(C3=9/24;C3-3/96;C3-1/48))

Anzeige
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 11:31:24
Joachim
Hallo onur,
ich versteh das Problem komplizierter:
wenn 6:10 gearbeitet wird sollen nur 10 Minuten abgezogen werden, keine halbe Stunde.
deshalb mein Vorschlag mit dem wenns()
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 11:33:12
onur
Das ist Quatsch und gesetzlich verboten !
Es gibt eine gesetzliche Pausenregelung und du kannst keine Eigene schreiben.
Anzeige
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 11:37:51
Joachim
nicht mein Circus, nicht meine Affen..
ich will ihm bei SEINEM Excel-Problem helfen und keine Grundsatzdiskussionen auslösen geschweige daran teilnehmen...
:-)
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 12:31:02
Oli
Hallo Joachim,
du hast es verstanden wie es gemeint ist.
Ich werde es mal so versuchen.
Grüße,
Oli
@onur Bitte ruhe bewahren. Meine Pausenzeiten sind im TVÖD geregelt. Bitte dort nachlesen und verstehen. Zum zweiten haben wir eine Stempeluhr die genau so mit den Pausenzeiten arbeitet. Ich benötige aber eine Auswertung welche ich nicht aus der Stempeluhr bekomme für eine getrennte Auswertung der Arbeitszeiten.
Anzeige
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 18:11:04
Torsten
Meine Lösung sieht wie folgt aus:

Public Function AZP(ptTime As Date)
Dim ldTMinut As Double
Dim ldDiff6h As Double
Dim ldDiff9h As Double
Dim ldPause As Double
'Die zu kontrollierende Zeit in Minuten umrechnen
ldTMinut = 60 * Hour(ptTime) + Minute(ptTime)
'Die Differenz zu 6 und 9 Stunden bestimmen
ldDiff6h = Max(0, ldTMinut - 6 * 60)
ldDiff9h = Max(0, ldTMinut - 9 * 60 - 30)
'Die Pausenzeit in zwei Schritten berechnen
ldPause = IIf(ldDiff6h > 30, 30, ldDiff6h)
ldPause = ldPause + IIf(ldDiff9h > 15, 15, ldDiff9h)
'Rückgabe-Array {Arbeitszeit, Pausenzeit}
AZP= Array(ptTime - TimeSerial(0, ldPause, 0), TimeSerial(0, ldPause, 0))
End Function

Anzeige
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 12:11:17
onur
Ausserdem hast du noch einen Denkfehler:
Bei einer Arbeitszeit von 6 Std und mehr muss 30 min Pause gemacht werden - d.h. bei einer Anwesenheit von 8:00 bis 14:15 müsstest du 16 min abziehen und nicht 15, denn sonst hätte der MA 6:00 std. und müsste 30 min Pause machen.
Abgesehen davon: du kannst nicht z.B. 10 min Pause abziehen, denn eine Pause muss mind. 15 min lang sein, um als Pause zu gelten.
Anzeige
AW: Verschachtelte Formel für Pausenregelung
28.02.2020 19:41:32
Oli
Hallo onur und alle anderen Beteiligten,
vielen, vielen Dank für die schnelle Hilfe !!!
Und ich habe nun auch mal wieder was dazu gelernt.
Grüße,
Oli
dass dieser alte Hut immer neu gekaut wird ...
28.02.2020 18:50:43
lupo1
=SUMMENPRODUKT((A1&gt{0.24.26.36.37}/96)*(A1-{0.24.26.36.37}/96)*{1.-1.1.-1.1})

ist die Arbeitszeit abzüglich korrekter Pause, die bei längerer Stempelzeit nie zu im Vergleich kürzerer Arbeitszeit führen kann
=SUMMENPRODUKT((A1&gt{24.26.36.37}/96)*(A1-{24.26.36.37}/96)*{1.-1.1.-1})

ist die Pause.
Statt A1 (Dauer) kann man natürlich auch (B1-A1) verwenden (Ende-Anfang).
Anzeige
AW: dass dieser alte Hut immer neu gekaut wird ...
02.03.2020 09:16:19
Oli
Hallo lupo1,
vielen Dank für deine Formel! Leider passt sie an einer Stelle nicht, siehe https://www.herber.de/bbs/user/135562.xlsx
Ich muss gestehen das ich mit "SUMMENPRODUKT" leider gar keine Erfahrung habe!
Könntest du dir das bitte noch einmal ansehen?
@onur, deine Formel hat leider ein Versionskompatibilitätsproblem "=_xlfn.ifs>=6/24;C4=9/24;C4-0,75/24;C4>6,5/24;C4-1/48)". In einem Excel/Rechner funktioniert es im anderen leider nicht. Das ist Sehr schade sonnst währe alles schon erledigt gewesen.
Ich bedanke mich schon mal im Voraus recht herzlich für eure Mühe und Unterstützung!
Grüße, Oli
Anzeige
AW: dass dieser alte Hut immer neu gekaut wird ...
02.03.2020 12:50:33
onur
Dann machst du halt 4 verschachtelte Wenn‘s:
WENN(Bed1;Aktion1;WENN(Bed2;Aktion2;WENN(Bed3;Aktion3; ...... ;““))))
AW: dass dieser alte Hut immer neu gekaut wird ...
02.03.2020 16:05:41
Oli
Das tut mir jetzt leid aber irgendwie hab ich jetzt den völligen Wurm drin!
Ich muss grad kapitulieren... ich bekomme es mit "WENN" einfach nicht hin und ich weis nicht warum...
Bitte kann mir nochmal jemand aus der Patsche helfen.
Vielen Dank, Oli
Anzeige
AW: dass dieser alte Hut immer neu gekaut wird ...
02.03.2020 17:10:14
onur
Ist es denn SOOO schwer? 
=WENN(C3=6/24;C3=9/24;C3-0,75/24;WENN(C3>6,5/24;C3-1/48; ""))))

AW: dass dieser alte Hut immer neu gekaut wird ...
02.03.2020 17:12:56
onur
Das UND kann auch weg: 
=WENN(C5=9/24;C5-0,75/24;WENN(C5>6,5/24;C5-1/48;""))))

;
Anzeige
Anzeige

Infobox / Tutorial

Verschachtelte Formel für die Pausenregelung in Excel

Schritt-für-Schritt-Anleitung

  1. Öffne deine Excel-Tabelle und stelle sicher, dass alle relevanten Daten der Arbeitszeiten vorhanden sind. Achte darauf, dass die Zeiten im richtigen Format (Uhrzeit) eingegeben sind.

  2. Definiere die Pausenregelung: Bei einer Arbeitszeit unter 6 Stunden wird keine Pause abgezogen. Ab 6 Stunden wird eine Pause von 30 Minuten abgezogen. Bei mehr als 9 Stunden sind es 45 Minuten.

  3. Erstelle die Formel: Du kannst die WENN-Funktion in Excel verwenden, um diese Regeln zusammenzufassen. Die Formel könnte wie folgt aussehen:

    =WENN(A1<6/24; A1; WENN(A1<9/24; A1-30/1440; A1-45/1440))

    Hierbei steht A1 für die Zelle mit der Arbeitszeit.

  4. Testen der Formel: Überprüfe die Formel mit verschiedenen Zeiten, um sicherzustellen, dass die Pausen korrekt abgezogen werden.

Häufige Fehler und Lösungen

  • Fehler: Pause abgezogen, aber nicht gemacht: Stelle sicher, dass die Arbeitszeit korrekt eingegeben ist und dass du die richtige Formel verwendest. Achte darauf, dass die Zeiten im richtigen Format sind.

  • Fehler bei der Berechnung: Wenn du die Formel für eine längere Zeitspanne anwendest, stelle sicher, dass du die Bedingungen in der Formel korrekt definiert hast.

  • Versionskompatibilität: Achte darauf, dass du eine Excel-Version verwendest, die die Funktionen unterstützt, die du verwendest (z.B. IFS-Funktion).

Alternative Methoden

  • Verwendung von SUMMENPRODUKT: Eine alternative Methode zur Berechnung der Pausen könnte die Verwendung von SUMMENPRODUKT sein, um die Arbeitszeit abzüglich der Pausen zu berechnen:

    =SUMMENPRODUKT((A1>{6;9}/24)*(A1-{6;9}/24)*{0;1})

  • VBA-Funktion: Du kannst auch eine benutzerdefinierte VBA-Funktion erstellen, um die Arbeitszeit und Pausenzeiten zu berechnen. Diese Methode bietet mehr Flexibilität und Anpassungsmöglichkeiten.

Praktische Beispiele

  1. Beispiel 1: Arbeitszeit von 5 Stunden

    • Formel: =WENN(5<6/24; 5; WENN(5<9/24; 5-30/1440; 5-45/1440))
    • Ergebnis: 5 Stunden (keine Pause)

  2. Beispiel 2: Arbeitszeit von 7 Stunden

    • Formel: =WENN(7<6/24; 7; WENN(7<9/24; 7-30/1440; 7-45/1440))
    • Ergebnis: 6,5 Stunden (30 Minuten Pause abgezogen)

  3. Beispiel 3: Arbeitszeit von 10 Stunden

    • Formel: =WENN(10<6/24; 10; WENN(10<9/24; 10-30/1440; 10-45/1440))
    • Ergebnis: 9,25 Stunden (45 Minuten Pause abgezogen)

Tipps für Profis

  • Verwende die WENN-Formel effizient: Kombiniere mehrere WENN-Funktionen, um komplexe Pausenregelungen zu erstellen.

  • Nutze bedingte Formatierungen: Hebe Zellen hervor, die eine bestimmte Anzahl an Pausenstunden überschreiten, um mögliche Fehler schnell zu erkennen.

  • Erstelle eine gesetzliche Pausenzeiten Tabelle: So hast du immer einen Überblick über die geltenden Vorschriften.

FAQ: Häufige Fragen

1. Ab wann wird eine halbe Stunde Pause abgezogen? Ab einer Arbeitszeit von 6 Stunden musst du 30 Minuten Pause einplanen, bei mehr als 9 Stunden sind es 45 Minuten.

2. Wie kann ich die Excel Arbeitszeit berechnen mit Pausen? Verwende die WENN-Formel, um die Arbeitszeit mit den entsprechenden Pausen abzuziehen. Achte darauf, die Regeln für die Pausenregelung zu befolgen.

3. Was ist, wenn die Pause abgezogen, aber nicht gemacht wurde? In diesem Fall solltest du die Arbeitszeit in Excel manuell anpassen und sicherstellen, dass die Regelungen klar definiert sind, um Missverständnisse zu vermeiden.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige