Arbeitszeiterfassung mit Pausen ohne zusätzliche Spalte

[Afonse]

Guten Tag alle zusammen,
Mein Problem ist folgendes:
Ich habe in A7 den Tag des Monats, in B7 den Wochentag, in C7 steht der Arbeitsbeginn und in D7 die Uhrzeit für Arbeitsende. Daraus errechne ich in E7 die gearbeiteten Stunden. In F7 steht nun wie an diesem Tag gearbeitet wurde. Also im Grundbetrieb „GB“ (oder bei Feiertagen sowie Zeitausgleich „ZA“), bei dem 45 min Pause bei einer Arbeitszeit über 9 Stunden, beziehungsweise 30 min Pause bei einer Arbeitszeit zwischen 6 und 9 Stunden miteinbezogen werden sollen. Dazu gibt es noch pausenfreien Grundbetrieb, wo keine Pause veranschlagt werden soll. Wenn also mehr als 6 Stunden gearbeitet wurde soll 30 min Pause abgezogen werden. Bei mehr als 9 Stunden sollen 45 min Pause abgezogen werden. Die Formel, die ich dabei ausgearbeitet habe kommt irgendwie nicht hin, da sie zwar die 45 min Pause bei über 9 Stunden miteinbezieht, aber keine Pause bei einem Arbeitszeitwert zwischen 6 und 9 Stunden veranschlagt. Und ich finde den Fehler nicht. Das Beispiel unten habe ich noch per Hand ausgerechnet.
Bisher sieht die Formel so aus:
=WENN(UND(ODER(F7="GB";F7="ZA";F7="Feiertag");(D7-C7)*24>WERT("6")=WERT("9"));(D7-C7)*24-0,75;(D7-C7)*24))
Ein Versuch war es
=WENN(UND(ODER(F7="GB";F7="ZA";F7="Feiertag");(D7-C7)*24>WERT("6"));(D7-C7)*24-0,5;WENN(UND(ODER(F7="GB";F7="ZA";F7="Feiertag");(D7-C7)*24>WERT("9"));(D7-C7)*24-0,75;(D7-C7)*24))
Das hatte aber nur zur Folge, dass die 45 min Pause nicht mehr einbezogen wird, sondern nur noch die 30 min Pause. Habe auch die Daten unterschiedlich eingegeben, sodass auch Werte über und unter 6 bzw. 9 herauskamen. Aber immer mit dem Ergebnis, dass es nur die halbe bzw. dreiviertel Stunde einbezogen wurde, aber nicht beide. Ich kann auch keine zusätzliche Spalte für manuelle Eingabe der Pausenzeichen mit aufnehmen, da es in ein vorgegebenes Dokument eingefügt wird, wo solch eine Spalte nicht vorgesehen ist. Deshalb versuche ich es mit einer Formel zu lösen.
Anbei die beiden Möglichkeiten der Formel in einer Excel Tabelle. Wenn mir jemand helfen kann, wäre ich sehr dankbar.

[Roger]

Hallo,

Schau dir das mal an, vielleicht passt das so.

Mit freundlichen Grüßen
Roger

[Klaus-Dieter]

Hallo,

versuche es mal so:

Tabelle1

	A	B	C	D	E	F	G
4
5	2	Mo	07:00	20:00	12,25	GB
6

Formeln der Tabelle

Zelle Formel E5 =WENN(ODER(F7="GB";F7="ZA";F7="Feiertag");REST(D5-C5;1)*24-SVERWEIS(REST(D5-C5;1)*24;{0.0;6.0,5;9.0,75};2;WAHR))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[Sulprobil]

Hallo,

es ist immer dasselbe. Mit Formeln bricht man sich die Finger.
Nimm am besten:
http://sulprobil.com/Get_it_done/IT/Exce...ediff.html

Viele Grüße,
Bernd P

[Afonse]

Vielen Dank für die schnellen Antworten.
Die Formel von dir Klaus-Dieter sieht am vielversprechendsten aus. Bekomme allerdings, wenn ich zum Beispiel PGB für pausenfreien Grundbetrieb eingebe "FALSCH" angezeigt.
Hierbei sollte allerdings nur keine Pause abgezogen werden.
Habe gerade auch schon rumprobiert, mir fällt aber nichts ein.

[Klaus-Dieter]

Hallo Roger,

PGB kam bisher nicht vor, oder habe ich etwas übersehen? Es ist immer sinnvoll, wenn die Beispiele der Realität entsprechen.

[Afonse]

Nein, PGB hatte ich bisher nicht erwähnt, weil ich nicht bedacht habe, dass es relevant sein könnte, da ich hierbei keine Pause abziehen wollte und mir der Gedanke deshalb nicht kam, dass es eben relevant sein könnte.

[schauan]

Hallöchen,

da könntest Du was alternatives im Falsch-Teil der WENN-Formel einfügen …
Oder Du musst vorne das ODER um PGB erweitern und natürlich auch das Array im SVERWEIS.
Je nachdem, was Du willst ...

[Afonse]

So ganz verstehe ich SVERWEIS nicht. also was mir die Matrix sagen möchte.
Die ODER Funktion um PGB zu erweitern bringt (zumindest in meinem Kopf) nichts, da ich bei "GB" "ZA" und "Feiertag" und einer Arbeitszeit über 6 Stunden, 45 min Pause abziehen möchte. Die Pause soll allerdings ja nicht abgezogen werden, wenn die Arbeitszeit unter 6 Stunden liegt und auch nicht, wenn als Buchungsgrund "PGB" anstelle von "GB" etc. angegeben wird. Die ODER Funktion würde ich doch nur erweitern um "PGB" und so würde dann auch bei "PGB" eine Pause abgezogen, oder irre ich mich?
Viele Grüße

[Klaus-Dieter]

Hallo,

lade doch mal eine Beispieldatei hoch, in der alle Eventualitäten eingetragen sind. Am besten mit den Wunschergebnissen. Es ist immer blöd, wenn man einen Vorschlag gemacht hat und dann wird noch was nachgeschoben.