Stundenabrechnung
Industriezeit und Stundensatz
Excel, alle Versionen
Diese Aufgabe aus dem globalen Bereich „Zeit und Datum” baut auf dem Trainingsteil auf, wo die Grundlagen einer einfach gestalteten Stundenabrechnung geschaffen worden sind. Die im Grundseminar erarbeiteten Daten finden Sie im Format 2007 zur weiteren, eigenen Bearbeitung vor. Grundsätzlich sind alle Abbildungen und auch Handlungsanweisungen auf die Version 2007 bzw. 2010 abgestellt, meist aber ist die 2003er-Version so ähnlich, dass Sie diese Anleitung auch dafür nutzen können.
Voraussetzungen
Um diese Aufgabe ohne Trainer/in zu bewältigen, sollten Sie über solide Grundkenntnisse des Excel verfügen. In einem Seminar mit Trainer/in genügt es, wenn Sie gut mit Excel vertraut sind.
Weiterhin ist es erforderlich, dass Sie mit der bis hierher erarbeiteten Version (siehe obige Dateien) sehr gut klar kommen sind und auch alles verstanden haben. Als Trainer/in beachten Sie bitte, dass Sie diese Unterlagen ohne schriftliche Genehmigung der GMG Computer-Consulting nicht verwenden dürfen.
Definition
Zu Beginn bedarf es einer Definition, einer Vereinbarung über den Begriff „Industriezeit”. Als Beispiel verwende ich hier die Zeit 2:30h, also 2 Stunden und 30 Minuten. Im normalen Sprachgebrauch werden Sie genau wie ich „2½ Stunden” sagen. Und dieses wiederum würde mathematisch mit dem Begriff „2,5 Stunden” ausgedrückt. Und das ist exakt das, was die Industriezeit ausmacht. Die Minuten werden als dezimaler Teil einer Stunde dargestellt. 6 Minuten sind 1⁄10 Stunde, 15 Minuten eine viertel Stunde, und so weiter.
Aus dem ersten Teil wissen Sie, dass kalendarische Daten und Zeiten nur in der uns gewohnten Form in Excel dargestellt werden. Die „Testzeit” von 2:30 ist für Excel intern ein Wert von 0,104166666666… und nicht 2,5 (Stunden). Um aber beispielsweise Arbeitszeit * Stundensatz berechnen zu können, muss ein korrekter Stundenwert vorliegen, jener Wert also, der durch die Industriezeit repräsentiert wird.
Excel-Zeit zu Industriezeit
Es wäre ein Leichtes, Ihnen hier die fertige Berechnung zu präsentieren. Sie würden diese auch ohne Erklärung hinnehmen und behalten. Aber wenn Sie ein wenig an der Lösung mitarbeiten, kann das nur dem Verständnis der Dinge dienen. Und das ist gut und gewollt. Sie sind schließlich in einem Excel-Seminar. 💡
Am besten, Sie versuchen selbst, durch etwas Experimentieren die Formel für die Umrechnung herauszubekommen. Dazu laden Sie erst einmal die bisherige Datei auf der Basis Excel 2007, falls noch nicht geschehen. In etwa sollte sich Ihnen dieses Bild zeigen:
Die Ausgangslage für die Industriezeit und den Stundenlohn
Der erste und zweite Januar enthält ja noch keine Daten aber in Spalte E die Formeln. Geben Sie nun in B6 0:00 und in C6 24:00 ein. Am 2. Januar ist der Beginn auch um 0:00 und das Ende um 12:00. An beiden Tagen ist keine Pause eingetragen. Das sollte nun so aussehen:
Die Berechnete Arbeitszeit im hh:mm – Formatierung
Vielleicht irritiert Sie das Ergebnis in E6. Erstens hatten Sie in C6 24:00 eingegeben, nicht 0:00 und zweitens sollten doch Nullwerte gar nicht angezeigt werden. So war es im ersten Teil vereinbart und auch umgesetzt. Nun, die Erklärung und die Erkenntnis werden gleich kommen.
Null Uhr und 24:00 Uhr liegen ja nicht wirklich weit auseinander, im Prinzip sind es die identischen Zeiten. Einmal ist es der volle alte Tag, das andere mal ist es der neue Tag, der zwar begonnen hat aber 0 Sekunden alt ist. Hier wird aus 24:00 (alter Tag) automatisch 0:00 (neuer Tag) gemacht.
Dass Excel die Darstellung (wieder einmal) umwandelt wird besser ersichtlich und auch bewiesen, wenn Sie das Format der beiden Zellen einmal auf das Standard-Zahlenformat ändern. Dazu markieren Sie am besten eine beliebige Zelle aus Spalte F, und übertragen Sie die Formatierung beispielsweise mit dem Pinselsymbol in die Zellen E6:E7:
Die errechneten Stunden beispielhaft als Zahl formatiert
Ah ja, das hatten wir doch schon einmal. 0:00 bis 24:00 sind ein ganzer Tag. (Bitte merken, auch wenn es banal ist.) 0:00 bis 12:00 sind ein halber Tag, auch klar. Und jetzt kommt die wichtige Überlegung. Bei einem halben Tag soll bei der Industriezeit ja nicht mit 0,5 gerechnet werden, sondern mit 12 (Stunden). Und bei einem ganzen Tag mit 24 (Stunden). Jetzt wird Ihnen auch klar sein, warum in E6 die 00:00 angezeigt worden und nicht unterdrückt ist. Der Inhalt der Zelle ist ja Eins Komma Null, also größer als Null!
Das bedeutet: Wenn ich mit Industriezeiten rechne, dann muss ich die berechneten Stunden mit 24 multiplizieren, um damit korrekt weiter rechnen zu können. Nochmals als Beispiel: 6 Stunden Arbeit á 50,00 € macht 300,00 €. Klar. 6 Stunden sind aber intern nur 1⁄4 Tag, also 0,25. Und 0,25 * 50 € ergibt alles andere als den korrekten Betrag für eine Rechnung. Das Ganze mal 24 wird aber das gewünschte und korrekte Ergebnis, nämlich 300,00 € bringen.
Also, Sie merken sich bitte: Für das rechnen mit der Industriezeit muss die berechnete Zeit im Format hh:mm immer mit 24 multipliziert werden.
Berechnung des Entgelts
Auf dieser Basis lässt sich nun auch eine „saubere” Lohnabrechnung erstellen. Zumindest was das Brutto-Entgelt betrifft. Die Formel ist recht einfach und logisch: Entgelt = (Industrie-) Stunden * Stundenlohn.
Vorbereitung
Als Vorbereitung schließen Sie bitte die eben bearbeitete Datei ohne diese zu speichern. Es sollte ja nur bewusst gemacht werden, wie sich die Industriezeit von der typischen Zeitdarstellung in Excel unterscheidet. Öffnen Sie nun noch einmal Ihre zuletzt gespeicherte Version oder greifen Sie auf die Version des Trainings für Excel 2007 zurück.
Schreiben Sie in die Zelle D3 den Text Stundenlohn: (mit einem angehängten Leerzeichen) und formatieren Sie die Zelle rechtsbündig. In E3 kommt der Stundenlohn, formatiert als Währung. Es macht sehr viel Sinn, wenn Sie E3 mit einem Bereichsnamen versehen. In der folgenden Abbildung ist dieses der Name StundenLohn.
Bereichsname „StundenLohn” vergeben
Im nächsten Schritt werden Sie die Überschriften in Zeile 5 ergänzen, damit auch deutlich wird, was dort im Datenbereich berechnet wird. Geben Sie in F5:G5 eine möglichst sinnvolle Überschrift ein und übertragen Sie das Format aus den Zellen links, damit auch die Rahmenlinie unten zu sehen ist:
Neue Überschriften für die Spalten F und G eingefügt
Wenn Sie so weit sind, haben Sie alle Vorbereitungen geschafft und Sie können an die eigentliche Arbeit der Berechnung der Stundenlöhne gehen.
Die Berechnung
Überzeugt sein ist bekanntlich meistens besser als nur an etwas glauben. Darum versuchen Sie doch einfach einmal, in G8 das Tagesentgelt zu berechnen. Eigentlich ist das doch ganz einfach, nämlich Arbeitszeit * Stundenlohn. Das entspricht der Formel
=E8*E3
oder wenn Sie sinnvollerweise gleich mit dem Bereichsnamen arbeiten:
=E8*StundenLohn
In beiden Fällen werden Sie ein für den Arbeitnehmer ausgesprochen unbefriedigendes Ergebnis bekommen, nämlich:
Auch falsche Ergebnisse sind möglich …
Sie erkennen, dass Excel die 8:20 nicht so interpretiert, wie es durch uns gewünscht ist. Eigentlich kennen Sie ja auch den Grund. Sie brauchen auch gar nicht so sehr experimentierfreudig sein… multiplizieren Sie schon einmal das bisherige Ergebnis mit 24 und Sie werden sehen, dass das schon eher hinkommt. Es ist sogar das korrekte Ergebnis. Und wenn Sie lieber ohne eine Spalte mit der Industriezeit arbeiten, kann diese Formel durchaus eine dauerhafte Lösung sein:
=E8*StundenLohn*24
Aber Sie sollen ja auch den etwas längeren Weg kennen lernen. Darum berechnen Sie in F8 erst einmal die Industriezeit der geleisteten Stunden:
Die korrekte aber unschön formatierte Industriezeit
Stimmt, 8 1⁄3 Stunden. Das Ganze sollte aber etwas hübscher aussehen, darum ändern Sie das Zahlenformat bitte noch etwas ab:
Eigenes Zahlenformat für die Stunden der Industriezeit erstellen
Da es in der Abbildung nicht so 100%ig erkennbar ist, hier noch einmal das Format, wie Sie es in das Feld eingeben: 0,00″ Std.” . Bitte exakt so eingeben, dann kommen Sie auch zu dem gewünschten Ergebnis, wie es auch in der folgenden Abbildung zu sehen ist.
Anschließend werden Sie noch einmal die Formel in G8 anpassen oder neu schreiben und das Ergebnis stimmt nun endlich:
Formatierte Industriezeit und Berechnung des Entgelts
Das sieht ja schon einmal gut aus. Im nächsten Schritt kopieren Sie bitte die Formeln aus F8 und G8 in die Bereiche F6:F36 und G6:G36. Die Ergebnisse stimmen, aber wieder einmal ist das Format nicht so, wie es gewünscht ist:
Alles ausgefüllt, aber noch unschön formatiert
Die Null-Werte stören wieder einmal. Sie könnten das mit einer WENN()-Funktion lösen, besser ist aber die Formatierung anzupassen. Ändern Sie das Format der Spalte F auf 0,00″ Std.”;;, Spalte G sollte gleichermaßen mit 2 Semikola in der Formatierungsvorschrift ergänzt werden. Idealerweise erstellen Sie hierzu wiederum ein benutzerdefiniertes Format:
Das benutzerdefinierte Format für ein besseres Aussehen
Selbstverständlich können Sie bei Ihrer Format-Vorgabe auch Anführungszeichen (an den richtigen Stellen) verwenden, hier aber ist es nicht erforderlich. – Bleibt eigentlich nur noch wenig zu tun. Summenbildung in Zeile 37 und entsprechende Formatierung. Das sollten Sie aber auch ohne Hilfe hin bekommen. Und damit ist auch diese Aufgabe fertiggestellt. Vergleichen Sie gerne Ihr Ergebnis mit dem, was das GMG-Team erarbeitet hat. Sie kommen hier an die Datei per Rechtsklick zum Download oder per Doppelklick zum direkten öffnen in Excel.
Die weiteren Module dieses Seminars:
Stundenabrechnung (1): Die Basis, eine sehr einfache Tabelle
Erweiterung_2: Nachtschicht, 0:00 – Grenze wird überschritten
Erweiterung_3: Bedingte Formatierung für Wochenenden und Feiertage.
Hinweis: Wir sind neugierig und möchten gerne wissen, wer sich für dieses Thema interessiert. Und wir wollen das Ganze auch etwas steuern. Darum brauchen Sie für die Erweiterung 2 und die Erweiterung 3 ein Passwort.Dieses senden wir Ihnen gerne zu, wenn Sie uns eine entsprechende e‑Mail senden. Und da wir Spam hassen versprechen wir Ihnen, dass wir nur ein einziges Mal nämlich mit dem Passwort etwas Werbung für unsere Seminare und Trainings machen, nie wieder. Und wir geben Ihre Mail-Adresse auch garantiert nicht weiter, denn das gehört sich einfach nicht!
Vielleicht möchten Sie ja auch im Vorwege wissen, was Sie in den beiden Kapiteln erwartet. Eine kurze Zusammenfassung finden Sie (natürlich ohne Zugriffsschutz) auf dieser Seite des Blogs.
| Weitere Lern-Einheiten | |
|---|---|
| Stundenabrechnung (1) | Stundenabrechnung (1) Die Basis, eine sehr einfache Tabelle |
| Stundenabrechnung (3) | Stundenabrechnung (3) Erweiterung 1, Industriezeit und Stundensatz |
| 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!)