Excel-Zeitformeln: Berechnen, Formatieren und Manipulieren von Zeit in Excel

Excel macht es einfach, mit Zahlen zu arbeiten, aber der Umgang mit Zeit ist (meiner Meinung nach) eine andere Geschichte. Der verwirrende Teil (auf den ich weiter unten eingehe) ist, dass Excel Zeitwerte als Bruchteile von Tagen speichert und verarbeitet, was zu unerwarteten Ergebnissen führen kann, wenn du damit nicht vertraut bist.

In diesem Tutorial führe ich dich durch alles, vom Erstellen und Formatieren von Zeitwerten bis hin zum Lösen von häufigen Problemen wie negativen Zeitdauern und Zeitüberläufen. Am Ende wirst du wissen, wie du intelligentere und zuverlässigere Tabellenkalkulationen für die Projektplanung, Gehaltsabrechnung oder was auch immer du gerade machst, erstellen kannst.

Das Datumssystem von Excel verstehen

Wir müssen mit dem Datumssystem von Excel beginnen. Excel speichert Datumsangaben als fortlaufende Nummern, beginnend mit dem 1. Januar 1900, der durch den Wert 1 dargestellt wird. Jeder weitere Tag erhöht die Seriennummer um 1. Zum Beispiel:

• 1. Januar 1900 = 1

• 2. Januar 1900 = 2

• 31. Dezember 2023 = 45205

Zeitwerte werden als Bruchteile eines Tages gespeichert. Zum Beispiel:

• 6:00 AM = 0.25 (6 Stunden sind 25% eines 24-Stunden-Tages)

• 12:00 PM = 0.5

• 6:00 PM = 0.75

Das Kombinieren von Datum und Uhrzeit ist so einfach wie das Zusammenzählen der beiden Werte. Die folgende Formel kombiniert zum Beispiel ein Datum und eine Uhrzeit:

=A2 + TIME(10, 30, 0)

Wenn A2 45205 (31. Dezember 2023) enthält, ist das Ergebnis 45205.4375, was 10:30 Uhr am 31. Dezember 2023 entspricht.

Wie man Zeitwerte in Excel erstellt

Sehen wir uns die beiden gängigsten Methoden an, um in Excel gültige Zeitwerte zu erstellen: die Funktion TIME() und die Konvertierung von Text mit TIMEVALUE().

Verwende die Funktion TIME()

Das habe ich bereits erwähnt. Mit der Funktion TIME() kannst du einen Zeitwert aus einzelnen Komponenten zusammensetzen: Stunde, Minute und Sekunde. Hier ist die Syntax:

=TIME(hour, minute, second)

Wenn du zum Beispiel 10:30 Uhr darstellen willst, kannst du schreiben:

=TIME(10, 30, 0)

Dies erzeugt einen korrekten Excel-Zeitwert für 10:30 Uhr, der intern als 0,4375 gespeichert wird (da 10 Stunden und 30 Minuten 43,75 % eines 24-Stunden-Tages sind).

Excel ist flexibel mit den Werten, die du eingibst. Wenn du die normalen Zeitgrenzen überschreitest, rollt Excel die zusätzliche Zeit auf. Zum Beispiel:

=TIME(27, 0, 0)

Das ergibt 3:00 Uhr morgens, weil 27 Stunden das Gleiche sind wie ein voller Tag (24 Stunden) plus 3 Stunden. Ähnlich:

=TIME(10, 75, 0)

Dies wird als 11:15 Uhr interpretiert, da aus 75 Minuten 1 Stunde und 15 Minuten werden.

Diese Randfälle sind wichtig zu verstehen, vor allem wenn du Zeitwerte dynamisch mit Formeln generierst.

Verwende die Funktion TIMEVALUE()

Mit TIMEVALUE() kannst du Text in Zeit umwandeln. Wenn deine Tabelle also eine Zeitangabe in Textform enthält, z. B. "14:30 Uhr" oder "14:45 Uhr", wird sie von Excel möglicherweise nicht automatisch als Zeitwert behandelt. Das ist der Punkt, an dem TIMEVALUE() ins Spiel kommt.

=TIMEVALUE("2:30 PM")

Dies wandelt die Textzeichenfolge in einen Excel-Zeitwert um. Du kannst es jetzt wie jedes andere Mal in Berechnungen verwenden.

Es lohnt sich, den Unterschied zwischen TIME() und TIMEVALUE() zu beachten:

• TIME() baut eine Zeit aus einzelnen Zahlen (Stunde, Minute, Sekunde) auf

• TIMEVALUE() wandelt einen Textstring in eine Zeit um

Beide geben Werte zurück, die Excel als gültige Zeiten erkennt, was du für genaue Berechnungen und Formatierungen brauchst.

Wie man Zeitunterschiede berechnet

Sobald du gültige Zeitwerte in Excel hast, besteht eine der häufigsten Aufgaben darin, zu berechnen, wie viel Zeit zwischen zwei Punkten vergangen ist. Egal, ob du die Länge einer Schicht misst oder die Zeit berechnest, die du für eine Aufgabe brauchst, Excel gibt dir mehrere Möglichkeiten, die Antwort zu erhalten.

Lass uns die nützlichsten Ansätze erkunden, von der einfachen Subtraktion bis hin zu fortgeschrittenen Tricks.

Zwei Mal direkt subtrahieren

Die einfachste Art, eine Zeitdifferenz zu berechnen, ist, die Startzeit von der Endzeit zu subtrahieren. 

Wenn die Zelle A1 beispielsweise 10:00 Uhr und B1 13:30 Uhr enthält, würde die Formel wie folgt aussehen:

=B1 - A1

Das Ergebnis wird 0,145833 sein. Das mag auf den ersten Blick seltsam aussehen, aber denk daran, dass Excel die Zeit als Bruchteil eines Tages darstellt. In diesem Fall sind 0,145833 gleich 3 Stunden und 30 Minuten.

Um das Ergebnis nützlicher zu machen, willst du es normalerweise in ein für Menschen lesbares Format umwandeln.

Zeitdifferenzen in Stunden, Minuten oder Sekunden umrechnen

Du kannst das Ergebnis einer Zeitsubtraktion multiplizieren, um die Differenz in Stunden, Minuten oder Sekunden auszudrücken. Hier erfährst du, wie du einen Zeitunterschied in Stunden umrechnen kannst:

=(B2 - A2) * 24

Hier erfährst du, wie du eine Zeitdifferenz in Minuten umrechnen kannst:

=(B2 - A2) * 1440

Hier erfährst du, wie du eine Zeitdifferenz in Sekunden umrechnen kannst:

=(B2 - A2) * 86400

Diese Formeln funktionieren, weil ein Tag 24 Stunden, 1.440 Minuten und 86.400 Sekunden hat. Wenn der Zeitunterschied dreieinhalb Stunden beträgt, erhältst du 3,5 aus der Stundenformel, 210 aus der Minutenformel und 12.600 aus der Sekundenformel.

Bestimmte Zeiteinheiten extrahieren

Wenn du nur einen Teil der Zeit abrufen willst, z.B. nur die Minuten oder Stunden, kannst du HOUR() verwenden. HOUR() gibt den Stundenanteil einer Zeit zurück.

=HOUR(B2 - A2)

MINUTE() gibt den Minutenanteil zurück.

=MINUTE(B2 - A2)

SECOND() gibt die Sekunden zurück.

=SECOND(B2 - A2)

Diese Funktionen sind nützlich, aber sie werden nach 23 Stunden, 59 Minuten oder 59 Sekunden zurückgesetzt. Wenn die Dauer also länger als ein ganzer Tag ist, wird nicht die gesamte verstrichene Zeit angegeben. In diesen Fällen ist es besser, die Differenz zu multiplizieren oder benutzerdefinierte Formate zu verwenden, auf die wir später eingehen werden.

Zeitwerte über 24 Stunden hinzufügen

Wenn du Stunden hinzufügen musst, die über 24 Stunden hinausgehen, funktioniert die direkte Verwendung von TIME() nicht wie erwartet. Stattdessen kannst du mit Bruchteilen eines Tages rechnen.

So addierst du die Stunden über 24.

=A2 + (28/24)  ' Adds 28 hours

Hier siehst du, wie du die Minuten über 60 addierst.

=A2 + (90/1440)  ' Adds 90 minutes

Hier siehst du, wie du die Sekunden über 60 addierst.

=A2 + (3600/86400)  ' Adds 3600 seconds

Excel speichert Zeit als Dezimalzahl, wobei 1 Tag gleich 1 ist. Wenn du also durch 24, 1440 oder 86400 teilst, wird der Wert so angepasst, dass er einen Teil eines Tages darstellt.

Umgang mit negativen Zeitwerten

Wenn du eine größere Zeit von einer kleineren Zeit subtrahierst (z. B. 10:00 AM - 2:00 PM), gibt Excel standardmäßig einen Fehler zurück oder zeigt die Zeichenfolge ### an.

Um negative Zeitwerte sicher zu handhaben, verwende die Funktion MOD(). Diese Funktion verhindert effektiv negative Ergebnisse, indem sie sie auf eine gültige Zeit umbricht.

Zum Beispiel, um 3 Stunden sicher abzuziehen:

=MOD(A2 - TIME(3, 0, 0), 1)

Diese Formel stellt sicher, dass das Ergebnis immer ein positiver Zeitwert ist, um unerwünschte Fehler oder Anzeigeprobleme zu vermeiden.

Wie man Zeitergebnisse richtig formatiert

Zeitberechnungen in Excel führen oft zu verwirrenden Dezimalwerten oder unerwarteten Ergebnissen, vor allem, wenn es um Zeiträume über 24 Stunden oder negative Zeitwerte geht. Die richtige Formatierung kann Rohdaten in lesbare, benutzerfreundliche Formate umwandeln, die leichter zu interpretieren und zu präsentieren sind.

Grundlegende Zeitformatierung verwenden

Wenn du eine Zeitdifferenz wie "3:30" anstelle einer Dezimalzahl anzeigen möchtest, verwende die Funktion TEXT().

=TEXT(B2 - A2, "h:mm")

Hier wird das Ergebnis in Stunden und Minuten angezeigt. Bedenke nur, dass die Ausgabe ein Text ist, der gut aussieht, aber du kannst ihn nicht für weitere mathematische Berechnungen verwenden, wenn du ihn nicht zurückverwandelst.

Anwenden von benutzerdefinierten Zeitformaten

Die in Excel integrierten Zeitformate können Standardzeitwerte effektiv verarbeiten, aber benutzerdefinierte Formate bieten mehr Flexibilität für die Anzeige von Zeiträumen, längeren Stunden oder bestimmten Zeitkomponenten.

Um ein benutzerdefiniertes Zeitformat anzuwenden, befolge diese Schritte:

1. Wähle die Zelle(n) aus, die die Zeitwerte enthalten.
2. Klicken Sie mit der rechten Maustaste und wählen Sie Zellen formatieren.
3. In der Nummer wählst du Benutzerdefiniert.
4. Gib den gewünschten Formatcode ein.

Hier sind einige gängige benutzerdefinierte Zeitformate:

• [h]:mm: Zeigt die gesamten Stunden und Minuten an, auch über 24 Stunden hinaus. Beispiel: 36:45 (36 Stunden und 45 Minuten)

• d "days" h:mm:ss: Zeigt Tage, Stunden, Minuten und Sekunden an. Beispiel: 1 days 12:30:00

• h:mm AM/PM: Zeigt die Uhrzeit mit AM/PM-Notation an. Beispiel: 10:30 PM

• mm:ss: Zeigt nur Minuten und Sekunden an. Hier ist ein Beispiel: 45:15

Mit benutzerdefinierten Formaten kannst du Zeitdaten auf eine Weise darstellen, die für bestimmte Anwendungsfälle intuitiver ist, z. B. Stundenzettel oder Arbeitsprotokolle.

Anzeige von freundlichen Zeitunterschieden

Wenn du eine Zeitdifferenz als lesbare Zeichenkette anzeigen möchtest, z. B. "2 Tage, 4 Stunden und 15 Minuten", kannst du mehrere Funktionen in einer einzigen Formel kombinieren.

Hier ist ein praktisches Beispiel:

=INT(B2 - A2) & " days, " & HOUR(B2 - A2) & " hours, " & MINUTE(B2 - A2) & " minutes"

In dieser Formel extrahiert die Funktion INT() die Anzahl der vollen Tage, während HOUR() und MINUTE() die restlichen Stunden und Minuten ermitteln.

Um zu verhindern, dass Nullwerte in der Ausgabe erscheinen, kannst du diese Berechnungen in IF() Anweisungen verschachteln. Zum Beispiel:

=IF(INT(B2 - A2) > 0, INT(B2 - A2) & " days, ", "") &
 IF(HOUR(B2 - A2) > 0, HOUR(B2 - A2) & " hours, ", "") &
 MINUTE(B2 - A2) & " minutes"

Diese Formel zeigt nur relevante Einheiten an, was die Ausgabe sauberer und lesbarer macht.

Wichtige Excel-Zeitfunktionen

Wie du bisher gesehen hast, verfügt Excel über eine Reihe von leistungsstarken Zeitfunktionen. Um die Buchhaltung zu vervollständigen, hier eine kurze Übersicht über die wichtigsten Excel-Zeitfunktionen.

Verwendung der Funktion TIME() zur Erstellung von Zeitwerten

TIME() konstruiert einen gültigen Zeitwert aus drei Komponenten: Stunden, Minuten und Sekunden. Diese Funktion ist ideal, wenn du einen Zeitwert programmatisch erstellen musst.

Hier ist ein gutes Beispiel:

=TIME(14, 30, 0)  

Das obige Beispiel ergibt 2:30 PM, was ein Ergebnis dieser Syntax ist.

=TIME(hour, minute, second)

Text in Zeit umwandeln mit TIMEVALUE()

Wenn du eine Uhrzeit in Form einer Textzeichenfolge hast (z. B. "14:45 Uhr"), kannst du sie mit TIMEVALUE() in einen richtigen Excel-Zeitwert umwandeln, der in Berechnungen verwendet werden kann.

=TIMEVALUE("2:45 PM")

Das Ergebnis ist zum Beispiel 0.614583, was einem Bruchteil eines Tages entspricht:

=TIMEVALUE("14:45")

Abrufen des aktuellen Datums und der Uhrzeit mit NOW()

NOW() gibt das aktuelle Datum und die Uhrzeit als einen einzigen Wert zurück. Sie wird automatisch aktualisiert, wenn das Arbeitsblatt neu berechnet wird.

=NOW()

Hier ist ein gutes Beispiel. Wenn die aktuelle Uhrzeit 3:15 PM ist, könnte NOW() 44710.63542 zurückgeben, wobei 44710 das Datum und .63542 die Uhrzeit ist.

Abrufen des aktuellen Datums mit TODAY()

Wenn du nur das aktuelle Datum ohne Uhrzeit benötigst, verwende TODAY(). Diese Funktion ist besonders nützlich für die Berechnung von Fristen oder Alterungsdaten.

=TODAY()

Extrahieren von Zeitkomponenten mit HOUR(), MINUTE() und SECOND()

Mit diesen Funktionen kannst du bestimmte Teile eines Zeitwerts isolieren, was sie für Berichte und bedingte Logik nützlich macht.

HOUR() liefert den Stundenanteil eines Zeitwerts (0-23).

=HOUR(A2)

MINUTE() gibt den Minutenanteil zurück (0-59).

=MINUTE(A2)

SECOND() gibt den Sekundenanteil zurück (0-59).

=SECOND(A2)

Zeitwerte mit TEXT() formatieren

TEXT() wandelt einen Zeitwert in ein bestimmtes Format um und ist damit ideal für die Anzeige von Zeiträumen oder die Erstellung von benutzerdefinierten Zeitstrings. Syntax:

=TEXT(A2, "h:mm:ss AM/PM")

Behandlung negativer Zeitwerte mit TEXT()

Beim Subtrahieren von Zeitwerten stellt MOD() sicher, dass das Ergebnis immer positiv ist, um Fehler durch negative Zeitwerte zu vermeiden.

=MOD(A2 - B2, 1)

Datumswerte mit DATE() erstellen

DATE() ermöglicht es dir, ein Datum aus einzelnen Komponenten zu erstellen: Jahr, Monat und Tag. Dies ist nützlich, um Daten und Zeiten zu einem einzigen Wert zu kombinieren.

=DATE(year, month, day)

Hier ist ein gutes Beispiel:

=DATE(2023, 12, 31) + TIME(14, 30, 0)

Der am 31.12.2023 um 14:30 Uhr zurückkehrt.

Arbeitstage mit NETWORKDAYS() erstellen

NETWORKDAYS() errechnet die Anzahl der Arbeitstage zwischen zwei Daten, ohne Wochenenden und optional angegebene Feiertage. Diese Funktion ist ideal für die Berechnung von Projektlaufzeiten oder Abrechnungszeiträumen.

=NETWORKDAYS(start_date, end_date, [holidays])

Zum Beispiel:

=NETWORKDAYS(A2, B2, C2:C5)

Wenn A2 12/1/2023 und B2 12/31/2023 enthält, ist das Ergebnis die Anzahl der Arbeitstage im Dezember, ohne die Feiertage, die im Bereich C2:C5 aufgeführt sind.

Arbeitstage und Wochenenden mit WORKDAY.INTL() erstellen

WORKDAY.INTL() ist eine erweiterte Version von WORKDAY(), mit der du festlegen kannst, welche Tage als Wochenenden gelten.

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Zum Beispiel:

=WORKDAY.INTL(A2, 10, "0110000")

Hier gibt 0110000 an, dass Dienstag (0) und Mittwoch (1) Wochenenden sind. Die Formel berechnet das Datum 10 Arbeitstage nach A2, außer Dienstag und Mittwoch.

Fazit

Der Umgang mit der Zeit in Excel kann sich kompliziert anfühlen, aber mit den richtigen Funktionen wird er viel übersichtlicher.

Wenn du deine Excel-Kenntnisse weiter ausbauen möchtest, dann schau dir unseren Kurs Einführung in Excel für grundlegende Kenntnisse oder den Kurs Datenanalyse in Excel an, um die Arbeit mit realen Daten zu üben.

Für fortgeschrittene Anwendungen bietet der Lernpfad Excel-Grundlagen geführte Projekte und Übungen, die dir helfen, Zeitberechnungen und mehr zu beherrschen.