HERBERS Excel-Forum - das Archiv

Thema: MM/TT/JJ in TT.MM.JJJJ

MM/TT/JJ in TT.MM.JJJJ
Christian
Hallo,

bitte helft mir. Ich denke, der Betreff sagt ja schon alles aus, wie bekomme ich das umgewandelt?

Habe es mit Zellen Formatieren in Datum, sowie mit PQ -> Typ ändern -> Datum probiert, aber beides hat nicht funktioniert.

Danke
Christian
AW: MM/TT/JJ in TT.MM.JJJJ
Onur
Hast du das Ganze mal als Datei vorliegen???
AW: MM/TT/JJ in TT.MM.JJJJ
Eifeljoi 5
Hallo

Dafür gibt es mehrere Wege für das in PQ durch zuführen.
Hier nur der einfachste Weg:
A) Datumspalte aufteilen in drei Spalten.
B) Abschließend wieder zusammenführen, aber in der Reihenfolge Tag , Monat, Jahr.
AW: MM/TT/JJ in TT.MM.JJJJ
daniel
Hi

Wenn es Text ist, führe mit der Spalte:
DATEN - Datentools - Duplikate-Entfernen
aus.
Klicke im Schritt 3 des Assistenten das Format "Datum" und wähle MTJ.

Gruß Daniel
Du könntest es in...
Case
Moin, :-)

... Power Query so probieren: ;-)
https://www.herber.de/bbs/user/170768.xlsx

In PQ im "Erweiterten Editor" siehst du zwischen den Kommentarzeichen die einzelnen Schritte. Das mit der einen Zeile ist nur für mich (Gehirnjogging fürs Alter). ;-)

Im Prinzip musst/kannst du mit "Text.Middle, Text.Start und Text.End" spielen. ;-)

Servus
Case
AW: Du könntest es in...
Eifeljoi 5
Hallo

@ Case
Eine super Lösung, diese ist besser als meine, ich habe absichtlich die leichte Version nur genommen.
Ich weiß nicht ob du es schon gelesen hast im Nachbar Forum, unsere vorgestellten Lösungen wie wir sie machen in 1 Schritt zupacken, wären"......idiotische Idee.....".
Wie schon mal gesagt eine Lachnummer sonders gleichen, und mittlerweile haben sie auch öffentlich zugegeben das sie auch Schritte zusammen fassen.
Desweitern habe ich öfter PQ-Lösungen im Kopf, aber manchmal weigere ich diese zu posten weil die TE's auf gut deutsch gesagt einfach zu faul sind für eine Beispieldatei und Wunschergebnis bereit zu stellen, so das die Helfer es um ein vielfaches leichter hätten es zu testen und ohne Nacharbeit eine funktionsfähige Lösung zu liefern.


@Christian
Warum multiplizieren bzw. addieren??
AW: MM/TT/JJ in TT.MM.JJJJ
daniel
Hi
noch ne elegante Formellösung für Excel 365. hier ist es egal. ob die Werte mit oder ohne führende 0 geschrieben sind, auch die Jahreszahl darf 2- oder 4-Stellig sein 
=--WECHSELN(TEXTNACH(A1;"/");"/";"."&TEXTVOR(A1;"/")&".")


für ältere Excelversionen auch so, hier muss aber der Text immer gleich lang sein (also mit führenden Nullen, Jahreszahl immer gleich, da über Postionen ausgelesen wird. 
=--WECHSELN(RECHTS(A1;5);"/";"."&LINKS(A1;2)&".")


Gruß Daniel

Testergebnisse
Christian
@ Eifeljoi, wie gesagt, erster Versuch gescheitert, wegen der 1900, 2000 Unterscheidung

@Daniel, erster Versuch gescheitert wegen Dupliate Entfernen
mit Text in Spalten, egal ob ich TMJ oder MJT auswähle, es wird in beiden Fällen, wenn ich 06/15/86 aufteilen will von links nach rechts in 6, 15 und 86 aufgeteilt. Wobei das ja der Datumsformel sowieso egal ist ich muss nur die Reihenfolge anpassen, jedenfalls mit dem Aufteilen und der Datumsformel funktioniert es.
Deine beiden weiteren Formelvorschläge funktionieren.

@ Onur, erster Versuch gescheitert, weil Datum als Text vorlag
die Formel =LET(d;$A$1;t;TEIL(d;4;2);j;RECHTS(d;4);m;LINKS(d;2);DATUM(j;m;t)) gibt #Zahl! aus, wenn in A1 06/15/86 steht.

@ Edgar, deine beiden Vorschläge funktionieren, die Zeitspanne war absichtlich so gewählt weil Daten vor 1910 deutlich wahrscheinlicher sind als nach 2010.

@Case, abgesehen davon dass ich beim Öffnen der Abfrage im PQ Editor eine Kompatibilitätswarnung bekommen habe, ist auch das eine funktionierende Lösung.

Jetzt habe ich die Qual der Wahl, welche Lösung ich nehme, werde da wohl nochmal eine Nacht drüber schlafen müssen. Auf jeden Fall euch allen vielen Dank.

Christian
AW: MM/TT/JJ in TT.MM.JJJJ
Uduuh
Hallo,
=LET(x;--TEXTTEILEN(E1;"/");DATUM(INDEX(x;3)+2000*(INDEX(x;3)<10);INDEX(x;1);INDEX(x;2)))

Gruß aus'm Pott
Udo
AW: MM/TT/JJ in TT.MM.JJJJ
Christian
Hallo Udo,

was soll ich sagen, funktioniert einwandfrei.
Danke
Christian
AW: Testergebnisse
Onur
Sorry, ich habe überlesen, dass ja das Format JJ war und nicht JJJJ.
So läuft es: 
=LET(d;$A$1;t;TEIL(d;4;2);j;RECHTS(d;2);m;LINKS(d;2);DATUM(j;m;t))
AW: Testergebnisse
BoskoBiati2
Hi,

danke für die Infos.

Gruß

Edgar
AW: Testergebnisse
Christian
Hallo Onur,

jetzt ist die Qual der Wahl noch größer :-), funktioniert auch, danke.

Wenn ich noch ein Wort zu deiner Bitte um eine Bsp Datei loswerden darf. Ich muss zugegeben, ich dachte zu Beginn mein Anliegen wäre so trivial, dass mir jemand nur sagen braucht, das geht so und so und damit ist die Sache abgeschlossen.

Ich habe mich deshalb anstatt eine Bsp Datei zu erstellen, parallel zu der Anfrage in diesem Forum selbst um eine Lösung bemüht, ich will ja auch was lernen und ich hab mir gedacht, das kann ich ja auch in dem ich meinen mit euren Vorschlägen vergleiche.

Als ich die eigene Lösung dann hatte, dachte ich mir, die Datei ist unnötig, da Problem gelöst.

Nächstes mal schicke ich eine mit.

Gruß
Christian
kleiner Nachtrag
Christian
... wobei mir das Problem mit dem Jahrhunderwechsel erst bei meinen eigenen Formelbemühungen aufgefallen war, das hatte ich beim Erstellen der Anfrage noch nicht auf dem Schirm
AW: Testergebnisse
Onur
MIT Beispieldatei hätte man sich unnötige Arbeit und Herumfragerei sparen können, weil man dann sofort sehen könnte, WIE das Datum vorliegt - als Text oder tatsächlich als Datum.

Wie ich geahnt hatte, war es ja gar kein Datum, sondern nur TEXT, das aussieht wie Datum.
Aber diese Frage hast du ja erst zum Schluss beantwortet.
AW: MM/TT/JJ in TT.MM.JJJJ
Christian
Hallo ihr 4,

was hat denn meine Bitte mit Duplikate entfernen zu tun?



Naja, habe jetzt mir eine hoffentlich funktionierende Formel zusammengebastelt:

Datum in E1, und Zeitraum zwischen 1910 und 2009:

=TEXT(DATUM(WENN(TEIL(E1;7;2)*1<10;2000+TEIL(E1;7;2);1900+TEIL(E1;7;2));LINKS(E1;2);TEIL(E1;4;2));"TT.MM.JJJJ")

Danke trotzdem für eure Hilfe
Christian

an Eifeljoi, Spalte trennen und wieder zusammenführen habe ich ja noch geschafft, aber die Unterscheidung bei welchen Daten ich 1900 und bei welchen ich 2000 addiere habe ich dann nicht mehr hinbekommen.
AW: MM/TT/JJ in TT.MM.JJJJ
Onur
=TEXT(DATUM(WENN(TEIL(E1;7;2)*1<10;2000+TEIL(E1;7;2);1900+TEIL(E1;7;2));LINKS(E1;2);TEIL(E1;4;2));"TT.MM.JJJJ")

Soooo kompliziert?

Genau deswegen wollte ich eine Datei.

Guckst du hier:

https://www.herber.de/bbs/user/170760.xlsx
AW: MM/TT/JJ in TT.MM.JJJJ
BoskoBiati2
Hi,

Hi,

wenn es mit Deiner Formel klappt, dann ginge es auch kürzer: 

=TEIL(E1;4;2)&"."&LINKS(E1;2)&"."&19+(--RECHTS(E1;2)<10)&RECHTS(E1;2)


oder als echtes Datum: 

=DATUM(19+(--RECHTS(E1;2)<10)&RECHTS(E1;2);LINKS(E1;2);TEIL(E1;4;2))


Ich weiß nicht, nach welchen Kriterien Du die Werte vor/nach 2000 einordnen willst, aber ich würde Alles, was größer als 25 ist, zu 1900 zählen, den Rest zu 2000: 

=TEIL(E1;4;2)&"."&LINKS(E1;2)&"."&19+(--RECHTS(E1;2)<25)&RECHTS(E1;2)

=DATUM(19+(--RECHTS(E1;2)<25)&RECHTS(E1;2);LINKS(E1;2);TEIL(E1;4;2))


Gruß

Edgar
AW: MM/TT/JJ in TT.MM.JJJJ
daniel
Sorry, meinte Text in Spalten
Datwert funktioniert nicht
Christian
z.b., bei 06/18/77 alias 18.06.1977, funktioniert nur wenn Tag kleiner gleich 12
ok ich kann noch...
Christian 
=WENNFEHLER(TEXT(DATUM(WENN(TEIL(E1;7;2)*1<10;2000;1900)+TEIL(E1;7;2);LINKS(E1;2);TEIL(E1;4;2));"TT.MM.JJJJ");"")


daraus machen, aber für noch weitere Vereinfachungen ist dann mein Excel Latein am Ende
AW: ok ich kann noch...
Onur
Wenn das Datum als tatsächlich Text vorliegt (wenn du Format auf "Standard" änderst und es wird KEINE Zahl wie 45481 daraus) : 
=LET(d;$A$8;t;TEIL(d;4;2);j;RECHTS(d;4);m;LINKS(d;2);DATUM(j;m;t))

Wenn das Datum wirklich ein Datum ist, einfach nur als Datum formatieren.