Office: (Office 2013) Datumsformat per VBA ändern

Hallo,

ich importiere per VBA Daten aus einer Arbeitsmappe in meine aktuelle Arbeitsmappe. In den kopierten Daten befindet sich eine Spalte mit Datumsangaben. Die Datumsangabe weisen ein etwas unübliches Format auf (01.Jan.2018, 05.May.2018, 10.Oct.2017 etc.). Schaue ich mir die Formatierung der Zelle an steht dort auch Standard und nicht Datum.

Wenn ich die Datumsangaben mit 1 multipliziere, erhalte ich zumindest für die Monate, in denen englische und deutsche Abkürzung identisch sind, eine Zahl, die ich wieder in ein Datum wandeln könnte. Die andern Monate (Mar, May, Oct, Dec) fallen leider raus.

Als Ergebnis benötige ich aber später wieder die englischen Kurzbezeichnungen der Monate.

Lange Rede lange Frage:

Weiss jemand wie ich (am Besten schon beim Kopieren der Daten per VBA) das o.g. Datumsformat so manipulieren kann, dass es am Ende die Form DD. MMM YYYY mit den englischen Monatsabkürzungen aufweist und auch von der deutschen Excelversion als Datum erkannt wird?

:)

 

Halo Pawel,

es ist kein Zahl (Datum) sondern Text. Mit Multiplikation mit 1 wird es eine Zahl.

 

Moin!
Mal ein wenig Grundlagenwissen:
Um ein echtes Datum in Deinem gewünschten Format zu erhalten, musst Du Dich an [$-409] halten (siehe Zeile 4).

	J	K
1	04.01.2017	28.12.2016
2	04. Jan 2017	28. Dec 2016
3	42739	#WERT!
4	04. Jan 2017	28. Dec 2016
5	04.01.2017	28.12.2016

Zelle	Format	Wert
J1	TT.MM.JJJJ	04.01.2017
K1	TT.MM.JJJJ	28.12.2016
J4	[$-409]TT. MMM JJJJ	04.01.2017
K4	[$-409]TT. MMM JJJJ	28.12.2016
J5	TT.MM.JJJJ	04.01.2017
K5	TT.MM.JJJJ	28.12.2016

Zelle	Formel
K1	=J1-7
J2	=TEXT(J1;"[$-409]TT. MMM JJJJ")
K2	=TEXT(K1;"[$-409]TT. MMM JJJJ")
J3	=--J2
K3	=--K2
J4	=J1
K4	=K1
J5	=--J4
K5	=--K4

Gruß Ralf

 

Moin Pawel,
ich verwende kein VBA zum einlesen derartiger Daten sondern das Add-In Power Query. Da ändere ich nach dem Import der Daten den Datentyp der Spalte auf Datum und PQ ist schlau genug, mir ohne Rückfrage ein deutsches Datum daraus zu machen.-

 

Moin Günther!
Wollte ich dem TE auch zunächst empfehlen, aber er schreibt ja ausdrücklich:

Deshalb mein Hinweis auf das Zahlenformat [$-409].
Licht ins Dunkle bringt imo ohnehin nur eine kleine Import-Datei.
Hajo hat wohl Recht, dass es sich um Text handelt, der

• zunächst in ein echtes Datum umgewandelt werden müsste
• und danach mit dem obigen Zahlenformat versehen werden muss.

Ich bastel mal etwas mit WECHSELN() oder auch per UDF

Gruß Ralf

 

Hallo zusammen,

danke für die Hilfe. hajo hatte natürlich Recht. Es handelt sich um einen Text.

Bevor ihr euch jetzt weiter bemüht, ich habe eine Lösung gefunden. In der Fragestellung ist wohl nicht klar rübergekommen, dass ich das Ganze per VBA lösen muss.

Hier meine Lösung:
Code:

Ich übergebe den "Datumstext" an die Funktion, zerlege ihn, suche in dem Array nach der Position des Monats und setze das Ganze am Ende wieder als Datum zusammen.

Vielleicht kennt jemand eine einfachere Lösung (immer her damit :-)). Diese funktioniert auf jeden Fall :-).

Mein Hinweis mit den Monatsnamen war Blödsinn, weil das für die weitere Auswertung nicht nötig ist. Da habe ich mich selbst in die Ecke manövriert. Manchmal denkt man einfach zu kompliziert.

 

Da ich schon dran war, hier die Lösung mit dem gewünschten echten Datum in englischer Schreibweise:
Modul Modul1

Gruß Ralf

 

Die hat Dir Drogist genannt.
Wenn Du "nur" die Umwandlung in ein Datum wünscht, nutze PowerQuery.
(ohne jegliche Eingriffe wird das Datum umgewandelt)

 

Und abschließend:
Für die (ausschließliche) Datumsumwandlung brauchst Du sicherlich kein VBA (UDF), für alles reicht auch eine kurze Formel, die alle Monate abdeckt:

	A	B
1	01.Jan.2017	01.01.2017
2	05.May.2018	05.05.2018
3	17.Sep.2015	17.09.2015
4	12.Dec.2014	12.12.2014
5	15.Jun.2001	15.06.2001
6	18.Oct.2015	18.10.2015

Zelle	Formel
B1	=--WECHSELN(WECHSELN(WECHSELN(WECHSELN(A1;"Mar";3);"May";5);"Oct";10);"Dec";12)