Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1688to1692
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 ins Format TT.MM.JJJJ bringen

Daten ins Format TT.MM.JJJJ bringen
05.05.2019 16:18:40
Jenny
Hallo, ich bin am verzweifeln
ich habe eine Tabelle, in der Daten in 6 verschiedenen Formaten stehen, für jede Möglichkeit habe ich ein Beispiel gebracht (Spalte A)
Was ich auch habe ist für jeden einzelnen Fall, eine Formel die das Datum auch umzuwandelt (falls man die doch teilweise recht komplizierten Formeln vereinfachen kann, gerne her damit). (Spalte C).
Was mir jetzt fehlt, ist eine Formel die erkennt, welcher der 6 Fälle vorliegt und entsprechend umwandelt, da brauche ich jetzt eure Hilfe. Sodass ich nur noch eine Formel in die ganze Spalte kopieren brauche, egal welcher Fall vorliegt.
Wenn jemand jetzt merkt, hey Jenny hat doch 7 Beispiele genannt, Zeile 7 ist nur dafür da zu verdeutlichen, dass die in Zeile 6 eingesetzte UDF so funktioniert, wie sie soll.
Wäre schön, wenn da jemand eine Formel einfällt, die alle 6 Fälle umwandeln kann.
Genauere Erklärungen stehen in der Tabelle
https://www.herber.de/bbs/user/129600.xlsm
Danke
Jenny

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Daten ins Format TT.MM.JJJJ bringen
05.05.2019 16:46:02
Günther
Moin Jenny,
das könnte sich mit Power Query lösen lassen. Da ist in Sachen Datum und so eine ganze Menge machbar.
Da ich aus dem Netz keine VBA-verdächtigen File herunterlade, kann ich das natürlich nicht prüfen…
Gruß
Günther
AW: Daten ins Format TT.MM.JJJJ bringen
05.05.2019 17:56:24
Jenny
Hallo Günther,
ich kann es dir auch ohne VBA schicken, da fehlen dann halt ein Ergebnisse, weil die UDF nicht zur Verfügung steht.
https://www.herber.de/bbs/user/129602.xlsx
AW: Daten ins Format TT.MM.JJJJ bringen
05.05.2019 19:56:15
Günther
So Jenny,
prinzipiell geht es mit Power Query. Zumindest habe ich wohl deine Wunsch-Ergebnisse hinbekommen. ;-)
Aber so ganz ohne eine bittere Pille ist das nicht machbar …
Punkt 1: DEINE Wunschberechnung der Tage vor 1900 differiert um 1 Tag zum Datenbank-Standard; Das ist der Tatsache geschuldet, dass Excel immer noch den nicht existierenden 29.2.1900 "kennt".
Punkt 2: Um das halbwegs automatisch hinzukriegen habe ich die Vor-1900-Werte als Text formatiert, dann wird wenigstens bei den neueren Daten das kalendarische Datum wie gewohnt angezeigt.
Dass du Power Query brauchst, wird dir gewiss klar sein. Und wegen der Ausnahmen und so ausnahmsweise mit etwas Programmierung in der Sprache M. Die einzelnen Schritte kannst du im rechten Seitenfenster nachsehen. Die Erklärung wäre jetzt wohl ähnlich aufwendig wie die Erarbeitung. ;-)
Muster-Lösung auf Nachfrage -> per PM/E-Mail oder in meiner Cloud, da ich meine Ordner-Struktur nicht wegen eines Forums umstelle ... => https://my.hidrive.com/lnk/jpgGDOrB
Gruß
Günther
Anzeige
AW: Daten ins Format TT.MM.JJJJ bringen
05.05.2019 20:13:57
Jenny
Hallo Günther,
das sieht ja schonmal super aus, aber wie bekomme ich das jetzt in meine Tabelle mit 26861 Zeilen übertagen?
Danke
Jenny
AW: Daten ins Format TT.MM.JJJJ bringen
05.05.2019 20:24:11
Günther
Moin Jenny,
wenn die Tabelle WIRKLICH den gleichen Aufbau hat: Einfach die Daten in meiner Datei durch deine ersetzen und dann in meiner (noch kleinen Tabelle) Rechtsklick-Aktualisieren.
Gruß
Günther
AW: Daten ins Format TT.MM.JJJJ bringen
05.05.2019 20:30:07
Jenny
nicht so ganz ich muss gestehen im Original stehen die Daten in Spalte E.
Aber noch was, ich hab richtig in Erinnerung, PQ aktualisiert nicht automatisch, sobald ich in dieser Spalte ein neues Datum einfüge, oder?
Aber zumindest mal eine gute Nachrictht, soweit ich es überblicken kann funktioniert es mit dem umwandeln.
Danke
Jenny
Anzeige
Nachtrag
05.05.2019 20:37:28
Jenny
und die Ausgabe hätte in Spalte F sein sollen.
AW: Nachtrag
05.05.2019 20:41:46
Günther
Wenn du nur die Spalte mit den kalendarischen Daten in den PQ-Editor importierst (und die Schritte in Sachen Spalten löschen entsprechend anpasst) dann kannst du problemlos bei "Schließen & laden in…" die Position bestimmen, wo die Ergebnistabelle gespeichert werden soll. Oder du schneidest die Tabelle komplett aus und schiebst diese an die Wunschposition. Daran wird sich nach Neuberechnung nichts ändern.
Gruß
Günther
AW: Nachtrag
05.05.2019 20:51:55
Jenny
und wie mache ich Spalte E zur Ausgangsspalte für die Daten?
Das mit dem Löschen der Spalte hat geklappt, jetzt stehen die Ergebnisse in Spalte F
Anzeige
AW: Nachtrag
05.05.2019 21:31:00
Jenny
gut Günther soweit hab ichs jetzt dass die Quelldaten in Spalte E stehen und die Zieldaten in Spalte J. Allerdings wenn ich die 4 Spalten dazwischen lösche, sagt man mir beim ajtualisieren, dass Spalte 3 nicht gefunden wird.
Versuche ich es, in den Editor zu laden, wird mir dort nur eine Spalte angezeigt.
Jetzt wollte ich es nochmal hochladen, kann aber keine Zeilen löschen und komme nicht unter die 300 KB Grenze.
Anzeige
AW: Nachtrag
05.05.2019 22:12:51
Günther
Hier gibt es diese Grenze nicht (meine eigene Homepage):
G.Mumme@PC-Hilfe-Nord.de
Gruß
Günther
AW: Nachtrag
05.05.2019 22:29:34
Jenny
ok, aber ich schicke es dir morgen, muss um halb 6 aufstehen.
Danke schonmal für die viele Mühe.
AW: Nachtrag
06.05.2019 16:13:23
Jenny
Hallo Günther,
hab dir eine email geschickt
Danke
Jenny
Ich hatte dir ebenfalls geantwortet! :-| owT
06.05.2019 17:44:01
Luc:-?
:-?
AW: Ich hatte dir ebenfalls geantwortet! :-| owT
06.05.2019 17:55:04
Jenny
Hallo Luc,
du möchtest mir also sagen, Excel meint, es gäbe einen 29.2.1900, den es aber im Kalender nicht gab.
Jetzt will ich mit Datedif rechnen, es gibt in meiner Tabelle keine Daten zwischen dem 1.1.1900 und dem 1.3.1900.
Datedif rechnet korrekt wenn beide Daten nach dem 1.3.1900 liegen
und wenn ich zu meiner UDF 1461000 addiere rechnet er auch mit den negativen Zahlen korrekt, solang beide Daten vor dem 1.1.1900 liegen.
Der Fall dass eine der Daten vor dem 1.1.1900 liegt und das andere nach dem 1.3.1900 kommt exakt einmal in der Tabelle vor von 27000 Zeilen. Hier gebe ich dir recht, wird der 29.2. zuviel gerechnet, aber vom Ergebnis einen Tag abziehen bekomme ich auch hin, ohne mich in deine UDF's reinzudenken. Deshalb arbeite ich bis heute nicht damit.
Gruß
Jenny
Anzeige
Tja, das ist deine Entscheidung, aber VBA ...
06.05.2019 20:33:21
Luc:-?
…rechnet richtig, Jenny,
und irgendwann könntest du (oder dein Nachfolger) mit deinem Flickwerk Probleme bekommen, denn Xl hatte das wohl aus Kompatibi­li­täts­gründen seinerzeit von Lotus 1-2-3 übernommen, andere Calc-Software wie LO/OOcalc folgt der VBA-Zählung, hat aber ein Intervall [-693 961, 11 274 306}, d.h., vom 1.1.0001 bis zum 31.12.32 767 wird das Datum so angezeigt, ab dem nächsten Neujahrstag dann, obwohl der Tageszähler positiv bleibt, so: 01.01.-32768*
* Wie weit das dann dort noch geht, habe ich nicht untersucht, könnte aber bis zum ZahlenLimit reichen und nur eine reine FormatFrage sein.
Es sieht also so aus, als ob man mit VBA auf der sicheren Seite wäre und alles andere irgendwann im Orkus landet.
Und dass du meine UDFs nicht verstehst, liegt an deinem Level, wobei du nicht erwarten kannst, dass eine nicht-triviale UDF für dieses Thema so simpel ist wie deine triviale. Das Programm einer nahezu beliebigen Xl-Standard-Fkt würdest du sicher auch nicht verstehen, wenn es dir denn zugänglich wäre…
Die angeführte Begründung (1.1.-29.2.1900) hattest du damals auch gebraucht; sie ist aber inzwischen keines­wegs rationaler gewor­den (und eine potenzielle FehlerQuelle geblieben!).
Luc :-?
Anzeige
AW: Tja, das ist deine Entscheidung, aber VBA ...
06.05.2019 20:51:20
Jenny
ganz einfach Luc, dann machen wir das so, ich habe ja eine Bsp Tabelle zur Verfügüng gestellt, geb mir eine UDF die sowohl meine Wünsche beinhaltet, was alles umgewandelt werden soll als auch deine UDF, die ja offensichtlich genauer rechnet als meine, sodass ich für dieganze Tabelle nur noch eine einzige UDF brauche, dann gebe ich dir recht, dass dein Vorschlag nicht nur sinnvoller sondern auch praktikabler ist. Aber du hast natürlich recht, ich bin keine erfahrene VBA Programmiererin, ich kann zwar da ich englisch kann ein wenig verstehen was da in so nem Makro passiert, aber selbst eins machen, übersteigt mein Level.
Wenn ich mich recht erinnere, hatte ich aber ursprünglich auch gar nicht nach einem großen Makro oder einer PQ Lösung, sondern nach einer Formel gefragt die meine vorhandenen Formeln, in die ich auch viel Mühe und Zeit investiert habe, vereint. Wohlwissend, dass ich nicht die VBA Kenntnisse habe, die hier von dem ein oder anderen erwartet werden.
Gruß
Jenny
Anzeige
Die UDFs hatte ich dir damals schon gegeben ...
07.05.2019 00:00:44
Luc:-?
…und eine auf dein Bsp passende einheitl Fml unter Verwendung von 3 UDFs (+1 in der anderen Fml) hatte ich dir bereits unten (in dem von dir bisher nicht beachteten Beitrag) gezeigt. Folgst du allen 5 im Beitrag enthaltenen Links, bekommst du alle UDFs und außerdem noch Bspp für die internationale Anwendung des in der Fml enthaltenen Umwandlungsteils.
Viell schaffst du es ja, den Umwandlungsteil ohne die beiden zusätzlichen UDFs hinzubekommen, die gewünschten Ergebnisse aber sicher nur mit Pfriemelei, wenn du die beiden UDFs von vor über 2 Jahren nicht verwendest.
Günther hatte dir ja schon angedeutet, dass PQ auch vom Xl-Datumsintervall abhängig ist. Und eine (einfache) StandardFml ist so kom­plex nicht möglich, wie du ja weißt. Die Alternative wäre eine komplette SubProzedur, falls PQ nicht doch weitergehende Pgmmier­Mög­lich­keiten bietet und man das komplett damit nachgestalten kann. Mal sehen, was Günther zuwege bringt… ;-]
Luc :-?
Anzeige
AW: Die UDFs hatte ich dir damals schon gegeben ...
07.05.2019 02:07:07
Günther
Moin Luc :-? (& Jenny),
normalerweise kann PQ das ganz gut und ich musste extra den einen oder anderen Klimmzug in die Formel einbauen, um auf das (auch aus meiner Sicht falsche aber nun einmal gegebene) Wunschergebnis zu kommen. Da hatte ich mich gewiss nicht so ganz klar ausgedrückt.
Diesen 1 Tag Differenz schmeiße ich gerne wieder raus, allerdings muss ich mir mit dem 31.12.1999 (oder war es doch der 1.1.1900?) etwas einfallen lassen, weil PQ da etwas ins stolpern kommt (vielleicht doch wegen Excel?). Also: der 24.12.1899 würde als Text selbstredend so angezeigt werden (auch in DeutschSprach, voll-Text oder Datum-numerisch) und den numerischen Wert -6 bekommen; SQL-like eben.
Für alle, die selber etwas herumprobieren möchten: Die kalendarischen Daten gerne in der US-Schreibweise "december, 24 1899" eingeben (den Silvester auch), dann als Typ Datum umwandeln und anschließend als Zahl. Und beim Jahresende etwas staunen ... ;-)
Gruß
Günther
Anzeige
Ja, Günther, ...
07.05.2019 15:39:02
Luc:-?
…das würde meine Auffassung stützen, dass die historisch begründete falsche Datumsdarstellung in Xl ein AuslaufModell ist, das auch in M von PQ irgendwie umgehbar ist und dabei das VB(A)-Modell aus praktischen Gründen zugrunde gelegt wird. Das ist ja schon bei LO/OOcalc sichtbar und könnte der Kompromiss sein (bzw wdn), auf dem alle späteren Entwicklungen basieren. Damit läge der Tages­zähler-Null-Wert auf dem 30.12.1899 und von dem aus würde dann auf- bzw abwärts gezählt, wobei dann entweder beim 1.1.0100 (VBA) oder beim 1.1.0001 abwärts bzw beim 31.12.9999 aufwärts Schluss wäre (zumindest für die automatische Erkennung von Text als Datum).
Bei LO/OOcalc ergibt sich so das stabile Intervall [-693961, 2958465], dessen Untergrenze nicht unterschritten wdn kann, ohne dass es zu Darstellungsfehlern kommt*. Allerdings kann die Obergrenze überschritten wdn → {2958466, 11274306}. Die Formatierung dieser Daten erfolgt aber nicht automatisch und ist, obwohl manuell durchführbar, nicht stabil (fällt auf 31.12.9999 zurück). Das gilt dann auch für Tageszähler ab 11274307, bei denen bei manueller Formatierung ein MinusZeichen vor die Jahreszahl gesetzt wird, obwohl das Datum ansonsten richtig dargestellt wird. Es könnte aber sein, dass sich das in den aktuellen LO/OO-Versionen wieder geändert hat.
Natürlich muss man <4-stellige Jahreszahlen sowohl in Xl als auch in LO/OOcalc 4stellig (mit Vornullen) angeben, damit die automa­ti­sche Erkennung richtig fktioniert.
* Allerdings wird Tag/Monat richtig erkannt, nur die Jahreszahl muss korrigiert wdn, was ohnehin erforderlich ist, denn vor dem 1.1.0001 liegt nunmal der 31.12.0001 vuZ, dessen Jahr LO/OOcalc als 0002 darstellt. Hier gäbe es also, mit Korrektur, auch noch weitere Möglichkeiten, die wohl einfacher zu bewerk­stel­ligen wären als mit xlVBA.
Gruß, Luc :-?
oT: Dir zur Info ...
05.05.2019 18:13:42
neopa
Hallo Günther,
... gestern wurde seitens ein automatisches Update zur Verfügung gestellt.
Nach dessen Ausführung, sind sämtliche Funktionsnamen und Optionen in PQ nun auch in meiner XL2016er Version wieder in deutscher Sprachen. Du erinnerst Dich doch sicherlich noch an meinen entsprechenden thread vom Mitte April. Ob dieses Update sowieso gekommen wäre oder ab mein entsprechendes "Feedback an MS" von Anfang dieser Woche da etwas nachgeholfen (wohl eher nicht) hat, weiß ich natürlich nicht. Jedenfalls ist es bei mir nun auch deutschsprachig.
Musste aber schon feststellen, dass wenn jemand ein PQ-Lösung in englischer Sprache in einer Datei aufstellt, das dann diese in der "Anzeige der Schritte" nicht übersetzt wird. In Standardexcel werden anderssprachige Original- Funktionsnamen dagegen automatisch in die Systemsprache übersetzt. Nach Rücksprache mit dem Erstellers der Datei mit der PQ-Lösung, habe ich die Auskunft erhalten, dass dies so für PQ normal wäre. Nun ja, in VBA ist ja auch alles in engl. Sprache. Wird dann wohl in einigen Jahren auch für Standardexcl bzw. dessen Nachfolger so sein.
Gruß Werner
.. , - ...
AW: oT: Dir zur Info ...
05.05.2019 18:20:55
Jenny
Hallo Werner,
du hast nicht zufällig auch eine Lösung meines Problems?
Gruß
Jenny
AW: oT: Dir zur Info ...
05.05.2019 20:07:49
Günther
@ Werner (Neopa C)
Danke für den Hinweis. Stimmt, mir ist auch aufgefallen, dass seit gestern die MS Office zugehörigen Symbole in der Statuszeile ein "moderneres" Outfit haben. ;-) Ich werde nachher oder morgen einmal nachschauen, ob die netten neuen Funktionen aus 2019/365 (beispielsweise Transformieren-Extrahieren-Text vor|nach|zwischen Trennzeichen) in der 2016er Version enthalten sind. Vielleicht ja sogar auch in den Add-Ins (2010/13)?
Gruß
Günther
Wieder das gleiche Problem, ...
05.05.2019 23:11:00
Luc:-?
…Jenny,
wie schon vor mehr als 2 Jahren im Jan 2017…‽
Damals wurde dir ebenso wie jetzt wieder gesagt, dass alle Xl-Datumswerte (Tageszähler) vor dem 1.3.1900 irreal sind, während VB(A) ab 1.1.0100 bis 31.12.9999 richtig zählt, weil hier der 31.12.1899 =1 und der Vortag =0 ist. Davor erst sind die Werte negativ (wird rückwärts gezählt). Trotzdem beharrst du weiter auf deinen falschen Zählern, die dir nur zusätzliche Probleme bereiten wdn! Dabei wäre es recht einfach, wenn du statt deiner untauglichen Trivial-UDF die verwenden würdest, die ich dir damals schon angeboten hatte!
Da jetzt aber auch spezifische engl Datumstexte ins Spiel kommen, müsste man diese erst in eine lokale (hier deutsche) Form über­füh­ren, was u.a. kürzlich hier wieder von mir gezeigt wurde (eignet sich auch für anderssprachige Datumstexte).
So wäre auch eine einzige Fml für alle deine gezeigten Fälle möglich:
 ABCD
1
    16. Jan 79 2887116.01.1979December 12, 19853139312.12.1985April, 1982 3004201.04.19822001 3689201.01.2001December 16, 1770-4713016.12.177031.12.1899 131.12.189901.01.1900 201.01.190028.02.1900 6028.02.190001.03.1900 6101.03.1900
2
3
4
5
6
7
8
9
10
Die verwendeten Fmln sähen hier dann so aus:
C1[:C10]:=WENN(A1="";"";WENN(VLike(A1;"####");TextToDate("1.1."&A1);WENNFEHLER(TextToDate(A1)/1;TextToDate(WENN(VLike(A1;"*#, ####");
INDEX(SplitVx(A1;{", "." "});2);1)&"."&VERGLEICH(INDEX(SplitVx(A1;{", "." "});1);INDEX(TEXT(DATUM(RECHTS(A1;2);ZEILE($1:$12);1);"[$-409]MMMM");
0);0)&"."&RECHTS(A1;4)))))

D1[:D10]:=DateToText(C1;;"TT.MM.JJJJ")
Falls nur das Ergebnis in Spalte D gewünscht wird, muss statt C1 in die 2.Fml nur die 1.Fml eingetragen wdn.
Zusätzliche UDF-Links:
SplitVx (Vs1.6) https://www.herber.de/bbs/user/84157.xls (BspDatei; Vs ohne Arg4 enthält wie Vs2.0 noch kleinen Fehler)
VLike (Vs1.2 o.VPairs) https://www.herber.de/cgi-bin/callthread.pl?index=1247144#1247699
Gruß, Luc :-?
Der 3.Link war ein Forumslink! owT
06.05.2019 11:58:30
Luc:-?
:-?

125 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige