Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1628to1632
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1628to1632
1628to1632
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Datum umwandeln

Datum umwandeln
25.06.2018 19:56:28
Christian
Hallo an euch alle,
ich bitte euch um Hilfe.
Suche eine Formel, die folgendes macht.
1. es steht ein Datum im Format TT. MMM JJ da, dies in das Format TT.MM.JJJJ bringen.
2. es steht nur eine Jahreszahl da, in diesem Fall den 1.1. dieses Jahres ausgeben
3. es steht ein Monat (in englischer Sprache geschrieben) und ein Jahr da, in diesem Fall soll der 1. diesen Monats ausgegeben werden.
Hier eine kleine Beispieldatei, was ich genau meine. Wünsche mir eine Anpassung meiner Formel in Spalte E.
Viele Grüße
Christian
PS: Es können natürlich alle Monatsnamen vorkommen, nicht nur März wie in meinem Beispiel.
https://www.herber.de/bbs/user/122320.xlsx

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
theoretisch einfach, aber ...
26.06.2018 08:15:51
Fennek
Hallo Christian,
um den englischen Monatsnamen zuzuordnen, braucht man eine Liste aller 12 Namen und muss die passenden suchen.
Das Problem ist, dass (vermutlich) niemand anfängt, bei solchen Einzelbeispielen einen Code bzw Formel zu schreiben, denn die Brfürchtung ist, dass dann scheibchenweise viele andere Formate nachgeschoen werden.
Wenn die schreibst "folgende 10 Formate sollen in ein Datum gewechselt werden:" findet sich (vielleicht) jemand.
mfg
AW: theoretisch einfach, aber ...
26.06.2018 08:29:50
Christian
Hallo Fennek,
es gibt genau 4 Möglichkeiten, was in der Tabelle stehen kann,
1. ein Datum wie in Zeile 2,
2. eine Jahreszahl wie in Zeile 3,
3. englischer Monat, Jahreszahl wie in Zeile 4
4. Leerzellen.
Mehr wird nicht kommen. Versprochen.
Gruß
Christian
Anzeige
AW: UDF
26.06.2018 09:25:34
Fennek
Hallo,
da konnte ich wieder etwas lernen:

Function Datum_wandeln(rng As Range)
With Application
Tx = rng.Value
For i = 1 To 12
If InStr(1, Tx, .GetCustomListContents(4)(i)) > 0 Then Tx = Replace(Tx, . _
GetCustomListContents(4)(i), .GetCustomListContents(8)(i))
Next i
If VBA.IsNumeric(Tx) Then Tx = "1.1." & Tx
Datum_wandeln = CDate(Tx)
End With
End Function
'Liste 4: eng. Monatsnamen
'Liste 8: dt. Monatsnamen
Diese UDF wird genutzt wie eine XL-Funktion, also "=Datumw" mit der Autovervollständigung und der relevanten Zelle eingeben.
mfg
Anzeige
AW: UDF
26.06.2018 09:52:17
Christian
Hallo Fennek,
danke erstmal für deine Mühe.
eine Sache macht deine UDF leider anders als meine ursprüngliche Formel.
Wenn die Ursprungszelle leer ist, hat meine Formel aufgrund dem Teil
WENN(C2=0;"")
nichts ausgegeben. Deine UDF gibt den 11.1.1900 aus.
Ich hab jetzt einfach =WENN(C2=0;"";Datum_wandeln(C2)) daraus gemacht, ich verstehe allerdings nicht wie deine Formel auf den 11.1.1900 kommt?
Gruß
Christian
AW: UDF: geändert
26.06.2018 10:32:27
Fennek
Hallo,
wenn der Tag 0, also der 0.1.1900, gezeigt würde, könnte ich das verstehen.
Hier die modifizierte UDF:

Function Datum_wandeln(rng As Range)
With Application
TX = rng.Value
For i = 1 To 12
If InStr(1, TX, .GetCustomListContents(4)(i)) > 0 Then TX = Replace(TX, . _
GetCustomListContents(4)(i), .GetCustomListContents(8)(i))
Next i
If Not IsEmpty(TX) Then
Datum_wandeln = CDate(TX)
Else
Datum_wandeln = ""
End If
End With
End Function
'Liste 4: eng. Monatsnamen
'Liste 8: dt. Monatsnamen
mfg
Anzeige
AW: Datum umwandeln
26.06.2018 10:21:43
Daniel
Hi
probiers mal mit folgenden Formel, sie sollte alle beschriebenen Fällte abdecken.
=WENN(ISTZAHL(C2);WENN(C2>10000;C2;DATUM(C2;1;1));WENN(ISTZAHL(FINDEN(".";C2));WERT(C2); WENN(ISTZAHL(FINDEN(",";C2));DATUM(--RECHTS(GLÄTTEN(C2);4);FINDEN(LINKS(GLÄTTEN(C2);3); "xxJanFebMarAprMayJunJulAugSepOctNovDec")/3;1);"kein Datum")))
Gruß Daniel
AW: Datum umwandeln
26.06.2018 11:29:08
Christian
https://www.herber.de/bbs/user/122331.xlsm
Hallo ihr beiden,
danke nochmal für eure Mühe. Habe nochmal eine Datei zusammengestellt, die alles nochmal aufzeigt, auch das Problem mit dem 11.1.
Ich habe mal noch eine Frage, aufgrund dessen ich entscheiden möchte, welche der beiden funktionierenden Lösungen ich im Endeffekt nehme.
In Spalte C sind es insgesamt ca. 12000 Einträge, also jedesmal bei jeder Änderung an der Tabelle würden 12000 Formeln berechnet werden. Welche der beiden Lösungen ist vorraussichtlich schneller?
Gruß
Christian
Anzeige
AW: Datum umwandeln
26.06.2018 12:45:15
Daniel
Hi
Prinzipiell berechnet Excel nur die Formeln neu, bei denenen sich im referenzierten Zellbereich was verändert hat.
Was jetzt konkret schneller ist, ist schwer zu sagen. Da müsstest du mal beide Varianten ausprobieren und die Zeit stoppen.
Gruß Daniel
was ist schneller UDF oder reine Excelfomel
26.06.2018 13:03:52
Daniel
Hi
meiner Erfahrung nach sind reine Excelformeln schneller als UDFs, sofern sie eine gleiche oder ähnliche Methode verwenden.
UDFs lohnen sich nur dann, wenn über die VBA-Programmierung eine deutlich effektivere Berechnungsmethode verwendet werden kann als mit den original Excelformeln (bspw das Speichern von mehrfach verwendeten Zwischenergebnissen in Variablen, während diese in einer Excelformel jedesmal neu berechnet werden müssen.
Gruß Daniel
Anzeige
AW: schnell ...
26.06.2018 11:43:15
Fennek
Hallo,
wenn sich bei einem Test die Bearbeitunszeit als zu lang herausstellt, lade die ganze Spalte auf einmal in ein Array, nutzt die UDF oder Daniels Formel und schreibe die Ergebnisse als Konstanten auf einmal ins Sheet zurück.
Das sollte bei "nur" 12.000 Zeilen weniger als 1 Sekunde benötigen.
mfg
AW: Datum umwandeln
26.06.2018 13:43:22
Christian
Hallo nochmal,
erstmal eine Frage, gehe ich recht davon aus, dass zwei verschiedene Daniels geantwortet haben?
Was ich nicht gesagt hatte,
Ich habe eine Lösung gesucht, bei der ich die Daten die umgewandelt werden sollen einzeln eingeben kann und sie direkt umgewandelt werden, damit ich direkt das Ergebnis sehe.
Günthers Vorschlag setzt wenn ich ihn richtig verstehe, voraus, dass ich entweder einmal diese Abfrage für alle Datensätze mache, oder ich muss für jeden Datensatz eine erneute Abfrage starten. Das ist leider beides nicht das was ich mir vorgestellt hatte.
Zu Fenneks Vorschlag mit dem Array, ich habe keine Ahnung, wie das umzusetzen ist.
Gruß
Christian
Anzeige
Entscheidung
26.06.2018 13:48:14
Christian
Ich habe mich im Endeffekt für Daniels Formel entschieden, weil die Berechnungszeit absolut akzeptabel ist und ich bei Formeln bei kleineren Änderungen selbst Hand anlegen kann, bei Makros bräuchte ich definitiv fremde Hilfe.
Gruß
Christian
Internationale Datumsformate
26.06.2018 16:16:46
Luc:-?
Tach allerseits;
es scheint bei den meisten AWern immer noch nicht angekommen zu sein, dass Xl LänderCodes für inter­natio­nale Datums­formate bereit­stellt, obwohl das schon des Öfteren in Lösungen gezeigt wurde. Damit bräuchte man auch keine MonatsnamenListen, sondern muss nur den entsprd LandesCode angeben, TEXT(…;[landescode]…), und dann damit VERGLEICHen.
Die Sonderfälle müssten dann aber noch entsprd berücksichtigt wdn…
Gruß, Luc :-?
Anzeige
AW: Internationale Datumsformate
26.06.2018 22:47:53
Christian
Hallo Luc,
dann zeig mir doch auch bitte, wie die entsprechende Formel aussieht.
Bitte allerdings eines zu berücksichtigen, die angegebene Office Version stimmt nicht. Ich habe Office 2016, die Kaufversion im Einzelhandel, ich beziehe absichtlich kein Abo über Office 365 auch wenn ich dafür auf die eine oder andere neue Funktionalität eventuell verzichten muss.
Gruß
Christian
Bsp für Landescode-Anwendung
27.06.2018 16:10:25
Luc:-?
Ja, Christian,
kann man in der Hilfe nachlesen und gibt's schon lange in diversen Varianten, zB so:
Heutiges Datum in A1 (Format: TT.MM.JJJJ), Fml in B1:=TEXT(A1;"[$-409]MMMM TT, JJJJ") → June 27, 2018
Für eines deiner Konkreta dann per (singularer) MatrixFml so:
A1: June 2018 → B1: 01.06.2018 ←
{=--(1&-VERGLEICH(LINKS(A1;SUCHEN(" ";A1)-1);TEXT(DATUM(2018;ZEILE($1:$12);1);"[$-409]MMMM");0)&-TEIL(A1;SUCHEN(" ";A1)+1;4))}
Das dann in eine umfassende Fml für alle 3 Fälle einzubauen, sollte dir nach den bisherigen Vorgaben wohl möglich sein.
Gruß, Luc :-?
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige