Meine bessere Hälfte bat mich darum, ihr eine von ihr angelegte Exceldatei zu überarbeiten. Ihr Wunsch: sie trägt nur die Arbeits- und Pausenzeiten ein und bekommt dann ausgerechnet, wie viele Stunden sie pro Tag, pro Woche, pro Monat und wie viele Überstunden gearbeitet hat. Problem an der ganzen Sache: sucht man im Internet nach „Uhrzeiten in Excel berechnen“, so bekommt man nicht hundertprozentig das, was es hier umzusetzen gilt. Das Ergebnis soll nämlich nicht im Format hh:mm (Stunden und Minuten) sein, sondern als Fließkommazahl (z.B. 8,75 Stunden). Das war für den Anfang etwas tricky, aber die Lösung ist nicht wirklich schwer. Einzig die Formel in Excel kann etwas aufgebläht wirken. Da habe ich aber keine Ahnung, ich bin schließlich kein Informatiker 😀

Beginnen wir also! Zur Vorbereitung legen wir uns fünf Spalten an: Beginn, Ende, Pause, Arbeitszeit und eine fünfte Spalte, zu der ich später noch komme. Optional kann man vor der Spalte „Beginn“ sich noch das Datum notieren. In meinem Fall sieht das dann erstmal so aus:

Wer mag, kann sich nun noch die erste Zeile fixieren, sodass man beim Scrollen durch das Tabellenblatt später noch die Spalten zuordnen kann. Okay, fünf Spalten sind jetzt nicht die Welt, aber hierbei können wir gleich diese Funktion noch einmal üben. Wir klicken dazu links auf die erste Zeile, also auf die in meinem Bild grau hinterlegte grüne Eins, die neben der Zelle A1 ist, klicken auf den Reiter „Ansicht“, dann „Fenster fixieren“ und wählen die Option „Oberste Zeile fixieren“. Nun haben wir eine feste Zeile, die beim Scrollen immer oben angezeigt wird.

Damit die Anzeige der Arbeitszeiten und die spätere Berechnung wie gewünscht reibungslos funktioniert, müssen wir uns die jeweiligen Zellen formatieren. Für das Datum kann man mehrere Formate nutzen. In meinem Beispiel nutze ich das Format Tag, DD. Monat YYYY, zum Beispiel: Montag, 12. August 2019. Um dies einzustellen klicken wir in die Zelle, gehen im Reiter „Start“ in dem Formatierungsblock auf den Pfeil rechts unten (im Bereich „Zahl“), wählen in dem neu geöffneten Fenster die Kategorie „Datum“ und wählen das zweite Format in der Liste „*Mittwoch, 14. März 2012“. Nachdem wir das Fenster mit einem Klick auf „OK“ geschlossen haben, tragen wir in die Zelle A2 ein Datum ein, z.B. „12.08.2019“, und bestätigen mit Enter. Die Zelle sollte nun den Inhalt „Montag, 12. August 2019“ tragen und sich automatisch auf die erforderliche Breite eingestellt haben. Die Zellen B2 und C2 können wir gemeinsam formatieren mit den eben genannten Schritten, nur wählen wir hier die Kategorie „Uhrzeit“ und das Format „13:30“ aus. Einzugeben ist die Uhrzeit in diesem Format, wobei die Stundenzahlen von null bis neun ohne vorangestellte null eingetragen werden können. Die Zelle D2 für die Pausenzeit, die ich hier in vollen Minuten angebe, stellen wir als Zahl ohne Nachkommastellen ein, die Zelle E2 als Zahl mit zwei Nachkommastellen. Im Feld E2 bleiben wir nun und geben die Formel ein. Entweder beginnen wir gleich mit dem Gleichheitszeichen oder gehen oben in die sogenannte Bearbeitungsleiste. Die Formel zur Berechnung lautet:

=WENNFEHLER(STUNDE(C2-B2-ZEIT(0;D2;0))+((MINUTE(C2-B2-ZEIT(0;D2;0))/100)*100/60);0)

Erklärung der Formel: Eingeleitet wird sie mit einer Fehlerabfrage „WENNFEHLER()“. Das verhindert, dass bei fehlenden Zelleninhalten der Text „#ZAHL!“ in der Ergebniszelle erscheint. Das ist für spätere Berechnungszwecke wichtig. Das Ergebnis selbst ist an sich eine Summe aus den vollen Stunden und den Minuten. In dem Teil STUNDE() wird die Differenz aus End-, und Anfangs- und Pausenzeit gebildet. Da ich die Pausenzeit hier in Minuten angebe, müssen diese erst in eine Zeit umgerechnet werden. Das klingt an dieser Stelle komisch, hat aber den Hintergrund, dass Excel Uhrzeiten in Kommazahlen umrechnet und mit diesen operiert. Die Formel ZEIT() benötigt also die Parameter Stunden, Minuten und Sekunden und gibt eine Kommazahl aus. Beispiel: ZEIT(0;30;0) gibt den Wert 0,0208333… zurück.
Bei den Minuten machen wir im Grunde das Gleiche, jedoch müssen wir hier noch etwas umrechnen. Da wir als Ergebnis eine Kommazahl haben wollen, darf der Teil nach dem Komma maximal den Wert 100 haben, also müssen wir erstmal durch 100 teilen. Danach rechnen wir diesen Wert über eine umgestellte Verhältnisgleichung von Minuten in den gleichwertigen Teil von 100 um. Kurz gesagt: wenn 15 Minuten eine Viertelstunde sind, ergibt das 0,25.
Nun können wir zum Test die Werte 7:30 als Beginn, 16:30 als Ende und 60 als Pause eingeben. Das Ergebnis sollte eine Arbeitszeit von acht Stunden sein.

Nun markieren wir die Zellen A2 bis E2 und packen an dem kleinen Quadrat am unteren rechten Ende des markierten Bereiches an und ziehen uns den Bereich auf die gewünschte Anzahl an Zeilen. In der Spalte F markieren wir nun die Zellen F2 bis F4, gehen im den Reiter  „Start“ im Bereich „Ausrichtung“ auf „Verbinden und zentrieren“ und wiederholen das mit den Zellen F5 und F6. In die Zelle F1 kann man nun sowas wie „Wochen- und Überstunden“ eintragen. In den Zellenbereich F2 bis F4 klicken wir hinein und bilden die Summe der in einer Woche geleisteten Arbeitszeit. Für die Erste Woche ist das z.B. =SUMME(E2:E6). Die Eingabe bestätigen wir mit Enter. Für die Überstunden geben wir im Zellenverbund F5 F6 die Formel =SUMME(E2:E6)-40 ein, alternativ =E2-40. Falls die Wochenarbeitszeit abweicht, ist die 40 hier anzupassen 😉

Für wen das ein Krampf ist, das alles hier abzuarbeiten, der kann sich hier eine Beispieldatei herunterladen.

  Arbeitszeiterfassung