Tipps & Tricks
11.10.2019, 12:45 Uhr
Excel: Ziffernfolge in Datum umwandeln
Ab einer Server-Anwendung erhalten Sie Excel-Listen mit Datumsangaben wie «20191001» anstelle von «01.10.2019». Hier drei Möglichkeiten, die Daten umzuwandeln – für Einsteiger und Excel-Nerds.
Sie erhalten ab einem Server oder aus einer bestimmten Anwendung Dateien, die Sie via Excel weiterverarbeiten wollen. Zu Ihrem Missfallen stehen in diesen Dateien die Kalenderdaten in einem falschen Format. Anstatt den 1. Oktober mit etwas wie 01.10.2019 darzustellen, meint die Datumsspalte im importierten File: «20191001».
Sie haben drei Möglichkeiten, die wir auf dieser und den zwei Folgeseiten zeigen:
- Variante 1: Per Excel-Intelligenz umwandeln
- Variante 2: Per Formel umwandeln
- Variante 3: Direkt richtig importieren
Variante 1: Per Excel-Intelligenz umwandeln
Excel versucht es seit einiger Zeit mit einem Schuss Intelligenz. Manchmal klappt das sogar recht gut. Fügen Sie neben der Spalte mit dem falsch dargestellten Datum eine weitere ein. In dieser neuen Spalte tippen Sie nun bei zwei bis drei verschiedenen Kalenderdaten das Datum so ein, wie es für Sie aussehen soll. Lassen Sie den Cursor gleich in jener Spalte und gehen Sie via Daten/Datentools zu Blitzvorschau. Excel füllt völlig automatisch die anderen Zellen so aus, wie Sie es ihm gezeigt haben.
Geben Sie Excel in der neuen Spalte zwei bis drei korrekte Beispiele, kann es mittels Blitzvorschau die gesamte Spalte adäquat ausfüllen
Wenn es genügend korrekte Beispiele von Ihnen erhalten hat, macht es das sogar richtig. Ausserdem stehen die Kalenderdaten in dieser Spalte nun bereits als feste Werte drin – und nicht als Formeln. Scrollen Sie einmal durch die Liste und prüfen Sie von Auge, ob Excel sich nirgends verheddert hat. Wenn alles passt und Sie die Spalte mit der falsch dargestellten Datumsangabe nicht mehr brauchen, können Sie jene löschen.
Nächste Seite: Variante 2: Per Formel umwandeln
Variante 2: Per Formel umwandeln
Variante 2: Per Formel umwandeln
Vielleicht haben Sie eine alte Excel-Version oder Sie trauen Excel bei der Blitzvorschau nicht ganz über den Weg. In diesem Fall bietet sich ein Umwandeln mittels Formel an, die Excel keinen Interpretationsspielraum bietet und die sicherstellt, dass die richtigen Zeichen in die richtigen Datumsangaben umgewandelt werden.
Sie müssen den Text «20191001» in seine Bestandteile (2019 - 10 - 01) zerlegen und anschliessend in ein Datum umwandeln. Sie benötigen dazu mehrere Funktionen:
1. Mit TEIL() können Sie einzelne Zeichen aus dem Text herauslesen. Der Ausdruck «=TEIL(B2;5;2)» liest aus der Zelle B2 ab dem fünften Zeichen 2 Zeichen heraus. In unserem Beispiel liefert er also den Monat («10»).
2. Zusätzlich müssen zwischen den Bestandteilen Punkte eingefügt werden, die man mittels «&»-Zeichen und den Anführungszeichen direkt in die Formel hinein verketten kann. Die Formel lautet vorläufig so:
=TEIL(B2;7;2)&"."&TEIL(B2;5;2)&"."&TEIL(B2;1;4)
=TEIL(B2;7;2)&"."&TEIL(B2;5;2)&"."&TEIL(B2;1;4)
3. Das Resultat dieser Formel sieht zwar korrekt aus (01.10.2019), es muss aber noch in ein Datumsformat umgewandelt werden, wenn Excel damit rechnen soll. Dies bewerkstelligt die Funktion DATWERT(). Die ganze Formel lautet also:
=DATWERT(TEIL(B2;7;2)&"."&TEIL(B2;5;2)&"."&TEIL(B2;1;4))
=DATWERT(TEIL(B2;7;2)&"."&TEIL(B2;5;2)&"."&TEIL(B2;1;4))
Mittels Funktion TEIL und dem Hinzufügen der Datumspunkte wird das Datum zusammengesetzt und mittels DATWERT als eine Datumsangabe definiert
4. Markieren Sie die Spalte und formatieren Sie diese via Rechtsklick und Zellen formatieren unter Zahlen noch als Datum, da das Datum sonst als fünfstellige Zahl (43739) dargestellt wird.
5. Wichtig: Im Moment stehen diese Kalenderdaten als Formeln in der Tabelle. Wenn Sie diese als feste Werte haben wollen, fügen Sie eine leere Spalte ein, kopieren Sie die korrekte Datumsspalte und fügen Sie diese mittels Einfügen/Inhalte einfügen/Werte in die leere Spalte ein.
Nächste Seite: Variante 3: Direkt richtig importieren
Variante 3: Direkt richtig importieren
Variante 3: Direkt richtig importieren
Damit Sie die Spalte mit dem Kalenderdatum nicht nachträglich korrigieren müssen, können Sie diesen Schritt direkt beim Import in Excel vornehmen. Das setzt aber voraus, dass Sie eine CSV-Datei erhalten (Comma Separated Values), die Sie als Textdatei importieren können. In diesem Fall geht es so: Öffnen Sie Excel zunächst mit einer leeren Tabelle, also ohne die erhaltene Datei zu öffnen.
Gehen Sie zum Reiter Daten. Im linken Bereich des Menübandes, bei Daten abrufen und transformieren klicken Sie auf Daten abrufen/Aus Datei/Aus Text/CSV. Klicken Sie sich zur gewünschten CSV-Datei durch und klicken Sie auf Importieren.
Benutzen Sie den (neuen) Importassistenten
Klicken Sie nun unten rechts auf Daten transformieren.
Noch ist das Datum falsch. Klicken Sie unten rechts auf «Daten transformieren»
Klicken Sie die betroffene Datumsspalte an und gehen Sie im Menübandbereich Transformieren zu Datentyp/Datum.
Wählen Sie im Menüband unter «Datentyp» den Eintrag «Datum»
Excel fragt allenfalls, ob es die «Typenkonvertierung» ersetzen soll. Klicken Sie auf Aktuelle ersetzen. Schon werden die Ziffernfolgen in der Vorschau durch korrekte Datumswerte ersetzt. Passen die Angaben, klicken Sie oben links auf Schliessen & laden.
Excel wandelt die Ziffernfolgen in ein Datum um
Nach kurzen Augenblicken fügt Excel die Liste als neue formatierte Tabelle ein – inklusive umgewandelten Kalenderdaten.
Excel hat die Daten nun korrekt importiert. Den Spaltentitel unserer Beispieltabelle können nun zu «Datum richtig» ändern...
27.03.2023
29.03.2023
29.03.2023
30.03.2023