Anzeige
Archiv - Navigation
1792to1796
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Daten umwandeln

Daten umwandeln
14.11.2020 17:01:33
Christian
https://www.herber.de/bbs/user/141559.xlsx
Hallo,
ich bitte um eure Hilfe.
Mein Wunsch wäre, die Daten im Blatt Leute Spalte I in das Format TT-MM-JJJJ zu bringen.
Ich weiß zwar, wie ich das per Powerquery umwandele aber auch da wären sehr viele offene Fragen, z.b. wie lege ich fest, dass die Ausgabe in dieser bestimmten Spalte erfolgen soll, oder wie verbinde ich die Umwandlung mit PQ mit dem SVERWEIS.
Eigentlich wünsche ich mir eine Formellösung, auch deshalb weil sie bei Änderungen am Blatt ANC nicht vorraussetzt, dass ich die PQ Abfrage aktualisiere, bevor sich die Änderungen auch auf das Blatt Leute auswirkt.
Was würdet ihr mir hier raten?
Die ganzen Lücken in den beiden Blättern kommen daher, dass ich alles was nicht für die Fragestellung relevant ist weggelassen habe.
Excel 2019, kein Office365.
Danke
Christian

39
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: welche Excelsprachversion hast Du? owT
14.11.2020 17:18:09
neopa
Gruß Werner
.. , - ...
AW: welche Excelsprachversion hast Du? owT
14.11.2020 17:19:16
Christian
wenn du die Menüsprache meinst, deutsch
AW: dann z.B. eine Möglichkeit ...
14.11.2020 17:36:20
neopa
Hallo Christian,
... mit folgender Formel:
=WENN(I2="";"";--(WECHSELN(TEIL(I2;FINDEN(" ";I2);2);",";"")+1&"."&SUCHEN(TEIL(I2;2;2);{"xanebarprayunulugepctovec"})/2&"."&RECHTS(I2;4)))
und nach unten kopieren.
Gruß Werner
.. , - ...
AW: dann z.B. eine Möglichkeit ...
14.11.2020 17:51:39
Christian
Hallo Werner,
irgendwie nicht so ganz, so leid es mir tut.
Bsp.
I2 = November 21, 2000
deine Formel: =WENN(I2="";"";--(WECHSELN(TEIL(I2;FINDEN(" ";I2);2);",";"")+1&"."&SUCHEN(TEIL(I2;2;2);{"xanebarprayunulugepctovec"})/2&"."&RECHTS(I2;4))) = 03.11.2000
anderes Beispiel
I6 = March 27, 2000
deine Formel: 03.03.2000
mit dem Tag scheint etwas nicht zu stimmen.
Monat und Jahr scheint, soweit ich es überblicken kann zu passen.
Außerdem, auch wenn das ganze sicherlich eine aufwändige Formel wird, lassen sich meine SVERWEIS Formel und deine auch zu einer machen?
Danke
Christian
Anzeige
Nachtrag
14.11.2020 17:54:18
Christian
sorry habe die Sortierung des Blatts Leute zwischenzeitlich geändert, aber da es ja den SVERWEIS gibt, sollte das ja kein Problem sein.
Verstehe Diskussion und Aufwand nicht!
14.11.2020 19:24:19
Luc:?
Das ist doch wieder ein ganz einfacher Anwendungsfall von TEXT mit internationaler Datumskennung, Leute;
das wurde ja nun schon oft genug vorgestellt!
Außerdem würde ich nicht die Fml im Blatt Leute ändern, sondern direkt an der Quelle, auf dem Blatt ANC ansetzen und eine Übersetzungsspalte mit singularer MatrixFml anfügen:
F3ff: {=WENN(E3="";"";--(RECHTS(E3;4)&"-"&VERGLEICH(LINKS(E3;SUCHEN(" ";E3)-1);
TEXT(DATUM(;ZEILE(1:12);1);"[$-409]MMMM");0)&"-"&LINKS(RECHTS(E3;8);2)))}
Dann kann die Fml auf Blatt Leute im Prinzip so bleiben:
=WENNFEHLER(WENN(SVERWEIS(B2;ANC!C:F;4;FALSCH)=0;#NV;SVERWEIS(B2;ANC!C:F;4;FALSCH));"")
Und als ZellFormat dann das angeben: TT-MM-JJJJ
Gruß + schöWE, Luc :-?
„Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder.“ Stapps ironisches Paradoxon
Anzeige
AW: damit hatte ich schon eher gerechnet, ...
14.11.2020 19:58:11
neopa
Hallo Luc,
... dass ein derartiger Hinweis kommt. Deshalb hatte ich bei meinem zuerst getätigten Formelvorschlag bewusst geschrieben: " ...z.B. eine Möglichkeit..."
Dazu aber noch folgende Anmerkungen:
- ich kann mir den Code für die englische Datumskennung nicht merken
- und bevor ich lange suche (worin ich nun nie gut war), konstruiere ich mir lieber meine Formellösung selbst, ..
- ... zumal mir persönlich das mehr Erbauung gibt.
Und wenn Du meinen zuerst eingestellten Formellösungsvorschlag analog Deiner Formel auch in ANC als Hilfsspaltenlösung einsetzt und die beiden Formeln verglichst, dann ist meine nicht nur kürzer sondern kommt mit 3 Funktionen weniger und auch ohne Matrixformelabschluss aus. Sie lediglich für einen weniger erfahrenen Exceluser möglicherweise weniger verständlich als Deine.
Gruß Werner
.. , - ...
Anzeige
AW: damit hatte ich schon eher gerechnet, ...
14.11.2020 20:20:50
Christian
Hallo Werner,
soviel Erfahrung habe ich noch, dass ich mit Matrixformeln klarkomme.
ZU deiner Lösung, ich denke, abgesehen davon dass ich nicht weiß was die beiden Minus sollen, kann ich auch nachvollziehen was da passiert. Aber das selbst zu konstruieren, wäre ich überfordert gewesen.
Du hast aber recht, Lucs Formel, insbesondere das $-409 ist für mich absolut unmöglich nachzuvollziehen.
Ich habe in meiner Antwort zu Lucs Beitrag dargelegt, weshalb ich deine Lösung bevorzuge.
Gruß
Christian
AW: hierzu ...
15.11.2020 08:58:00
neopa
Hallo Christin,
... das: -- =-1*-1 und wird deshalb auch oft als "doppelte Negation" bezeichnet und wandelt durch diese mathematische Operation Textwerte bestehend aus Ziffernketten (mit oder ohne Komma) in einen Zahlenwert. Anstelle -- kann das Gleiche durch eine Multiplikation mit 1 oder durch eine Addition einer 0 herbeigeführt werden. Teste es einfach mal selbst.
Gruß Werner
.. , - ...
Anzeige
AW: hierzu ...
15.11.2020 14:04:36
Christian
wieder was dazugelernt. Danke
AW: gerne owT
15.11.2020 19:40:17
neopa
Gruß Werner
.. , - ...
Es war ja nun genau umgekehrt, ...
18.11.2020 01:57:20
Luc:?
…Werner,
und Deine Fml war ihm verständlicher… ;-)
Morhn, Luc :-?
AW: Verstehe Diskussion und Aufwand nicht!
14.11.2020 20:16:24
Christian
Hallo Luc,
danke auch für deine Mühe.
Ich gebe zu, ich habe das in dieser Deutlichkeit nicht erwähnt, aber ich wünsche mir das Ergebnis an der Zentralen stelle, wo ich es brauche, d.h. Leute Spalte I.
Ich kenne mich, ich habe irgendwann alle Hilfsspalten aus meinen Mappen entfernt weil ich nach Jahren nicht mehr wusste, für was sie gut waren und ich mir das habe durch sich nochmal in alle Formeln hineinversetzen wieder erarbeiten müssen.
Daher hatte ich das Ganze zu einer Formel zusammenlegen wollen.
Aber nun zu deinen Formeln, die erste funktioniert leider nicht, sie gibt in ANC Zeile 11 den 14.8.96 statt den 14.4.96 aus.
Auch dir ein schönes Wochenende
Christian
Anzeige
Korrektur:
14.11.2020 20:19:26
Luc:?
Es muss natürlich ZEILE($1:$12), also absolut statt relativ adressiert heißen.
Luc :-?
AW: Korrektur:
14.11.2020 20:23:44
Christian
Hallo Luc,
jetzt habe ich auch die andere Formel getestet und kann nur sagen, es funktionieren beide.
Weshalb ich trotzdem Werners Vorschlag bevorzuge hatte ich ja bereits geschrieben.
Danke trotzdem für deine Mühe und Schönes Wochenende
Christian
Das m.d.Hilfssp. ist kein treffendes Argument, …
17.11.2020 01:07:02
Luc:?
…Christian,
denn die könnte man auch in den 2.Zellen eines 2spaltig-2zelligen Verbunds unterbringen, so dass sie nicht mal sichtbar, aber dafür vor Löschen bzw Überschreiben geschützt wäre. Alternativ könnte man 2 Teile eines geeigneten FmlTextes, der die QuellZelle nur 1× direkt anspricht, per VBA-Prozedur um die OriginalTexte „herumlegen“, so dass diese erhalten bleiben und bei Bedarf wiederhergestellt wdn könnten.
Aber natürlich kann man das auch bei der SVERWEIS-Fml machen, nur sollte die dann nur 1× in der Fml auftreten. Die bisherigen Vorschläge beziehen sich ja quasi auch auf eine Hilfszelle, die den SVERWEIS enthält, nur steht die schon auf dem Blatt Leute. Alternativ kann man der SVERWEIS-Fml aber auch per Namensmanager einen definierten Namen* geben, den man dann auch mehrfach in der ZellFml verwenden könnte. Eine solche Fml (hier: PrinzipLösung mit 2maligem Einsatz einer UDF → kann aber auch auf eine andere Fml angewendet wdn!) kann so aussehen:
{=WENNFEHLER(--WECHSELN(MaskOn(ANCverweis;"num");" ";-VERGLEICH(MaskOn(ANCverweis;"bst"); TEXT(1&-SPALTE(A:L)&-20;"[$-409]MMMM");0)&"-");"") }
ANCverweis: =WENNFEHLER(WENN(SVERWEIS(B2;ANC!C:E;3;FALSCH)=0;#NV;SVERWEIS(B2;ANC!C:E;3;FALSCH));"")
MaskOn-Link: https://www.herber.de/forum/archiv/1344to1348/1344962_Initialen_bei_Doppelnamen.html#1345181
Und natürlich ist Unkenntnis der Xl-LänderCodes für Datumsformate auch kein relevantes Argument. Allerdings sind die auch schwer zu finden.
* Geht wahrscheinlich auch mit der neuen xlFkt LET (bzw dt Gott_SEI_bei_uns).
Gruß, Luc :-?
Anzeige
AW: die +1 war falsch "platziert" ...
14.11.2020 18:22:08
neopa
Hallo Christian,
... sorry, richtig sollte es so lauten:
=WENN(I2="";"";--(WECHSELN(TEIL(I2;FINDEN(" ";I2)+1;2);",";"")&"."&SUCHEN(TEIL(I2;2;2); {"xanebarprayunulugepctovec"})/2&"."&RECHTS(I2;4)))
Gruß Werner
.. , - ...
AW: die +1 war falsch "platziert" ...
14.11.2020 18:28:36
Christian
Hallo Werner,
alles kein Problem, kann passieren.
So funktioniert es.
Jetzt muss ich es nur noch irgendwie schaffen, die beiden Formeln zu vereinen.
Danke
Christian
ich habs jetzt probiert mit
14.11.2020 18:34:28
Christian

=WENNFEHLER(WENN(SVERWEIS(B2;ANC!C:E;3;FALSCH)=0;"";WECHSELN(TEIL(SVERWEIS(B2;ANC!C:E;3;FALSCH); _
FINDEN(" ";SVERWEIS(B2;ANC!C:E;3;FALSCH))+1;2;".";"")&"."&SUCHEN(TEIL(SVERWEIS(B2;ANC!C:E;3;FALSCH);2;2);{"xanebarprayunulugepctovec"})/2&"."&RECHTS(SVERWEIS(B2;ANC!C:E;3;FALSCH);4)));"")

aber ich hätte zu viele Argumente eingegeben, wo ist der Fehler?
Anzeige
AW: dazu verwende nur den maßgebl. Teil Deiner...
14.11.2020 18:44:16
neopa
Hallo Christian,
in I2:
=WENNFEHLER(--(WECHSELN(TEIL(SVERWEIS(B2;ANC!C:E;3;0);FINDEN(" ";SVERWEIS(B2;ANC!C:E;3;0))+1;2);",";"")&"."&SUCHEN(TEIL(SVERWEIS(B2;ANC!C:E;3;0);2;2);{"xanebarprayunulugepctovec"})/2&"."&RECHTS(SVERWEIS(B2;ANC!C:E;3;0);4));"")
Gruß Werner
.. , - ...
AW: dazu verwende nur den maßgebl. Teil Deiner...
14.11.2020 18:47:17
Christian
Hallo Werner,
vielen Dank.
Entnehme ich deiner Formel richtig, dass die WENNFEHLER Formel die Prüfung =0 am Anfang abdeckt und damit die Prüfung überflüssig war?
Danke
Christian
AW: zusätzl. Überprüfung auf 0-Ergebniswerte ...
14.11.2020 19:07:13
neopa
Hallo Christian,
... ist hier nicht notwendig, weil solche nicht entstehen können sondern es entstehen durch die Formel Textwertwandlung in einen Datumswert entweder solche oder Fehlerwerte und für die Fehlerwerte kommt dann eben WENNFEHLER() zum Zuge.
Gruß Werner
.. , - ...
Anzeige
AW: zusätzl. Überprüfung auf 0-Ergebniswerte ...
14.11.2020 20:08:29
Christian
Hallo Werner,
danke für die Erklärung und die Mühe.
Schönes Wochenende
Christian
AW: bitteschön owT
15.11.2020 08:58:25
neopa
Gruß Werner
.. , - ...
AW: die +1 war falsch "platziert" ...
14.11.2020 21:01:10
Niclaus
Hallo Werner
Ich habe zuerst gedacht, Du machst einen Witz mit Deinem "xanebarprayunulugepctovec".
Hat lange gedauert, bis ich's durchschaut habe!
Grüsse Niclaus
AW: prima owT
15.11.2020 08:59:05
neopa
Gruß Werner
.. , - ...
AW: Daten umwandeln noch ne Variante.
14.11.2020 23:26:55
Daniel
Hi
Für Zeile 7:
=DATUM(RECHTS(I7;4);FINDEN(LINKS(I7;3);"xxJanFebMarAprMaxJunJulAugSepOctNovDec")/3;LINKS(RECHTS(I7;8) ;2))
Gruß Daniel
Wer ist Max?! ;-) owT
15.11.2020 03:33:26
Luc:?
:-?
AW: Wer ist Max?! ;-) owT
15.11.2020 03:45:24
Daniel
Sehr gut.
Du hast diesen Fehler schnell gefunden.
Wäre er dir in der Version von Werner genauso schnell aufgefallen?
Anzeige
Vielleicht, sicher bei 1.Nachprüfung! owT
15.11.2020 05:06:50
Luc:?
:-?
Hier sofort, ohne Nachprüfung
15.11.2020 13:33:51
Daniel
;-)
Nee, fiel mir beim Ausprobieren auf, ...
15.11.2020 16:14:06
Luc:?
…Daniel,
die Mai-Datumswerte fehlten.
Luc :-?
AW: Daten umwandeln noch ne Variante.
15.11.2020 06:30:06
Christian
Hallo Daniel,
ha daraus noch

=WENNFEHLER(DATUM(RECHTS(SVERWEIS(B2;ANC!C:E;3;0);4);FINDEN(LINKS(SVERWEIS(B2;ANC!C:E;3;0);3); "xxJanFebMarAprMayJunJulAugSepOctNovDec")/3;LINKS(RECHTS(SVERWEIS(B2;ANC!C:E;3;0);8);2));"")  
gemacht. Klappt.
Christian
12 Buchstaben weniger
15.11.2020 07:25:09
WF
Hi,
=DATUM(RECHTS(I7;4);FINDEN(TEIL(I7;2;2);"#anebarprayunulugepctovec")/2;LINKS(RECHTS(I7;8);2))
WF
AW: 12 Buchstaben weniger
15.11.2020 13:40:20
Daniel
Dafür versteht jeder sofort, was Sache ist, ohne groß drüber nachzudenken.
Außerdem gehe ich jede Wette ein, dass die Mehrheit die 12 Zeichen mehr schneller tippen kann.
Also wo ist der Vorteil?
ach Gottchen
15.11.2020 13:42:32
WF
.
Was besseres fällt dir nicht ein?
15.11.2020 13:47:56
Daniel
...
AW: Daten umwandeln noch ne Variante.
15.11.2020 14:12:00
Christian
also ich finde das teilweise schon lustig, welche Diskussionen hier so entstehen.
Aber versetzt euch doch mal in meine Lage. Ich schreibe ja eigentlich nicht um "50" Lösungen zu bekommen, sondern, um mit einer funktionierenden Lösung weiterarbeiten zu können. Jetzt in diesem Fall war das nicht so, aber ich hatte auch schon oft hier Dinge gefragt, die ich nur temporär benötigt habe, z.B. für Fehlersuche, bei denen ich dann direkt mit der ersten Lösung die Fehler gefunden und beseitigt hatte und ich dann Schwierigkeiten hatte die anderen Ansätze zu testen, weil die möglichen Fehlerquellen, die noch in der Beispieldatei vorhanden waren, inzwischen behoben wurden.
Nicht falsch verstehen, ich bin trotzdem danbar für jeden, der sich die Zeit nimmt und die Mühe macht, mir zu helfen.
Gruß
Christian
AW: Daten umwandeln noch ne Variante.
15.11.2020 16:33:29
Christian
also ich finde das teilweise schon lustig, welche Diskussionen hier so entstehen.
Aber versetzt euch doch mal in meine Lage. Ich schreibe ja eigentlich nicht um "50" Lösungen zu bekommen, sondern, um mit einer funktionierenden Lösung weiterarbeiten zu können. Jetzt in diesem Fall war das nicht so, aber ich hatte auch schon oft hier Dinge gefragt, die ich nur temporär benötigt habe, z.B. für Fehlersuche, bei denen ich dann direkt mit der ersten Lösung die Fehler gefunden und beseitigt hatte und ich dann Schwierigkeiten hatte die anderen Ansätze zu testen, weil die möglichen Fehlerquellen, die noch in der Beispieldatei vorhanden waren, inzwischen behoben wurden.
Nicht falsch verstehen, ich bin trotzdem danbar für jeden, der sich die Zeit nimmt und die Mühe macht, mir zu helfen.
Gruß
Christian

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige