VBA Datumsformat ändern
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
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
Ewald schrieb am 15.03.2006
> 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:
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.
> 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
> 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)
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-1]
Microsoft Excel - Die ExpertenTipps:
(http://tinyurl.com/9ov3l und http://tinyurl.com/cmned)
Thomas Ramel
Ewald schrieb am 15.03.2006
> Hallo Thomas,
>
>> 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.
>> 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
Thomas Ramel
Ewald schrieb am 15.03.2006
> Hallo Thomas,
>
> nochmals Danke.
>
>> 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.
> 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.
> 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.
Ewald
abermals Danke, kann dir leider erst jetzt antworten
"Thomas Ramel" wrote:
> 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
> > 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
Eric March
> 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?
--
Eric March
Kenne die Vergangenheit. In der Unwissenheit über die Vergangenheit
liegt das Verderben der Zukunft.
Thomas Ramel
Ewald schrieb am 16.03.2006
> abermals Danke, kann dir leider erst jetzt antworten
Kein Problem; ich bin auch erst jetzt wieder hier online.
> "Thomas Ramel" wrote:
>> 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.
>>> 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.
> 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.
>
> 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.
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
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]