Tipps rund um das Rechnen mit Uhrzeiten (Excel)
01.07.2011

Tipps rund um das Rechnen mit Uhrzeiten,
Versionen: Excel 97, 2000 und 2002

Das Rechnen mit Zeitangaben ist in Excel grundsätzlich kein Problem und viele Anwender nutzen diese Möglichkeiten beispielsweise zur Arbeitszeiterfassung oder für Abrechnungen aller Art. Die zahlreichen Anfragen an unsere Helpline zeigen allerdings, dass es viele Sonderfälle gibt, in denen man entweder ein wenig tricksen muss, um zum Ziel zu kommen. In diesem und im nächsten Tipp der Woche finden Sie daher einige nützliche Hinweise und fertige Formeln zu so häufigen Problemen wie dem Rechnen mit negativen Zeitangaben, dem Rechnen über Datumsgrenzen, der Darstellung von Zeiten über 24 Stunden, der Umwandlung von Uhrzeiten in Dezimalzahlen (und umgekehrt) oder dem Runden von Zeiten auf die nächsten x Minuten.

Beginnen wir aber mit einigen Grundlagen, die Ihnen helfen werden, die Funktionsweise der Tipps besser zu verstehen. Obwohl Excel eine Uhrzeit in der Form "12:00" darstellen kann, wird sie intern nicht so verarbeitet. Für jede Uhrzeit zwischen 0:00 Uhr und 24:00 Uhr gibt es einen Dezimalwert zwischen 0 und 1. Also zum Beispiel:

0,25 = 6:00 Uhr (=1/4 des Tages)

0,5 = 12:00 Uhr (=die Hälfte des Tages)

0,75 = 18:00 Uhr (=3/4 des Tages)

Auf dieser Basis lässt sich jede beliebige Uhrzeit bis auf die Sekunde abbilden. Sie können das ganz einfach testen, indem Sie eine beliebige Uhrzeit in eine Excel-Zelle eingeben und dann das Menü Format-Zellen anwählen, um Zahlenformat "Standard" zuzuweisen. Excel zeigt dann automatisch die Dezimalzahl an, die hinter der eingegebenen Uhrzeit steckt.

Das einfache Rechnen mit Uhrzeiten ist auf Grundlage von diesem System kein Problem. Geben Sie beispielsweise in ein leeres Arbeitsblatt in A1 "12:00" als Arbeitsbeginn und in A2 "18:00" als Arbeitsende ein (jeweils ohne Anführungszeichen). Über die Formel:

=A2-A1

erhalten Sie dann die Anzahl der gearbeiteten Stunden:

6:00

Excel rechnet dabei ganz einfach:

=0,75-0,5

Das Ergebnis 0,25 entspricht wie oben erwähnt der Uhrzeit "6:00". Allerdings, und das ist die erste Besonderheit, die beim Rechnen mit Uhrzeiten zu beachten ist, können Sie Uhrzeiten nicht direkt in einer Formel verwendet. Die folgende Formel wird daher mit einem Fehler quittiert:

=18:00-12:00

Die Uhrzeiten sind lediglich spezielle Formatierungen, mit denen nicht gerechnet werden kann. In einer Formel müssen Sie eine Uhrzeit daher zunächst über die Funktion ZEITWERT in die dezimale Entsprechung umwandeln. Korrekt wäre daher die folgende Formel:

=ZEITWERT("18:00")-ZEITWERT("12:00")

An diesen Beispielen erkennen Sie schon, dass das Rechnen mit Uhrzeiten nur bei ganz simplen Additionen und Subtraktionen wirklich einfach ist. Möchten Sie beispielsweise einen Arbeitslohn auf Grundlage der berechneten Arbeitszeit ermitteln, fangen die Probleme schon an, denn die Multiplikation von "6:00" mit einem Stundenlohn von beispielsweise 15 EUR führt natürlich nicht zu dem korrekten Ergebnis. Für diese und andere Anwendungen ist es daher notwendig, die Uhrzeiten in eine Dezimalzahl zu verwandeln (zum Beispiel 8,5 für 8:30). Zu diesem Zweck bringen Sie die Dezimalzahl, die hinter der Uhrzeit steht durch die Multiplikation mit 24 Stunden auf eine 24-Stunden-Basis:

=ZEITWERT("6:00")*24

Das Ergebnis ist in diesem Fall "6" und diese Zahl können Sie dann ganz einfach mit einem Stundensatz multiplizieren. Genauso einfach ist es, eine Dezimalzahl in einer Uhrzeit umzuwandeln: dazu dividieren Sie die Zahl einfach durch 24. Zum Beispiel:

=9,5/24

Das Ergebnis: 9:30 (nach entsprechender Formatierung über Format-Zellen).

 

---

 

Nachdem wir im ersten Teil dieses Tipps gezeigt haben, wie Excel Uhrzeiten intern verarbeitet und einfache Berechnungen ausgeführt werden können, geht es im folgenden vor allem um Tipps zu den am häufigsten nachgefragten Problemstellungen. Beginnen wir mit der Darstellung von negativen Zeitangaben, die vor allem bei der Arbeitszeiterfassung immer wieder auftauchen, die aber von Excel nicht unterstützt werden. Zum Beispiel in A1 die Sollarbeitszeit 8:00 und in A2 die tatsächliche Arbeitszeit 6:15. Die Über-/Minderstunden berechnen Sie dann mit:

=A2-A1

In diesem Fall ist das Ergebnis eine Folge von #-Zeichen, da Excel keine negativen Zeiten kennt. Die einzige Lösung ist eine Formel, die die negativen Stundenangaben als Text darstellt:

=WENN(A2-A1<0;"-"&TEXT(ABS(A2-A1);"-[h]:mm");A2-A1)

Sie erhalten daraufhin auch bei negativen Zeiten das korrekte Ergebnis, aber die Lösung hat den Nachteil, dass es sich nicht mehr um eine Zahl handelt, mit der Sie rechnen können. In solchen Situationen bietet es sich an, die Uhrzeiten für Berechnungen in Dezimalzahlen umzuwandeln. Wie im ersten Teil des Tipps beschrieben, müssen Sie die Uhrzeiten dafür lediglich mit "24" multiplizieren. Aus "8:30" wird dann beispielsweise "8,5" oder aus 6:45 wird "6,75". Sie verwenden dann die gezeigte Formel, um die Über-/Minderstunden anzuzeigen und nutzen die Dezimalzahlen um beispielsweise Wochenstunden zu berechnen oder ein Honorar zu ermitteln.

Etwas einfacher zu lösen ist die Berechnung von Zeiten über Tagesgrenzen hinweg. Wenn Sie beispielsweise im Schichtdienst um 22:00 Uhr beginnen und bis 6:00 Uhr arbeiten, liefert eine normale Subtraktion natürlich das falsche Ergebnis. Abhilfe schafft die folgende Formel (Arbeitsbeginn in A1, Arbeitsende in A2):

=WENN(A2>A1;A2-A1;(1-A1)+A2)

Über WENN prüfen wir hier zunächst, ob die Uhrzeit des Arbeitsendes größer ist als die Uhrzeit des Arbeitszeitbeginns. Wenn das der Fall ist, liegen beide Zeiten am gleichen Tag und zur Berechnung genügt eine einfache Subtraktion. Wurde die Tagesgrenze übersprungen, berechnet die Formel zunächst die Stunden bis 24:00 Uhr (=1) und addiert dann die Stunden des neuen Tages.

Das nächste Problem taucht auf, wenn Sie Zeitangaben beispielsweise wochen- oder monatsweise summieren. Hier liegt das Ergebnis in den meisten Fällen über 24 Stunden und genau dann scheitern Sie mit Excel. Nehmen wir beispielsweise einmal die folgenden Arbeitszeiten (eines fleißigen) Mitarbeiters an:

10:00

12:00

09:30

Das Ergebnis sollte sein: 31:30. Tatsächlich liefert Excel aber das Ergebnis 7:30. Der Grund wird deutlich, wenn Sie der Zelle mit der Summenformel das Standard-Zahlenformat zuweisen. Aus "7:30" wird dann: 1,3125. Dabei handelt es sich um die korrekte Summe aus den Dezimalzahlen, die hinter den aufgeführten Stundenangaben stecken. Da Excel allerdings (wie im ersten Teil beschrieben) jede Uhrzeit mit einer Zahl zwischen 0 und 1 darstellt, werden bei der Summe nur die Nachkommastellen beachtet und dementsprechend "0,3125" als "7:30" dargestellt.

Damit Sie aber dennoch mit Zeitangaben über 24 Stunden arbeiten können, bietet Excel ein spezielles Zahlenformat nur für diesen Zweck an. Markieren Sie dazu die entsprechende Zelle, wählen Sie das Menü Format-Zellen an und wechseln Sie auf die Registerkarte "Zahlen". Markieren Sie hier die Kategorie "Uhrzeit" und wählen Sie den Eintrag "37:30:55" aus. Nachdem Sie das Dialogfeld mit Ok bestätigt haben, wird "1,3125" korrekt als "31:30:00" angezeigt.

Zu guter letzt noch ein Tipp zum Runden von Zeitangaben. So ist es vor allem bei der Abrechnung von Dienstleistungen üblich, in bestimmten Zeiteinheiten abzurechnen (zum Beispiel 10 oder 15 Minuten). Die tatsächlich gearbeitete Zeit soll daher entsprechend gerundet werden. Die entsprechende Formel sieht folgendermaßen aus:

=RUNDEN(A1*24/(1/x);0)*(1/x)/24

Dabei ist A1 die Zelle mit der Zeit, die gerundet werden soll und "x" der Stundenanteil, auf den Sie runden möchte. Zum Runden auf die nächsten 5 Minuten, lautet die Formel dementsprechend (5 Minuten = 1/12 einer Stunde:

=RUNDEN(A1*24/(1/12);0)*(1/12)/24

Geben Sie nun in A1 die Zeit 2:03 ein, liefert die Formel: 2:05. Aus 1:14 wird 1:15 usw. Das Runden auf die nächsten 10 Minuten erreichen Sie dementsprechend mit:

=RUNDEN(A1*24/(1/6);0)*(1/6)/24

Oder auf die nächsten 15 Minuten:

=RUNDEN(A1*24/(1/4);0)*(1/4)/24

Öffnungszeiten
Ladengeschäft:
Montag bis Donnerstag
15:00-18:00 Uhr
Freitag
8:30:13:00 Uhr
Samstag
nach Terminvereinbarung

Wir vereinbaren auch
gerne Termine über unsere
Öffnungszeiten hinaus.

Telefon:
06723 889005

Telefax:
06723 889006

E-Mail:
info@tibe.de

Adresse:
Tibe Services
Inhaber: Tim Berlebach
Zangerstraße 22
65375 Oestrich-Winkel /
Ortsteil Hallgarten

Öffentlicher Parkplatz:
Taunusstraße/
Ecke Zangerstraße

 Preisänderungen und Irrtümer bleiben vorbehalten.  Angebot gilt solange Vorrat reicht!