Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Text als Datum

Text als Datum
Sascha

Hallo zusammen,
ich habe folgendes Problem.
Ich habe eine Tabelle mit folgenden Textfeldern
30-DEC-2010 22:45:00
30-DEC-2010 14:45:00
30-DEC-2010 06:45:00
Diese möchte ich in ein Datumsfeld umwandeln um anschliessend die Zeitdifferenz zwischen den einzelnen Zeilen zu ermitteln. Leider scheitert es schon am Umformatieren in ein Datumsfeld.
Vielen Dank!

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Text als Datum
04.02.2011 22:13:45
Josef

Hallo Sascha,
Tabelle2

 ABC
130-DEC-2010 22:45:00 30.12.2010 22:45:00
230-DEC-2010 14:45:00 30.12.2010 14:45:00
330-DEC-2010 06:45:00 30.12.2010 06:45:00

Formeln der Tabelle
ZelleFormel
C1=DATUM(TEIL(A1;8;4); INDEX({1;2;3;4;5;6;7;8;9;10;11;12};VERGLEICH(TEIL(A1;4;3); {"JAN";"FEB";"MAR";"APR";"MAY";"JUN";"JUL";"AUG";"SEP";"OCT";"NOV";"DEC"};0)); LINKS(A1;2))+RECHTS(A1;8)*1


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Sepp

Anzeige
AW: Text als Datum
04.02.2011 22:44:54
Sascha
Super Sepp - Vielen Dank!!!!
Mein Wochenende ist gerettet:-)
Alternative
05.02.2011 08:52:45
Erich
Hi,
wäre die Formel in Spalte B eine Alternative?
 ABC
130-DEC-2010 22:45:0030.12.2010  22:45:0030.12.2010  22:45:00
230-MAR-2010 22:45:0530.03.2010  22:45:0530.03.2010  22:45:05
330-MAY-2010 22:45:1030.05.2010  22:45:1030.05.2010  22:45:10

Formeln der Tabelle
ZelleFormel
B1=DATWERT(WECHSELN(WECHSELN(WECHSELN(LINKS(A1;12); "C";"Z"); "Y";"I"); "AR";"ÄR"))+RECHTS(A1;8)
C1=DATUM(TEIL(A1;8;4); INDEX({1;2;3;4;5;6;7;8;9;10;11;12};VERGLEICH(TEIL(A1;4;3); {"JAN";"FEB";"MAR";"APR";"MAY";"JUN";"JUL";"AUG";"SEP";"OCT";"NOV";"DEC"};0)); LINKS(A1;2))+RECHTS(A1;8)*1

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Alternative
05.02.2011 09:54:03
Wilfried
Hallo Erich!
Die Formel ist OK, ist viel kürzer.
Aber wofür sind die Buchstaben "C";"Z"); "Y";"I"); "AR";"ÄR") in Deiner Formel.
Danke für eine Antwort
Gruß
Wilfried
Korrektur der Alternative
05.02.2011 11:35:52
Erich
Hi Wilfried,
C -> Z sollte DEC in das deutsche DEZ ändern,
Y -> I soll MAY in das deutsche MAI ändern,
AR -> ÄR soll MAR in das deutsche MÄR ändern.
Übersehen hatte ich dabei den OCT, bei dem das C ja nicht in ein Z, sondern in ein K geändert werden muss.
So klappt es etwas besser:
 ABC
130-MAR-2010 22:45:0530.03.10 22:45:0530.03.10 22:45:05
230-MAY-2010 22:45:1030.05.10 22:45:1030.05.10 22:45:10
330-OCT-2010 22:45:1530.10.10 22:45:1530.10.10 22:45:15
430-DEC-2010 22:45:0030.12.10 22:45:0030.12.10 22:45:00

Formeln der Tabelle
ZelleFormel
B1=DATWERT(WECHSELN(WECHSELN(WECHSELN(WECHSELN(LINKS(A1;12); "OC";"OK"); "EC";"EZ"); "Y";"I"); "AR";"ÄR"))+RECHTS(A1;8)
C1=DATUM(TEIL(A1;8;4); INDEX({1;2;3;4;5;6;7;8;9;10;11;12};VERGLEICH(TEIL(A1;4;3); {"JAN";"FEB";"MAR";"APR";"MAY";"JUN";"JUL";"AUG";"SEP";"OCT";"NOV";"DEC"};0)); LINKS(A1;2))+RECHTS(A1;8)*1

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Korrektur der Alternative
05.02.2011 13:06:49
Sascha
Hallo Wilfried, Hallo Erich,
Beide Lösungen funktionieren. Die Formatierung des Monats ist nicht wichtig, sondern das ich ein Format habe, was ich anschließend verrechnen kann. Ich wollte ja die Zeitdifferenz in Minuten zwischen den einzelnen Zeitstempeln ermitteln und das funktioniert super mit D1= C1-C2*1440
Habe jetzt aber noch eine neue Herausforderung gefunden. Und zwar formatiert die Applikation, womit ich die Daten exportiere, diese nicht immer gleich. Die Monate März, Mai, Oktober, Dezember werden als Standardformat und die restlichen Monate als Datumsformat exportiert. Oh jetzt dämmert mir was, wo ich das lese was ich schreibe :-) Das sind genau die Monate die Excel falsch interpretiert und die Du Erich in deiner Lösung korrigierst.
Ich würde also gerne ein Makro erstellen, dass das Format der Zelle überprüft. Wenn es sich um ein Standard bzw. Textformat handelt, dieses mit eurer Formel umformatiert und zurück in die Zelle schreibt.
Vielen Dank nochmal!
Endlich mal ein Forum, wo einem konstruktiv geholfen wird und man keine blöden Sprüche an den Kopf geschmissen bekommt.
Gruß,
Sascha
Anzeige
Export oder Import mit falschen Formaten?
05.02.2011 13:26:12
Erich
Hi Sascha,
"formatiert die Applikation, womit ich die Daten exportiere, diese nicht immer gleich. Die Monate März, Mai,
Oktober, Dezember werden als Standardformat und die restlichen Monate als Datumsformat exportiert."
glaube ich noch nicht so recht.
Ist es nicht eher so, dass Excel beim Öffnen/Lesen/Import einen Teil der Texte als Datum interpretieren kann,
einen anderen Teil (eben den mit den englischen Monaten) nicht?
Kannst du dir die Datei z. B. mit dem Texteditor (Notepad, Notizblock) ansehen?
Sieht man da schon den Fehler?
Ich versuche immer, Fehler nicht später irgendwann zu bereinigenm, sondern so früh wie möglich.
Optimal ist es natürlich, wenn Fehler gar nicht erst entstehen.
Wenn also die Quell-Applikation den Fehler macht (was ich wie gesagt nicht glaube):
Könntest du das dort beheben?
Wenn Excel den Fehler beim Öffnen macht:
Wie liegen die Daten vor? Als Textdatei, evtl.l CSV?
Wie bekommst du derzeit die Daten in Excel hinein?
Es ist vermutlich sinnvoll, dafür zu sorgen, dass die Spalte mit dem teilw. falschen Datum
komplett als Text geöffnet/eingelesen/importiert wird.
Wie man das machen würde, hängt von der Quelldatei ab und davon, wie du bislang vorgehst.
Setzt du dabei bereits VBA ein?
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Export oder Import mit falschen Formaten?
05.02.2011 13:47:47
Sascha
Hallo Erich,
Du hast natürlich recht. Es wird so sein, dass Excel dies beim Öffnen falsch formatiert, da die Applikation aus der die Daten exportiert weden in den Staaten programmiert wurden. Die Datei (xls) habe ich von einem Kollegen geschickt bekommen und wollte diese probeweise auswerten. Werde also erst am Montag dazu kommen, mir das Exportformat genauer anzuschauen.
VBA setze ich zur Zeit noch nicht zum Auswerten ein, da ich gerade erst am Anfang stehe. Jedoch werde ich mir die Daten aus den letzen 10 Jahren anschauen und somit macht es Sinn dies dann zu automatieren:-)
Gruß,
Sascha
Anzeige
AW: Korrektur der Alternative
05.02.2011 13:26:31
Josef

Hallo Sacha,
per Makro geht das so. (Wrd auf die ausgewählten Zellen angewendet!)
Sub umwandeln()
  Dim rng As Range
  
  Selection.NumberFormat = "dd-MMM-yyyy hh:mm:ss"
  
  For Each rng In Selection
    If Not IsDate(rng) Then
      rng = CDate(Replace(Replace(Replace(Replace(rng.Text, _
        "DEC", "DEZ"), "OCT", "OKT"), "MAR", "MRZ"), "MAY", "MAI"))
    Else
      rng = CDate(rng)
    End If
  Next
End Sub


Gruß Sepp

Anzeige
AW: Korrektur der Alternative
05.02.2011 13:49:38
Sascha
Hallo Sepp!
Ich muss jetzt leider weg und werde deine Lösung später testen. Schätze aber mal, das sie fuktioniert ;-)
Melde mich dann....
Gruß,
Sascha
AW: Korrektur der Alternative
05.02.2011 22:40:39
Sascha
Hallo Sepp,
auch Dir nochmal einen dickes Lob, dein Makro funktioniert ganz hervoragend.
Vielen Dank.
Gruß,
Sascha
AW: Korrektur der Alternative
05.02.2011 17:01:48
Wilfried
Hallo Erich!
Danke für die Erklärung und für die neue Formel
Gruß
Wilfried

327 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige