Mit schön­er Regelmäßigkeit werde ich gefragt, wie fol­gen­des real­isiert wer­den kann:

• Eingabe von beispiel­sweise kalen­darischen Dat­en, Uhrzeit­en, IP-Adressen oder ähn­lichen Zif­fer­n­fol­gen, jedoch ohne die Trennze­ichen (Punkt, Schrägstrich oder Minusze­ichen bei einem Datum, Dop­pelpunkt bei ein­er Uhrzeit oder auch den Punkt bei IP-Adressen) mit eingeben zu müssen.

Die Moti­va­tion als auch die Sinnhaftigkeit will ich hier nicht disku­tieren, der Wun­sch ist gegeben. In erster Lin­ie bietet sich dabei natür­lich ein selb­st definiertes Zahlen­for­mat an. Das Ergeb­nis sieht dann zwar wie gewollt aus, aber Excel „sieht” den­noch die ursprüngliche Eingabe und macht unter typ­is­chen Umstän­den keineswegs ein Datum, eine Uhrzeit oder eine auswert­bare IP daraus. Für die Uhrzeit wird hier im Blog als auch beispiel­sweise hier eine Lösung ange­boten, die Eingabe direkt zu „kor­rigieren”. Wenn es bei der Dateneingabe aber auf Geschwindigkeit ankommt, dann ist das gewiss nicht der beste Weg.

Mit der Nutzung des Pow­er Query bietet sich die Möglichkeit, eine kom­plette Spalte rasch umzu­for­matieren bzw. eine neue Spalte mit den kor­rek­ten (gewün­scht­en) Werten und Darstel­lun­gen zu gener­ieren.

▲ nach oben …

Datum (Kalendarische Daten)

Ihr erster Gedanke kön­nte nun sein, dass das mit dem Datum genau­so funk­tion­iert wie mit der Zeit (siehe hier im Blog). Nun ja, in gewis­sen Gren­zen ist das gewiss richtig. Aber eben nicht immer. Am ehesten wer­den sie zum gewün­scht­en Ziel kom­men, wenn die Norm der Zif­fer­n­folge mit lan­desüblich­er Darstel­lung (und der Vor­gabe dieser Übung entsprechend ohne die tren­nen­den Punk­te) übere­in­stimmt: TTMMJJJJ (ddM­Myyyy). Und wenn die Dateneingabe kon­se­quent 6- bzw. 8‑Stellig ist, dann wird beispiel­sweise Neu­jahr 2020 der­art als Text eingegeben: 010120 oder 01012020, wie hier in Deutsch­land üblich bzw. Stan­dard in Sachen Rei­hen­folge.

Aber lei­der ist es ja so, dass ger­ade bei kalen­darischen Dat­en eine unge­meine Vielfalt möglich ist und Excel in der Lage sein soll, all diese For­mate richtig zu deuten. Ich werde mich hier im Wesentlichen den­noch auf die Eingabe des deutschen Datums­for­mats beschränken, fremde For­mate (USA oder ANSI) wer­den ja meist aus exter­nen Quellen über­nom­men und sind dort schon in einem „vernün­fti­gen” For­mat vorhan­den, also mit Trennze­ichen zwis­chen den Ein­heit­en. Die einzige Aus­nahme ist ANSI ohne Trennze­ichen, worauf ich weit­er unten auch noch einge­hen werde.

Datum TTMMJJJJ

Erst ein­mal ein Hin­weis mein­er­seits: Ich werde im fol­gen­den Text fast auss­chließlich die kor­rek­te Mehrzahl-Form des Wortes „Datum” ver­wen­den: „Dat­en”. Okay, das kann im nor­malen deutschen Sprachge­brauch auch missver­ständlich sein, darum ver­wende ich der Ein­deutigkeit wegen in meinen Beiträ­gen auch sehr oft „kalen­darische Dat­en”. In diesem Beitrag möchte ich mir diese Kon­struk­te weit­ge­hend „verkneifen” und Sie wis­sen gewiss auch so in jedem Fall, was gemeint ist. 😎 

Grund­sät­zlich gilt auch für die in Excel von Hand eingegebe­nen Dat­en, dass die Eingabe expliz­it als Text erfol­gt ist. Wichtig ist dabei, dass die Spalte vor der Eingabe der Zif­fer­n­fol­gen als Zahlen­for­mat Text ein­gerichtet (for­matiert) wor­den ist oder diese Dat­en-Eingabe immer mit einem Aus­las­sungsze­ichen ' begin­nt. Wenn Sie beispiel­sweise aus einem ERP-Pro­gramm exportierte csv-Files in Pow­er Query importieren, sollte das Orig­i­nal-For­mat auch führende Nullen enthal­ten und diese Dat­en kön­nen dann nach dem Import in PQ gut in die gewün­schte Form gebracht wer­den. Die csv-Dat­en stellen sich dann beispiel­sweise so dar (gerne hier zum Down­load „nur” als Übungsplat­tform):

Da.tum;(Datum)
01.01.2000;01012000
10.01.2000;10012000
01.01.00;010100
10.01.00;100100
17.03.1919;17031919
17.03.19;170319
29.02.2020 14:50;29022020 1450
1–1‑00;1100

Wie auch im ersten Beitrag dieser Trilo­gie habe ich eine 2‑spaltige Mus­ter­datei erstellt, wo in der ersten Spalte das Wun­sch-Datum (Wun­schergeb­nis) dargestellt wird und in der zweit­en, vorher als Text for­matierten Spalte die händis­chen Text-Dat­en, welche gle­ich im Anschluss zu einem kor­rek­ten Datum kon­vertiert wer­den sollen. Laden Sie dieses File hier herunter; es sind die gle­ichen Dat­en wie in der csv, nur bere­its im Arbeits­blatt nach meinen Vor­gaben for­matiert. Nach dem Import in den Pow­er Query-Edi­tor¿ stellt sich das dann so dar:

Direkt nach dem Import

Auch wenn sie es sich nicht vorstellen kön­nen, solch ein „Kud­del­mud­del” von unter­schiedlichen Dat­en gibt es tat­säch­lich in der Prax­is, wenn auch sehr sel­ten. Das passiert schon ein­mal, wenn mehrere Nutzer in ein­er Tabelle Dat­en eingeben oder unter­schiedliche eigen­ständi­ge Arbeits­blät­ter zusam­menge­fügt wur­den.  – Ich nutze die Gele­gen­heit mit Ihnen gemein­sam zu erar­beit­en, wie sie da eine gewisse Logik hinein­brin­gen kön­nen und Sie es schaf­fen, Schritt für Schritt die Spalte (Datum) zu einem „echt­en” Datum­swert umzuwan­deln bzw. in ein­er weit­eren Spalte darzustellen.

Analyse

Stellen sich zuerst ein­mal die Frage, welche Dat­en gle­ichar­tig, gle­ich­för­mig sind. Weit­er­hin soll­ten Sie erken­nen, ob dort auch wirk­lich grobe „Aus­rutsch­er” dabei sind. Ich komme bei mein­er Analyse auf 3 bzw. 4, Grup­pen. Prinzip­iell wären dieses:

• Grund­sät­zlich kor­rek­tes Datum (ohne Tren­ner), wobei Sie noch zwis­chen 2‑oder 4‑stelliger Jahreszahl unter­schei­den kön­nten (also eine oder zwei Grup­pen).
• Datum mit Uhrzeit
• Prinzip­iell fehler­haftes Datum (Zeile 8).

Ich beginne ein­fach ein­mal mit dieser fehler­haften Zeile 8, die auch von/in Plain Excel in dieser Form beim besten Willen nicht als Datum inter­pretiert wer­den kann. Auch wenn das der 1.1.2000 (oder vielle­icht sog­ar 1900?) sein soll, es kön­nte ja auch der Monat 11 des Jahres 2000 (oder 1900) sein (was dann als der 1.11.00 inter­pretiert wer­den würde). Die aus mein­er Sicht einzig logis­che Fol­gerung: Löschen Sie diese Zeile entwed­er in Excel oder aber hier im Pow­er Query-Edi­tor. Alter­na­tiv kön­nen Sie es naturgemäß bei dieser Zif­fer­n­folge belassen und darauf ver­trauen, dass PQ in vie­len Fällen einen Fehler erzeugt, der dann entsprechend in der Auswer­tung als leeres Feld oder als Fehler erkennbar ist. Eine Kor­rek­tur in Excel kommt gewiss nur dann infrage, wenn Sie ganz genau wis­sen, welch­es Datum dieses sein soll.

Tripelschritte, aber verständlich

Sie wer­den es sich beim Lesen der Über­schrift denken kön­nen, dass hier viele kleine Schritte zum gewün­scht­en Erfolg führen. Das hat in jedem Fall den Vorteil, dass nicht so ver­sierte User die Vorge­hensweise gewiss bess­er ver­ste­hen und auch nachvol­lziehen kön­nen. Das Grund­prinzip basiert auf fol­gen­der Über­legung: In den Text sollen ja auf jeden Fall nach den ersten zwei und dann wiederum nach den näch­sten zwei Zif­fern ein Punkt einge­fügt wer­den, damit zumin­d­est erst ein­mal aus optis­ch­er Sicht ein Datum erkennbar ist. Gehen Sie dazu so vor:

• Markieren Sie die Über­schrift (Datum).
• Spalte teilen | Nach Anzahl von Zeichen | Anzahl von Zeichen 2 | Ein­mal, so weit links wie möglich.
• Markieren Sie nun die Über­schrift (Datum).2.
• Auch hier das gle­iche Vorge­hen wie eben, also ein Mal nach dem zweit­en Zeichen (was MM entspricht) teilen.
• Markieren Sie die Spalte (Datum).1 und anschließend mit Shift oder Strg in die Spalte (Datum). 2.1 Klick­en.
• Im Menüband (Start) Gruppe Trans­formieren den Daten­typ: Text auswählen.
• Nun alle drei restlichen Spal­ten ab (Datum).1 markieren, Recht­sklick in eine der markierten Über­schriften und Spal­ten zusam­men­führen.
• Bei Trennze­ichen wählen Sie –Benutzerdefiniert– und schreiben dann in das leere Textfeld darunter nur einen Punkt . .
• Neuer Spal­tenname (option­al) sollte Datum sein.

Vielle­icht fällt Ihnen jet­zt ja auf, dass die Dat­en mit den Tagen vor dem 10. des Monats auch nur ein­stel­lig dargestellt wer­den. Das liegt daran, dass sie nach dem tren­nen der Spal­ten die Schrit­tumwand­lung zu Text für die Tages- und Monatss­palte gemein­sam durchge­führt haben. Wenn Sie auch an dieser Stelle schon beispiel­sweise die Darstel­lung 01.01.2000 haben möcht­en, dann hät­ten sie vorher die Spal­ten für den Tag und den Monat einzel­nen, also nacheinan­der in den Typ Text kon­vertieren müssen. Und ach ja, wie auch zuvor schon ange­merkt ist die Zeile 8 nicht wirk­lich als Datum erkennbar. 😆 

Wech­seln Sie nun erforder­lichen­falls zum Menü Trans­formieren, markieren Sie die Spalte Datum und wählen in der Gruppe Datums- & Uhrzeitspalte das Sym­bol Datum. Im Drop­down ist der einzig wählbare Punkt Analysieren und genau da genau darauf wer­den sie auch Klick­en.

Die ersten 6 Zeilen sehen ja nun sehr vernün­ftig aus und sind auch offen­sichtlich im kor­rek­ten For­mat. Und Zeile 8 ist schon so gut wie klar, dass hier eine Fehler­mel­dung kommt. Bei der Zeile darüber ist das aber nicht so ganz ersichtlich. Aber im Gegen­satz zu Excel nimmt es Pow­er Query sehr genau. Das was dort im let­zten Schritt stand ist nicht der Daten­typ Datum son­dern Datum/Uhrzeit. Und darum „meck­ert” Pow­er Query auch diese Zelle an.

Sonderfall Zeile 7

Noch ein­mal vor­ab ange­merkt: In eine Spalte ein­er ordentlichen Tabelle gehört immer nur der gle­iche Daten­typ. Wird diese Regel nicht einge­hal­ten, wird es voraus­sichtlich immer wieder irgend­wo zu Kom­p­lika­tio­nen (wie in diesem Fall) kom­men. Aber es gibt dur­chaus Möglichkeit­en, das Ziel zu erre­ichen. Hier ist es ja nicht das „reine” Datum son­dern auch die Uhrzeit, welche den Unter­schied zu den darüber liegen­den Zellen aus­macht.

Begin­nen Sie damit, dass sie den let­zten Schritt im recht­en Seit­en­fen­ster (hier: Analysiertes Datum) löschen. Jet­zt sind wieder alle Dat­en eis­chließlich Zeile 7 vorhan­den. Die gewiss ein­fach­ste und kon­se­quenteste Lösung wäre, die Uhrzeit schlichtweg zu ignori­eren, nicht zu berück­sichti­gen. Dann bleibt es auch bei ein­er einzi­gen Spalte mit dem Datum und dem immer gle­ichen Daten­typ Datum.

Um das zu erre­ichen wer­den sie Datum markieren, Recht­sklick und Spalte teilen | Nach Trennze­ichen | Leerze­ichen und Pow­er Query teilt die Spalte Datum entsprechend. Sie erken­nen zweier­lei Änderun­gen: Es gibt 2 Datum-Spal­ten, wo in der 2. Spalte die abge­tren­nte Uhrzeit ste­ht aber auch in der 1. Spalte wieder eine Umwand­lung in Ganze Zahl vorgenom­men wor­den ist. Der let­zte (automa­tisch einge­fügte) Schritt ist Geän­dert­er Typ3. Löschen Sie hier im recht­en Seit­en­fen­ster diesen Schritt und die Spalte Datum. 1 enthält wieder ein Datum.

Löschen Sie nun Spalte Datum.2. Benen­nen Sie Datum.1 wiederum zu Datum um. Nun kön­nen Sie den Daten­typ beispiel­sweise über das Menü Home oder per Recht­sklick ändern oder aber sie wählen wie bere­its vorher den Weg über Trans­formieren und der automa­tis­chen Analyse des Datums. Der Effekt ist jew­eils iden­tisch.

Das Ziel ist erre­icht, auch wenn beim irreg­ulären Datum der Zeile 8 der Wert Error einge­tra­gen ist. Schließen & laden oder Schließen & laden in… und sie haben ein gutes Ergeb­nis. Dieses „glänzt” auch nicht durch eine Fehler­mel­dung in der let­zten Zeile, hier ist der Wert ein­fach nur leer geblieben. 😎 

ANSI ohne Trennzeichen, yyyyMMdd

Ein echt­es ANSI-Datum ist immer 8‑stellig und auch immer diese Rei­hen­folge: Jahr (4 Stellen), danach jew­eils 2‑stellig Monat und Tag. Die vielfach ver­wen­dete offizielle Beze­ich­nung dafür ist ISO 8601. Und wenn es nach der Norm geht, gilt dieses For­mat auch in Deutsch­land, Öster­re­ich, Schweiz und weit­ere Staat­en. Das For­mat gilt zumin­d­est, wenn kein Trennze­ichen zwis­chen den Ele­menten ver­wen­det wird. Ein Binde­strich - wird in den meis­ten Fällen beim „inter­na­tionalen Datums­for­mat” sehr oft ver­wen­det und dann erken­nt Pow­er Query sog­ar automa­tisch das kor­rek­te Datum.

Heute (wo ich ger­ade diesen Text ver­fasse) ist der 19. Juni 2020, was entsprechend ohne Tren­ner nach der ANSI-Norm 20200609 geschrieben wer­den würde.  Die erste, denkbare Möglichkeit lehnt sich stark an die weit­er oben gezeigte, allererste Lösungsmöglichkeit an. Zuerst ein­mal wer­den Sie Sorge dafür tra­gen, dass der Inhalt der Spalte vom Daten­typ Text ist. Nor­maler­weise sind das ja reine Zahlen. Dann Spalte teilen nach 4 Zeichen und die verbleiben­den (let­zten) 4 Ziffern/Zeichen erst in den Daten­typ Text umwan­deln oder die let­zte Zeile bei Angewen­dete Schritte (Geän­dert­er Typ) löschen (son­st ste­ht in diesem Beispiel nur die Zahl bzw. Zif­fer­n­folge 609 dort) und dann den 4‑stelligen String nach 2 Stellen teilen. Auch wenn das Ergeb­nis nun wieder alles Daten­typ: Zahl ist, das macht in diesem Fall nichts und kann so bleiben.

Wenn es Ihnen hil­ft, kön­nen Sie den Spal­ten auch die Über­schriften Jahr, Monat, Tag geben; das ist weniger abstrakt. Klick­en Sie nun zuerst in die Über­schrift Tag, um die Spalte zu markieren. Strg und dann Monat markieren und anschließend auch noch Jahr. Recht­sklick in eine der markierten Über­schriften und Spal­ten zusam­men­führen. Als Trennze­ichen gehen Sie wiederum über –Benutzerdefiniert– und tra­gen den Punkt. ein. Alter­na­tiv kön­nten Sie auch den Binde­strich (Minusze­ichen) - ver­wen­den. In jedem Fall wer­den Sie anschließend den Daten­typ: Datum direkt wählen und das Datum ste­ht wie gewün­scht dort.

▲ nach oben …

Möcht­en Sie den Weg über eine neue Spalte und den Funk­tions-Edi­tor gehen, dann wech­seln Sie zum Menü Spalte hinzufü­gen und wählen dort den Ein­trag Spalte aus Beispie­len. Ich habe ein­mal für die Demon­stra­tion 2 weit­ere Dat­en einge­fügt und das stellt sich dann so dar:

Eine Spalte aus Beispie­len hinzufü­gen, das Start-Bild

Im recht­en Teil des inneren Fen­sters ist eine leere Tabelle mit der Über­schrift Spalte2 einge­fügt wor­den. Dort ist in einem dun­kleren Grün die erste Zeile markiert, in einem helleren Grün die erste Zeile der Spalte1. Schreiben Sie das gewün­schte Ergeb­nis:

Pow­er Query schlägt auch sin­nvolle Ein­träge vor…

… und wenn Sie dann in der zweit­en Zeile den 11.03.2019 von Hand ein­tra­gen, wird PQ automatsch die restlichen verbleibendn Zeilen nach diesem Muster aus­füllen. Bestäti­gen Sie mit OK und die Dat­en wer­den als Text die Abfrage über­nom­men. Die erforder­liche Zuweisung des Daten­for­mats sollte Ihnen nun geläu­fig sein.

▲ nach oben …

IP-Adresse

Auf deutschen Tas­taturen ist es wirk­lich unbe­quem, Kolon­nen von IP-Adressen einzugeben. Die 1 bis 3 Zif­fern liegen alle so schön auf dem Num­mern­block rechts und der Punkt ist dann außer­halb dieses Bere­ichs. Natür­lich kön­nten Sie statt des Punk­tes ein­fach das Kom­ma vom Zehnerblock nehmen und nach Abschluss der Eingabe in der entsprechen­den Spalte das Kom­ma durch den Punkt erset­zen. Aber na ja, schön ist etwas anderes, oder? Ach ja, wenn Sie die Trenn-Meth­ode anwen­den wollen ist es natür­lich aus­ge­sprochen wichtig, dass bei der Eingabe der Zif­fern immer auf 3 Stellen je Block durch führende Nullen aufge­füllt wird. Son­st klappt das ganze Vorhaben nicht oder die Meth­ode mit einem beliebi­gen Trenn-Zeichen der Zehn­er­tas­tatur (Zif­fern­block) ist doch die bessere. 🙄 

Um beim bekan­nten Prinzip zu bleiben gilt auch hier, die eingegebe­nen Zif­fern erst ein­mal wegen eventuell führen­der Nullen in das Text-For­mat umzuwan­deln. Anschließend über Start | Spalte teilen | Nach Anzahl von Zeichen eine 3 eingeben und Wieder­holt markiert lassen. Die 4 Ergeb­nis-Spal­ten wer­den dann Zahlen enthal­ten, das kann aber so bleiben. Denn stan­dard­mäßig wer­den die IP-Adressen ohne führende Nullen dargestellt. Also von links nach rechts markieren, Spal­ten zusam­men­führen und den Punkt als Tren­ner ver­wen­den. Bei ein­er alter­na­tiv­en Eingabe-Form ent­fällt natür­lich das Zusam­men­führen und das hil­f­sweise genutzte Zeichen muss nur durch den Punkt erset­zt wer­den.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,50 € freuen … (← Klick mich!)