5. Der fast perfekte Überstundenrechner – Stundenerfassung in Excel

Überstunden Uhr

Arbeitsstunden pro Tag eingeben und Überstunden berechnen. Auswertung mit Pivot-Tabelle inkl. Darstellung über Monate und Jahre.

>>>Wer die fertige Datei will, klickt einfach hier.<<<

 

Mehrarbeit ist ja in vielen Bereichen ein normales Phänomen. Ein Problem, dass ich hier nicht diskutiere… Aber natürlich besteht bei vielen Arbeitnehmern der Wunsch, die Arbeitszeit mit einer Stundenerfassung möglichst gut zu dokumentieren. Das funktioniert mit Excel sehr gut…

Die Überstundendokumentation machen inzwischen viele in Excel… ich zeige hier eine Möglichkeit, wie es ziemlich gut funktioniert. So gut, dass man später die Arbeitszeiten der Jahre miteinander vergleichen kann.

Als ersten Schritt öffnet man ein frisches Tabellenblatt.

Ich formatiere meine Tabellenblätter immer gleich in 8-Punkt-Schrift. Diese ist noch ausreichend lesbar und spart Papier und Anpassungsbedarf beim Druck.

STEP 1 – Das Datenmodell

Die Arbeit beginnt (wie immer) ganz oben links. Die Stundenübersicht wird als Daten(bank)-Modell aufgebaut, welches später für Auswertungszwecke genutzt wird. In die Tabelle kommen also wirklich nur die Tage (und keine Zwischensummen o.ä.).

Die Zeile 1 wird von A, B, C folgend mit den notwendigen Datenfeldern gefüllt. Dazu kommen ein paar berechnete Felder.

A1: Datum
B1: Typ (dieses Feld wird benötigt um zu beschreiben, ob es sich z.B. um einen Arbeitstag oder Urlaubstag handelt)
C1: Soll (dieses ist die Arbeitszeit, die gearbeitet werden sollte)
D1: Start (Uhrzeit für Arbeitsbeginn)
E1: Ende (Uhrzeit für Arbeitsende)
F1: Pause (Pausenzeit)

Optional kann man zwei Felder einfügen, die beschreiben, was man getan hat und ob es Besonderheiten gab. Das könnte wichtig sein, wenn man tatsächlich die Überstunden gegenüber dem Arbeitgeber angeben will. Überstunden müssen nämlich angewiesen sein…

G1: Arbeitsschwerpunkt/Job
H1: Besonderheit

Nun folgen berechnete Felder:

I1: ISThmm (Die Arbeitszeit des Tages in Stunden und Minuten, z.B. 7:50) … ohne Pause!
J1: ISTdez (Die Arbeitszeit als Dezimalzahl, z.B. 7,83 h)
K1: ÜBERdez (die Überstunden als Dezimalzahl – also nicht mit Minuten)

Als letztes werden Felder für die Auswertung des Datums benötigt.

L1: Wochentag (zeigt den Wochentag als Zahl an)
M1: Kalenderwoche
N1: Monat
O1: Jahr

Eigentlich sollte man es vermeiden, in Excel Daten und Informationen zu mischen. Da Pivot-Tabellen keine berechneten Felder haben, müssen aber Ausnahmen gemacht werden. So werden in Datentabellen oft wichtige Summen gebildet oder Datumsfelder ausgewertet.

So sieht das dann aus (erste Zeile fett formatiert mit Anwahl der gesamten Zeile und ‚F‘ für fette Formatierung):

Step 2: Die erste Zeile mit Daten

Als nächster Schritt werden die ersten Daten eingetragen. Das passiert in der zweiten Zeile.

A2: Das Datum z.B. 01.01.2017
B2: Typ … A für Arbeitstag, W für Wochenende, U für Urlaub, K für Krankheit und F für Feiertag
C2: Die Sollarbeitszeit kann manuell eingetragen werden (z.B. 8 für 8 Stunden) oder per Formel

Bei einer normalen 40-Stunden-Wochen (o.ä.) ist eine Formel zu empfehlen. Diese lautet:

=WENN(B2=“A“; 8; 0)

Wenn es sich also um einen Arbeittag handelt, werden immer 8 Stunden eingetragen. In allen anderen Fällen beträgt die Sollarbeitszeit Null.

D2: Der Arbeitsbeginn… eingegeben mit Stunde:Minute … also z.B. 09:00
E2: Arbeitsende… eingeben mit Stunde:Minute … also z.B. 18:00
F2: Pausenzeit als dezimale Zeit! Also z.B. 1 für eine Stunde oder 0,75 für 45 Minuten. Achtung: Dieses Feld muss als Dezimalzahl formatiert sein (Format „Zahl“)
G2/H2: optinale Info-Einträge

Nun folgen die berechneten Felder:

I2: Die berechnete IST-Zeit in Stunden und Minuten – ohne Pause mit der Formel: =(E2-D2)
J2: Die berechnete Arbeitszeit als Dezimalzahl mit Berücksichtigung der Pause: =(G2*24)-F2

>> Excel arbeitet bei Zeiten mit dem „Zeitformat“ mit Doppelpunkten. Bei Berechnungen mit Zeiten muss man sehr aufpassen. Daher sollten Zeiten möglichst schnell in Dezimalwerte (also mit Komma) umgerechnet werden <<<<<

K2: Die berechneten ‚Überstunden‘: =J2-C2

Und die Informationsfelder aus dem Datum:

L2: =WOCHENTAG(A2;2) … hier wird der Wochentag (Montag=1) angezeigt.

Standard wäre, dass Excel für Sonntag die 1 anzeigt. Durch die Option ‚2‘ hier dem Semikolon wird erreicht, dass Montag als erster Tag der Woche angezeigt wird.

M2: =KALENDERWOCHE(A2;21)

Die Option ’21‘ hinter dem Semikolon wird genommen, damit der ISO-Standard für Kalenderwochen eingehalten wird. Ansonsten würde der 01.01.2017 in dem Jahr schon zur ersten Kalenderwoche gehören, was nach dem ISO-Standard nicht so ist.

N2: =Monat(A2)
O2: =Jahr(A2)

Und so sollte das jetzt aussehen…

Step 3: Felder nach unten ausfüllen

Jezt werden die Felder nach unten ausgefüllt. Ich würde erst einmal so ca. 20 Felder empfehlen.

Hier nutze ich das kleine schwarze Kreuz, welches erscheint, wenn man an den rechten, unteren Rand einer Zelle geht.

Also: Gehe mit der Maus zu Zelle A2 und an den unteren Rand, rechte Ecke bis das schwarze Kreuz erscheint. Klicke, halte gedrückt und ziehe das Feld ca. 20 Felder nach unten.

Der gleiche Vorgang erfolgt nun für alle Spalten, in denen Formeln stehen. Den Block von ‚I‘ bis Spalte ‚O‘ kann man dabei insgesamt selektieren und in der rechten Ecke von der Zelle ‚O2‘ anfassen und nach unten kopieren.

Zur besseren Übersicht würde ich jetzt die Spalte A formatieren. Das Ziel ist, dass hier  auch der Wochentag angezeigt wird. Dazu selektiert man die ganze Spalte indem man auf das ‚A‘ ganz oben am Tabellenrand klickt. Jetzt wählt man im Bereich des Startmenüs im Dropdown der Zahlenformate ‚Weitere Zahlenformate‘ aus.

Wähle im sich öffnenden Fenster ‚Benutzerdefiniert‘. Also Format schreibst Du: TTT, TT.MM.JJ .

Das Ergebnis sieht so aus: So, 01.01.2017. Die drei TTT stehen für die Bezeichnung des Wochentages mit 2 Buchstaben. Bei 4 (TTTT) würde der Wochentag ausgeschrieben.

Jetzt kann man noch den Typ des Tages auffüllen. Das mache ich manuell bzw. über das kleine schwarze Kreuz oder Copy & Paste.

Das Ergebnis sieht jetzt so aus:

Step 4: Formatierung

Die erste Spalte A wurde ja schon formatiert. Zur Übersicht formatiere ich jetzt die Spalten in denen ich keine Werte eingeben werde. Ich mache sie bunt.

Die Spalte Überstunden formatiere ich mit dem Format „Zahl“ (d.h. zwei Dezimalzeichen), ebenso die Spalte K mit den Überstunden.

Jetzt sollten noch die Wochenenden, Urlaub, Feiertag und Krankheit farblich abgesetzt werden. Das funktioniert mit einer bedingten Formatierung.

Der Umgang mit bedingten Formatierungen ist ’nicht ohne‘. Das muss Frau und Mann erst einmal kapieren.

Schrittweise:

Das Ziel: Wenn ein Tag ein ‚Feiertag‘ ist (also ein F in der Spalte B steht), dann soll dieser Tag rot mit dunkelroter Schrift dargestellt werden.

1.  Gehe zu ‚Bedingte Formatierungen. Regel verwalten.

2. Wähle das kleine Plus-Zeichen, um eine Regel hinzuzufügen.

3. Wähle Auswahl bei „Klassisch“. Wähle als Typ „Formel“.

4. Wähle „Formel für die Ermittlung…“.

5. Wähle „Bestimmter Text mit Inhalt“. Gebe jetzt die Formel für Feiertag ein: =$B1=“F“

6. Wähle Deinen Formatierungswunsch: z.B. Hellrot mit Dunkelrot.

7. Jetzt bestätige. Das Ergebnis wird noch nicht funktionieren!

8. Im Übersichtsfenster wähle „Formatierungsregeln für „dieses Blatt“.

9. Bei „Angewendet auf“ gebe bitte ein: Tabelle1!$A:$O

So muss das aussehen:

 

Wenn man jetzt in ein Feld der Spalte „B“ ein „F“ für Feiertag einträgt, wird diese Zeile „Rot“.

Jetzt die Schritte für „W“, „U“ und „K“ (Wochenende, Urlaub, Krankheit) wiederholen.

ACHTUNG BUG: Wenn man mit „OK“ bestätigt hat man zunächst nicht das richtige Ergebnis. Man muss erneut in das Fenster „Regeln verwalten“ und dort das „$B100.XX“ durch $B1 ersetzen. Danach funktioniert es.

Das Ergebnis sieht so aus:

 

Hinweis: Ich habe zum Test natürlich ein paar Urlaubstage und Krankheitstag eingetragen.

Die Ansicht der Tabelle ist daraufhin wie folgt:

Step 5: Testdaten und erste Auswertung

Eine ganz einfache Auswertung der Arbeitsstunden erreicht man über die eine weitere berechnete Spalte. Diese Spalte kann man hinter der Spalte K einfügen. In L2 sollte stehen „=K2“). In L3 sollte stehen: „=L2+K3“. Wenn man das jetzt nach unten ausfüllt (kleines schwarzes Kreuz), dann entstehen kumulierte Werte. Das Feld nenne ich „Kum“.

Achtung: Bei der Verwendung von kumulierten Werten in Datentabellen, muss Excel viel rechnen! Wenn die Tabelle sehr groß wird, dann wird die Bearbeitung oft langsam. Auch hier gilt: Überlegen, ob dies notwendig ist oder die Lösung nicht später mit einer Pivot-Tabelle zu lösen ist.

Vor der Auswertung teste ich das Modell und gebe Daten ein…

Step 6: Auswertung mit Pivot-Tabelle

Wer die Artikel meines Blogs liest, wird merken, dass ich in vielen Fällen – und zwar immer, wenn es sich um langsfristig genutzte Tabellen handelt – mit Pivot-Tabellen arbeite. Hier greift Excel auf Tabellen zu, die wie eine Datenbank (Liste mit Feldern) angelegt sind.

Dazu sind folgende Schritte notwendig:

1. Datenbereich mit „Namen“ festlegen. Wähle den Datenbereich „A“ bis „P“, indem Du alle Spalten markierst. Gehe zum Reiter „Formeln“, wähle „Namen definieren“.

2. Gib als Namen einen Namen ein, den Du erinnerst. Wenn nur eine Tabelle in der Arbeitsmappe ist, verwende ich oft den Namen Liste oder den Namen Database.

Als Ergebnis haben die Zellen von A bis P nun den Namen Liste.

3. Jetzt erstelle ein neues Tabellenblatt (oder wähle ein neues aus).

4. Wähle den Reiter „Einfügen“ und dort Pivot-Tabelle.

5. Im Dialog wählst Du für den Bereich „Tabelle oder Bereich“ den von Dir gewählten Namen. D.h. Du tippst ihn dort ein. Also z.B. „Liste“. Du kannst vorhandenes Arbeitsblatt wählen…

6. Nun erscheint eine leere Pivot-Tabelle und ein Pivot-Table-Generator.

7. Im Feld „Werte“ erscheinen die berechneten Werte. Ziehe das Feld „Über“ aus der Auswahlliste des Pivot-Generators in das Feld Summen-Werte.

Achtung: In vielen Excel-Versionen (Mac, alte Windows-Versionen) wird als Standard im Summenfeld des Pivot-Generators die Funktion „Anzahl“ ausgewählt. Über das kleine ‚i‘ kann dies in „Summe“ geändert werden – meist benötigt man hier eine Summe.

8. Nachdem sichergestellt ist, dass hier wirklich summiert wird (Auswahl Summe), kann man auch noch das Zahlenformat auf „Zahl zwei Dezimalstellen“ oder benutzerdefiniert auf 0,00″ h“ o.ä. einstellen.

9. Das Ergebnis sieht man jetzt bereits in der links entstehenden Pivot-Tabelle

 

10. Nun kann man z.B. das „Jahr“ aus der Feldliste in den Berichtsfilter ziehen. Und den „Monat“ in die Zeilen. Damit man hier etwas sieht, sollte man die Testdaten vielleicht mind. um einen Monat erweitern…

11. Über den Befehl ‚rechte Maustaste‘ „Daten aktualisieren“ kann man die Daten der Pivot-Tabelle aktualisieren.

12. Zunächst wird immer „leer“ zusätzlich angezeigt. Über das Filtersymbol bei den Zeilenbeschriftungen kann man leere Dateninhalte ausblenden.

13. Im Reiter „Entwurf“ gibt es schickere Farbvorlagen, als das Standard-Grün.

14. Das Wort Zeilenüberschriften lässt sich sehr einfach entfernen, indem man in die entsprechende Zelle geht und es z.B. durch „Monat“ ersetzt.

15. Jetzt könnte man die Auswertung noch durch die Kalenderwoche erweitern, indem man das Feld „Woche“ im Generator unter das Feld „Monat“ zieht.

16. Wenn die Reihenfolge nicht gefällt (in diesem Fall die Woche 52, die als letztes erscheint) so wählt man genau die Zelle mit der 52 aus und sucht am oberen Tabellenrand das Symbol der Hand. Damit kann man jetzt die „52“ vor die „1“ ziehen.

17. Beim ersten Filter „Jahr“ kann man z.B. 2017 auswählen.

FERTIG!

Step 7: Letzter Schliff… Filter und Bereiche einfrieren

1. Einfach die Tabelle (Liste) anwählen in eine Zelle mit Daten gehen und im Reiter Start auf der rechten Seite „Sortieren und Filtern“ „Filtern“ anwählen.

2. In eine Zelle der Zeile 2 gehen, z.B. „D2“. Dann über den Reiter „Ansicht“ den Button „Bereiche einfrieren“ wählen. Dieses sorgt dafür, dass die erste Zeile und der linke Bereich immer im Sichtfeld sind.

Step 8: Die fertige Datei einfach hier herunter laden

Hier kannst Du die fertige Datei gleich herunter laden… stundenerfassung_winfridtiede[/vc_column_text][/vc_column][/vc_row]

ueberstundenschritt7AnsichtmitDatenundKum

Excel Know-How in Ihrem Unternehmen...

Holen Sie sich mein Know-How in Ihre Firma. Als Unternehmensberater finde ich arbeitszeitfressende Excel-Dateien. Ich schule Sie, entwerfe für Sie (oder mit Ihnen) neue Modelle. Sparen Sie Zeit und entdecken Sie Potentiale in Ihren Unternehmensdaten. Einfach mal anfragen.

Winfrid Tiede
Unternehmensberater
Share
This