(C) 2009 - 2021 by Mourad Louha · Alle Rechte vorbehalten

ISODATETIME

Derzeit entwickle ich die LAMBDA-Funktion JSON.FROMRANGE, die Daten aus einem Zellbereich in ein JSON-Datenformat konvertieren soll. Das JSON-Datenformat kennt per Definition verschiedene Datentypen, wie beispielsweise Texte, Zahlen oder Wahrheitswerte. Jedoch ist im JSON-Datenformat kein expliziter Datentyp zur Darstellung von Datums-und Zeitangaben vorgesehen. In der Regel hat sich jedoch die ISO-Norm ISO 8601 zur Darstellung von Datum- und Zeitangaben in JSON-Dateien durchgesetzt. Hierbei werden Datums- und Zeitangaben in Textform, also als String, angegeben.

Um nun Datum- und Zeitabgaben innerhalb meiner Funktion JSON.FROMRANGE in das JSON-Format umzuwandeln, erschien es mir sinnvoll, dafür eine separate Funktion zu implementieren. Zumal die Funktion JSON.FROMRANGE sowieso schon recht komplex sein wird.

Dieser Artikel stellt die Funktion ISODATETIME vor, die diese Aufgabe übernimmt. Die Funktion implementiert jedoch nicht alle Definitionen der ISO-Norm. Beispielweise ist die Darstellung von Perioden oder Wochenangaben nicht enthalten. ISODATETIME basiert auf der Ende 2020 in Excel eingeführten LAMBDA-Funktion.

ISODATETIME

ISO 8601

Die ISO-Norm ISO 8601 legt einige grundlegenden Regeln zur Darstellung von Datums- und Zeitangaben fest. So sind Datumsangaben in der Form Jahr-Monat-Tag anzugeben, wobei ein Bindestrich als Trennzeichen zwischen dem Jahr, Monat und Tag optional ist. Empfohlen wird jedoch den Bindestrich generell zu verwenden, da bei nur einer Angabe vom Jahr und vom Monat der Bindestrich wiederum Pflicht wird. Alle Werte sind mit Führungsnullen anzugeben. Ein valides Datum wäre dann beispielsweise 2021-03-13.

Ähnlich verhält es sich bei Zeitangaben. So ist beispielsweise 08:30:45 eine gemäß ISO 8601 valide Angabe. Als Basis dient das 24-Stunden-Format. Werden Datum und Zeit kombiniert, ist der Zeitangabe ein T voranzustellen, Beispiel: 2021-03-13T08:30:45.

Bezieht sich die Zeitangabe auf UTC, also auf die koordinierte Weltzeit, wird empfohlen, der Angabe den Buchstaben Z anzuhängen. Soll die Zeit als lokale Zeit relativ zu UTC angegeben werden, wird die Differenz zur UTC-Zeit an die Zeitangabe angehängt. Beispiele: 2021-03-13T08:30:45+02:30 oder 2021-03-13T08:30:45-05:00.

Die ISO-Norm legt noch weitere Darstellungsformen fest, wie zu Angaben von Datums- und Zeitintervallen, Kalenderwochen oder Wochentagen.

Anforderungen

Die LAMBDA-Funktion soll in der Lage sein, aus einer Datums- und/oder Zeitangabe einen Text gemäß der ISO-Norm erzeugen zu können. Dazu muss die LAMBDA-Funktion wissen, ob der zu konvertierende Wert ein Datum, eine Zeit oder eine Kombination aus einem Datum und einer Zeit ist. Zudem ist es sinnvoll, über eine Parameterangabe zu steuern, ob sich die Zeit auf UTC bezieht oder nicht. Und, ein Parameter soll es möglich machen, die Differenz zu UTC als Dezimalzahl anzugeben. ISODATETIME definiert sich somit wie folgt:

=ISODATETIME(Value;Type;UTC;Offset)

Das erste Argument stellt den Datums- und/oder Zeitwert dar. Das zweite Argument legt den Datentyp fest, da es in Excel leider nicht zu 100 Prozent möglich ist, per Formel ein Datums- und/oder Zeitformat automatisiert zu erkennen. Das dritte Argument bestimmt, ob Z an die Zeitangabe angehängt wird oder nicht. Und das vierte Argument bestimmt die Zeitdifferenz – als Dezimalzahl – zu UTC.

Folgende Werte sind für das zweite Argument Type vorzusehen:

1 : Value ist vom Typ Datum
2 : Value ist vom Typ Zeit
3 : Value ist vom Typ Datum und Zeit

Einige beispielhafte Aufrufe der Funktion ISODATETIME sind:

=ISODATETIME(A1;1;0;0)
=ISODATETIME(A1;1;0;-2,5)
=ISODATETIME(DATUM(2021;1;15);3;0;5)
=ISODATETIME(A1:A10;B1:B10;;)

ISODATETIME

Nachfolgende Abbildung zeigt einige Ergebnisse, die aus dem Aufruf der Funktion ISODATETIME resultieren. Je nach Typ werden unterschiedliche Texte generiert. Eine Besonderheit stellt die Kombination Uhrzeit und Typ gleich 3 dar: hier wird das aktuelle Datum in die Angabe integriert. Und, die Angabe von Offset hat Vorrang vor UTC, wie in Zelle E7 zu sehen.

ISODATETIME - Beispiele

Einer der ersten Schritte im Aufbau einer LAMBDA-Funktion ist zumeist der Aufbau einer LET-Formel, die es ermöglicht, Variablen anzulegen. So lässt sich eine LET-Funktion als Pseudocode wie folgt definieren:

=LET(Value;A2;
     Type;B2;
     UTC;C2;
     Offset;D2;
     V;Hilfsvariable für Value;
     D;Hilfsvariable zum Aufbau des Textteils zum Datum;
     T;Hilfsvariable zum Aufbau des Textteils zur Uhrzeit;
     Z;Hilfsvariable für UTC;
     O;Hilfsvariable für Offset;
       Berechnung vom Ergebnis)

Wie zuvor erwähnt, soll ISODATETIME bei der Kombination Type gleich 3 und nur Angabe einer Uhrzeit das aktuelle Datum in der Ausgabe hinzufügen. Daher prüfen wir zunächst, ob der Wert im ersten Argument eine Zahl größer Null enthält. Wenn nicht, fügen wir das aktuelle Datum hinzu. Dadurch ergibt sich:

V=WENN(ABRUNDEN(Value;0)<1;HEUTE()+Value;Value)

Die Variable D soll den Textanteil für das Datum aufbauen. Zu berücksichtigen ist hier, dass keine länderspezifischen Formatierungsangaben - wie z.B. JJJJ für das Jahr - verwendet werden, da sonst die Formel in anderen Sprachen nicht funktionieren würde. In ähnlicher Weise berechnen wir T zum Aufbau des Textteils für die Uhrzeit. Für D und T ergeben sich:

D=JAHR(V)&"-"&TEXT(MONAT(V);"00")&"-"&TEXT(TAG(V);"00")
T=TEXT(STUNDE(V);"00")&":"&TEXT(MINUTE(V);"00")&":"&TEXT(SEKUNDE(V);"00")

Das Ermittlen der Hilfsvariablen für Z und O ist recht einfach: für Z prüfen wir, ob ein Wert angegeben wurde und wenn ja, ob dieser größer Null ist. Und, wir greifen auf die erste Zelle zu, falls (versehentlich) ein Bereich angegeben wurde. Ähnlich verfahren wir mit der Variable O zum Offset, wo aber eine Prüfung auf ungleich Null gemacht wird, da Offset ja auch negativ sein kann.

Z=WENN(WENNFEHLER(--INDEX(UTC;1;1);0)>0;1;0);
O=WENN(WENNFEHLER(--INDEX(Offset;1;1);0)<>0;Offset;0)

Der letzte Schritt besteht nun die Berechnung durchzuführen, die anhand der Funktion WAHL die drei Typen unterscheidet. Und, falls die Variable Offset gesetzt wurde, wird der Wert von Offset priorisiert, in eine Zeit umgerechnet und angehängt.

WENNFEHLER(WAHL(Type;D;T;D&"T"&T&
  WENN(O<>0;WENN(O<0;"-";"+")&
            TEXT(KÜRZEN(ABS(O);0);"00")&":"&
            TEXT(60*(ABS(O)-KÜRZEN(ABS(O);0));"00");
            WENN(Z>0;"Z";"")));"-")

Damit wäre die LET-Formel vollständig und es ergibt sich folgende LAMBDA-Funktion, die anschließend im Namensmanager unter dem Namen ISODATETIME angelegt werden kann.

=LAMBDA(Value;Type;UTC;Offset;
 LET(V;WENN(ABRUNDEN(Value;0)<1;HEUTE()+Value;Value);
     D;JAHR(V)&"-"&TEXT(MONAT(V);"00")&"-"&TEXT(TAG(V);"00");
     T;TEXT(STUNDE(V);"00")&":"&TEXT(MINUTE(V);"00")&":"&TEXT(SEKUNDE(V);"00");
     Z;WENN(WENNFEHLER(--INDEX(UTC;1;1);0)>0;1;0);
     O;WENN(WENNFEHLER(--INDEX(Offset;1;1);0)<>0;Offset;0);
       ""&WENNFEHLER(WAHL(Type;D;T;D&"T"&T&WENN(O<>0;
          WENN(O<0;"-";"+")&TEXT(KÜRZEN(ABS(O);0);"00")&":"&
          TEXT(60*(ABS(O)-KÜRZEN(ABS(O);0));"00");
          WENN(Z>0;"Z";"")));"-")))

Da ich weiss, dass dieser Blog auch von recht vielen englischsprachigen Personen gelesen wird, folgend die Übersetzung über meinen Excel Formel-Übersetzer der LAMBDA-Funktion in Englisch:

=LAMBDA(Value,Type,UTC,Offset,
 LET(V,IF(ROUNDDOWN(Value,0)<1,TODAY()+Value,Value),
     D,YEAR(V)&"-"&TEXT(MONTH(V),"00")&"-"&TEXT(DAY(V),"00"),
     T,TEXT(HOUR(V),"00")&":"&TEXT(MINUTE(V),"00")&":"&TEXT(SECOND(V),"00"),
     Z,IF(IFERROR(--INDEX(UTC,1,1),0)>0,1,0),
     O,IF(IFERROR(--INDEX(Offset,1,1),0)<>0,Offset,0),
       ""&IFERROR(CHOOSE(Type,D,T,D&"T"&T&IF(O<>0,
          IF(O<0,"-","+")&TEXT(TRUNC(ABS(O),0),"00")&":"&
          TEXT(60*(ABS(O)-TRUNC(ABS(O),0)),"00"),
          IF(Z>0,"Z",""))),"-")))

Fazit

ISODATETIME berücksichtigt zwar nicht alle Definitionen der ISO-Norm ISO 8601, wird aber als Hilfsfunktion für JSON.FROMRANGE ausreichend sein und die Komplexität meiner JSON-Funktion reduzieren.

Happy Exceling :-)

 

 
Comments

No comments yet.