Der Umgang mit Uhrzeiten ist ein gängiger Anwendungsfall in Excel. Zeiterfassung, Dienstpläne Überstundenberechnung, Maschinenlaufzeiten oder Terminplanung – überall kommen Zeiten zum Einsatz, die erfasst, berechnet oder ausgewertet werden müssen.
In diesem Artikel gebe ich einen Überblick über die verschiedenen Möglichkeiten, wie man in Excel mit Zeitangaben umgehen kann. Und was es für dabei für Besonderheiten zu beachten gilt.
Und so geht’s:
Beispieldatei herunterladen
Aktuelle Uhrzeit per Tastenkürzel einfügen
Wie bekommt man am schnellsten die aktuelle Uhrzeit in eine Zelle? Um zum Beispiel einen fixen Zeitstempel zu generieren?
Ganz einfach:
Mit der Tastenkombination Strg + Umschalt + :
Tastenkombination Strg+Umschalt+Doppelpunkt
Tipp:
Nur mit Strg + . erhält man das aktuelle Datum
Uhrzeit als Formel einfügen
Soll hingegen fortlaufend die aktuelle Uhrzeit angezeigt werden, kommt die Funktion JETZT zum Einsatz:
Die JETZT-Funktion
Die Funktion liefert das heutige Datum mit der aktuellen Uhrzeit. Wenn du nur die Uhrzeit sehen möchtest, dann ändere einfach das Zahlenformat:
Zahlenformat ändern auf Uhrzeit
Der Unterschied zur Eingabe über die Tastenkombination ist, dass die Uhrzeit nun laufend neu berechnet wird, sobald sich irgendetwas im Arbeitsblatt ändert – oder man die Funktionstaste F9 drückt.
Hinweis:
Diese Funktion ist eine sogenannte volatile Funktion, da sie eben andauernd neu berechnet wird. Es gibt in Excel noch eine Reihe weiterer solcher Funktionen, die man aus Performance-Gründen grundsätzlich mit etwas Vorsicht genießen sollte. Einzelheiten dazu findest du in diesem Beitrag: Excel im Schneckentempo: Volatile Funktionen
Aber keine Angst, eine einzelne JETZT-Funktion in deiner Arbeitsmappe wird Excel sicherlich verkraften 😉
Listen mit Uhrzeiten manuell erfassen
Du hast lange Listen mit vielen Uhrzeiten (oder Arbeitszeiten) , die du manuell in Excel erfassen musst?
Zahlenkolonnen gibt der Schnelltipper üblicherweise auf dem separaten Nummernblock rechts auf der Tastatur ein. Auch Datumsangaben lassen sich so schnell erfassen, wenn man statt des dort fehlenden Punkts einfach den Schrägstrich verwendet.
Wie aber erfasst man Zeitangaben? Einen Doppelpunkt gibt es ja auf dem Nummernblock auch nicht. Da hilft dir der folgende Tipp, der von meinem Excel-Kollegen Johannes Sandkamp stammt:
Er hat sich nämlich einfach eine Autokorrektur eingerichtet, welche zwei Kommas durch einen Doppelpunkt ersetzt:
Menü „Datei | Optionen | Dokumentenprüfung | AutoKorrekturOptionen“
Autokorrektur einrichten
Wenn du danach auf dem Nummernblock beispielsweise 13,,45 eingibst, wird automatisch 13:45 daraus. Sehr praktisch!
Zeitdifferenzen berechnen
Wie ein Datum ist auch eine Uhrzeit nichts anderes als eine Zahl, die lediglich in einem speziellen Format angezeigt wird. Daher kann man auch über eine ganz normale Subtraktion die Differenz aus zwei Uhrzeiten berechnen:
Einfache Subtraktion von Uhrzeiten
Problematisch wird es nur, wenn die Tagesgrenze überschritten wird. Wenn also die Endzeit nach Mitternacht liegt, wie das im Schichtdienst ja durchaus üblich ist:
Problem beim Tageswechsel
Was ist hier das Problem?
Die Ende-Zeit ist kleiner als die Beginn-Zeit, damit käme bei der Subtraktion ein negativer Wert heraus. Negative Uhrzeiten gibt es aber nicht, zumindest nicht in Excel. Ein relativ einfache Lösung bietet die REST-Funktion:
=REST(C8-B8;1)
Lösung mit der REST-Funktion
Dazu folgende Erklärung:
Eine Uhrzeit ist, wie oben erwähnt eine Zahl. Genauer gesagt, ein Bruchteil von 1 (1 = ein ganzer Tag). Die REST-Funktion liefert grundsätzlich den Rest einer Division. Wird nun der errechnete negative Wert durch 1 geteilt, dann ist der Rest die Differenz zum vollen Tag. Das negative Vorzeichen fällt bei der Division unter den Tisch. Und somit erhalten wir das gewünschte Ergebnis.
Wer in seinen Tabellen gerne einheitliche Formeln verwendet, kann diese Variante mit der REST-Funktion natürlich auch für alle anderen Berechnungen verwenden. Sie funktioniert selbstverständlich auch bei den „normalen“ Zeitdifferenzen.
Zeiten addieren
Natürlich lassen sich Zeiten nicht nur subtrahieren, sondern auch addieren. Dabei kann es aber zu einem weiteren Problem kommen. Der Kollege, dessen Stunden in diesem Beispiel summiert wurden, wird sich sicherlich sehr bedanken:
Die Summe liefert ein merkwürdiges Ergebnis
Auch diesem Problem hängt mit dem Tageswechsel zusammen. Da ein Tag maximal 24 Stunden hat, wird bei allem was drüber geht, einfach wieder von vorne gerechnet. In dem Beispiel hat der Kollege mehr als 24 Stunden gearbeitet, genauer gesagt eben 27:11 Stunden. Angezeigt werden jetzt nur die 3:11 Stunden, die über die 24 hinausgingen.
Die Lösung ist ganz einfach: Ein benutzerdefiniertes Zahlenformat [hh]:mm
Lösung mit einem benutzerdefinierten Zahlenformat
Die eckigen Klammern um die Stunden hh sorgen dafür, dass einfach weitergezählt wird.
Zeiten umrechnen
Vielleicht möchtest du die berechneten Zeiten gar nicht als Zeit darstellen, sondern im Dezimalformat mit entsprechenden Nachkommastellen. Das geht mit folgender einfachen Rechnung
=(C3-B3)*24
Zeiten in Dezimalzahlen umrechnen
Die berechnete Differenz wird also einfach mit 24 multipliziert. Zur Erinnerung: Zeiten sind in Excel Bruchteile von 1 (= von ganzen Tagen). Du musst nur darauf achten, dass du auch ein normales Zahlenformat dafür verwendest. Ansonsten wird Excel vermutlich automatisch wieder das Zeitformat anwenden, was dann sehr merkwürdige Ergebnisse liefert.
Natürlich geht das ganze auch andersrum:
Aus einer Dezimalzahl wird eine Zeitangabe, wenn die Zahl durch 24 geteilt wird:
Dezimalzahlen in Zeiten umrechnen
Hier muss dass Zahlenformat natürlich auf „Uhrzeit“ umgestellt werden.
Zeiten auf- oder abrunden
Manchmal geht es nicht so genau und die Zeiten sollen beispielsweise auf volle 10 (oder 15 oder 30) Minuten aufgerundet oder abgerundet werden.
Dafür gibt es die Funktionen OBERGRENZE.MATHEMATIK (bzw. veraltet OBERGRENZE) und UNTERGRENZE.MATHEMATIK (bzw. UNTERGRENZE)
Aufrunden auf die nächsten 10 Minuten sieht dann beispielsweise so aus:
=OBERGRENZE.MATHEMATIK(D3;"0:10")
Aufrunden auf 10 Minuten
Und das Abrunden eben so:
=UNTERGRENZE.MATHEMATIK(D3;"0:10")
Abrunden auf 10 Minuten
Datenüberprüfung und Uhrzeiten
Wer sicherstellen möchte, dass nur Zeiten in einem bestimmten Zeitfenster eingegeben werden, der richtet am besten eine entsprechende Datenüberprüfung ein:
Datenüberprüfung für Uhrzeiten
Nachdem im Feld „Zulassen“ die Option „Zeit“ ausgewählt wurde, lässt sich über die nächsten Felder genau steuern, welche Zeiten erlaubt sein sollen. Für etwas mehr Anwenderfreundlichkeit hinterlegt man auch eine aussagekräftige Fehlermeldung:
Fehlermeldung für falsche Eingaben
Eine versuchte Eingabe außerhalb des Zeitfensters wird dann mit der entsprechenden Meldung abgewiesen:
Fehler bei Falscheingabe
Soll hingegen die Zeiterfassung nur in 5-Minuten-Schritten gewünscht sein, hilft die vorhin schon gezeigte Funktion OBERGRENZE.MATHEMATIK. Im Feld „Zulassen“ muss dazu „Benutzerdefiniert“ gewählt und anschließend folgende Formel eingeben werden:
=B3=OBERGRENZE.MATHEMATIK(B3;"0:05")
Datenprüfung für 5-Minuten-Eingaben
Wenn man die dann die Fehlermeldung auch noch anpasst, weiß der Anwender, was von ihm erwartet wird:
Angepasste Fehlermeldung
Dynamische Zeitreihen (erst ab Excel 2021)
Mit der SEQUENZ-Funktion lassen sich auch bequem und ohne große manuellen Eingaben dynamische Zeitreihen erstellen. Dabei handelt es sich um eine der dynamischen Array-Funktionen, die in den neueren Excel-Versionen verfügbar sind.
Dann wäre definitiv mein Online-Kurs etwas für dich! Hier findest du alle weiteren Infos dazu:
Online-Kurs „Dynamische Array-Funktionen“
Beispiel 1: Reihe mit volle Stunden
=SEQUENZ(24;;0;1/24)
Das erzeugt zunächst eine Liste mit Dezimalzahlen. Mit dem benutzerdefinierten Zahlenformat hh:mm werden daraus aber Zeiten:
Zeitreihe mit SEQUENZ-Funktion
Wer sich den manuellen Schritt mit der Formatierung sparen möchte, kann das Ganze noch mit der TEXT-Funktion kombinieren und dort die gewünschte Darstellung festlegen:
=TEXT(SEQUENZ(24;;0;1/24);"hh:mm")
SEQUENZ + TEXT für formatierte Ausgabe
Beispiel 2: Reihe mit 15-Minuten-Intervallen
Eine Zahlenreihen für 4 Stunden, beginnend ab 8:00 Uhr mit 15-Minuten-Intervallen sähe dann so aus:
=TEXT(SEQUENZ(16;;"8:00";1/1440*15);"hh:mm")
Zeitreihe mit 15-Minuten -Intervall
Zur Erklärung:
Der Wert 16 kommt von den 4 Stunden mit je 4 * 15 Minuten-Intervallen.
Und warum 1/1400?
Ein Tag hat 1400 Minuten.
Beispiel 3: Stundenmatrix mit 5-Minuten-Intervallen
Dargestellt werden soll eine Matrix mit vollen Stunden (zeilenweise) und 5-Minuten-Intervallen (spaltenweise), und zwar für das Zeitfenster 8:00 – 18:00 Uhr
=TEXT(SEQUENZ(10;12;"8:00";1/1440*5);"hh:mm")
Zeitmatrix: Stunden und 5-Minuten-Intervalle
Mit etwas Fantasie kannst du somit fast beliebige Zeitintervalle definieren.
So, dass war’s mit den Zeitfunktionen in Excel. Habe ich etwas Wichtiges vergessen? Dann schreib es in die Kommentare!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Bei Zeiten umrechnen
=(C3-B3)*24
ist oft noch ein RUNDEN notwendig
=RUNDEN((C3-B3)*24;2)
falls man damit exakt weiter rechnen will.
Hallo Martin,
das stimmt, wenn die Sekunden nicht wichtig sind (was wahrscheinlich meistens der Fall ist), kann die Rundung sehr hilfreich sein. Danke für die Ergänzung!
Schöne Grüße,
Martin
Sehr schön zusammengefasst. Danke schön.
Danke für dein Feedback, Frank. Gern geschehen!
Schöne Grüße,
Martin
Hallo Martin,
probiere doch mal =HEUTE().
Lieber Martin, hochinteressant! Ich selbst erfasse meine Arbeitszeiten als zweite Seite auf meinen Rechnungen an meine Kunden (als Nachweis für sie und mich) in Excel. Ich habe verschiedene Zeiterfassungsprogramme ausprobiert – und kehre immer wieder zum einfachen Excel zurück. Wenn es einmal richtig aufgesetzt ist, ist das null Problem. Datum (sogar mit Tagesangabe), Beginn, Ende, total Arbeitszeit – einfach und unkompliziert und durch eine Verknüpfung erscheint die totale Stunden/Minuten-Anzahl gleich auf der ersten Seite der Rechnung und wirft sogleich den Betrag in CHF aus. Was will man mehr????
Und noch ein Goodie ohne Zeit – auf dem Tabellenblatt erfasse ich den Monatsnamen mit Jahr und dieser wird auch gleich auf der Rechnung als übergeordneter Zeitraum ausgewiesen.
Eine gute Zeit – im wahrsten Sinne des Wortes! – und liebe Grüsse aus der Schweiz, Barbara
Halo Martin,
wieder ein sehr schöner Artikel.
Die Zeitreihe kannst du auch ausfüllen, wen du als Schrittweite einen String im Format Stunden:Minuten[:Sekunden] angibst. Dann muss man sich nicht die 1440 für die Minuten eines Tages merken.
=SEQUENZ(16;;“8:00″;“0:15″)
Hallo Martin,
auch ich unterrichte genau nach diesen Themen die Uhrzeit (incl. Trick mit der AutoKorrektur).
Hier mal nur ein kleiner Flüchtigkeitsfehler:
In Deinem Beispiel 2 schreibst Du
„Zur Erklärung:
Der Wert 16 kommt von den 4 Stunden mit je 4 * 15 Minuten-Intervallen.
Und warum 1/1400?
Ein Tag hat 1400 Minuten.“
Richtig müsste es natürlich heißen: 1/1440 und Ein Tag hat 1440 Minuten.
Ansonsten wie immer eine tolle Arbeit!
Weiter so!!! (Auch wenn vielleicht in Bayern der Tag nur 1400 Minuten hat 😉)
Elle