VBA Datumsformat ändern

[Ewald]

Hallo NG,
mit VBA muß ich die Daten vom Format "TT.MM.JJJJ" ins Format "MMM JJ", was
mir aber leider nicht gelingt. Meine Datenbasis(D) und das doch sehr
merkwürdige (Ergebnis(F), wobei die Werte 05. rechtsbündig und die 2
Zellen mit MMM JJ rechtsbündig in den Zellen stehen:

Spalte D F(angezeigt) F (tats. Zellinhalt)
17.01.2005 05. Jan 05.01.2006 ->2006!!
18.01.2005 05. Feb 05.02.2006 ->2006!!
19.01.2005 05. Apr 05.04.2006 ->2006!!
31.01.2005 05. Jun 05.06.2006 ->2006!!
23.02.2005 05.Jul 05.07.2006 ->2006!!
11.04.2005 05.Sep 05.09.2006 ->2006!!
12.04.2005 Okt 05 Okt 05 OK
22.04.2005 05. Nov 05.09.2006 ->2006!!
10.06.2005 Dez 05 Dez 05 OK
21.06.2005
01.07.2005
30.09.2005
06.10.2005
04.10.2005
24.10.2005
02.11.2005
25.11.2005
12.12.2005
22.12.2005
Die dazugehörige Prozedur mit Grppenwechsel, damit jeder Monat nur einmal
vorkommt:
For I = 1 To 19
X = CLng(CDate(Cells(I, 4)))
VN = Format(X, "MMM YY")
If VN <> VA Then
NZ = NZ + 1
Cells(NZ, 6).Value = VN
End If
VA = VN
Next I
Das Ergebnis in Spalte F benötige ich für ei Diagramm, wobei im Diagramm
ALLE Ticks im Format MMM JJ dargestellt werden, aber als Sahnehäubchen ist
die Jahreszahl nicht "05" sondern "06" (bis auf Okt und Dez)!!!!!
Vor der Verarbeitung sind die Zellen der Sp. D im Format "TT.MM.JJJJ", der
Sp. F
"Standard". In der Sp. F haben die Zellen mit Inhalt 05. ... das Format
"TT.MMM"!!!!

Dieser Sch... beschäftigt mich mich nun seit 1 1/2 Tagen. Kann mir
eigentlich nicht vorstellen, daß es für dieses Problem keine Lösung gibt. Die
Monate im Format MMM JJ benötige ich für die Beschriftung der X-Achse eines
Diagramms, wobei fehlende Monate im Diagramm mitangezeigt werden sollen.
Gerne stelle ich die EXCEL- Mappe zur Verfügung.
Danke für eure Hilfe.

MfG
Ewald
EXCEL 2003 SP2

[Thomas Ramel]

Grüezi Ewald

Ewald schrieb am 15.03.2006

Post by Ewald
mit VBA muß ich die Daten vom Format "TT.MM.JJJJ" ins Format "MMM JJ", was
mir aber leider nicht gelingt. Meine Datenbasis(D) und das doch sehr
merkwürdige (Ergebnis(F), wobei die Werte 05. rechtsbündig und die 2

Ja, die 'ungewünschten' Werte werden von Excel als Datum interpretiert und
auch so dargestellt - die Ausgangswerte für die korrekten Ergebnisse lassen
sich in Monat und Tag nicht vertauschen, daher werden sie korrekt
umgesetzt.

Post by Ewald
For I = 1 To 19
X = CLng(CDate(Cells(I, 4)))
VN = Format(X, "MMM YY")
If VN <> VA Then
NZ = NZ + 1
Cells(NZ, 6).Value = VN
End If
VA = VN
Next I
Das Ergebnis in Spalte F benötige ich für ei Diagramm, wobei im Diagramm
ALLE Ticks im Format MMM JJ dargestellt werden, aber als Sahnehäubchen ist
die Jahreszahl nicht "05" sondern "06" (bis auf Okt und Dez)!!!!!
Vor der Verarbeitung sind die Zellen der Sp. D im Format "TT.MM.JJJJ", der
Sp. F "Standard". In der Sp. F haben die Zellen mit Inhalt 05. ... das Format
"TT.MMM"!!!!

Ja, das ist die Excel-eigene Logik die dem User (manchmal) Arbeit
abnimmt....

Formatiere die Zellen in Spalte F *vor* dem Eintragen der Werte als Text,
dann klappt es:

For I = 1 To 19
VN = Format(Cells(I, 4), "MMM YY")
If VN <> VA Then
NZ = NZ + 1
Cells(NZ, 6).NumberFormat = "@"
Cells(NZ, 6).Value = VN
End If
VA = VN
Next I
End Sub

Post by Ewald
Dieser Sch... beschäftigt mich mich nun seit 1 1/2 Tagen. Kann mir
eigentlich nicht vorstellen, daß es für dieses Problem keine Lösung gibt. Die
Monate im Format MMM JJ benötige ich für die Beschriftung der X-Achse eines
Diagramms, wobei fehlende Monate im Diagramm mitangezeigt werden sollen.

Die Daten stehen nach obiger Anpassung als Text in der Spalte; prüfe ob
dies deinem Diagramm entspricht.
Allenfalls müsstest Du sonst die Werte im Format 1.1.05 usf. verwenden und
die Zellen, respektive die Achsen-Beschriftung mit "MMM JJ" formatiert
darstellen lassen.



Mit freundlichen Grüssen
Thomas Ramel (@work)

[Ewald]

Hallo Thomas,

Post by Thomas Ramel
Die Daten stehen nach obiger Anpassung als Text in der Spalte; prüfe ob
dies deinem Diagramm entspricht.

danke für deine Hilfe, aber beim Text-Format habe ich keine "Lücken" auf der
Achse, diese soll jedoch lückenlos (Jan 05 Feb 05 .... Dez 05) dargestellt
werden.

Post by Thomas Ramel
Allenfalls müsstest Du sonst die Werte im Format 1.1.05 usf. verwenden und
die Zellen, respektive die Achsen-Beschriftung mit "MMM JJ" formatiert
darstellen lassen.

das hat auch nicht hingehauen.

Gruß
Ewald

[Thomas Ramel]

Grüezi Ewald

Ewald schrieb am 15.03.2006

Post by Ewald
Hallo Thomas,

Post by Thomas Ramel
Die Daten stehen nach obiger Anpassung als Text in der Spalte; prüfe ob
dies deinem Diagramm entspricht.

danke für deine Hilfe, aber beim Text-Format habe ich keine "Lücken" auf der
Achse, diese soll jedoch lückenlos (Jan 05 Feb 05 .... Dez 05) dargestellt
werden.

Ja, das hatte ich schon fast vermutet aufgrund der Ausführungen die Du
gemacht hattest.

Post by Ewald

Post by Thomas Ramel
Allenfalls müsstest Du sonst die Werte im Format 1.1.05 usf. verwenden und
die Zellen, respektive die Achsen-Beschriftung mit "MMM JJ" formatiert
darstellen lassen.

das hat auch nicht hingehauen.

Doch, das tut; dann muss aber der Wert als 'echtes' Datum eingetragen
werden und nicht als Text-String:

For I = 1 To 19
With Cells(I, 4)
VN = DateSerial(Year(.Value), Month(.Value), 1)
If VN <> VA Then
NZ = NZ + 1
Cells(NZ, 6).NumberFormat = "MMM YY"
Cells(NZ, 6).Value = VN
End If
VA = VN
End With
Next I



Mit freundlichen Grüssen
Thomas Ramel (@work)

[Ewald]

Hallo Thomas,

nochmals Danke.

Post by Thomas Ramel
Cells(NZ, 6).NumberFormat = "MMM YY"
Cells(NZ, 6).Value = VN

bewirkt, daß in Cells(NZ, 6) das Datum in Form "TT.MM.JJJJ" steht und auch
so angezeigt wird; die Zelle ist mit Format "Benutzerdefiniert, MMM JJ"
belegt.
Müßte das Datum dann nicht als z.B. Jan 05 dargestellt werden?
Im Diagramm stehen dann 01.01.2005... aber wieder OHNE Lücken.

Gruß
Ewald

[Thomas Ramel]

Grüezi Ewald

Ewald schrieb am 15.03.2006

Post by Ewald
Hallo Thomas,
nochmals Danke.

Post by Thomas Ramel
Cells(NZ, 6).NumberFormat = "MMM YY"
Cells(NZ, 6).Value = VN

bewirkt, daß in Cells(NZ, 6) das Datum in Form "TT.MM.JJJJ" steht und auch
so angezeigt wird; die Zelle ist mit Format "Benutzerdefiniert, MMM JJ"
belegt.

Dann müsste die Anzeige in der Zelle auch diesem Format entsprchen; der
eigentliche Inhalt ist der Monatserste.
Nur damit kannst Du in der Achse des Diagrammes die Daten auch entsprechend
dargestellt bekommen.

Post by Ewald
Müßte das Datum dann nicht als z.B. Jan 05 dargestellt werden?

Ja, das müsste es - probiers mal mit einer neuen Datei, womöglich ist die
aktuelle korrupt.

Post by Ewald
Im Diagramm stehen dann 01.01.2005... aber wieder OHNE Lücken.

Diese Achse kannst/musst Du entsprechend formatieren - Lücken wolltest Du
aber ja IMO keine?

Oder habe ich da etwas falsch verstanden?
Dann schildere bitte etwas mehr des Hintergrundes deiner Frage.



Mit freundlichen Grüssen
Thomas Ramel (@work)

[Ewald]

Hallo Thomas,

abermals Danke, kann dir leider erst jetzt antworten

Post by Thomas Ramel
Dann müsste die Anzeige in der Zelle auch diesem Format entsprchen; der
eigentliche Inhalt ist der Monatserste.

Das tut's bei mir nicht; in X-Varianten getestet

Post by Thomas Ramel

Post by Ewald
Müßte das Datum dann nicht als z.B. Jan 05 dargestellt werden?

Ja, das müsste es - probiers mal mit einer neuen Datei, womöglich ist die
aktuelle korrupt.

nicht korrupt

Nochmals zu den Anweisungen
Cells(NZ, 6).NumberFormat = "MMM YY"
Cells(NZ, 6).Value = VN
diese schreiben das Datum in Form "TT.MM.JJJJ" in die Zelle - und egal wie
die Zelle vorher formatiert war, LINKSBÜNDIG als TEXT. Angezeigt
wird zwar "MMM JJ" ist aber trotzdem Text. Jetzt kann ich selbst mit
"händischem" formatieren nichts mehr bewirken.EXCEL nimmt zwar das andere
Format an, die Darstellung bleibt aber trotzdem: TT.MM.JJJJ" LINKSBÜNDIG als
TEXT!!!!!!

Ich hab das Problem jetzt so gelöst:
mit Cells(MZ, 2) = DateSerial(Year(.Value), Month(.Value), 1)
weise ich der zuvor mit "TT.MM.JJJJ" formatierten Spalte B den Monatsersten zu

dann, (das wollte ich eigentlich vermeiden, weil von hinten durch die Brust
ins Auge) weise ich der zuvor mit "MMM JJ" formatierten Spalte C die Spalte B
zu (=Bn), dort steht dann z.B." Mrz 05".
In der Schleife kopiere ich dann die Zelle nach Spalte F die ich für's
Diagramm benötige und jetzt endlich habe ich die Daten RECHTSBÜNDIG als
richtige DATEN.
Kopierbefehl:
If VN <> VA Then
Summe_1 = 0
Summe_2 = 0
NZ = NZ + 1
Cells(MZ, 3).Select
Selection.Copy
Cells(NZ, 6).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
operation:=xlNone, _
skipblanks:=False, Transpose:=False
Cells(NZ, 7) = Summe_1
Cells(NZ, 8) = Summe_2
End If

MfG
Ewald

[Thomas Ramel]

Grüezi Ewald

Ewald schrieb am 16.03.2006

Post by Ewald
abermals Danke, kann dir leider erst jetzt antworten

Kein Problem; ich bin auch erst jetzt wieder hier online.

Post by Ewald

Post by Thomas Ramel
Dann müsste die Anzeige in der Zelle auch diesem Format entsprchen; der
eigentliche Inhalt ist der Monatserste.

Das tut's bei mir nicht; in X-Varianten getestet

Hmmm, das kann ich ohne die Mappe nicht beurteilen.

Post by Ewald

Post by Thomas Ramel

Post by Ewald
Müßte das Datum dann nicht als z.B. Jan 05 dargestellt werden?

Ja, das müsste es - probiers mal mit einer neuen Datei, womöglich ist die
aktuelle korrupt.

nicht korrupt

Ok.

Post by Ewald
Nochmals zu den Anweisungen
Cells(NZ, 6).NumberFormat = "MMM YY"
Cells(NZ, 6).Value = VN
diese schreiben das Datum in Form "TT.MM.JJJJ" in die Zelle - und egal wie
die Zelle vorher formatiert war, LINKSBÜNDIG als TEXT. Angezeigt
wird zwar "MMM JJ" ist aber trotzdem Text. Jetzt kann ich selbst mit
"händischem" formatieren nichts mehr bewirken.EXCEL nimmt zwar das andere
Format an, die Darstellung bleibt aber trotzdem: TT.MM.JJJJ" LINKSBÜNDIG als
TEXT!!!!!!

Dies hingegen dürfte am vorherigen Makro liegen, das die Zellen als Test
formatiert hatte. Excel ist dann manchmal sehr störrisch, was das
Umformatieren anbelangt.

Wie schaute es aus, wenn Du dasselbe in einer völlig neuen Mappe versuchst?
Bei mit hat es anstandslos geklappt, sonst hätte ich den Code nicht
gepostet.

Post by Ewald
mit Cells(MZ, 2) = DateSerial(Year(.Value), Month(.Value), 1)
weise ich der zuvor mit "TT.MM.JJJJ" formatierten Spalte B den Monatsersten zu
dann, (das wollte ich eigentlich vermeiden, weil von hinten durch die Brust
ins Auge) weise ich der zuvor mit "MMM JJ" formatierten Spalte C die Spalte B
zu (=Bn), dort steht dann z.B." Mrz 05".
In der Schleife kopiere ich dann die Zelle nach Spalte F die ich für's
Diagramm benötige und jetzt endlich habe ich die Daten RECHTSBÜNDIG als
richtige DATEN.

Fein, dass Du einen Workaround gefunden hast - schöner wärs allerdings auf
dem direkten Weg, von dem ich noch immer überzeugt bin, dass er klappt.


Mit freundlichen Grüssen
Thomas Ramel

[unknown]

Post by Ewald
Dieser Sch... beschäftigt mich mich nun seit 1 1/2 Tagen. Kann mir
eigentlich nicht vorstellen, daß es für dieses Problem keine Lösung gibt. Die
Monate im Format MMM JJ benötige ich für die Beschriftung der X-Achse eines
Diagramms, wobei fehlende Monate im Diagramm mitangezeigt werden sollen.
Gerne stelle ich die EXCEL- Mappe zur Verfügung.
Danke für eure Hilfe.

Mein Mitleid hast du.
Ich verfiele da einzig auf die dümmstmögliche Lösung die Daten über
Textvaraiblen und etwa Select als Text in die Quellzellen der Tabelle zu
platzeiren - evtl. sortieren.

Was mir noch nicht 100% klar ist: Wie soll die Achse auf der sich die
Datümser verteilen skaliert sein - 365 Tage? Oder als 12 Monate?
Und mehr noch - was sagt das Diagramm schließlich aus? Ob _an_ Datum X
etwas passiert oder wie viel dort/dann passiert ist?