Mehrere Wenn (Formel)-Verschachtelungen

Hewal „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 09:44 Optionen

Hi Hewal,

ich glaube, das Problem hier ist einfach zu lösen - Excel nimmt Zeit (in diesem Fall die Stunden) immer als Teil eines Tages, sprich: 1h ist in Excel der Wert 1/24 - deswegen kommt auch das Chaos bei der zweiten Formel: wenn du von einer Stundenanzahl von 6 z.B. den Wert 0,5 (für Excel ist das ein halber Tag, d.h 12 Stunden) abziehst, gibt das logischerweise -0,5 (6 Stunden - 12 Stunden = - 6 Stunden = -0,5).

Was du einfach machen must ist die 0,5 Stunden ins Tagesformat wandeln - der Wert wäre ganz einfach 0,5/24 bzw für die DreiViertelstunde den Wert 0,75/25 zu nehmen.

BG,

Bergi2002

Hewal

gelöscht_101060 „Hi Hewal, ich glaube, das Problem hier ist einfach zu lösen ...“

25.01.2013, 09:57 Optionen

Hi Bergi,

habe die Formel wie folgt abgeändert:
=WENN(B16="";"";WENN(SUMME(C15:D15)<=6;SUMME(C15:D15)-0,0208;WENN(SUMME(C15:D15)>9;SUMME(C15:D15)-0,03125)))

Jetzt schreibt er mir bei 9:45 Arbeitszeit den Wert 0,03125 hin?! Wenn ich statt den Berechneten Werten die Formel 0,75/24 schreibe, erscheint das gleiche.

Grüße

mumpel1

Hewal „Hi Bergi, habe die Formel wie folgt abgeändert: ...“

25.01.2013, 10:21 Optionen

Hallo!

1. Wie hast Du die Ergebniszelle formatiert? Muss [hh]:mm sein.
2. Du hast ohenhin einen Denkfehler drin. Richtig wäre "Beginn-Ende-Pause", und nicht "Summe(Beginn:Ende)-Pause". Und immer mit "Rest" arbeiten, damit auch Arbeitszeiten über den Tag hinaus berechnet werden können. Und nicht mit Industrieminuten arbeiten, sondern mit "Echtzeit". Die Pause also einfach in Anführungszeichen setzen ("0:15"). Also zum Beispiel: Rest(D15-C15;1)-"0:15"

3. Schau mal auf www.excelformeln.de Dort gibt es passende Formeln

Gruß, René

mumpel1

Hewal „Hi Bergi, habe die Formel wie folgt abgeändert: ...“

25.01.2013, 10:45 Optionen

So, hier mal die passende Formel auf "Echtzeitbasis".
=WENN(B16="";0;WENN((REST(D15-C15;1)*24)>9;REST(D15-C15;1)-"00:45";REST(D15-C15;1)-"00:30"))

PeterP1

Hewal „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 10:15 Optionen

Hallo Hewal,

deine obere Formel ist falsch. Die untere tut bei mir genau das was sie soll und was du oben geschrieben hast.
Die Startzeit (C15) muß negativ sein, dann stimmt das Ganze.

ABER:
Du hast nicht geschrieben, was bei Arbeitszeiten zwischen 6 und 9 Stunden passieren soll, und deine Formel weiß das auch nicht. Deshalb gibt sie bei Arbeitszeiten zwischen 6 und 9 Stunden einfach FALSCH aus, weil dieser Fall bei dir nicht definiert ist.

Wenn es aber so ist:
bis 6 Stunden wird nichts abgezogen
zwischen 6 und 9 Stunden werden 0,5 Stunden abgezogen
über 9 Stunden werden 0,75 Stunden abgezogen

Dann funktioniert (bei mir) die Formel:

=WENN(B16="";"";WENN(C15+D15<=6;C15+D15;WENN(C15+D15<=9;C15+D15-0,5;WENN(C15+D15>9;C15+D15-0,75))))

Ich habe dabei den Begriff SUMME(C15:D15) einfach durch C15+D15 ersetzt.

Gruß
Peter

mumpel1

PeterP1 „Hallo Hewal, deine obere Formel ist falsch. Die untere tut ...“

25.01.2013, 11:02 Optionen

Du hast nicht geschrieben, was bei Arbeitszeiten zwischen 6 und 9 Stunden passieren soll

Doch hat er. Bei bis zu 6 Stunden und weniger als 9 Stunden 30 Minuten (0,5 Stunden), über 9 Stunden 45 Minuten (0,75 Stunden). Hat er sich etwas falsch ausgedrückt.

Hewal

PeterP1 „Hallo Hewal, deine obere Formel ist falsch. Die untere tut ...“

25.01.2013, 11:09 Optionen

Hilfe :)

Also, Durch eure Denkansätze habe ich es jetzt fast geschafft, wie ich es möchte. Formel arbeitet einwandfrei und alle Bedingungen passen. So sieht meine Formel jetzt aus:

=WENN(B16="";"";WENN((D16-C16)*24<=6;(D16-C16)*24;WENN((D16-C16)*24<=9;(D16-C16)*24-0,5;(D16-C16)*24-0,75)))

bis 6h Arbeitszeit keine Pause abziehen
zw. 6 und 9h 0,5h Pause
ab 9h 0,75h Pause abziehen.

Beispiel einer Zeile
Begin 7:00, Ende 17:05 Ergebnis 9,33333

Wie bringe ich das ganze jetzt noch in das Format 9:20 ?

mumpel1

Hewal „Hilfe :) Also, Durch eure Denkansätze habe ich es jetzt ...“

25.01.2013, 11:15 Optionen

Besser so:
=WENN(ODER(B16="";(REST(D15-C15;1)*24)<=6);0;WENN((REST(D15-C15;1)*24)>9;REST(D15-C15;1)-"00:45";REST(D15-C15;1)-"00:30"))

Zelle mit hh:mm formatieren.

Hewal

mumpel1 „Besser so: ...“

25.01.2013, 11:29 Optionen

Hi mumpel,

Deine Formel verstehe ich nicht so ganz :) Funktioniert soweit richtig, bis auf den Fall, wenn die Arbeitszeit <=6h ist, schreibt er 0:00.

Da ich die Formel nicht so ganz verstehe, hab ich auch noch nicht rausbekommen, wie ich diese Umstelle, damit er, falls in Spalte B NICHTS stehst, auch NICHTS in der Formelspalte ausgibt (also auch keine Null)

Achja, und ich musste das erste B16 durch B15 ersetzen (sicherlich irgendwo ein Copy&Paste Fehler?)

Grüße

mumpel1

Hewal „Hi mumpel, Deine Formel verstehe ich nicht so ganz :) ...“

25.01.2013, 11:39 Optionen

schreibt er 0:00

Das ist im ersten Formelteil so deklariert: WENN(ODER(B16="";(REST(D15-C15;1)*24)<=6);0
Das "0" in diesem Teil kannst Du auch durch Gleichheitszeichen ersetzen

=WENN(ODER(B15="";(REST(D15-C15;1)*24)<=6);"";WENN((REST(D15-C15;1)*24)>9;REST(D15-C15;1)-"00:45";REST(D15-C15;1)-"00:30"))

sicherlich irgendwo ein Copy&Paste Fehler?)

Das habe ich Deiner Formel in Deiner Frage entnommen, dort schreibst Du auch "B16".

Hewal

mumpel1 „Das ist im ersten Formelteil so deklariert: ...“

25.01.2013, 12:03 Optionen

Das Problem an deiner Formel wäre dann noch, dass er bei Arbeitsstunden <=6 h 0:00 ausgibt (also keine Zeit berechnet).

Ich verstehe den Befehl "REST" nicht so ganz? Kannst du mir den kurz erklären?

Grüße

mumpel1

Hewal „Das Problem an deiner Formel wäre dann noch, dass er bei ...“

25.01.2013, 12:18 Optionen

Hoppla, hast recht. Aber ich arbeite immer mit mehreren Spalten. Ich gebe die Pause immer in eigenen Spalten (Beginn und Ende) ein und rechne die Pausen dann ab. Das erspart mir das umständliche Umrechnen von Stunden in Industriestunden und wieder zurück.

Mit "Rest" wird auch die Zeit über den Tag hinaus korrekt berechnet, was bei Deiner Formel nicht ist. Also wenn Du z.B. von 20:00 heute bis 6:00 morgen früh arbeiten würdest, dann würde Deine Formel logischer Weise nicht richtig rechnen, und da setzt man dann "Rest" ein.

mumpel1

Hewal „Das Problem an deiner Formel wäre dann noch, dass er bei ...“

25.01.2013, 12:21 Optionen

Oder laienhaft erklärt:

Rest(D15-C15;1) entspricht in diesem Fall der Formel: Wenn(C15<D15;D15-C15;C15-D15)

Die richtige Erklärung für "Rest" ist das natürlich nicht. Rest kann man nicht nur für Zeitrechnungen verwenden.

mumpel1

Hewal „Hilfe :) Also, Durch eure Denkansätze habe ich es jetzt ...“

25.01.2013, 11:23 Optionen

Bei Deiner Formel müsstest Du das Ergebnis noch mit 24 dividieren, also die Industriezeit wieder in das Stundenformat bringen.

Hewal

mumpel1 „Bei Deiner Formel müsstest Du das Ergebnis noch mit 24 ...“

25.01.2013, 11:36 Optionen

Ich glaub, ich hab es geschafft:

=WENN(B16="";"";WENN((D16-C16)*24<=6;(D16-C16);WENN((D16-C16)*24<=9;(D16-C16)-0,5/24;(D16-C16)-0,75/24)))

wichtig war, wenn man das *24 in der Berechnung (nicht bei der Abfrage) weglässt, dass man dann die Abzugszeit /24 nimmt.

Jetzt scheint alles so zu sein, wie ich es mir vorgestellt habe. Schwere Geburt :)

Vielen Dank an alle und Grüße

mumpel1

Hewal „Ich glaub, ich hab es geschafft: ...“

25.01.2013, 11:45 Optionen

Da ist meine Formel IMHO besser, weniger "Wenns" drin. Und berechnet auch über Mitternacht hinaus.

ObelixSB

Hewal „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 13:02 Optionen

Hallo,

ich würde hier gar nicht mit einer Formel arbeiten, das ist doch ein schöner Fall für einen sverweis.

Gut erklärt hier im Video:

http://www.youtube.com/watch?v=37RUiApU1z8

gelöscht_265507

Hewal „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 14:33 Optionen

Eine WENN-Abfrage hat immer eine Bedingung und ZWEI Ergebnisse.

Das muss immer so aussehen WENN( ??? ; .... ; .... )

Bei Deiner letzten Wenn-Formel gibt es nur EIN ERGEBNIS.

WENN(SUMME(C15:D15)>9;SUMME(C15:D15-0,75); ???????????)

Hewal Nachtrag zu: „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 14:58 Optionen

Hallo IT-Look,

der "Sonst" Parameter ist optional und muss nicht zwingend angegeben werden. :)

Aber ich habe den Parameter für "sonst" schon in meiner Formel eingebaut.

Grüße

Hewal Nachtrag zu: „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 14:59 Optionen

Vielen Dank für eure Ratschläge, Bemühungen, Tips und Erklärungen :)

Grüße
Hewal

PeterP1

Hewal „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 15:03 Optionen

Das ist grundsätzlich richtig. Aber bei der letzten WENN-Abfrage hat er zuvor alle anderen Möglichkeiten (falls <=9) bereits abgefragt. Deshalb kann die letzte WENN-Abfrage entfallen und die von Hewal angegebene Formel

=WENN(B16="";"";WENN((D16-C16)*24<=6;(D16-C16);WENN((D16-C16)*24<=9;(D16-C16)-0,5/24;(D16-C16)-0,75/24)))

bringt das richtige Ergebnis.

Ein anderes ist die Frage, ob es sinnvoll ist, die Abfragen erst mit 24 zu multiplizieren und das Resultat dann durch 24 zu teilen. Wenn man alle Spalten (C, D und E) als Uhrzeit (hh:mm) formatiert, braucht man das nicht. Die von Mumpel1 vorgeschlagene Lösung hat allerdings nach seiner Aussage den Vorteil, daß sie auch Ende-Zeiten nach Mitternacht berücksichtigt (ich habe das nicht geprüft).

Gruß
Peter

Hewal Nachtrag zu: „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 15:22 Optionen

Bei meiner Tabelle sind von Anfang an Start- und Endzeit mit hh:mm formatiert. Aber das hatte nicht funktioniert ohne dem multiplizieren mit 24 in der Formel.

Grüße

hddiesel

Hewal „Bei meiner Tabelle sind von Anfang an Start- und Endzeit mit ...“

26.01.2013, 09:55 Optionen

Hallo Hewal,

wenn Arbeitszeiten über Mitternacht möglich sind, z.B. 22:00 bis 6:00
Bis 6 Std. keine Pause
Über 6 Std. bis 9 Std. 30 Min. Pause
Über 9 Std. 45 Min. Pause

dann geht auch folgende Formel
=WENN(B16="";"";REST(D16-C16;1)-WENN(REST(D16-C16;1)>ZEIT(9;0;0);ZEIT(0;45;0);WENN(REST(D16-C16;1)>ZEIT(6;0;0);ZEIT(0;30;0);0)))

Wenn die Arbeitszeit erst nach dem Kommen- und Gehen- Eintrag angezeigt werden soll, dann so.
=WENN(UND(C16>0;D16>0);REST(D16-C16;1)-WENN(REST(D16-C16;1)>ZEIT(9;0;0);ZEIT(0;45;0);WENN(REST(D16-C16;1)>ZEIT(6;0;0);ZEIT(0;30;0);0));"")

PeterP1

Hewal „Bei meiner Tabelle sind von Anfang an Start- und Endzeit mit ...“

26.01.2013, 13:12 Optionen

Du hast recht, ich habe mich geirrt.

Weil Excel Uhrzeiten immer als Teil des Tages (24 Stunden =1) rechnet, muß man entweder die Anfangs- und Endezeiten mit 24 multiplizieren oder die Stundenwerte (6, 9, 0,5 bzw. 0,75 Stunden) durch 24 dividieren.

Eine Lösung, die auch über Mitternacht hinaus funktioniert, ist einfach zu realisieren, wenn man eine zusätzliche Spalte in Excel spendiert, z.B. so:

Spalte C: Anfangszeit
Spalte D: Endezeit
Spalte E: Anwesenheitsdauer
Spalte F: Arbeitszeit

Alle Spalten werden als Uhrzeit (hh:mm) formatiert und die Daten in Spalte C und D entsprechend eingegeben.

In das Feld E15 kommt die Formel:
=WENN(B15="";"";WENN(D15-C15>=0;D15-C15;D15-C15+1))

In das Feld F15 kommt die Formel:
=WENN(B15="";"";WENN(E15<=6/24;E15;WENN(E15<=9/24;E15-0,5/24;E15-0,75/24)))

Gruß
Peter

hddiesel

Hewal „Mehrere Wenn (Formel)-Verschachtelungen“

25.01.2013, 19:51 Optionen

Hallo Hewal,

Bei bis zu 6 Std. Arbeitszeit, 30 Min. Pause abziehen
Ab 9 Std. Arbeitszeit 45 Min. Pause abziehen

Bleibt noch die Arbeitszeit, zwischen mehr als 6 Std. und bis 9 Std. Arbeitszeit???
Soll in diesem Bereich keine Pause abgezogen werden?

Hewal Nachtrag zu: „Mehrere Wenn (Formel)-Verschachtelungen“

26.01.2013, 16:36 Optionen

Hi Hddiesel,

bis 6h keine Pause abziehen, zwischen 6 und 9 Stunden 0,5h und über 9h 0,75h abziehen. So ist der Plan.

hddiesel

Hewal „Hi Hddiesel, bis 6h keine Pause abziehen, zwischen 6 und 9 ...“

26.01.2013, 18:22 Optionen

Hallo Hewal,

Danke für die Info!

Dann passen auch meine beiden Formeln bei Nachtarbeit, über 24:00 hinaus, wenn alle Zellen im Zeitformat Formatiert sind.

Beim Zellformat
[hh]:mm
wird auch 24:00 angezeigt, nach der Eingabe von 24:00.

Frage: Warum fragst du die Zelle in Spalte B ab, ob sie Leer ist, wie bei meiner 1. Formel

Meine 2. Formel führt die Berechnung erst durch, wenn bei Beginn und Ende, die Zeit eingetragen ist.

Hewal Nachtrag zu: „Hi Hddiesel, bis 6h keine Pause abziehen, zwischen 6 und 9 ...“

26.01.2013, 21:07 Optionen

Guten Abend hddiesel,

in Spalte B steht, was ich getan habe, und es gibt Tage, an denen ich für diesen Auftraggeber nichts tue, und dann soll bei der Zeitberechnung in dieser Zeile (wegen der Übersicht) nichts stehen.

Grüße

hddiesel

Hewal „Guten Abend hddiesel, in Spalte B steht, was ich getan habe, ...“

26.01.2013, 21:27 Optionen

Hallo Hewal,

Danke für die Info!

Office - Word, Excel und Co. 9.753 Themen, 41.597 Beiträge