Google Sheets-Kochbuch: Rechnen mit Kalenderdaten

Um mit Kalenderdaten zu rechnen, gibt es eine ganze Reihe von Funktionen. Es ist dabei sinnvoll zu wissen, wie das Kalenderdatum intern gespeichert und verarbeitet wird.

Kalenderdaten in Google Sheets rechnen mit Tagen seit dem 30.12.1899. Dabei ist der 30.12.1899 Tag 0, der 01.01.1900 Tag 2 usw. Nachkommastellen werden als Uhrzeit interpretiert.

In MS-Excel ist das zwar im Prinzip gleich, allerdings zählt Excel den 01.01.1990 als Tag 1. Die Abweichung besteht aber nur bis zum 01.03.1900. Excel benutzt aus historischen Gründen (falsch) das Jahr 1900 als Schaltjahr mit 29 Tagen, das Jahr 1900 war aber kein Schaltjahr. Damit zählen beide Programme den 01.03.1900 als Tag 61.

Datum und Datumswert

Mit der Funktion DATEVALUE() können Sie den jeweiligen Datumswert berechnen.

• =DATEVALUE("2024-07-30")
  • ergibt die Zahl 45503, d.h. es ist der 45503. Tag seit dem 30.12.1899.
  • Das Datum muss dabei in einem kompatiblen Format angegeben werden, das ist von den Länder- und Spracheinstellungen abhängig.

Umgekehrt berechnet die Funktion TO_DATE() aus einer Zahl ein Datum.

• =TO_DATE(45503)
  • gibt das Datum 30.07.2024.
  • Das ausgebene Format ist von den Länder- und Spracheinstellungen abhängig.

Formatierung von Daten

Um ein bestimmtes Ausgabeformat zu erzwingen, was z.B. bei der QUERY-Funktion notwendig ist oder wenn wir einfach ein bestimmtes Format anzeigen wollen, verwendet man die TEXT-Funktion.

• =TEXT(DATEVALUE("30.07.2024");"yyyy-mm-dd")
  • erzeugt die Ausgabe 2024-07-30
  • Das ist das Format, das in der QUERY-Funktion benötigt wird.

Formatierungstoken Datumsformate am Beispiel Dienstag, 06.08.2024

Code	Beschreibung	Ergebnis
M	Monatsnummer ohne führende Null.	8
MM	Monatsnummer mit führender Null.	08
MMM	Abkürzung des Monats mit vier Buchstaben. Hat der Monat nur vier Buchstaben, wird er nicht abgeürzt.	Aug.
MMMM	Vollständiger Monatsname	August
MMMMM	Erster Buchstabe des Monats.	A
D	Tag des Monats ohne führende Null.	6
DD	Tag des Monats mit führender Null.	06
DDD	Abkürzung des Tages mit drei Buchstaben.	Di.
DDDD	Vollständiger Tag	Dienstag
=LEFT(TEXT(DATEVALUE("06.08.2024");"DDDD"))	Um andere Formate herzustellen, verwendet man die verschiedenen Textfunktionen.	D
YY	zweistelliges Jahr	24
YYYY	vierstelliges Jahr	2024

Zeichen die nicht als Token verwendet werden sind Literale, werden also so ausgegeben, wie sie im Formatstring vorkommen.

Alle Formatierungstoken findet man im Handbuch: Datum & Zeitformatmuster

Rechnen mit Tagen

Da das Datum als fortlaufende Zahl gespeichert wird, ist das Rechnen mit Kalenderdaten recht intuitiv möglich.

Wann ist heute, gestern, morgen?

• =TODAY()
  • Das tagesaktuelle Datum. Es wird jedesmal aktualisiert, wenn die Tabelle neu berechnet wird.
  • Brauchen Sie auch die Uhrzeit, verwenden Sie die Funktion =NOW()
• =TODAY()-1
  • Gestern.
• =TODAY()+1
  • Morgen.

Tage bis/von

Sie können die Funktion =DAYS(Enddatum;Startdatum) verwenden, oder die Daten einfach voneinander abziehen.

An welchem Wochentag war ...

... mein Geburts-/Jahres-/sonstiger Tag

• =TEXT(WEEKDAY(Datum);"DDDD")
  • Die Funktion WEEKDAY() gibt die Tagesnummer als Zahl aus. WEEKDAY zählt ohne Parameter den Sonntag als Tag 1.
  • Die TEXT() Funktion zählt genauso, interpretiert die 1 als Sonntag.

An welchem Datum ist der n-te Tag des Monat?

An welchem Datum liegt der dritte Freitag im Mai 2024?

• =LET(_date;"01.05.2024";_day;6;_n;3;
IF(WEEKDAY(_date)>_day;_n*7+_day-WEEKDAY(_date)+1;(_n-1)*7+_day-WEEKDAY(_date)+1)
)

Das lässt sich genausogut als LAMBDA-Funktion schreiben, wobei die Parameter am Ende übergeben werden.

• =LAMBDA(_date;_day;_n;
IF(WEEKDAY(_date)>_day;_n*7+_day-WEEKDAY(_date)+1;(_n-1)*7+_day-WEEKDAY(_date)+1)
)("01.05.2024";6;3)
  • Dabei ist _date = der erste Tag des Monats als Kalenderdatum, _day eine Zahl zwischen 1 (für Sonntag) und 7 (für Samstag) und _n die n-te Anzahl.
  • Die eigentliche Berechnung beginnt hinter dem IF. Wenn der gesuchte Wochentag vor dem ersten Tag des Monats liegt addieren wir n*7 Tage zum ersten Auftreten des Wochentages im Monat hinzu.
  • Um die obige Frage zu beantworten: _date = 01.05.2024, _day = 6, _n = 3
  • Das Ergebnis ist der 17.
  • Die Funktion ist nicht gegen Eingabefehler geschützt.
  • Liegt das Ergebnis im Folgemonat erfolgt keine Fehlermeldung, das werden wir gleich beheben.

Wir wollen das ganze Datum und nicht nur den Kalendertag. Außerdem soll eine Fehlermeldung ausgegeben werden, wenn der Tag im Folgemonat liegt:

• =LAMBDA(_date;_day;_n;
LET(_result;DATE(YEAR(_date);MONTH(_date);IF(WEEKDAY(_date)>_day;_n*7+_day-WEEKDAY(_date)+1;(_n-1)*7+_day-WEEKDAY(_date)+1));
IF(MONTH(_result)=MONTH(_date);_result;"Es gibt keinen " &_n & ". Tag in diesem Monat.")
)
)("01.05.2024";6;3)
  • Auch hier ist die Funktion nicht gegen Eingabefehler geschützt.

Die LAMBDA-Funktion könnten wir jetzt zu einer benannten Funktion machen.

Arbeitstage

WORKDAY.INTL()

• DATE()
• TO_DATE()
• DATEVALUE
• WEEKDAY()

Kalenderdaten rechnen mit Tagen seit dem 30.12.1899. ... tbd ...

Die Beispiele auf dieser Seite wurden getestet: 08/2024