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

Hallo,

sehe dir das mal an

http://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.

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

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

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

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

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.

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

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

an Gruß

Helmut

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

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

Hallo Helmut!

Berichtigung:

Mit Deiner Formel in D14:

=WENN(D13<=C25;D13;C25)

geht es doch nicht, denn sollte D13 weniger als C25 sein, stünde z.B. bei 5 Tagen statt 40 Std. jede Zahl die in D13 steht und das wären nicht mehr die Sollstunden.

Vielleicht gibts eine kürzere Formel, aber die obige geht leider nicht.
Bis dahin muß die lange Wenn-Formel in D14 verwendet werden.

Ich hoffe Eberhard ließt dieses Schreiben.

allseits schöne Grüße

Paul1

Hallo Paul1,

das ist kein Problem, da der Bereich eh ausgeblendet wird.

Die Sollstunden stehen ja in C25 und noch in F12 durch die Arbeitstage.

Kannst Du auf meiner Testdatei sehen.

Danke noch einmal für Deine netten Worte, denen ich mich selbst verständlich für alle Beteiligten anschließe.
Aber wie heißt es so schön: Gut Ding braucht Weile!

In diesem Sinne allseits schöne Grüße
Eberhard

Hallo Eberhard!

Gib in D14 statt der Monsterformel, wo noch ein WENN gefehlt hätte (0 Tage) z.B. wenn der Samstag der Erste ist, folgende Formel ein, die auch kürzer ist:

=SVERWEIS(B25;{0.0;1.8;2.16;3.24;4.32;5.40;6."Fehler"};2)


Sollte es nicht klappen die berichtigte WENN-Formel in D14:

=WENN(B25=0;0;WENN(B25=1;8;WENN(B25=2;16;WENN(B25=3;24;WENN(B25=4;32;WENN(B25=5;40;WENN(B25>5;"Fehler")))))))


Die SVERWEIS Formel hat bei mir, obwohl geschwungene Klammern dabei sind (Array-Formel) ganz normal funktioniert.

Jetzt gehe ich schnell schlafen


guten Morgen


Paul1

Hallo,

habenocheinmal versucht die Beispiedatei zu kopieren, funktioniert micht. Warum ? die kopierte Datei hat immer 0 KB.

@Paul1



ergibt die tatsächlichen Stunden

wenn du immerauf die Sollstunden gehst, dann reicht in Zelle D14

=C25

Gruß

Helmut

Hallo an Alle!

@Helmut

BINGO!!!
> in D14: = C25 ist die einfachste Möglichkeit, nur würde ich B25 und C25 sperren, damit keine Direkteingabe mehr möglich ist.

Denn das war mein Gedanke mit der Formel in D14:
=SVERWEIS(B25;{0.0;1.8;2.16;3.24;4.32;5.40;6."Fehler"};2)
Mann könnte auch z.B. in D14 nehmen:
=WENN(C25<=40;C25;“Fehler“)
Die zeigen eine Fehlermeldung, wenn irrtümlich als Direkteingabe in B25 mehr als 5 Tage eingegeben werden bzw wenn dadurch oder auch durch Direkteingabe in C25 mehr als 40 Stunden stehen.

Ich würde Eberhard überhaupt empfehlen, jene Zellen zu sperren in denen auf Grund von Formeln Berechnungen durchgeführt werden, so dass keine Direkteingaben möglich sind, aber ich glaube, dass es jetzt so läuft, wie Eberhard es wollte.

Nochmals schöne Grüße an Alle

Paul1

Hallo Paul1 und Helmut,

Ich werde alle Zellen sperren, bis auf die Bereiche:
Datum: B5-B9,B11+B12
Stunden: D5-D9, D11+D12
K+U: E5-E9

Ich wollte aber, wenn in z B. in E5 ein "K" oder "U" eingetragen wird, das dann in D5 automatisch eine "8" steht. (Formel habe ich)
In D5 kann ich aber diese Formel nicht eintragen, da der Bereich durch die Std-Eingabe nicht geschützt ist.

Jetzt mein Gedanke:
In F15 eintragen: Wenn E5 ein "K" oder "U" dann ist D5 "8"
geht das und wie müßte dann die Formel lauten?

Nochmals schöne Grüße und einen schönen Sonntag
Eberhard

p.s. ich glaube das ist meine letzte Frage zu diesem Thema, daß das endlich abgeschlossen wird.

Hallo Eberhard!

Mein Vorschlag:

F15 steht K
G15 die Formel: =SUMMEWENN(E5:E9;"K";D5:D9)

F16 steht U
G16 dieFormel: =SUMMEWENN(E5:E9;"U";D5:D9)


Alles andere kannst Du so lassen wie es ist, auch wass die Eingabe der Stunden betrifft.
In E5 bis E9 stehen dann entweder und K oder U zwecks der Übersicht und dadurch Unterscheidung zu den Arbeitsstunden

In einer Nebenrechnung stehen dann praktisch in G15 die Krankenstandsstunden und in G16 die Urlaubsstunden pro Woche.

schöne Grüße

Paul1

Hallo,

du kannst aber ach mit der Formel in E10

=(ZÄHLENWENN(E5:E9;"K")+ZÄHLENWENN(E5:E9;"U"))*8+SUMME(E5:E9)

und Eintragung von K oder U in E5 bis E9, sowie der Ausgleichstunden in E5 bis E9 es lösen.

Etwas grundsätzliches, daher werde ich hier nicht weitermachen, da du auf eine gefundene Lösung immer wieder ein neues Problem draufsetzt, kann der Helfer sich nur Stück für Stück weiterarbeiten und dir nicht eine viel bessere Lösung anbieten, wenn er deine Gesamtproblematik kennen würde.

Gruß

Helmut

Hallo Helmut,

vielen Dank für deine Formel, jetzt sind alle meine Wünsche erfüllt und dieses Projekt ist gelöst, für alle Beteiligten.

Das dieses Projekt so derartig ausartet hatte ich auch nicht gedacht.
Ich hatte meine Vorstellung wie es sein sollte (Theoretisch), aber die Praxis sieht eben anders aus. Und irgend wann kommt eben der Punkt wo es nur noch Schritt für Schritt weiter geht. Hat man ein Problem gelöst, taucht woanders wieder ein Neues auf. Man kann eben nicht an alle Eventualitäten denken.

Aber wie sagt man so schön: Ende gut, Alles gut.

In diesem Sinne, Vielen herzlichen Dank an alle Helfer
Eberhard