Office: (Office 365) Wochenarbeitszeit berechnen

Hallo zusammen,
ich habe eine Excel in der ich meine Arbeitszeit führe. Also ein Tabellenblatt am Anfang für die Summen der Monate und ein wenig Statistik und dann pro Monat ein Tabellenblatt.
Jedes Tabellenblatt hat 31 Zeilen für Startzeit, Endzeit und Pause.
Auf dem ersten Blatt trage ich das Jahr ein und den Rest rechnet Excel dann für mich, also wie viele Tage hat der Februar, welche Tage sind bewegliche Feiertage und markiert mir diese und die Wochenenden auch in der Excel.
Die Abschlusszeile einer Woche ist immer der Sonntag, in dem wird die Summe der Stunden berechnet. Das mache ich aktuell mit folgender Formel:
=WENN($A22<>"";WENN(UND(ZEILE($A22)>4;WOCHENTAG($A22;2)=7);SUMME(INDIREKT(ADRESSE(I22;5)&":"&ADRESSE(H22;5)));"");"")

Start und Ende der Summe nehme ich aus Spalte I und H, die werden folgendermaßen befüllt:
H: =WENN(ZEILE($A22)>3;ZEILE($A22)-2;"")
I: =WENN(UND(ZEILE($A22)>4;H22>7);H22-4;3)
Su funktioniert das mit der ersten Woche des Monats einwandfrei. Probleme macht eigentlich nur die letzte Woche des Monats, wenn der Monat nicht mit einem Sonntag endet. Ich würde dafür auch eine andere Formel akzeptieren, weil die ja in Zeile 32 steht, also Quasi unterhalb der Tabelle.
Auch die Formeln vorher könnte man überarbeiten, da man ja mit WOCHENTAG prüfen könnte welcher Wochentag in der Zeile steht, aber man müsste Excel so viel auf einmal erklären, zumindest kommt es mir so vor.
Hat da jemand schon mal was gebaut? Wenn's geht würde ich auch gerne auf VBA verzichten, aber wenn's sein muss dann eben auch mit.

Wäre schön, wenn ihr mir da helfen könntet.

 

Die meisten bauen Deine Datei nicht nach. Die Zeit hat schon jemand investiert.

Ein Nachbau sieht meist anders aus als das Original. <B>Darum sollte das Original verlinkt werden.</b>
Die meisten möchten es am Original testen um den gleichen Fehler zu erhalten.

Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender*, UserForm* , Forum* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.

Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten ändern. Schaue Datei
http://hajo-excel.de/gepackt/fremd/Datei_verschluesseln.zip

Ich führe keine Liste unrter welchem Namen die Datei gespoeichert ist.
Der Dateiname steht ja im Beitrag.

Das ist nur meine Meinung zu dem Thema.

Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest, beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)

 

Hallo,

der grundsätzliche Fehler ist di Aufteilung in Monatsblätter. Lege ein Kalendarium für ein, oder mehrere Jahre an, dann gibt es mit der Auswertung keine Probleme.

 

Okay, Datei hängt an. Arbeitszeiten hab ich mal drin gelassen, aber Stundenlohn ist geändert und Tätigkeiten hab ich rausgeworfen, braucht man ja nicht dafür.
@Klaus-Dieter: ich benutze die Datei, damit ich wöchentlich meinem Kunden sagen kann, wie viele Tage ich gearbeitet habe und monatlich weiß, wie viele Tage ich in Rechnung stellen muss.
Ich finde die Übersichtlichkeit in der Form ganz gut und wüsste nicht, was mir ein durchgehender Kalender, außer dass ich mich nurnoch um eine Woche im Jahr kümmern muss, helfen soll.
Wenn ich hier keine Lösung finde, dann passe ich eben nächstes Jahr wieder 12 letzte Wochen manuell an, aber ich habe bisher jedes Jahr an dem Kalender eine Sache verbessert und das ist für mich die nächste Optimierung, damit ich nächstes Jahr nur noch die Stunden rauswerfen muss und die neue Jahreszahl eintrage.

 

Moin DaBe,
"ich wüsste nicht ..." Na, dann setze ich mal genau dort an: Du nennst die Notwendigkeit, Deinen Kunden Wochenübersichten präsentieren zu können, als den Grund für Dein Layout. Nachvollziehbar, aber leider alles andere als sinnvoll. Denn genau das ist das Problem. Damit praktizierst Du bestes Papierdenken, nicht aber EDV-Denken. Das aber wäre der Schlüssel zur Lösung Deines Problems.

Ein Sheet mit allen Kalender- und AZ-Daten aller Kunden und Mitarbeiter -für den Kunden natürlich unübersichtlich wie Sau- für XL aber die beste Grundlage aller notwendigen Auszüge. Wochenübersichten, Lohnzettel für MA u.v.a.m. lassen sich daraus per Prozedur in wenigen Sekunden ziehen, als Bildschirmansicht, als PDF oder Export. Jede vorherige Aufteilung in Wochen, Monate Mitarbeiter, Standorte, usw. macht die Sache unnötig kompliziert.

Was ich meine, ist das für die EDV grundlegende EVA-Prinzip: Eingabe, Verarbeitung, Ausgabe. Die Eingabe sind Deine erfassten Daten, die Verarbeitung z.B. die Berechnung der Stunden/Löhne, die Ausgabe die Lohnzettel und Wochenübersichten.

Das einmal sauber ausgearbeitet braucht's auch keine manuelle Anpassung für 12 oder 91/2 Wochen. Nur einmal etwas moderner gedacht.

Schöne Grüße

 

Nachtrag: "nächstes Jahr nur noch die Stunden rauswerfen muss und die neue Jah...". In einem durchgehenden Kalender könntest Du Dir sogar das sparen ... Das würde einfach XL für Dich übernehmen.

Mahlzeit

 

Hallo,

anbei eine Beispieldatei. Die kommentiere ich jetzt nicht weiter, aber vielleicht verstehst du beim Betrachten, was ich unter #3 gemeint habe.

 

Hi,

ersetze Deine Formel in F durch diese:

Code:

=WENN((A3="")*(ZEILE()<34);"";WENN((REST(A3;7)=1)+(A3="");SUMME(INDEX(E2:E$2;WENNFEHLER(VERWEIS(9;1/(REST($A2:A$2;7)=1);ZEILE($A2:A$2))-1;2)):E2);""))

 

Hallo DaBe1812,

für alle Monate eines jeden Jahres:
Lege eine Schablone/Korsett für die max. Ausdehnung eines Monats an. Dort packst du deine Daten rein.
Dann brauchst du jedes Jahr nur die Daten zu löschen, den Monat/Jahr ändern und wieder verwenden. (s. Bsp)


Gruß Sigi

 

Hallo,

ich habe dir in jedes der Monatsarbeitsblätter

1) in A3 eine Arrayformel eingefügt, die den Bereich der färbigen Datumsspalte befüllt:
=LET(a;A2;e;MONATSENDE(a;0);SEQUENZ(e-a+1;;a))
Sie greift dazu auf Zelle A2 zu.

2) in E2 eine benutzerdefinierte Array-Funktion eingefügt: =Monatsarbeitszeit(A3#)
Sie befindet sich im Namensmanager und sie greift jeweils auf den Bereich mit den berandeten Zellen zu: Das sind die färbigen Zellen der Spalte A und die dazugehörigen Zellen der Spalten B, C und D . Sie erzeugt jeweils im Bereich E2:G36 einen Ausgabebereich (unabhängig von der Monatslänge) - so wie du das auch bisher hattest - mit einer Summenzeile in E36:G36.

3) Die Zeilennummern der Spalten H und I habe ich entfernt.

Datei liegt bei.

 

Wow, da sind ja ein paar tolle Lösungen dabei.
@Klaus-Dieter & @d'r Bastler: Ich habe den Vorschlag das einfach in einem Blatt zu machen schon verstanden, aber dann muss ich noch zig andere Änderungen bauen, damit ich wieder bei dem Komfort bin, den ich aktuell habe. Was ist denn, wenn ich sagen soll wie viele Tage ich pro Woche, pro Monat geleistet habe? Dann muss ich die Einzeltage aus einer vollen Woche mit den Fingern zusammen zählen, oder mir einen Bericht/eine View auf die Daten bauen. Für so etwas würde ich dann eine ordentliche Lösung programmieren mit einem richtigen DB-Backend und wenn ich ganz viel Lust habe, dann noch eine eigen App dafür, damit ich die Zeiterfassung zukünftig direkt auf dem Handy machen kann, wollte ich aber nicht.

@Sigi.21: Die Lösung ist ja richtig lustig. So weit hatte ich jetzt auch nicht gedacht, dass der Sonntag quasi immer in derselben Zeile bleiben kann und sich einfach der Monat drum herum bewegt. Das Prinzip könnte ich auch wieder in 12 Blättern, die sich das Startdatum vom ersten Blatt holen, umsetzen.

@Exl121150: Das ist mit die schönste Lösung, weil sie quasi direkt in meiner aktuellen Version integriert ist. Aber: ich verstehe es nicht. Kannst du nochmal erklären, was da wo berechnet wird, damit das funktioniert? Gefühlt steht in jeder Zelle fast dasselbe und kapiere einfach nicht, wo die Berechnung wirklich statt findet.

 

Hallo,

in meinem Posting #10 in diesem Thread habe ich dir die fürs Erste nötigen Hinweise gegeben.

Ich habe dir in jedem Monatsarbeitsblatt 2 Formeln eingefügt:

1) in Zelle A3 die Formel zum Erzeugen der monatlichen Datumsangabe (zB. für Januar im Bereich '01'!A3:A33)
Formel: =LET(a;A2;e;MONATSENDE(a;0);SEQUENZ(e-a+1;;a))
Mit Hilfe der LET-Funktion wird der Variablen "a" das Datum aus A2 (=Monatsanfang) zugewiesen;
der Variablen "e" wird basierend auf a mittels MONATSENDE-Funktion das Datum des Monatsletzten zugewiesen;
schließlich werden mit der Arrayfunktion SEQUENZ die 31 Datumsangaben für die Januar-Daten erzeugt. (Wäre es der Februar 2024 gewesen, hätte diese Funktion die 29 Datumsangaben für den Februar 24 erzeugt, in einem Gemeinjahr die 28 Datumsangaben).

2) in Zelle E2 habe ich dir die benutzerdefinierte Funktion =Monatsarbeitszeit(A3#) eingefügt. Diese findest du im Namensmanager unter dem Namen "Monatsarbeitszeit". Das Zeichen "#" im Anschluss an "A3" bedeutet, dass an die Funktion nicht bloß die Zelle A3 übergeben wird, sondern der ganze Zellbereich, der durch die Formel in 1) erzeugt wird, d.h. im Januar der Zellbereich A3:A33.
Der Funktionscode im Namensmanager ist etwas länglich. Um ihn übersichtlicher darzustellen, kann man ihn mit dem Microsoft-Add-In "Excel-LAB" strukturierter darstellen. Ich habe etliche Kommentarzeilen eingefügt (diese beginnen mit "/**", bzw. "//"). Aber all die Zeilen zu erklären, würde den Platz hier sprengen. Ausgehend vom Zellbereich A3# (=A3:A33), werden auch die Zellen der Spalten B,C und D hinzugenommen (also Variable "DatEingabe" erfasst somit den Zellbereich A3:D33). Dieser Bereich wird ausgewertet und die Ergebnisse davon im Zellbereich E2:G36 zurückgegeben (egal wieviele Tage der Monat hat).

Code:

/**
Ermittelt die Arbeitszeit eines Monats:
Param 'MonatsTage' = alle Tage eines Monats
*/
Monatsarbeitszeit = LAMBDA(MonatsTage; LET(
    //Eingabe: Daten ermitteln
    Tg1aktMo; ÜBERNEHMEN(MonatsTage; 1);
    TgLvorMo; Tg1aktMo - 1;
    Tg1nxtMo; ÜBERNEHMEN(MonatsTage; -1) + 1;
    nxtMo; MONAT(Tg1nxtMo);
    MonatsTageNxt; VSTAPELN(MonatsTage; Tg1nxtMo);
    DatEingabe; VSTAPELN(
        BEREICH.VERSCHIEBEN(MonatsTage; ; ; ; 4);
        HSTAPELN(Tg1nxtMo; 0; 0; 0)
    );
    //Ausgabe Spalte "Arbeitszeit"
    TgZt; NACHZEILE(
        DatEingabe;
        LAMBDA(zl;
            LET(zt; INDEX(zl; 3) - INDEX(zl; 2) - INDEX(zl; 4); WENN(zt = 0; ""; zt))
    ));
    //Kumulierte Wochenarbeitszeit ermitteln - wird nicht ausgegeben
    woTgZtKum; SCAN(
        0;
        MonatsTageNxt;
        LAMBDA(Akk; Tg;
            WENN(
                WOCHENTAG(Tg; 2) = 7;
                0;
                LET(a; INDEX(TgZt; Tg - TgLvorMo); w; WENN(a = ""; 0; a); Akk + w)
            )));
    //Ausgabe Spalte "Stunden"
    woStd; NACHZEILE(
        MonatsTageNxt;
        LAMBDA(Tg;
            WENNS(
                WOCHENTAG(Tg; 2) = 7; INDEX(woTgZtKum; Tg-Tg1AktMo);
                MONAT(Tg) = nxtMo;    ÜBERNEHMEN(woTgZtKum; -1);
                WAHR;                 0
            )));
    //Ausgabe Spalte "Tagen"
    woTg; NACHZEILE(woStd; LAMBDA(zl; zl / 8 * 24));

    //Ausgabe Kopfzeile, Datenbereich, Summenzeile
    VSTAPELN(
        HSTAPELN("Arbeitszeit"; "Stunden"; "Tagen");
        ERWEITERN(HSTAPELN(TgZt; woStd; woTg); 33;;"");
        HSTAPELN("Summe"; SUMME(woStd); SUMME(woTg))
    )
));;

 

Kann das sein, dass Du XL noch nicht so richtig verstanden hast? Diesen Satz empfinde ich als Frechheit - sorry!

Richtig ist, dass Du Dir alle gewünschten Auszüge, Reports, Übersichten, Papierformate, Listchen, Preisschildchen und Kassenzettel einmalig erstellen musst, um dann jahrelang in den Genuss der grandiosen Funktionalitäten von XL + VBA zu kommen.

Ich geh' jetzt mal Hände waschen ...

 

Spannend, möchte jetzt hier keinen Flame-War vom Zaun brechen, aber zum Thema Frechheit: Ich habe genau das an der Version "Datenhalde" bemängelt, dass ich eben für alles, was ich jetzt auf einen Blick sehe einen Report o.ä. brauche. Dabei habe ich niemanden beleidigt und von keinem die Kompetenz in Frage gestellt. Mir keine Ahnung von Excel zu unterstellen und sich nach dem Schreiben "die Hände wachen müssen" ist allerdings das Gegenteil davon. Wenn ich Excel als Schwerpunkt meines digitalen Schaffens sehen würde, dann wären meine Fragen tatsächlich peinlich und Basic, ist es aber nicht.
Zusätzlich bin ich mit einer Konkreten Frage an dieses Forum herangetreten: ich habe eine Tabelle, die kann das, was sie soll, ist schon seit Jahren im Einsatz, wird jedes Jahr mal mehr oder mal weniger verändert um mir das Leben einfach noch ein bisschen einfacher zu machen und als Antwort bekomme ich "Schmeiß deinen Scheiß weg, bau das alles nochmal neu und investiere Tage deiner Freizeit darin, und auf deine konkrete Frage habe ich nicht mal eine Antwort, sondern wenn du meine Lösung nicht gut findest, dann finde ich dich frech".
Und außer deiner Fähigkeit mein Problem zu verstehen habe ich dich auch mit dieser Antwort in keinster Weise angegriffen, nicht deine Fähigkeiten in Excel angezweifelt oder geschmälert oder mich darüber aufgeregt, dass ich jetzt so sauer war, dass ich einen Post schreiben musste.
Hier haben auch Leute geantwortet, die eine Lösung dargeboten haben, ohne meine Fähigkeiten an zu zweifeln, versucht haben mein Problem zu verstehen und sich zu allem Überfluss sogar die Mühe machen mir den gezeigten Code zu erklären. Und warum lasse ich mir den Code erklären? Weil ich weiß, dass ich nicht alles weiß und gerne besser werden möchte.

@Exl121150 : Danke für die Erklärung, den Code hatte ich nicht gesehen, könnte daran liegen, dass ich bisher keine Ahnung hatte, was ein Namensmanager ist.

 

Hallo DaBe,

mach dir keinen Kopf, unser Heimwerker vergreift sich gerne mal im Ton.