Stundenabrechnung
Zum Einstieg eine sehr einfache Version
Excel, alle Versionen
Immer wieder beliebt und immer wieder mit Problemen behaftet: Stundenzettel für den Nachweis der Arbeitszeit oder zum Berechnen des Entgelts. In diesem (ersten) Teil werden Sie einen sehr einfachen Nachweis erstellen, der wenig „Schnickschnack” enthält aber dennoch eine Basis darstellt und zweckmäßig ist. Bitte bedenken Sie: Es geht hier nicht um die fertige Lösung sondern darum, dass Sie sich diese Excel-Tabelle Schritt für Schritt selbst erarbeiten. Wenn Sie „nur” an der fertigen Tabelle, der endgültigen Version der einfachen Ausführung interessiert sind, finden Sie hier den Link für Excel 2007. Sie können das File laden und dann auf Ihrem Rechner speichern (Rechtsklick auf den Link) oder direkt per Doppelklick öffnen. Sollten Sie noch mit einer älteren Excel-Version arbeiten, so können Sie entweder das Kompatibilitäts-Pack einsetzen oder Sie nehmen mit uns Verbindung auf, dann haben wir auch noch eine passende Version für Sie auf unserem Server und senden Ihnen diese dann als Anhang (zip-gepackt) per E‑Mail zu.
Voraussetzungen
Um diese Aufgabe ohne Trainer/in zu bewältigen, sollten Sie über solide Grundkenntnisse des Excel verfügen. In einem Seminar mit Vortragendem genügt es, wenn Sie gut mit Excel vertraut sind. Als Trainer/in nehmen Sie bitte mit uns Kontakt auf, bevor Sie diese Unterlagen in Veranstaltungen jeglicher Art verwenden. Beachten Sie auch, dass das Copyright © in jedem Fall bei GMG Computer-Consulting verbleibt.
Einstieg
Beginnen Sie mit einer leeren Mappe in Excel und füllen Sie diese erst einmal mit den hier gezeigten Daten. Natürlich werden Sie Ihren eigenen Namen statt Hugo Hurtig eintragen. Die Formatierung überlasse ich Ihnen, nur die Nummern der Zeilen und Spalten sollten übereinstimmen. Sie haben es dann leichter, genau jene Position zu finden, die hier im Script stets mit der A1-Schreibweise angesprochen wird.
Die Rohform des Stundenzettels
Alternativ können Sie dieses erste Stadium der Datei auch im hier herunterladen und dann damit arbeiten. Erforderlichenfalls wählen Sie die Registerkarte / die Arbeitsmappe Schritt 1 aus. – Im folgenden Schritt geben Sie in Zelle A6 ein Datum ein. Zweckmäßigerweise ist es immer der Erste des jeweiligen Monats. Hier im Beispiel verwende ich den Januar 2011, es kann aber auch jeder andere Monat eines beliebigen Jahres sein. Hilfreich wäre es jedoch, wenn der Monat 31 Tage hat. Sie werden später erkennen, wozu das gut ist. 😉 Tipp: Geben Sie das Datum doch einmal auf der Zehnerblock Ihrer Tastatur so ein: 1–1‑11 … also mit dem Minuszeichen des Nummernblocks statt des Punktes. Die Hand kann dann bei einer Standard-PC – Tastatur in dem Bereich des 10er Nummernblocks bleiben. Und Excel erkennt Ihre Absicht und handelt weise … 💡 Sie erreichen übrigens den gleichen Effekt mit der Divisions-Taste der Zehnertastatur / oder ÷. Markieren Sie nun die Zelle B3 und tragen Sie die in der folgenden Abbildung gezeigte Formel ein:
Formel für den Monat eintragen
Jetzt steht in B3 auch der gleiche Wert wie in A6. Allerdings soll dort ja nur der Monat und das Jahr zu sehen sein, nicht der Tag. Darum markieren Sie erforderlichenfalls noch einmal die Zelle B3 und formatieren Sie diese beispielsweise so, wie hier gezeigt:
Formatierung der Zelle mit dem Monat
Jetzt wird das Datum, der Abrechnungsmonat in einer besseren Ansicht dargestellt. Alternativ können Sie natürlich auch ein benutzerdefiniertes Format wählen, dass dort beispielsweise Jan. 2011 (das Jahr also 4‑stellig) dargestellt wird. Da sich der Wert der Zelle an sich nicht verändert hat, wird der Inhalt auch immer noch rechtsbündig angezeigt, wie das für Zahlen der Standard ist. Das ändern Sie nun, indem Sie das entsprechende Symbol anklicken. Dann noch auf das Icon für Fettschrift Klicken und das Ganze sieht schon richtig gut aus. Vielleicht fragen Sie sich, warum der Umstand mit der Formel. Warum ich nicht gleich den Text für Monat und Jahr eingegeben habe. Die Lösung ist ganz einfach: Ändern Sie probehalber einmal in A6 das Datum auf einen anderen Monat und/oder Jahr. Das Ergebnis sollte Sie überzeugen. Bitte denken Sie aber daran, wieder das alte Datum (01.01.2011) einzutragen, Sie brauchen es im nächsten Schritt.
Das Daten-Gerüst fertigstellen
Die eigentliche Aufgabe des Excel ist ja das Rechnen. Und genau diese Möglichkeit nutzen wir auch, um das Datum des Folgetages einzutragen: 
Das funktioniert übrigens so gut, weil Excel zwar das Datum als solches in der Zelle darstellt, intern aber ist der 1.1.11 eine ganz normale Zahl, nämlich 40544. Der Hintergrund: Es ist der 40544ste Tag ab dem 1.1.1900; dieser Tag hat nämlich intern die Nummer (Zahl) 1. Und nebenbei bemerkt: Wenn Sie nicht das Jahr 2011 meinen sondern 1911, dann müssen Sie das Jahr vierstellig eingeben. Bei zweistelliger Eingabe wird automatisch dieses, das 21. Jahrhundert angenommen, solange der Wert unter 30 ist. Ab beispielsweise 1–1‑30 bzw. 1.1.30 wählt Excel automatisch das Jahr 1930. Die Jahreszahl 2030 müssten Sie aus diesem Grund wiederum 4‑stellig eingeben. Füllen Sie nun die Zelle A7 nach unten bis zum 31. Januar aus. Das geht am besten, wenn Sie mit dem Mauszeiger genau auf das Ausfüllkästchen gehen. Der Cursor wird dann zu einem Kreuz, wie hier zu sehen:
Ausfüllen des Datums bis zum 31. des Monats
Jetzt ein Linksklick mit der Maus und erweitern Sie die Markierung bis zur Zelle A36. Und schon sind alle Datumswerte des Januar eingetragen. Damit ist das Grundgerüst fast fertig. Um das Ganze noch etwas zu verschönern und auch zweckmäßiger zu gestalten, markieren Sie A37:E37 und formatieren den Bereich so, dass oben eine einfache und unten eine doppelte Linie sichtbar ist. Das Ergebnis sollte dann so aussehen:
Das Gerüst ist mit den Rahmenlinien unten fertig
Auch wenn Sie sich jetzt fragen, wie das denn im Februar oder anderen Monaten mit weniger als 31 Tagen aussehen wird, Sie arbeiten erst einmal so weiter. Dieses Thema wird etwas später angefasst. Jetzt ist es an der Zeit, die Datei aus Gründen der Sicherheit zu speichern. So haben Sie im Falle eines Falles immer einen sicheren Zugriffspunkt. Ich habe das auch getan und Sie können meine Version hier öffnen oder herunterladen (mit Rechtsklick). Die Tabelle Schritt 2 ist dann das korrekte Arbeitsblatt.
Eingabe erster Daten
Nun werden Sie die ersten Daten eintragen. Der erste Arbeitstag war der 3. Januar 2011, denn Neujahr ist ein Feiertag und der 2. Januar war ein Sonntag. Tragen Sie bitte als Arbeitsbeginn 7:30, als Arbeitsende 16:20 und für die Pause 30 Minuten (0:30) ein. Nun muss nur noch die effektive Arbeitszeit berechnet werden. Und das geht genau so, wie „im richtigen Leben”: Sie berechnen die Differenz zwischen Kommen und Gehen und subtrahieren davon die Pausenzeit. In Excel sieht das dann beispielsweise so aus:
Eingabe erster Daten und Berechnung der Arbeitszeit
Vollziehen Sie die Berechnung unbedingt nach. Sie müssen verstehen, warum das so gerechnet wird. Prinzip ist es ja „nur” Ende minus Anfang minus Pause. Und wenn Sie nachrechnen, dann werden Sie erkennen, dass Excel keinen Fehler macht. 😆 Tipp: Nervt es Sie auch, dass das Eingeben des Doppelpunktes bei Zeiten immer so umständlich ist? MS Office hat da eine wunderschöne Funktionalität eingebaut, die Autokorrektur. Sie kennen das vielleicht, dass in Word oder auch Excel manch falsch geschriebene Worte automatisch zur korrekten Schreibweise umgewandelt werden. Bei den 2003er-Versionen war es schon so, dass ein „daß” automatisch zu „dass” wurde. Auch jetzt ist es noch so, dass ein „sien” zu „sein” verändert wird, ohne dass Sie etwas dazu tun müssen. Nutzen Sie diese Möglichkeit und erstellen Sie Ihren persönlichen Autokorrektur-Eintrag. Lassen Sie 2 direkt aufeinander folgende Kommas „ zu einem Doppelpunkt : ändern. Das erspart viel Arbeit, Fehleingaben und Umstand! Wie das prinzipiell geht, finden Sie in Ihrer Version des Office-Programms in der Hilfe unter dem Stichwort Autokorrektur. Hier zur Verdeutlichung einmal der Eintrag in der Autokorrektur-Liste, wie ich ihn bei mir und manch einem Kunden angelegt habe:
Doppelpunkt leicht dank Autokorrektur eingegeben
OK, so viel zum Intermezzo. Tragen Sie für den Dienstag die gleichen Beginn- und Ende-Zeiten ein, aber 45 Minuten (0:45) Pause. Am Mittwoch: 7:00, 16:40, 1:00, am Donnerstag die gleichen Zeiten und am Freitag 7:00, 15:10, 0:35. Nun kopieren Sie die Berechnungsformel von E8 bis zur Zeile 12 nach unten. Ihr SpreadSheet sollte nun etwa so aussehen:
Die Daten der ersten Woche sind eingegeben
Das Prinzip ist ja bei allen Wochen gleich, darum belasse ich es erst einmal bei diesen Werten. Der Zweck, den ich erreichen will, ist erreicht. Im Grunde genommen geht es nun nur noch darum, die Stunden des Monats zu summieren. Nichts einfacher als das …
Berechnungen
Geben Sie in Zelle A37 erst einmal den Text Monatssumme ein und in E37 diese Formel: =SUMME(E6:E36) Das Ergebnis wird Sie wahrscheinlich überraschen. 1,72222222 – das kann doch nicht richtig sein. Etwa ein dreiviertel, also noch nicht einmal zwei Stunden; Unsinn! Excel rechnet also falsch oder wir haben eine fehlerhafte Formel eingegeben. Weder – noch! Excel rechnet 100% richtig und die Formel ist auch korrekt. Im Prinzip ist es das gleiche „Spielchen”, wie beim Datum. Dort wurde ja der 1. Januar 2011 angezeigt, intern aber rechnet Excel mit ganz normalen (ganzen) Zahlen. Hier sind es 1,72222222, also knapp ein dreiviertel Tage, nicht Stunden! Und das kommt auch hin. Also brauchen wir an und für sich nur das Format der Darstellung zu ändern:
Formatierung der Uhrzeit (1)
Von all dem, was angeboten wird, könnte das genau richtig sein. Und auf den ersten Blick sieht das Ergebnis ‑zumindest in diesem Fenster- auch ganz vernünftig aus. Aber nur auf den ersten Blick. Denn da wird mir beim angezeigten Ergebnis nun doch glatt ein ganzer Tag unterschlagen. Es werden nur die Stunden angezeigt, die den Überhang der vollen Tage bilden. Nach wie vor ist der Wert, der hinter dieser Anzeige steckt, immer noch 24:00+17:20 Stunden! Dieser Stand ist in Schritt 3 dieser Arbeitsmappe festgehalten.
Spezielle Formate
Es muss also die Anzeige der Werte verändert werden. Und zwar so, dass beispielsweise 1 Tag plus 6 Stunden auch als 30 Stunden und nicht nur als 6 Stunden angezeigt werden. Bei der im oberen Bild gezeigten Auswahl finden Sie nicht das, was Sie suchen, aber wenn Sie jetzt einmal die Kategorie Benutzerdefiniert wählen, können Sie in die Eingabezeile bei Typ ein selbst definiertes Format eingeben:
Formatierung der Uhrzeit (2)
Sie schreiben dort ein [h] (in eine eckige Klammer gefasst), dann der Doppelpunkt : und danach (die beiden) mm für die Minutenanzeige. Wenn Sie mögen, dürfen es auch zwei [hh] sein, dann werden die Stunden immer zweistellig angezeigt. Wichtig ist nur, dass Sie alles klein schreiben! [hh]:mm oder [h]:mm ist gleichwertig; wenn es mehr als 9 Stunden sind, ist die Anzeige immer gleich. Mindestens 2‑stellig, bei Bedarf (also mindestens 100 Stunden) auch 3‑stellig. OK, prinzipiell war’s das auch schon. Sie können weitere Zeiten eingeben. Am folgenden Montag (10.1.) beispielsweise 7:15, 15:25, 1:15. Kopieren Sie nun die Berechnungsformel, wenn Sie schon einmal dabei sind, komplett in die Zellen E6:E36. Dass dabei alle vorhandenen Formeln in diesem Bereich überschrieben werden, macht nichts. Aber vielleicht macht es etwas aus, dass Excel nun auch die Zeiten an den freien Tagen nicht nur berechnet sondern auch mit 00:00 anzeigt. Das Ergebnis ist ja richtig, aber das sieht nicht so gut aus. Aber auch in diesem Fall hilft die Einstellung eines benutzerdefinierten Zahlenformats weiter:
Formatierung der Uhrzeit (3)
Sie erkennen in Eingabezeile bei Typ den normalen Wert hh:mm hier aber gefolgt von 2 Semikola. Das erste Semikolon bewirkt, dass negative Werte nicht angezeigt werden und das zweite sorgt dafür, dass Nullwerte unterdrückt werden. Und genau das ist ja der gewünschte Effekt. Natürlich werden Sie vor dem Speichern dieses Formats noch die beiden eckigen Klammern um die Stunden setzen, damit alles korrekt angezeigt wird. Fertig. Wenn Sie es ganz einfach haben wollen, dann können Sie es hierbei belassen. Jeweils zu Beginn eines neuen Monats speichern Sie den derzeitigen Stand beispielsweise unter dem Namen „Arbeitszeiten 2011–01” ab. Löschen die Eingaben (und nur die, nicht die kalendarischen Daten und nicht die Formeln). Ändern Sie im neuen Monat das Datum in der Zelle A6 auf den Ersten des Folgemonats. Das wäre hier der 1. Februar 2011. Speichern Sie jetzt unbedingt die Datei noch einmal unter dem Namen des neuen Monats ab, sonst wird die alte Datei vielleicht unbeabsichtigt überschrieben. Stören Sie sich auch nicht daran, dass in Monaten mit weniger als 31 Tagen die ersten Tage des Folgemonats in den letzten Zeilen mit aufgeführt werden. Das ist ein vorübergehender Schönheitsfehler. Einfach dort keine Daten eingeben und Excel wird dennoch alles richtig berechnen. So oder so, speichern Sie nun noch einmal Ihr Werk. Den jetzigen Stand können Sie auch hier herunterladen. Und da dieses der vierte Arbeitsschritt ist, wechseln Sie bitte zum Tabellenblatt Schritt 4. Auf Dauer werden Sie auch die überflüssigen Datumswerte des Folgemonats in den Monaten Februar, April, Juni, … stören. Darum zum Abschluss hier noch ein Weg, wie so etwas besser gelöst wird als bisher. Als Beispiel dient immer noch die Januar-Tabelle aus 2011, die eventuell bereits auf den Februar angepasst worden ist.
28, 29, 30 Tage
Zu Beginn die Frage: Wie viele Tage hat jeder Monat mindestens? Richtig, 28. Folglich kann bis zum 28. jeden Monats alles so bleiben, wie es derzeit per Formel berechnet worden ist. Aber ab dem 29. muss Excel erkennen, ob der Tag noch zu dem gleichen Monat gehört wie der 1 oder der 28. Für solch eine Prüfung gibt es diverse Wege, einen einigermaßen begreifbaren zeige ich hier auf. Löschen Sie erst einmal A34:A36, also die nicht immer zum gleichen Monat gehörenden Tage. Es geht nun darum, dass in die Zellen unterhalb des 28. nur dann ein Datum eingetragen wird, wenn dieser Folgetag auch zu diesem Monat gehört. Da gibt es keine einfache, vorgefertigte Funktion, aber es ist durchaus machbar. Geben Sie nun in A34 diese Formel exakt so ein: =WENN(MONAT(A$33+1)=MONAT(A$33); A33+1; „”)
Eine kurze Erklärung folgt gleich. Kopieren Sie aber erst einmal diese Formel auch in die beiden folgenden Zellen A35:A36, auch wenn Sie diese gleich noch etwas abändern werden. Jetzt wird im Januar wieder alles bis zum 31. ausgefüllt sein. Ändern Sie nun bitte noch in A35 die Formel so ab: =WENN(MONAT(A$33+2)=MONAT(A$33); A34+1; „”) Und in A36 ändern Sie bitte auf „+3″ ab. Wenn Sie nun in A6 den 1.2.2011 eingeben, werden Sie erkennen, dass die letzten drei Zeilen des Eingabebereichs kein Datum haben, sie bleiben leer. Und ja, anno 2012 ist ein Schaltjahr. Probieren Sie das doch gleich einmal aus. Auch hier ist Excel auf der richtigen Fährte und schreibt den 29. Februar als Datum in die Zelle A34. Das Ganze funktioniert natürlich auch bei allen weiteren Monaten mit nur 30 Tagen, sofern Sie die Formeln korrekt geändert haben. Zur Erklärung der Formel in A34: Wenn A33+1 im gleichen Monat liegt wie A33 selbst, dann soll das Ergebnis der Berechnung A33+1 in die Zelle geschrieben werden, sonst nichts („”). In A35 haben Sie das +1 durch ein +2 ersetzt. Das bedeutet, wenn A33+2 im gleichen Monat liegt wie A33, dann soll das berechnete Datum eingetragen werden, sonst nichts.
In A36 ist die gleiche Logik. Die $-Zeichen haben dabei eine besondere Bedeutung. Sie bewirken, dass beim kopieren der Wert nach dem Zeichen nicht automatisch angepasst wird. Beim Kopieren der Formel von A34 nach A35 und A36 wurde jede Zeile, die mit einem $-Zeichen versehen war, nicht angepasst. Es blieb beim Bezug zu A33. Im rechten Teil der Formel wäre sonst aus A33+1 automatisch A34+1 bzw. A35+1 geworden, wie das beim kopieren ja typisch ist. Hinweis: Ja, es geht um einiges eleganter mit anderen Formeln. Aber dieser Teil der Trainings dient dazu, die absoluten Grundlagen zu legen und so wenig „Anspruchsvolles” (=Schwieriges) wie möglich zu verwenden. Nur die Funktionalität zählt.
Abschluss
Für diese ganz einfache Ausführung der Zeitberechnung war es das nun auch. Damit lässt sich durchaus arbeiten. Natürlich kann „man” daran noch vieles verbessern, vereinfachen, erweitern und auch gegen Fehlbedienung absichern. Aber das ist nicht der Sinn dieses kleinen Seminars gewesen. Hier ging es darum, dass Sie in relativ kurzer Zeit mit eigenen Händen eine Tabelle für die Zeitabrechnung erstellen. Tipp: Ein Bonbon doch noch, ganz zum Schluss. Da Sie ja jeden Monat die Daten nach dem Speichern löschen werden, können Sie sich das etwas leichter gestalten. Gehen Sie dazu so vor: Markieren Sie den Bereich, wo Daten eingegeben werden. Das entspricht B6:E36
Arbeiten mit Bereichsnamen (1)
Klicken Sie in das Feld, wo derzeit die Ankerzelle ausgegeben wird (hier: B6, es ist in der Abbildung hierüber ganz links unterhalb des Menübandes, weiße Schrift auf schwarzem Grund und tragen Sie dort einen Bereichsnamen ein, beispielsweise DatenBereich:
Arbeiten mit Bereichsnamen (2)
Ich verwende gerne die etwas gewöhnungsbedürftige Großschreibung mitten im Wort, weil das die Lesbarkeit erhöht. Leerzeichen sind bei Bereichsnamen nämlich nicht erlaubt. Den Vorteil werden Sie am Beginn des neuen Monats erkennen. Um nach dem Speichern die alten eingegebenen Daten zu löschen, ein mal F5 …
Arbeiten mit Bereichsnamen (3)
Doppelklick auf den Bereichsnamen und OK. Sofort wird exakt der vorgegebene Bereich markiert. Nicht mehr und nicht weniger. Nur noch Entf und die Daten sind gelöscht. Startdatum ändern, unter dem Namen des neuen Monats abspeichern und es kann nichts mehr schief gehen.
Wie schon erwähnt, ist dieses nur eine Basis für ganz einfache Anforderungen. Aufbauend auf dieser Tabelle gibt es noch diverse Erweiterungen, die bei Interesse und genügend Zeit gerne diskutiert und geübt werden können. Die folgenden drei wären diese:
| Weitere Lern-Einheiten | |
|---|---|
| Stundenabrechnung (2) | Stundenabrechnung (2) Erweiterung 1, Industriezeit und Stundensatz |
| Stundenabrechnung (3) | Stundenabrechnung (3) Nachtschicht, 0:00 – Grenze wird überschritten |
| Stundenabrechnung (4) | Stundenabrechnung (4) Erweiterung 3, Bedingte Formatierung für Wochenenden und Feiertage |
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00 € freuen … (← Klick mich!)