In ei­nem Fo­rum wur­de von ei­nem Fra­ge­stel­ler die Auf­gabe zur Diskus­sion ge­stellt, wie Da­tum- und Zeit-Ein­träge unter­schiedlich­er Gebi­ets­for­matierun­gen (hier: DE und US) in ein ein­heitlich­es For­mat als nur Da­tum im heimis­chen For­mat dar­ge­stellt wer­den kön­nen. Zum Zeit­punkt der Erstel­lung die­ses Bei­tra­ges lag eine Lö­sung in VBA vor, die dur­chaus prak­tik­a­bel ist und in vie­len Fäl­len auch die ers­te Wahl sein kann. In man­chen Un­ter­neh­men ist je­doch VBA/Ma­kro aus gu­ten Grün­den unter­sagt bzw. deak­tiviert. Da bie­tet es sich an, über Pow­er Que­ry als Lö­sung nachzu­denken.

Ich habe die Text-ba­sier­te Auf­stel­lung in eine Ex­cel-Ta­bel­le über­tra­gen und als Ba­sis für zwei mög­li­che Lösun­gen mit Pow­er Que­ry ver­wen­det. La­den Sie die­ses File hier her­un­ter. Bei­de von mir vorgestell­ten Lösun­gen sind gle­ich­w­er­tig, weit­ere An­sät­ze sind natür­lich auch denk­bar.

Begin­nen Sie da­mit, die Dat­en in eine „intel­li­gente” Ta­bel­le/Lis­te zu for­matieren. Da­nach im­por­tie­ren Sie die Dat­en in den Pow­er Que­ry-Edi­tor. An­schlie­ßend stellt sich das dann so dar:

Di­rekt nach dem Im­port

Der ers­te Arbeitss­chritt wird sein, die Dat­en der Spal­te1 als Daten­typ: Text zu for­matieren. Als Ein­steiger wer­den sie ge­wiss im Reg­is­ter Home und Grup­pe Trans­formieren den Daten­typ ent­spre­chend än­dern. Wenn Sie exper­i­men­tier­freudig sind, kön­nen Sie di­rekt nach dem Im­port die Bear­beitungszeile dahinge­hend än­dern, dass sie das let­zte Argu­ment type any in type text (Klein­schrei­bung!) än­dern. Das geht zwar nicht schnel­ler, führt aber ge­wiss zum Ge­winn neu­er Erken­nt­nisse. 😉 

Mög­lich­keit 1

Um das Da­tum von der Uhr­zeit zu tren­nen, gibt es (natür­lich) meh­re­re Möglichkeit­en. Auch in äl­te­ren Ver­sio­nen des Pow­er Que­ry funk­tion­iert ga­ran­tiert die Meth­ode, die Spal­te nach Trennze­ichen zu tei­len. Das funk­tion­iert, weil die Uhr­zeit in je­dem Fal­le durch ein Leerze­ichen vom Da­tum getren­nt ist. Ge­hen Sie also in der Grup­pe Trans­formieren über Spal­te tei­len | Nach Trennze­ichen und wäh­len Sie statt des Vor­schla­ges Dop­pelpunkt das Leerze­ichen. An­schlie­ßend lö­schen Sie (aus Grün­den der Über­sichtlichkeit) die abge­tren­nte Spal­te mit der Uhr­zeit. Ich sel­ber bin (in der Ver­sion 2019) fol­gen­den Weg gegan­gen: Trans­formieren | Text­spal­te | Ex­tra­hie­ren | Text vor Trennze­ichen und gebe im Text­feld Trennze­ichen über die Tas­tatur ein Leerze­ichen ein. Da­mit erre­iche ich, dass kei­ne zusät­zliche Spal­te mit der Uhr­zeit gener­iert wird.

Eine direk­te Kon­vertierung die­ser kalen­darischen Dat­en im gemis­cht­en Lan­des-For­mat (DE und US) ist nur in VBA mög­lich, mei­nes Wis­sens nicht Pow­er Que­ry. Mit ein­er For­mel in ein­er be­nut­zer­de­fi­nier­ten Spal­te ist das aber gut mach­bar. Ak­ti­vie­ren Sie das Reg­is­ter Spal­te hinzufü­gen und wäh­len Sie das Sym­bol Be­nut­zer­de­fi­nier­te Spal­te. Es öff­net sich ein Dia­log-Fen­ster und dort tra­gen Sie die hier ge­zeig­ten Wer­te ein:

Die­se Be­nut­zer­de­fi­nier­te For­mel ist ziel­füh­rend

Das gan­ze hier noch ein­mal in Text­form: Bei Neu­er Spal­tenname ge­ben Sie als Über­schrift Da­tum ein. Und die Be­nut­zer­de­fi­nier­te Spal­tenformel sieht so aus:

= if Text.Contains([Spalte1], ".")
then Date.From([Spalte1])
else Date.From([Spalte1], "us-US")

Über­set­zt be­deu­tet das: Wenn der Wert der jew­eili­gen Zei­le in Spal­te1 ei­nen Punkt ent­hält, dann soll der Wert aus Spal­te1 di­rekt ver­wen­det wer­den, anson­sten eben­falls der Wert aus Spal­te1, je­doch un­ter dem As­pekt, dass die­ses Da­tum im US-For­mat vor­liegt.

Prinzip­iell ist da­mit das Ziel erre­icht. For­mal soll­ten je­doch noch zwei Schrit­te gegan­gen wer­den: Recht­sklick in die Spal­te Da­tum und im Kon­textmenü Typ än­dern | Da­tum und als prak­tisch let­zten Schritt wer­den Sie Spal­te1 lö­schen; es wird ja nur das auf lo­ka­le Ver­hält­nisse an­ge­passt Da­tum ge­braucht. Schlie­ßen & la­den oder Schlie­ßen & la­den in…, um die Ab­fra­ge in ein Ex­cel Arbeits­blatt zu spe­ich­ern.

▲ nach oben …

Mög­lich­keit 2

Die­se Mög­lich­keit ist vielle­icht et­was „schlan­ker”, muss deswe­gen aber nicht bess­er sein. Sie wer­den die Dat­en natür­lich vom Excel-Arbeits­blatt in den Pow­er Que­ry-Edi­tor im­por­tie­ren. Auch die Kon­vertierung die­ser Spal­te in den Daten­typ Text ist zum Vorge­hen der ers­ten Mög­lich­keit iden­tisch. Die Uhr­zeit bleibt hier aber erhal­ten, ob­wohl das Ziel auch hier das rei­ne Da­tum ist. Das wird in der fol­gen­den For­mel be­dacht.

Nun wer­den sie wie­der eine Be­nut­zer­de­fi­nier­te Spal­te er­stel­len und als Neu­er Spal­tenname natür­lich wie­der Da­tum schrei­ben. Die hier ver­wen­dete For­mel stellt sich so dar:

= if Text.Contains([Spalte1], ".")
then DateTime.From([Spalte1])
else DateTime.From([Spalte1], "us-US")

Der ei­gent­li­che Unter­schied zur vor­her auf­ge­zeig­ten For­mel ist, dass hier die Funk­tio­nen Date­Time und nicht Date ver­wen­det wur­de. Und das Ergeb­nis ist na­tur­ge­mäß auch vom Typ Da­tum und Zeit:

Die Rück­gabe der For­mel ist vom Typ Da­tum und Zeit

Wech­sel zum Reg­is­ter Trans­formieren, Grup­pe Aus Da­tum & Uhr­zeit | Da­tum | Nur Da­tum oder Recht­sklick in die Über­schrift und dort Trans­formieren | Nur Da­tum. In je­dem Fall hat die Ergeb­nis-Spalte bere­its den Daten­typ Da­tum, braucht nun also nicht mehr an­ge­passt wer­den. Bleibt nur noch, die ers­te Spal­te mit den Orig­i­nal-Dat­en zu lö­schen und die­se Ab­fra­ge an der Wun­sch­po­si­tion zu spe­ich­ern.

▲ nach oben …

Hat Ih­nen der Bei­trag ge­fal­len?
Er­leich­tert die­ser Bei­trag Ihre Ar­beit?

Dann wür­de ich mich über ei­nen Bei­trag Ihrer­seits z.B. 1,00€ bis 2,00€ freu­en …