Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1384to1388
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

Kalenderdaten umwandeln

Kalenderdaten umwandeln
30.09.2014 11:15:27
Christian
Hallo an alle,
ich hoffe es hat jemand eine Lösung für mich und hilft mir bitte.
In angehängter Datei geht es mir darum, die Spalte H korrekt zu berechnen, also die Daten in Spalte G in das Format TT.MM.JJJJ umzuwandeln. Wie ihr seht, ist das in H15 und H16 schiefgelaufen.
Dazu muss ich sagen, ihr seht, da steht eine Funktion dahinter, die mir jemand nettes hier einmal programmiert hat, aber ursprünglich für andere Schreibweisen der Kalenderdaten gedacht war, und wohl auch deshalb in diesem Fall nicht richtig funktioniert.
Hat da jemand eine Lösung? Vielleicht helfen auch Teile der Formel in Spalte D, welche für diese Art der Schreibweise in Spalte C einwandfrei funktioniert, weiter.
Würde auch eine normale Formel nehmen, bevor sich jemand an die Arbeit machen muss den VBA-Code zu ändern.
Die Formeln sollen natürlich für jeden Kalendermonat funktionieren, nicht nur für die 5 im Beispiel.
Vielen Dank
Christian
https://www.herber.de/bbs/user/92903.xlsm

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Kalenderdaten umwandeln
30.09.2014 11:34:56
Luschi
Hallo Christian,
ändere in der Vba-Funktion 'fncDatumUmwandlung' Folgendes:

statt:
If Len(strJahr) = 2 Then
strJahr = "20" & strJahr
End If
das hier:
If Len(strJahr) = 2 Then
strJahr = CStr(Year(rngDatum))
End If
Gruß von Luschi
aus klein-Paris

AW: Kalenderdaten umwandeln
30.09.2014 11:45:55
Christian
Hallo Luschi,
wo ist eigentlich Klein-Paris?
mit den Daten aus meinem Beispiel fuktioniert es, danke.
Falls es mit weiteren Daten nicht funktionieren sollte, melde ich mich nochmal.
Falls ich mich nicht mehr melde, es also geklappt hat, wünsche ich dir noch einen schönen Tag.
Gruß
Christian

Anzeige
AW: Kalenderdaten umwandeln
30.09.2014 12:03:33
Christian
Hallo Luschi,
mir ist da leider doch noch etwas aufgefallen, was nicht funktioniert.
Wie du vielleicht an dem VBA Code erkennst, gibt die Funktion den letzten Tag eines Monats aus, wenn ich in Spalte G nur einen Monat und eine Jahreszahl angebe.
Jetzt habe ich in Spalte G September 1968 eingegeben, Excel hat daraus Sep 68 gemacht, die VBA Formel gibt #Datumsfehler! aus
gebe ich einen englischsprachigen Monat in diesem Format ein, z.B. January 1968, bleibt der Text so wie er ist, erhalten und die VBA-Funktion funktioniert, bzw. gibt wie gewünscht 31.01.1968 aus.
Schaust du dir das bitte nochmal an?
Viele Grüße
Christian

Anzeige
AW: Kalenderdaten umwandeln
30.09.2014 12:25:45
Christian
Hallo Luschi,
auch February 1994 als Eintrag bringt einen Datumsfehler
Gruß
Christian

AW: Kalenderdaten umwandeln
30.09.2014 14:36:49
yummi
Hallo Christian,
du verwendest

If Len(strJahr) = 2 Then
strJahr = "20" & strJahr
End If
also immer wenn du 2stellige jahre hast zählst du 2000 drauf, deshalb 2095. Bei 2stelligen jahresangaben musst du dir überlegen was du willst. Wenn das datum nicht in der zukunft liegen kann oder nur bis zu einem bestimmten bereich dann musst du eine entsprechende unterscheidung machen.
z.B.

If Len(strJahr) = 2 Then
if Val(strJahr) 

Gruß
yummi

Anzeige
AW: Kalenderdaten umwandeln
30.09.2014 15:46:44
Christian
Hallo Yummi,
dein Code macht leider bei mir aus jeder Jahreszahl das Jahr 2000.
Das Problem mit dem 23. Aug 95 hat bereits Luschi mit dem Code

