Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 00:20:14
Bestimmte Spalten als xls exportieren per Makro
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1756to1760
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1756to1760
1756to1760
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Überstundenberechnung

Überstundenberechnung
02.05.2020 23:42:40
Marcel
Guten Abend liebe Gemeinde, ich bin schon lange hier im Forum als Gast unterwegs und bisher konnte ich meine Fragen anhand vorhandener Beiträge selber beantworten, doch jetzt muss ich doch selber mal was Schreiben.
Und zwar bin ich dabei, mir eine Excel Tabelle zur Arbeitszeiterfassung mit gleichzeitigem Ansatz für die Lohnberechnung zu basteln. Klappt soweit auch ganz gut und arbeite auch schon eine gewissen Zeit mit der Tabelle, doch jetzt muss ich mal was ändern, was ich nicht hinbekomme.
Es geht im Prinzip um die Spalte mit den Überstunden. Oben seht ihr, wie es mir Excel im Moment ausgibt mit meinen momentanen Formeln. So wie es Unten ist, soll NEU werden.
Die Stunden werden automatisch berechnet und der Unterschied ist, dass nun hinter der jeweiligen Arbeit steht, ob es eine Überstunde ist oder nicht.
Ich hoffe, ihr könnt mir weiterhelfen, wie die neuen Formeln aussehen könnten.
LG
https:\/\/www.herber.de/bbs/user/137214.xlsm

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Lange im Forum und dann eine .xlsm statt .xlsx?
03.05.2020 03:50:43
lupo1
AW: Überstundenberechnung
03.05.2020 09:11:39
mumpel
Das war kein Fehler. XLSX kann keine Makros. Du darfst hier auch XLSM posten.
So ganz verstehe ich nicht was Du erreichen möchtest. "-" bedeutet Minderleistung? Und was soll da jetzt wo stehen?
Nicht jeder Antworter öffnet eine .xlsm ...
03.05.2020 10:36:42
lupo1
... auch wenn er mittlerweile die Makros deaktiviert lassen kann.
Daher ist es immer besser, den Code in eine Zelle zu kopieren. Dann kann der Antworter ihn selbst in einem Modul testen. Oder es lassen.
Von Dürfen war nicht die Rede. Eher von Rücksichtnahme.
Anzeige
AW: Überstundenberechnung
03.05.2020 11:53:45
Marcel
Ja ok, vertshe ich!
"-" bedeutet, die Person hat an dem Tag seine Soll-Stunden von 8h nicht erreicht... "+" bedeutet, er hat mehr gearbeitet.
Das brauche ich dafür, weil die Plusstunden mit 25% Überstundenzuschlag vergütet werden, und diese aber auch extra nochmal nach Kostenstelle aufgegliedert werden. Deswegen brauche ich hinter jeder Arbeit bzw. Kostenstelle die Anzahl an Stunden welche mit "'+25% vergütet werden.
AW: ast Dir selbst viele Überstunden gemacht ...
03.05.2020 10:12:10
neopa
Hallo Marcu,
... mit Deiner Arbeitszeitabrechnung. Da könnte wohl mehr als die Hälfte Deiner an- und aufgestellten Berechnungen eingespart werden. Und wenn Du wenigstens das Datum des Arbeitstages mitführen würdest, dann davon nochmal so viel weniger. Aber das ist jetzt nicht Dein Thema gewesen.
Für Deine Datenstruktur und das was Du jetzt anstrebst für M20:M###, dafür dann folgende Formel in M20:

=WENN((K20="")+(SUMME(INDEX(K:K;VERWEIS(9;1/(A$20:A20"");ZEILE(A$20:A20))):K20)&lt8)*(B21"");""; SUMME(INDEX(K:K;VERWEIS(9;1/(A$20:A20"");ZEILE(A$20:A20))):K20)-8-SUMME(INDEX(M:M;VERWEIS(9; 1/(A$20:A20"");ZEILE(A$20:A20))):INDEX(M:M;ZEILE()-1)))
und diese nach unten kopiert.
Und wie schon geschrieben, auch diese Formel lässt sich auf unter die Hälfte dieser Formellänge reduzieren, wenn wenigstens in A20:A### immer das Datum eingeschrieben wäre.
Gruß Werner
.. , - ...
Anzeige
AW: Nachtrag hierzu ...
03.05.2020 10:45:06
neopa
Hallo Marcel,
... die 8 in meiner Formel steht für Deine Soll-Stundenzeit. Diese müsste natürlich noch entweder übernommen oder berechnet (auch als Teilformel innerhalb der Formel möglich) werden. Das hatte ich mir gespart.
Gruß Werner
.. , - ...
AW: Nachtrag hierzu ...
03.05.2020 10:48:58
Marcel
Das mit den 8 Stunden passt, das ist die Anzahl der Soll Stunden ;)
AW: sicher nicht, wenn ein Feiertag, oder? owT
03.05.2020 10:58:04
neopa
Gruß Werner
.. , - ...
AW: sicher nicht, wenn ein Feiertag, oder? owT
03.05.2020 12:42:07
Marcel
Wenn es ein Feiertag ist, lasse ich in Spalte M gar nix berechnen, denn Feiertage haben wieder eine andere Vergütungsgruppe... die Auflistung der Feiertagsstunden habe ich schon anders gemacht.
AW: ast Dir selbst viele Überstunden gemacht ...
03.05.2020 10:45:14
Marcel
Vielen Dank für die schnelle Antwort!
Dass jemand sagt, dass ich das alles sehr umständlich aufgebaut habe war mir fast klar. Ich habe mit meinen beschränkten Excel Kenntnissen und bisl google diese Tabelle zusammen geflickt und bin froh, dass sie funktioniert ^^"
Ich habe deine Formel eingefügt und bin fasziniert :) Das einzige was mir aufgefallen ist, wieso steht in M23 eine Null drin, die sollte da nicht sein.
Wenn ich in B25 04:00 Uhr eingebe, dann passiert da das gleiche, also quasi, wenn die 8 Stunden genau ereeicht werden erscheint Null.
Die Null darf aber nur kommen, wenn danach keine weiteren Arbeiten durchgeführt wurden.
LG Marcel
Anzeige
AW: dafür ein zusätzliches Gleichheitszeichen ...
03.05.2020 10:48:11
neopa
Hallo Marcel,
... vor der 8 in der Formel. Also so: ... K20)&lt=8)*(B21...
Gruß Werner
.. , - ...
AW: dafür ein zusätzliches Gleichheitszeichen ...
03.05.2020 10:53:40
Marcel
Das habe ich schon gemacht gehabt, dann wird aber auch die Null in M21 nicht mehr angezeigt. Dort soll sie aber sein, weil danach kam ja keine weitere Arbeit an dem Tag.
AW: hatte ich mir auch schon gedacht, s.u. owT
03.05.2020 10:56:50
neopa
Gruß Werner
.. , - ...
AW: diese bewirkt jedoch ...
03.05.2020 10:55:54
neopa
Hallo,
... dass die 0 in M21 nicht mehr angezeigt wird. Wenn diese weiterhin angezeigt werden soll, dann belasse die Zellformel ohne das zusätzliche "=" und definiere dafür für M20:M### folgende bedingte Formatierung:
=(M20=0)*(A24=0) mit dem benutzerdefinierte Zahlenformat: 0;;
Gruß Werner
.. , - ...
Anzeige
AW: fast aber noch nicht ganz
03.05.2020 11:45:35
Marcel
Ich glaube ich belasse es doch erstmal bei Variante 1, mit dem Kleiner-Gleich vllt wird das andere mit den Nullen zu unübersichtlich.
Ich lade dir hier jetzt mal meine veränderte Datei hoch. In M18, M19 und M20 steht nicht das drin, was drin stehen soll... und ich verstehe leider nicht, warum er meckert, dass in M19 ein Zirkelbezug vorhanden ist :(
https:\/\/www.herber.de/bbs/user/137222.xlsx
Danke für deine Hilfe Werner!
AW: dazu wäre eine andere Formel notwendig ...
03.05.2020 14:00:12
neopa
Hallo Marcel,
... bzw. eine Verlängerung der bisherigen, um all die neuen Varianten auch noch zu berücksichtigen.
Doch zunächst nachgefragt, warum wird in Spalte A nicht in allen Zellen das Datum erfasst?
Dann würde die Auswertungsformel viel einfacher. Wenn das nur deswegen nicht getan wird, um Datum wie bisher dargestellt zu haben, dann kann man das mit einer bedingten Formatierung ermöglichen.
Was spricht also dagegen oder dafür?
Gruß Werner
.. , - ...
Anzeige
AW Datum Spalte A
03.05.2020 14:18:27
Marcel
In Spalte A steht nicht in jeder Zeile das Datum, weil ich mir einfach den Schritt, jedes mal das Datum mit hin zu schreiben sparen will.
Aber wenn es dir weiter hilft... in Spalte AAR findest du das Datum in jeder Zeile, halt nur per Formel ausgewertet... Die Spalten da hinten sind meine Hilfsspalten. Das Datum in jeder Zeile habe ich unter anderem für den Feiertagszuschlag gebraucht.
Also das Datum in AAR5 entspricht dem Datum von A5, AAR6 von A6... usw... hoffe du kannst auch mit den Daten was anfangen, sodass ich es weiterhin so machen kann mit meinem Datum in Spalte A.
Wenn ich einen ganzen Tag lang Überstunden abfeier, dann schreibe ich nur das Datum hin, bei Arbeit Überstunden und bei Stunden steht 0, sodass bei Ü.St. "-8" steht.
Gruß Marcel
Anzeige
AW: war mein Vorschlag; aber Deine Entscheid. ...
03.05.2020 14:31:58
neopa
Hallo Marcel,
... wenn Du nur beim ersten Tageseintrag das Datum einschreibst (wie in Deiner Beispieldatei), dann in M5:
=WENN((K5="")+(SUMME(INDEX(K:K;VERWEIS(9;1/(A$5:A5"");ZEILE(A$5:A5))):K5)&lt8)*(A6="")*(B6"");""; SUMME(INDEX(K:K;VERWEIS(9;1/(A$5:A5"");ZEILE(A$5:A5))):K5)-8-WENN((A5"")*(A6"");0;SUMME(INDEX(M:M;VERWEIS(9; 1/(A$5:A5"");ZEILE(A$5:A5))):INDEX(M:M;ZEILE()-1))))
Aber wenn ein zweites Mal das gleiche Datum erfasst wird, kann es ungewollte Ergebnisse geben, wie Du leicht erkennen kannst, wenn Du dann z.B. in A13 nochmals den 3.4.20 eingeben würdest.
Gruß Werner
.. , - ...
Anzeige
AW: war mein Vorschlag; aber Deine Entscheid. ...
03.05.2020 14:52:40
Marcel
Wenn ich die Formel in M5 kopiere unter runter ziehe und danach das nächste Datum in Spalte A eingeben will, meckert er rum, dass es einen Zirkelbezug gibt.
Wenn es wesentlich einfacher ist jeden Tag das Datum einzugeben, dann machen wir das einfach so, ich werde es schon überleben.
Oder?
Grüße Marcel
AW: kann ich nicht nachvollziehen, denn ...
03.05.2020 15:03:30
neopa
Hallo Marcel,
... bei der zuletzt von mir eingestellten Formel gibt es keinen Zirkelbezug in Deiner bereitgestellten Beispieldatei, wenn Du diese Formel bis nach unten kopierst.
Anderenfalls stelle bitte die entsprechende Datei ein.
Gruß Werner
.. , - ...
AW: kann ich nicht nachvollziehen, denn ...
03.05.2020 15:11:26
Marcel
Wenn du in diese Datei bei A25 nun zB den 17.04.2020 eingibst, gibt es bei M25 einen Zirkelbezug.
https:\/\/www.herber.de/bbs/user/137227.xlsx
Grüße und wie gesagt, vielen Dank, dass du mir hilfst.
Anzeige
AW: für Berücksichtigung auch dieses Falles ...
03.05.2020 16:01:00
neopa
Hallo Marcel,
... verlängert sich die Formel weiter.
In M5:
=WENN((K5="")+(SUMME(INDEX(K:K;VERWEIS(9;1/(A$5:A5"");ZEILE(A$5:A5))):K5)&lt8)*(A6="")*(B6"")+((D5="")+(K5=0))*(LINKS(E5;1)"Ü");""; SUMME(INDEX(K:K;VERWEIS(9;1/(A$5:A5"");ZEILE(A$5:A5))):K5)-8-WENN((A5"")*(A6"")+(A5"")*(B6="");0;SUMME(INDEX(M:M;VERWEIS(9; 1/(A$5:A5"");ZEILE(A$5:A5))):INDEX(M:M;ZEILE()-1))))
Meine getroffenen Hinweise bzgl. Datumseingabe in Spalte A gelten auch da. Außerdem ist mir aufgefallen, dass Du "IST"-Stunden in Spalte K ermittelst, wenn Du keine Endzeit in Spalte D eingibst.
Gruß Werner
.. , - ...
Anzeige
AW: für Berücksichtigung auch dieses Falles ...
03.05.2020 18:52:02
Marcel
So, wieder da... Ich bin beeindruckt! Aber ich hab noch eine Kleinigkeit gefunden und ich hoffe ich treibe dich nicht zur Verzweiflung ^^" Echt cool was du hier auf die Beine stellst.
Ich lade jetzt nochmal eine Datei hoch.
https:\/\/www.herber.de/bbs/user/137233.xlsx
Es kann sein, dass ich auch mal abends anfange mit arbeiten und es kommt auch vor (sehr sehr selten) dass ich mal eine Doppelschicht mache... dann arbeite ich über 0 Uhr hinweg, dabei ist mir jetzt folgendes aufgefalen, wenn du aus "D17" eine 8 Uhr machst kommt ein Zirkelbezug, bei 7 Uhr gehts noch.
Außerdem hätte ich noch 2 Sachen, welche ich gerne nochmal anbringen würde.
1. Die Null in der Überstundenspalte bekomm ich durch setzen des Kleiner-Gleich 8 nicht mehr weg ^^" und
2. eine neue Sache die, wenn du möchtest nochmal herausfinden könntest, dann würde die Tabelle wirklich von alleine laufen...
In der Zeit, zwischen 22 Uhr und 5 Uhr gibt es einen Nachtzuschlag. Ist es möglich zB in Spalte O sich genau so wie die Überstundenanzeigen zu lassen, wie viele Stunden der ausgeführten Arbeit in diesen Zeitraum fallen?
Grüße Marcel
AW: den von Dir festgestellten Zirkelbezug ...
04.05.2020 09:58:40
Dir
Hallo Marcel,
... gibt es nur dann, wenn in der Formel nur mit kleiner 8 verglichen wird.
Anstelle dessen muss in der Formel wirklich mit ... &lt=8... verglichen werden.
Deine Aussage: "Die Null in der Überstundenspalte bekomme ich durch setzen des Kleiner-Gleich 8 nicht mehr weg" kann ich nicht nachvollziehen. Die Ergebnis 0-Werte in M6; M21 und M22 sind doch von Dir so gewollt gewesen. Oder was genau meinst Du nun?
Der Überstundenzuschlag lässt sich natürlich auch berechnen. Die Bedingungen dafür sowie die dafür zugeordneten Werte sind wo genau in der Datei definiert?
Gruß Werner
.. , - ...
AW: den von Dir festgestellten Zirkelbezug ...
09.05.2020 13:49:05
Dir
Hallo Werner, vllt liest du das hier ja noch. Ich war die ganze Woche unterwegs und komm erst jetzt wieder an PC.
Ich habe heute früh noch ein bisschen rum gebastelt und bin nun mit dem Ergebnis so zufrieden, dass ich erstmal nichts mehr dran verändern möchte und erstmal schauen will wie es jetzt über die Zeit läuft.
Ich danke dir vielmals für deine Hilfe und deine Geduld und hoffe, dass du mir bei weiteren Fragen in kommender Zeit wieder einmal zur Seite stehen kannst. :-)
In diesem Sinne, Thema gelöst / beantwortet...
Grüße Marcel

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige