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.
- Excel: Mit Datum und Uhrzeit rechnen - Tipps und Lösungen
- Excel: Die wichtigsten Datums- und Zeitfunktionen
- Excel Zeitfunktionen: Probleme und Lösungen
- Excel: Wichtige Kürzel für individuelle Zeit-Formate
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.
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.
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.
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.
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)))))).
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.
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.