If Len(strJahr) = 2 Then
strJahr = CStr(Year(rngDatum))
End If
gelöst.
Ich hänge mal nochmal eine Datei an. Diese Datei beinhaltet die Formeln mit der Version von Luschi.
Das Makro hat ebenfalls den Zweck, wenn nur ein Monat und ein Jahr dasteht, egal in welcher Form, den letzten Tag dieses Monats auszugeben, was nicht immer funktioniert. Siehe H25, H26, H40 und H41. Ebenso soll es, wenn nur eine Jahreszahl da steht, den letzten Tag dieses Jahres ausgeben. Das funktioniert, siehe H92 und H93 oder H21 und H22.
Habe in Spalte K geschrieben, was die Formel statt dem Datumsfehler hätte ausgeben sollen.
Würde mich freuen, wenn du nochmal danach schaust.
Gruß
Christian
https://www.herber.de/bbs/user/92915.xlsm

Anzeige
AW: Kalenderdaten umwandeln
30.09.2014 16:22:34
yummi
Hallo Christian,
es gibt eine vba Funktion, die dir die Arbeit abnimmt ;-)
ändere mal am Ende deiner Funktion folgendes:

dim dateLTag
dateLTag = CDate(DateSerial(intJahr, intMonat + 1, 0))
fncDatumUmwandlung = dateLTag
' Ergebnis auf gültiges Datum prüfen
' If IsDate(Format(intJahr, "0000") & "-" & Format(intMonat, "00") & "-" _
'         & Format(intTag, "00")) Then
'   fncDatumUmwandlung = DateSerial(intJahr, intMonat, intTag)
' End If
und voila du hast den letzten des monats. das sollte es sein. Der 0.Tag des Folgemonats = letzter Tag im Monat ;-)
Gruß
yummi

Anzeige
AW: Kalenderdaten umwandeln
30.09.2014 21:24:02
Christian
Hallo Yummi,
da lag dann wohl ein Missverständnis vor, wenn ein Tag in Spalte G steht, soll dieser auch behalten werden.
Das mit dem letzten des Monats sollte nur ausgegeben werden, wenn kein Tag in Spalte G steht.
Bsp: H1 soll 10.02.1999 sein, aber es wird der 28.02.1999 ausgegeben, das mit dem letzten Tag des Monats soll sich nur auf die Zellen wie z.B. G40 beziehen, in denen nur ein Monat und eine Jahreszahl, aber kein Tag steht.
Gruß
Christian

AW: Kalenderdaten umwandeln
30.09.2014 16:36:23
Rudi
Hallo,
das geht viel einfacher.
Function fncDatumUmwandlung(rngDatum As Range) As Variant
Dim iPos As Integer, arrEN, arrDE
arrEN = Array("January", "February", "March", "April", "May", "June", "July", "August", " _
September", "October", "November", "December")
arrDE = Array("Januar", "Februar", "März", "Apr", "Mai", "Juni", "Juli", "August", "September" _
, "Oktober", "November", "Dezember")
strText = Trim(rngDatum.Text)
If Len(strText) = 4 And IsNumeric(strText) Then
fncDatumUmwandlung = DateSerial(--strText, 12, 31)
End If
If fncDatumUmwandlung = "" Then
If IsDate(strText) Then
fncDatumUmwandlung = CDate(strText)
End If
End If
If fncDatumUmwandlung = "" Then
For iPos = 0 To 11
strText = Replace(strText, arrEN(iPos), arrDE(iPos))
Next
If IsDate(strText) Then
fncDatumUmwandlung = CDate(strText)
End If
End If
If fncDatumUmwandlung = "" Then
For iPos = 0 To 11
strText = Replace(strText, Left(arrEN(iPos), 3), arrDE(iPos))
Next
If IsDate(strText) Then
fncDatumUmwandlung = CDate(strText)
End If
End If
Select Case strText
Case Format(fncDatumUmwandlung, "MMM YY"), Format(fncDatumUmwandlung, "MMM YYYY"), _
Format(fncDatumUmwandlung, "MMMM YY"), Format(fncDatumUmwandlung, "MMMM YYYY")
fncDatumUmwandlung = DateSerial(Year(fncDatumUmwandlung), Month(fncDatumUmwandlung) + 1,  _
0)
End Select
End Function

Gruß
Rudi

Anzeige
AW: Kalenderdaten umwandeln
30.09.2014 21:29:38
Christian
Hallo Rudi,
erstmal auch dir danke für deine Mühe.
Hab versucht, den Code in das Modul einzufügen, die kompleten Zeilen
  arrEN = Array("January", "February", "March", "April", "May", "June", "July", "August", " _
September", "October", "November", "December")
macht er sofort rot und beim Komplilieren sagt er dann Syntaxfehler und markiert gleich die erste Zeile gelb.
Außerdem, hat das einen Grund, weshalb du den April im arrDE nicht ausgeschrieben hast? oder einfach übersehen?
Gruß
Christian

AW: Kalenderdaten umwandeln
01.10.2014 07:27:32
Hajo_Zi
Hallo Christian,
schreibe es in eine Zeile, das bricht die Forumssoftware um und nicht immer korrekt.

Anzeige
AW: Kalenderdaten umwandeln
01.10.2014 09:05:33
Christian
Nochmal einen schönen guten Morgen an alle die sich an der Lösung meines Problems beteiligt haben.
Ich danke euch vielmals. Mit Hajos Vorschlag und der kleinen Änderung von Apr in April in Rudis Vorschlag funktioniert das Ganze jetzt.
Schönen Tag noch und Gruß
Christian

Nachtrag - noch zwei Vorschläge
01.10.2014 10:19:03
Erich
Hi ,
hier noch eine Excelfkt. und eine UDF:
 GHI
1QuelleFormelDatUmw()
210 February 199910.02.199910.02.1999
325. Aug 9525.08.199525.08.1995
4199531.12.199531.12.1995
5200131.12.200131.12.2001
6Sep 6801.09.196801.09.1968
7February 199428.02.199428.02.1994
85 March 200005.03.200005.03.2000
907 Aug 9507.08.199507.08.1995
10February 200429.02.200429.02.2004
11Feb 201229.02.201229.02.2012
12Feb 201328.02.201328.02.2013
13Feb 5828.02.205828.02.2058
14Feb 6328.02.196328.02.1963

Formeln der Tabelle
ZelleFormel
H2=WENN(ISTZAHL(G2)*(LÄNGE(G2)=4); 0+("31.12."&G2); WENN(ISTZAHL(G2); G2;DATUM(WENN(TEIL(G2;LÄNGE(G2)-2;1)=" ";19+(RECHTS(G2;2)<"60")&RECHTS(G2;2); RECHTS(G2;4)); VERWEIS(9;1/SUCHEN({"j";"f";"m";"p";"ay";"un";"ul";"au";"se";"o";"v";"d"};G2); ZEILE($1:$12))+1-ISTZAHL(0+LINKS(G2)); WENN(ISTZAHL(0+LINKS(G2)); WECHSELN(LINKS(G2;2); ".";""); 0))))
I2=DatUmw(G2)

Bei den linksbündig angezeigten Werten in Spalte G handelt es sich um Texte.
In den Zeilen G4:G5 stehen Zahlen.
In den Zeilen G3, G6 und G9 stehen ebenfalls Zahlen, wobei die Zellen mit Datumsformat versehen sind.
(In G6 steht die Zahl 25082 - das entspricht dem 01.09.1968. Da hier auch der Tag vorgegeben ist,
wird nicht das Monatsende ausgegeben.)
Und hier der Coder der UDF DatUmw():

Option Explicit
Function DatUmw(rngQ As Range)
Dim jjj As Integer, mmm As Integer, ttt As Integer, arM
With Application
If .IsNumber(rngQ) Then
If Len(rngQ) = 4 Then DatUmw = DateSerial(rngQ, 12, 31) Else DatUmw = rngQ
Else
If Mid$(rngQ, Len(rngQ) - 2, 1) = " " Then
jjj = 19 - (Right$(rngQ, 2)  0 Then Exit For
Next mmm
If Left$(rngQ, 1)  "9" Then mmm = mmm + 1
If Left$(rngQ, 1) >= "0" And Left$(rngQ, 1) 
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Nachtrag - noch zwei Vorschläge
01.10.2014 10:57:35
Christian
Hallo Erich,
danke erstmal
leider bringt mir deine Formel nicht allzuviel, wie du ja selbst schon sagtest, in H6 in deinem Beispiel müsste der 30.09.1968 stehen, nicht der 01.09.1968.
Zu dem VBA Code, ich höre leider zum allerersten mal etwas von UDF. Habe also keine Ahnung, wohin mit diesem Code, in ein Modul, ins Tabellenblatt, in die gesamte Mappe?
Aber auch hier ja offensichtlich dasselbe Problem mit I6 in deinem Beispiel.
Gruß
Christian

Versuch einer Erklärung
01.10.2014 11:24:49
Erich
Hi Christian,
der Code gehört in ein "normales" Modul in der Mappe, wo er gebraucht wird.
"wie du ja selbst schon sagtest, in H6 in deinem Beispiel müsste der 30.09.1968 stehen, nicht der 01.09.1968"
Das stimmt nicht! Ich habe geschrieben: "wird nicht das Monatsende ausgegeben".
Ich hatte auch geschrieben, dass in G6 die Zahl 25082 (Anzahl Tage ab 01.01.1900 bis zum 01.09.1968) steht.
Der 30.09.1968 würde als die Zahl 25111 gespeichert.
Schau dir mal z. B. hier an, was in Excel ein Datum ist: http://www.online-excel.de/excel/singsel.php?f=128
Du musst sorgfältig unterscheiden zwischen dem Wert einer Zelle (Datum, Zahl 25082),
dem Zahlenformat (z. B. "T. MMM. JJJJ" oder "MMM JJ" und der Anzeige in der Zelle.
Wenn du in einer Zelle das Datum 01.09.1968 stehen hast (also die Zahl 25082) und dann das Zahlenformat
"M" einstellst, wird nur noch eine 9 angezeigt - und trotzdem ist immer noch das komplette Datum gespeichert!
Beim Format "JJJ" sieht man nur das Jahr...
Da in G6 ein komplettes Datum (incl. Tag = 1) steht, ist da nichts zu ergänzen.
Denk doch noch mal drüber nach, ob du den Tag 01 durch 30 ersetzen willst,
nur weil man ihn wegen des Zahlenformats gerade mal nicht sieht.
Geht es dir um die Werte in den Zellen oder das, was man davon sieht?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Versuch einer Erklärung
01.10.2014 11:32:15
Christian
Hallo Erich,
das Ganze dient zwei Zwecken. Erstens soll diese Spalte als erstes Sortierkriterium dienen von länger zurückliegend hin zu kürzer zurückliegendem Datum. Ich hätte halt gerne, dass dann von der Reihenfolge her zuerst die Daten kommen, bei denen ein genaues Datum bekannt ist und dann erst die, bei denen kein genaues Datum bekannt ist. Wenn ich jetzt den 1. des Monats da stehen habe und dann sortiere, ist es genau umgekehrt.
der zweite Grund ist der, dass in den Spalten I und J die Formeln
=WENN(C1"";DATEDIF(D1;H1;"Y");"")
und
=WENN(C1"";DATEDIF(D1;H1;"YD");"")
stehen, auch die würden andere als die gewünschten Ergebnisse ausgeben, wenn da plötzlich der erste statt dem letzten Tag des Monats steht.
Gruß
Christian

leicht geändertes Beispiel
01.10.2014 12:01:01
Erich
Hi Christian,
das was ich schreibe, findet wenig Niederschlag in deinen Antworten, z. B.
"Da in G6 ein komplettes Datum (incl. Tag = 1) steht, ist da nichts zu ergänzen."
Hier in Zeile 2 mal der 25.09.1968 als Beispieldatum. Das steht in G2 - als Zahl 25106.
Angezeigt wird wegen Datumsformat "MMM JJ" nur "Sep 68".
Das bedeutet keineswegs, dass der Tag nicht bekannt wäre: Es ist und bleibt der 25,
auch wenn im Zahlenformat gerade mal kein T vorkommt.
Ein Datum ohne Tag ist in Excel nixht möglich. Eine positive ganze Zahl lässt sich als Datum darstellen oder nicht,
wenn ja, dann immer mit Jahr, Monat und Tag.
Ein unvollständiges "Datum" ist nur als Text möglich, wie z. B. "Sep 69", ein Text auf 3 Buchstaben,
einem Leerzeichen und zwei Ziffern. Aber das ist kein Datum.
Dass und warum du das zum 30.09.1969 ergänzen möchtest, verstehe ich durchaus.
Das sieht man auch schon an den Zeilen 10:14 meiner ersten Beispieltabelle.
 GHIJ
1QuelleFormelDatUmw()Standardformat
2Sep 6825.09.196825.09.196825106

Formeln der Tabelle
ZelleFormel
H2=WENN(ISTZAHL(G2)*(LÄNGE(G2)=4); 0+("31.12."&G2); WENN(ISTZAHL(G2); G2;DATUM(WENN(TEIL(G2;LÄNGE(G2)-2;1)=" ";19+(RECHTS(G2;2)<"60")&RECHTS(G2;2); RECHTS(G2;4)); VERWEIS(9;1/SUCHEN({"j";"f";"m";"p";"ay";"un";"ul";"au";"se";"o";"v";"d"};G2); ZEILE($1:$12))+1-ISTZAHL(0+LINKS(G2)); WENN(ISTZAHL(0+LINKS(G2)); WECHSELN(LINKS(G2;2); ".";""); 0))))
I2=DatUmw(G2)
J2=G2

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige