Zeitfunktionen, Formeln und mehr

Excel: Mit Datum und Uhrzeit rechnen - Tipps und Lösungen

Ob Stunden, Minuten oder Tage: Excel und Co. sind auf das Rechnen mit Zahlen und nicht mit Datums- und Zeitwerten spezialisiert. Wir geben Tipps, wie es dennoch klappt.

Arbeit mit Datenbanken

© SFIO CRACHO/ shutterstock

buissnessman

Ob Microsoft Excel, Google Docs oder Libre Office: Datums- und Zeitfunktionen werden in der Praxis für die unterschiedlichsten Aufgaben benötigt, etwa um Termine zu planen, Urlaubslisten zu verwalten, Arbeitszeiten zu erfassen, Betriebszugehörigkeiten zu ermitteln, Projektzeiträume zu berechnen, Fälligkeiten zu benennen u.v.m. 

Vorweg: Wenn auch zum Teil umständlich, in der Regel lässt sich letztendlich alles umsetzen. Mal sind es einfache Formeln, mal integrierte Funktionen, welche, kombiniert mit speziellen Formaten oder Einstellungen, zum gewünschten Ergebnis führen. Zur Problemlösung braucht es aber häufig einen Trick.

Hier patzen Tabellenkalkulationen

Haben Sie schon einmal versucht, verschiedene Stundenangaben im Zeitformat in Excel zu addieren, etwa 13:00 plus 12:00? Das Ergebnis lautet 01:00. Für jeden, der beispielsweise einen Stundenzettel in einer Tabelle führt, ist das völlig unakzeptabel. Google Docs und Calc aus dem Libre-Office liefern identische Resultate. Auch mit dem kleinen Einmaleins scheint es nicht zu klappen. Multipliziert man 8:00 Stunden mit der Zahl fünf, lautet das Ergebnis 16:00 (Calc: 1,66667). 

Auffällig: Es fehlen bei beiden Berechnungen jeweils 24:00 Stunden. Das liegt daran, dass Tabellenkalkulationen Stunden als Anteil von Tagen interpretieren. Eine Stunde entspricht einem Vierundzwanzigstel (1/24). Gehen die Werte darüber hinaus, werden diese von der Tabellenkalkulation standardmäßig einfach ignoriert.

Es gibt jedoch eine Lösung in Form eines benutzerdefinierten Zahlenformats. Man markiert die Ergebniszelle/n. Über die Tastenkombination [Strg] + [1] gelangt man in Excel und Calc in das Fenster Zellen formatieren. Im Register Zahlen befindet sich unter Kategorien der Eintrag Benutzerdefiniert. Dort wird das Format auf [h]:mm oder [hh]:mm geändert.

Achtung: Excel und Calc sind pingelig und akzeptieren keine Abweichung. Also: Unbedingt die eckigen Klammern setzen. Nach erfolgter Bestätigung erscheint das korrekte Ergebnis. Anwender von Google Docs finden den fertigen Formatcode unter Format/Zahl

Datum und Zeit (1)

© PC Magazin

Bei Falschanzeige: mit benutzerdefiniertem Format zum korrekten Ergebnis.

Ein weiterer Schwachpunkt der Tabellenkalkulationen zeigt sich, wenn sich negative Zeitwerte ergeben, etwa die Soll-Arbeitszeit nicht erreicht wird. Beispiel: Die tägliche Arbeitszeit beläuft sich auf 8:00 Stunden. Ein Arbeitnehmer macht nach 7:15 Stunden Feierabend. Saldiert man die Werte, müssten eigentlich 45 Minusminuten (-0:45) angezeigt werden. Stattdessen erhält man als Excel-Anwender eine Reihe Rauten (######). 

Hier führt der Lösungsweg über Datei/Optionen/Erweitert. Im Bereich Beim Berechnen dieser Arbeitsmappe muss das Kontrollkästchen 1904-Datumswerte verwenden abgehakt werden. Da das Fenster Erweitert sehr umfangreich ist, müssen Sie dazu weit nach unten scrollen.

Nach Ihrer Bestätigung erscheint das korrekte Resultat mit negativem Vorzeichen. Google Docs und Calc sind sich einig und liefern beide falsche 23:15 Stunden. In Calc und Google Docs hilft das 24-Stunden-Format [hh]:mm bzw. [h]:mm, den Fehler zu beheben. 

Datum und Zeit (2)

© PC Magazin

1904-Datumswerte verwenden: Diese Excel-Option hat Einfluss auf das Rechnen mit negativen Zeiten.

Kombination von Datum und Text

Eine Kombination von Datums- und Zeitwerten mit Texten gelingt nicht, ohne dass der Anwender nachbessert. Beispiel: In einer Rechnung soll automatisch das Fälligkeitsdatum ausgewiesen werden. Letztes kann mit Hilfe der Funktion HEUTE schnell errechnet werden (etwa =HEUTE()+14).

Soll nun der Text, zum Beispiel „Bitte überweisen Sie den Rechnungsbetrag bis zum 15.06.2019.“ erscheinen, sollte das eigentlich über eine Formel möglich sein (etwa =“Bitte überweisen Sie bis zum „&HEUTE()+14&“.“). Anstelle des Datums liefert die Formel jedoch eine serielle fünfstellige Zahl („... bis zum 42082.“) Datumsformate helfen hier nicht weiter.

Lesetipp: Tipps zu Datenbanken in Excel

Die Lösung: Die Funktion TEXT sorgt dafür, dass das Datum in der gewünschten Form interpretiert und angezeigt wird. TEXT verlangt zwei Argumente, Wert und Textformat. Wert entspricht der Angabe HEUTE()+14, Textformat dem Eintrag „TT.MM.JJJJ“ (Anführungszeichen!).

Die vollständige Formel lautet in Excel und Calc =“Bitte überweisen Sie bis zum „&(TEXT(HEUTE()+14;“TT.MM.JJJJ“ ). Ganz wichtig: Anführungszeichen nicht vergessen! Anwender von Google Docs ersetzen das Kürzel T durch d und J durch y.

Datum und Zeit (3)

© PC Magazin

Umständlich gelöst: Kombination von Datumswert und Text.

Mit diesen Datums- und Zeitfunktionen klappt die Planung

Für die Planung von Zeiten gibt es Funktionen in Form von vordefinierten Formeln. Ein Beispiel: Für ein Projekt, welches am 03.06.2019 starten soll, werden 60 Arbeitstage benötigt. Zu berechnen ist der Fertigstellungstermin unter Berücksichtigung von Wochenenden und Feiertagen. Hier hilft ARBEITSTAG mit den Argumenten (Angaben) Ausgangsdatum, Tage (Dauer) und Freie_Tage. Freie_Tage sind Tage, an denen nicht gearbeitet wird und die nicht auf ein Wochenende fallen. Sie finden die entsprechende Funktion in Excel im Register Formeln in der Funktionsbibliothek unter Datum und Uhrzeit, in Calc und Google Docs unter Einfügen/Funktion

Ähnlich arbeitet ARBEITSTAG.INTL(Ausgangsdatum;Tage;[Wochenende];[Freie_Tage]). Die Funktion liefert die fortlaufende Zahl des Datums vor oder nach einer bestimmten Anzahl von Arbeitstagen. Im Gegensatz zu ARBEITSTAG haben Sie hier die Möglichkeit, die Wochenendparameter individuell zu bestimmen.

Das heißt, Sie können über das optionale Argument Wochenende angeben, welche Tage als Wochenendtage behandelt und nicht als Arbeitstage betrachtet werden. Wochenende entspricht einer Nummer bzw. einer Ziffernfolge. Geben Sie in Excel die Ziffer 11 an, ist nur der Sonntag arbeitsfrei (Ziffer 17: nur Samstag). Ist für das vorangegangene Beispiel nur der Sonntag arbeitsfrei, fällt das Fertigstellungsdatum auf den 14.08.2019. 

Weitere Zifferbelegungen entnehmen Sie bitte der Hilfe des von Ihnen eingesetzten Tabellenkalkulationsprogramms. Die Funktionen NETTOARBEITSTAGE und NETTOARBEITSTAGE.INTL arbeiten ähnlich wie ARBEITSTAG und ARBEITSTAG.INTL. Allerdings wird hier ein Zeitraum, nämlich die Anzahl der Arbeitstage, aus einem Start- und Endtermin berechnet. Beispiel: Wie viele Arbeitstage fallen in das erste Quartal des Jahres 2019? Die Formel=NETTOARBEITSTAGE(„01.01.2019“;“31.03.2019“) liefert das Ergebnis 64.

Datum und Zeit (4)

© PC Magazin

Einige Datums- und Zeitfunktionen erfordern die Angabe von freien Tagen.

Wiederkehrende Termine

Häufig finden Besprechungen, Konferenzen, Datensicherungen und andere Termine immer an einem bestimmten Tag statt, etwa am letzten Freitag im Monat. Mit Excel lässt sich formelgesteuert eine Liste erzeugen, die immer einen bestimmten ersten, zweiten bis letzten Wochentag im Monat nennt. Wenn man diese Liste noch mit Feiertagen abgleicht, steht einer perfekten Terminplanung nichts mehr im Wege. 

Bei der Auflistung des letzten Tages im Monat hilft MONATSENDE mit den Argumenten Ausgangsdatum und Monate. Als Ausgangsdatum für eine Jahresliste nehmen Sie den 01.01.2019. Wenn Sie für Monate eine 0 eingeben, wird der Monatsletzte des aktuellen Monats, hier Januar, angezeigt. Die vollständige Formel lautet =MONATSENDE(„01.01.“&2019;0). Setzen Sie Monate beim Ausgangsdatum 01.01. auf 1, also =MONATSENDE(„01.01.“&2019;1), erhalten Sie den Monatsletzten im Februar, bei 2 den letzten Tag im März usw. 

In der Nachbarspalte wird ermittelt, auf welchen Wochentag der letzte Tag des Monats fällt. Angenommen, der erste Eintrag befindet sich in A2, dann geben Sie in B2 =WOCHENTAG(A2) ein. WOCHENTAG() liefert für den Sonntag die Kennziffer 1, für den Montag eine 2 usw. Für Samstag wird entsprechend die Zahl 7 angezeigt. Soll der letzte Freitag genannt werden, ziehen Sie in der Nachbarspalte im Falle des Samstags einen Tag ab. Fällt der Monatsletzte auf einen Sonntag, subtrahieren Sie zwei Tage usw. Die Formel dafür ist recht komplex: =WENN(B2=7;A2-1;WENN(B2=1;A2-2;WENN(B2=2;A2-3;WENN(B2=3;A2-4;WENN(B2=4;A2-5;WENN(B2=5;A2-6;A2-7)))))).

Datum und Zeit (5)

© PC Magazin

Komplexe Formeln helfen bei der Terminplanung an festgelegten Wochentagen.

Anwender von Excel 2019 haben es deutlich einfacher. Anstatt der komplizierten Verschachtelung der WENN-Funktionen nimmt man die Funktion WENNS. Abschließend wird das Datumsformat über den Dialog Zellen formatieren, Register Zahlen festgelegt. Damit der Wochentag genannt wird, verwenden Sie in Excel die Kategorie Datum und den Typ Mittwoch, 14. März 2001 (Google Docs: Format/Zahl/Weitere Formate/Weitere Datums- und Zeitformate, Calc hat Darstellungsprobleme)

Nun gibt es noch einen Kritikpunkt: Möglicherweise fällt der letzte Freitag des Monats auf einen Feiertag. Das prüft die vorgestellte Formel nämlich nicht. Sie können den Abgleich den Funktionen ODER und IDENTISCH in Zusammenarbeit mit einer Auflistung von Feiertagen überlassen. 

Feiertage berechnen

Wer häufig Feiertage beim Einsatz von Datumsfunktionen einsetzt, weiß, wie lästig es ist, flexible Feiertage Jahr für Jahr anzupassen. Für Excel gibt es verschiedene Formeln zur Berechnung des Ostersonntag.

Datum und Zeit (6)

© PC Magazin

Flexible Feiertage ergeben sich auf Basis der Jahreszahl.

Die folgende Rechenvorschrift beispielsweise können Sie eins zu eins übernehmen, wenn Sie das Jahr in A2 schreiben: =DATUM(A2;3;28)+REST(24-REST(A2;19)*10,63;29)-REST(KÜRZEN(A2*5/4)+REST(24-REST(A2;19)*10,63;29)+1;7). Diese Formel können Sie problemlos für die kommenden Jahre einsetzen. Alle weiteren, flexiblen Feiertage ergeben sich ausgehend vom Ostersonntag. Für den Ostermontag addieren Sie die Zahl 1, für Christi Himmelfahrt +39, Pfingstsonntag +50 und Fronleichnam +60.

Mehr zum Thema

excel nach farben sortieren
Anleitung

Unsere kurze Anleitung zu Microsoft Office erklärt Schritt für Schritt, wie Sie eine Auflistung in Excel nach Farben sortieren.
excel dropdown liste erstellen
Office 2010/2013/2016/2019

Möchten Sie in Excel Auswahlmöglichkeiten zur Verfügung stellen? Dann befolgen Sie unsere Anleitung für das Einrichten einer Excel Dropdown-Liste.
10 Tipps zu GOOGLE MAPS
App und Routenplaner optimal nutzen

Google Maps ist längst mehr als nur ein Kartendienst. Wie Sie alle praktischen Funktionen optimal nutzen, zeigen wir hier.
excel kalender erstellen anleitung
Anleitung mit Beispielen

Möchten Sie Ihren eigenen, individuellen Excel Jahreskalender für 2020 erstellen? Unsere Anleitung zeigt Ihnen unterschiedliche Kalendervorlagen.
Windows 10
Microsoft-Betriebssystem besser nutzen

Wir präsentieren hilfreiche Tipps und Tricks, mit denen Sie mehr aus Ihrem Windows-10-System herausholen können.