Zählen bis und weiter

Question

Hallo alle zusammen,

ich habe von Paul1 schon diese 2 Formeln erhalten,

=WENN(B15<=40;B15;40)
=WENN(B15>3;B15-B16;"")

Das sieht dann so aus:

Stunden
B8 Mo 10
B9 Di 9
B10 Mi 8
B11 Do 10
B12 Fr 8
B13 Sa 5
B14 So
B15 Std Mo-Sa 50
B16 Sollstunden 40
B17 Überstunden 10
B18 25,00%
B19 50,00%

Jetzt möchte ich das ganze noch wie folgt erweitern:

In B18 soll von B17 weiter gezählt werden bis max. 3 und der Rest in B19

Kann mir da jemand weiter helfen?

Gruß Eberhard

[*]
[sup]*Threadedit* 24.08.2010, 08:31:39
Admininfo: Führe Threads bitte nicht fort, indem du weitere eröffnest, und vermeide Mehrfachanfragen! Die Datenbank und User werden es dir danken. Siehe FAQ 2, #3.[/sup]

Answer 1

Hallo Helmut,

ich bekomme die Adresse leider nicht als Link mit kopieren und einfügen rüber, nur so, ich weiß nicht woran es liegt.

http://www.file-upload.net/download-2771400/Muster.xls.html

Hoffendlich hilft die Musterdatei weiter.

Gruß
Eberhard

Answer 2

Hallo,

sehe dir das mal an

www.file-upload.net/download-2772103/Muster-1-.xls.html

könnte passen

Gruß

Helmut

p.s. wenn der 2. Thread geschlossen wurde hast du vorher auf 2 Hochzeiten getanzt.

Answer 3

Hallo Eberhard!


Vor dem Testen ist es wichtig, dass beide Formeln in B16 und B17 eingegeben werden, zumal die Formel in B16 von der Formel in B17 abhängig ist.

In B15: =D13
Hier stehen die Stunden wie im alten Beispiel

>und so gehts weiter:

In B16:

=WENN(B15<=16;B15-B17;WENN(B15<=8;B15-B17;WENN(B15<=24;B15-B17;WENN(B15<=32;B15-B17;WENN(B15<=40;B15-B17;40)))))

inB17:

=WENN(B15>=40;B15-B16;WENN(B15>=32;B15-32;WENN(B15>=24;B15-24;WENN(B15>=16;B15-16;WENN(B15>=8;B15-8;B15-B15)))))


Wenn es so ist wie in Deinem Beispiel dargestellt, ist es noch leicht und läuft auch problemlos:
8........1...........Do...........10.....
9.........2...........Fr.............8......
Die Summe = 18 steht in D13 (wie in der Frage beschrieben)



Sollten die Dienstnehmer in einer Rumpfwoche von 1 Tag bis 4 Tagen 8 Normalstunden pro Tag geleistet haben, werden die übersteigenden Stunden als Überstunden in B17 korrekt ausgeworfen.
Bei 5 Tagen wird ohnehin alles was über 40 Stunden ist in B17 ausgegeben.

Problematisch wird es aber, wenn z.B. ein Dienstnehmer in 2 Tagen angenommen 17 Stunden gearbeitet hat und zwar:

Do: 10 Stunden
Fr: 7 Stunden

Insgesamt 17 Stunden, dann gibt die Formel in B17 1 Überstunde aus, obwohl am Donnerstag 2 Überstunden geleistet wurden, was eigentlich nicht korrekt ist.
Jedoch ist das von Kollektivvertrag zu Kollektivvertrag verschieden.

Ein korrektes Ergebnis wäre in diesem Fall zu erwarten:

Do: 9 Stunden
Fr: 8 Stunden

Meine Formel erkennt dann automatisch 2 Tage zu je 8 Stunden und wirft 1 Überstunde in B17 aus.

Theoretisch und auch praktisch könnten in 2 Tagen auch 30 Stunden zusammenkommen, in diesem Fall rechnet die Formel 6 Überstunden, obwohl es 14 Überstunden sein könnten (aber auch nur dann, wenn pro Arbeitstag 8 Normalstunden erreicht wurden).

Von Nachtstunden, Sonn- und Feiertagsstunden (die auch anfallen können) ganz abgesehen.

Zusammenfassend würde ich meinen das Ganze als kreisgeschlossenes logisch fortlaufendes System zu sehen (Komplettlösung), sonst wird es ein Faß ohne Boden.

In diesem Fall habe ich mich nur auf die von Dir gestellte Frage mit den konkreten Angaben bezogen.


Schöne Grüße

Paul1

Answer 4

Hallo Eberhard!

Ich musste gerade feststellen, dass die Dir bereits übermittelten Formeln nur bis max. 7 Überstunden pro Stufe 1Tag, 2 Tage, 3 Tage usw. berechnen können.

Alternative:
B10 = Anzahl der Tage
D13 = Stundenanzahl
Bis hierher dürfte glaube ich eh alles klar sein (wie die Anzahl ermittelt wird usw...)

B15 = Stundeanzahl von D13
B16 = Sollstunden
B17 = Überstunden gesamt

Neu D16 = Fehlstunden


Vielleicht kannst Du mit den nachfolgenden Formeln was anfangen, sie bewirken:

B10: Anzahl der Tage (Formel ist bereits bekannt)
D13: Stundenanzahl (Berechnung ist bereits bekannt)
B15: =D13
B16:
=WENN(B10=1;8;WENN(B10=2;16;WENN(B10=3;24;WENN(B10=4;32;WENN(B10=5;40;WENN(B10<=7;40;B15))))))
B17:
=WENN(B15>B16;B15-B16;0)
D16:
=WENN(B15<B16;B15-B16;"")


Die Überstunden für 1 Tag, wenn 8 Stunden überschritten werden
Die Überstunden für 2 Tage, wenn 16 Stunden überschritten werden
Die Überstunden für 3 Tage, wenn 24 Stunden überschritten werden
Die Überstunden für 4 Tage, wenn 32 Stunden überschritten werden
Die Überstunden für 5 Tage, wenn 40 Stunden überschritten werden

Dabei ist es bei dieser Berechnung egal an welchem Tag wie viele Stunden geleistet wurden
z. B. bei 2 Tagen (16 Sollstunden)
Tag 1: 7 Stunden
Tag 2: 10 Stunden

Ergibt aussaldiert 1 Überstunde, obwohl am Tag 2 „2“ Überstunden geleistet wurden.
Aber wenn diesbezüglich kollektivvertraglich nichts Zwingendes geregelt ist, kann man die Überstunden auf Basis der Sollstunden berechnen.
Was Nachstunden, Sonn- und Feiertagsstunden (wenn sie überhaupt anfallen event. 100% Übstd.Zuschlag) und Samstagstunden (wenn sie nicht von der Wochenarbeitszeit abhängig sind) betrifft, deckt diese Formel nicht ab.

Wie bereits gesagt, wäre es von Vorteil im Rahmen einer Komplettlösung alles neu aufzubereiten, um ein Flickwerk zu vermeiden
Ich arbeite schon längere Zeit an einer Personalverrechnung mit Excel (mit integrierter Zeiterfassung) und es läuft noch immer nicht wie ich es mir vorstelle.

Vielleicht ist etwas Brauchbares für Dich dabei

Nochmals schöne Grüße

Paul1

Answer 5

Hallo Helmut, Hallo Paul1,

habe eine neue Datei mit genaueren Informationen (hoffe ich) erstellt.
Schaut sie mal an

http://www.file-upload.net/download-2776130/Testdatei.xls.html

Schöne Grüße

Eberhard

Answer 6

Hallo Eberhard!



Natürlich müssen alle Varianten in Nr. 1 vereint werden, zumal kein Monat am Ersten mit Montag anfängt und am Letzten mit Freitag aufhört und es kann auch sein, dass in einer Rumpfwoche nur der Samstag mit oder ohne Überstunden steht.

Eventuelle Sonntags- Feiertags- und Nachtstunden sind in den Formeln nicht berücksichtigt, aber ansonsten glaube ich könnte man es belassen.

Das mit den Sonntagsstunden hast Du ja ohnehin recht passabel hinbekommen.

Angepasst an die Variante 1 in dem Tabellenblatt

In D14 (Sollstunden)
Statt: =WENN(D13<=40;D13;40)
Da Sollstunden nur entweder 8, 16, 24, 32 oder 40 Stunden sein können!
Mit der obigen Formel könnte es jede beliebige Stundenanzahl sein, was nicht stimmen kann.

Daher in D14:

=WENN(B25=1;8;WENN(B25=2;16;WENN(B25=3;24;WENN(B25=4;32;WENN(B25=5;40;WENN(B25<=7;40;B15))))))

Alles was unter den Sollstunden ist sagen wir steht z.B.
in F14 (Fehlstunden):

=WENN(D13<D14;D13-D14-D11;"")

In D17 Überstunden:
Statt: =WENN(D13>=0;D13-D14;"")

Denn wenn D13 mehr als Null ist (z.B. 2) und die Sollstunden 40, käme ein Minus von -38 Stunden raus.
Außerdem, wenn in einer Woche nur der Samstag wäre, bleiben die Samstagstunden stehen, sind andere Überstunden vorhanden dann alle Überstunden einschließlich Samstag

Daher in D17:

=WENN(D13>D14;D13-D14;D11)


Ich hoffe, dass es mit den neuen Formeln so funktioniert, wie es Deinen Vorstellungen entspricht.

Gruß

Paul1

Answer 7

Hallo Paul1,

ich kann nur sagen "Perfekt", so habe ich es mir vorgestellt.

Es klappt 100% zig

Ich sage recht herzlich Dank für Deine Mühe und geopferten Schlaf (25.08)

Schöne Grüße

Eberhard

p.s. Danke für die Belobigung der Sonntags Lösung.

Answer 8

Hallo,
warum so umständlich

=WENN(B25=1;8;WENN(B25=2;16;WENN(B25=3;24;WENN(B25=4;32;WENN(B25=5;40;WENN(B25<=7;40;B15))))))

geht auch so
=WENN(D13<=C25;D13;C25)

sehe dir mal meine Beispieldatei aus deiner entwickelt

www.file-upload.net/download-2777568/Testdatei-1-.xls.html

an Gruß

Helmut

Answer 9

Hallo Helmut,

der Download geht nicht. Kann es sein, das keine Datei unter dem Link ist, denn bei Dateigröße steht: 0 Kbyte

Gruß Eberhard

Answer 10

Hallo Eberhard und Helmut!

Für Dich Eberhard freut es mich, dass es jetzt zu Deiner Zufriedenheit funktioniert.

@ Helmut:

Du hast recht es wäre auch mit Deiner Formel in D14 gegangen(ich habe unten auf C25 nicht hingesehen.
Ich würde es auch Eberhard so empfehlen, da die Wenn Formel (6 WENNS) ohnehin lastig ist.

Trotzdem in D17 muß wegen der Samstagstunden stehen:

=WENN(D13>D14;D13-D14;D11)

Und wegen der Fehlstunden auf die Sollstunden sollte wegen Evidenzhaltung in F14 stehen:

=WENN(D13<D14;D13-D14-D11;"")

Dank der Ausdauer die in dieser Frage notwendig war, haben wir es mit vereinten Kräften (damit schließe ich alle ein) und besonders Helmut (der nie aufgegeben hat) geschafft für Eberhard eine brauchbare Lösung zu liefern.

Da ich selbst an einem Personalverrechnungsprogramm werke lag mir das Anliegen von Eberhard am Herzen und hat mir Freude bereitet.

Wenn jeder jeden hilft haben wir alle was davon.

nochmals schöne Grüße an Alle

Paul1