20.10.2017, 10:49 (Dieser Beitrag wurde zuletzt bearbeitet: 24.10.2017, 08:22 von Rabe.
Bearbeitungsgrund: Mini-Schriftformatierung entfernt
)
Hallo zusammen,
über die Suchfunktion habe ich nichts passendes gefunden, daher ein neues Thema:
Ich versuche für unsere Serviceabteilung ein Zeiterfassungstool in Excel aufzubauen. Dabei möchte ich gleitende Zeiträume von 24 Wochen betrachten und die durchschnittlich geleistete Arbeitszeit ermitteln.
Ich habe meine Arbeitsmappe der direkt mal hochgeladen.
Horizontal sollen die jeweiligen Arbeitszeiten in Zeile 22 vom MA eingetragen werden, bzw. aus einem anderen Blatt mit gleichem Aufbau hereinkopiert werden.
In den Zeilen darüber wird die Art der Arbeit kategorisiert und per Formeln aufgeteilt. Ich habe mit der grundlegenden Aufteilung und der Unterscheidung der Überstunden und dem Zählen per Formel bisher kein Problem (auch wenn ich nicht weiß ob meine Wege effizient oder korrekt sind), aber wenn es darum geht, die durchschnittliche Arbeitszeit in 24-Wochenblöcken zu berechnen, dann weiß ich nicht, wie ich das machen soll.
Ich möchte erreichen, dass ich in Zeile 32 für jeden Arbeitstag den Durchschnitt der geleisteten Stunden vom aktuellen Arbeitstag genau bis 24 Wochen nach vorne sehe. Bevor ihr euch wundert, so lange nichts vom Mitarbeiter eingetragen wird, rechnet das Tool mit den üblichen 8 Stunden je Werktag in Zeile 21, auf die Sich die Berechnung beziehen soll.
Ziel ist, zu erkennen, ab wann der Mitarbeiter durchschnittlich in 24Wochen mehr als 8 Stunden arbeitet und daher Ausgleich erhalten muss.
Gleitender Zeitraum deswegen, weil für jeden (Arbeits-)Tag der Zeitraum verschoben werden muss, dass eben immer 24 Wochen verglichen werden. Also von Mo, den 01.01.2018 bis Mo, den 11.06.2018 und am Di, dann von 02.01.2018 bis zum 12.06.2018 und so weiter.
Ich muss mich auf die Kalenderwochen beziehen, da das deutsche Arbeitszeitgesetzt in Wochenzeiträumen von 24 Wochen Obergrenzen vorgibt. Weiterhin müssen Samstage sowie Sonn- und Feiertage berücksichtigt werden, da es auch da zu Arbeitszeiten kommen kann. Urlaub, Krankheit etc. meine ich bereits per Formeln ausgeschlossen zu haben (Stunden werden dann über Eingabe in Zeile 26,27) genullt.
Vorerst hoffe ich, dass grundlegend klar ist, was ich erreichen möchte.
Ich freue mich über Denkanstöße, Hilfestellungen oder Feedback!
ohne in Deine Datei geschaut zu haben erst mal etwas Theorie.
Die Anzahl von Arbeitstagen in einem Zeitraum bekommt man über NETTOARBEITSTAGE. In dieser Funktion kann man auch Feriertage berücksichtigen. Allerdings weiß ich jetzt nicht, welchen Einfluss das auf die 24 Wochen hat.
Hier mal ein Ansatz, der 24 Wochen berücksichtigt - Formel in C1, bezogen auf heute. Darunter habe ich mal eine Proberechnung.
23.10.2017, 07:54 (Dieser Beitrag wurde zuletzt bearbeitet: 23.10.2017, 07:54 von Bastian.)
Hallo André,
zu erst danke für deinen Ansatz.
Ich versuche gerade deinen Ansatz auf mein Anliegen anzuwenden, bin mir aber noch nicht ganz sicher, ob mit den Nettoarbeitstagen der richtige Weg gefunden ist, da die Servicemitarbeiter für die ich diese Zeiterfassung erstelle, jeden Tag arbeiten könnten (also auch Sonn- und Feiertage). Gerade das muss entsprechend auch berücksichtigt werden.
Und ich glaube du zählst jetzt gerade die Nettoarbeitstage in einem Zeitraum? Ich brauche die Arbeitsstunden der Arbeitstage.
Wenn wir einfach auf die ersten 6 Monate des Jahres gucken, dann sind das 168 Tage.
Voreingestellt hat meine Datei dabei für jede Woche 5 Arbeitstage mit je 8 Stunden hinterlegt.
Der Mitarbeiter pflegt seine Stunden in einer leeren Datei mit gleichem Aufbau. Nachdem ich dann seine Stunde hereinkopiere muss meine Version der Datei rechnen wie seine Arbeitszeiten im Durchschnitt sind.
Und das eben für jeden Tag neu. Also Am Montag den 01.01.2018 bis zum 11.06.2018. Am Montag den 08.01.2018 müssen dann entsprechend alle Tage und Arbeitszeiten bis zum 25.06.2018 berücksichtigt und der Durchschnitt gebildet werden. Vielleicht hilft ja doch ein Blick in meine Datei.
Darüber hinaus kann nicht mit HEUTE als Datum gearbeitet werden, sondern immer mit dem Datum was gerade auch der aktuelle Tag ist auf den sich die Formel bezieht. Das in einer Formel abzubilden ist kein Problem, aber ich dachte ich erwähne es für die Mitlesenden. Die Formel soll für JEDEN Tag neu rechnen aber auch immer für die vorherigen stehenbleiben.
Ich glaube aktuell, dass Andrés Ansatz mir nicht weiterhilft. Oder ich habe ihn falsch verstanden. ;)
ja, da ging einiges unterwegs verloren ... Wenn Du Dir meine Formel angeschaut hättest, hättest Du gesehen, dass da nix von NETTOARBEITSTAGEN drin steht. Ich habe das nur erwähnt, weil in Deiner Frage auch mal (Arbeits)Tage stand.
Der aktuelle Tag ist bei mir übrigens immer HEUTE() und nicht gestern oder morgen.
Wenn Du mit dem aktuellen Tag den von vor zwei Wochen meinst - in meinem Beispiel der letzte Eintrag vom 12.10., dann kannst Du statt HEUTE auch auf die Zelle gehen, in dem Fall A10.
. \\\|/// Hoffe, geholfen zu haben. ( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
ich habe mir deinen Vorschlag durchaus angeguckt, war aber durch die Darstellung mit den Monaten etwas verwirrt. Wie ich ja schon sagte, habe ich deinen Vorschlag wohl nicht komplett verstanden.
Außerdem habe ich mich beim Erläutern wohl nicht klar genug ausgedrückt was das Thema des aktuellen Tages angeht. Es ging mir darum, für jeden Tag der in dem Arbeitsblatt vorkommt, einen neuen gleitenden Durchschnitt für 24 Wochen darzustellen.
Zitat:Hallo André der TS ist nebenan aktiver...
Ich bin eigentlich nicht nebenan aktiver, sondern habe nebenan einfach zuerst geantwortet. Nicht jedem der neu in den Foren postet, ist automatisch bewusst, dass alle Beteiligten in allen existierenden Foren aktiv sind. Gerade deswegen vermutet man ja, dass man durch zwei Foren schneller zum Ergebnis kommt. Das man querverlinken soll habe ich mittlerweile auch verstanden und werde dies zukünftig berücksichtigen.
Hallo André,
nachdem ich deine Formel einfach mal in eine Excelmappe überführt habe und anschließend versucht habe diese an meine Bedürfnisse anzupassen, ist mir aufgefallen, dass die Formel so wahrscheinlich nicht von mir verwendet werden kann. Eine kleine Anpassung habe ich aber direkt vorgenommen:
Ich habe hinten einen Zusatz eingefügt, da ich die Stunden pro Woche brauche, um wieder den verwendeten Zeitraum von 24 Wochen abbilden zu können.
Wenn ich die Formel nun nach deiner Anleitung von hinten "einsetze" und nach vorne ziehe, dann kann ich die Formel leider nur bis etwa der Hälfte des Jahres (von hinten) verwenden. Sobald ich einen Tag auswerten möchte, der weniger als 24 Wochen vom Jahresbeginn entfernt liegt, wird der Zeitraum der berücksichtigt wird kleiner als 24 Wochen. Und somit habe ich verfälschte Ergebnisse. Zumindest interpretiere/verstehe ich deinen mir zur Verfügung gestellten Ansatz so.
Daher vielleicht noch ein, zwei Voraussetzungen für die Lösung von meiner Seite:
- Die Formel muss von vorne funktionieren
- Betrachtungszeitraum ist ein Jahr, beginnend ab dem 01. Januar bzw. der 1KW.
- Alle Tage wo noch keine tatsächliche Arbeitszeit eingetragen ist, werden automatisch vorausgefüllt. Die Tabelle kann und soll somit auch als Simulation nach vorne funktionieren.
- Das was zum Fehler führt (weniger als 24 Wochen als Betrachtungsgrundlage) passiert ja auch vorwärts irgendwann. Aber die Komplexität, dass die Formel sich dann den zu betrachtenden Zeitraum so verschiebt, dass es nicht über das Ende des Jahres hinaus geht, wollte ich erst mal außen vor lassen. Ich stoße so ja schon an meine Grenzen ;)
Ist das überhaupt realisierbar was ich versuche abzubilden?
und nicht gestern oder morgen.