Microsoft Excel

Herbers Excel/VBA-Archiv

Kalenderdaten umwandeln

Betrifft: Kalenderdaten umwandeln von: Christian
Geschrieben am: 30.09.2014 11:15:27

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

  

Betrifft: AW: Kalenderdaten umwandeln von: Luschi
Geschrieben am: 30.09.2014 11:34:56

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


  

Betrifft: AW: Kalenderdaten umwandeln von: Christian
Geschrieben am: 30.09.2014 11:45:55

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


  

Betrifft: AW: Kalenderdaten umwandeln von: Christian
Geschrieben am: 30.09.2014 12:03:33

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


  

Betrifft: AW: Kalenderdaten umwandeln von: Christian
Geschrieben am: 30.09.2014 12:25:45

Hallo Luschi,

auch February 1994 als Eintrag bringt einen Datumsfehler

Gruß
Christian


  

Betrifft: AW: Kalenderdaten umwandeln von: yummi
Geschrieben am: 30.09.2014 14:36:49

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) < 50 then
                    strJahr = "19" & strJahr
                else
                    strJahr = "20" & strJahr
                end if
             End If

Gruß
yummi


  

Betrifft: AW: Kalenderdaten umwandeln von: Christian
Geschrieben am: 30.09.2014 15:46:44

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


  

Betrifft: AW: Kalenderdaten umwandeln von: yummi
Geschrieben am: 30.09.2014 16:22:34

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


  

Betrifft: AW: Kalenderdaten umwandeln von: Christian
Geschrieben am: 30.09.2014 21:24:02

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


  

Betrifft: AW: Kalenderdaten umwandeln von: Rudi Maintaire
Geschrieben am: 30.09.2014 16:36:23

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


  

Betrifft: AW: Kalenderdaten umwandeln von: Christian
Geschrieben am: 30.09.2014 21:29:38

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


  

Betrifft: AW: Kalenderdaten umwandeln von: Hajo_Zi
Geschrieben am: 01.10.2014 07:27:32

Hallo Christian,

schreibe es in eine Zeile, das bricht die Forumssoftware um und nicht immer korrekt.

GrußformelHomepage


  

Betrifft: AW: Kalenderdaten umwandeln von: Christian
Geschrieben am: 01.10.2014 09:05:33

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


  

Betrifft: Nachtrag - noch zwei Vorschläge von: Erich G.
Geschrieben am: 01.10.2014 10:19:03

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) < "60") & Right$(rngQ, 2)
         Else
            jjj = Right$(rngQ, 4)
         End If

         arM = Array("j", "f", "m", "p", "ay", "un", "ul", "au", "se", "o", "v", "d")
         For mmm = 1 To 12
            If InStr(LCase$(rngQ), arM(mmm - 1)) > 0 Then Exit For
         Next mmm
         If Left$(rngQ, 1) < "0" Or Left$(rngQ, 1) > "9" Then mmm = mmm + 1

         If Left$(rngQ, 1) >= "0" And Left$(rngQ, 1) <= "9" Then _
            ttt = Replace(Left(rngQ, 2), ".", "")
         DatUmw = DateSerial(jjj, mmm, ttt)
      End If
   End With
End Function
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Nachtrag - noch zwei Vorschläge von: Christian
Geschrieben am: 01.10.2014 10:57:35

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


  

Betrifft: Versuch einer Erklärung von: Erich G.
Geschrieben am: 01.10.2014 11:24:49

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


  

Betrifft: AW: Versuch einer Erklärung von: Christian
Geschrieben am: 01.10.2014 11:32:15

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


  

Betrifft: leicht geändertes Beispiel von: Erich G.
Geschrieben am: 01.10.2014 12:01:01

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


 

Beiträge aus den Excel-Beispielen zum Thema "Kalenderdaten umwandeln"