Microsoft Excel

Herbers Excel/VBA-Archiv

unbekanntes (US-) Datumsformat wandeln

Betrifft: unbekanntes (US-) Datumsformat wandeln von: Wolfango
Geschrieben am: 29.04.2014 16:31:29

Hallo Experten,
von einem EDV-System erhalte ich Datumswerte (exportiert in Excel) in folgender Form:

Jan 3 2014 9:16AM
Jan 4 2014 11:39AM
Jan 10 2014 7:39AM
Jan 11 2014 11:50AM


Also unterschiedlich lang und OHNE Leertaste vor AM/PM.
Excel interpretiert diese Werte als Text; ich kann also nicht damit rechnen.
Wie kann ich daraus nun ein (deutsches), rechenbares Datumsformat machen?

Am liebsten über eine Formel, da der Befehl "Text in Spalten" (Register Daten) immer neu angestoßen werden muss.

Besten Dank und Gruß,

Wo

  

Betrifft: AW: unbekanntes (US-) Datumsformat wandeln von: Hajo_Zi
Geschrieben am: 29.04.2014 16:46:25

ich bin nun nicht der Formelmensch, aber es geht.

Tabelle6

 AB
14Jan 3 2014 9:16AM03.01.14 09:16
15Jan 4 2014 11:39PM04.01.14 23:39
16Jan 10 2014 7:39AM10.01.14 07:39
17Jan 11 2014 11:50AM11.01.14 11:50

verwendete Formeln
Zelle Formel Bereich N/A
B14:B17=LINKS(TEIL(WECHSELN(A14;" ";".jan.";2);SUCHEN(" ";A14)+1;99);SUCHEN(" ";TEIL(WECHSELN(A14;" ";".jan.";2);SUCHEN(" ";A14)+1;99) )-1)*1+LINKS(TEIL(TEIL(WECHSELN(A14;" ";".jan.";2);SUCHEN(" ";A14)+1;99);SUCHEN(" ";TEIL(WECHSELN(A14;" ";".jan.";2);SUCHEN(" ";A14)+1;99) )+1;10);LÄNGE(TEIL(TEIL(WECHSELN(A14;" ";".jan.";2);SUCHEN(" ";A14)+1;99);SUCHEN(" ";TEIL(WECHSELN(A14;" ";".jan.";2);SUCHEN(" ";A14)+1;99) )+1;10) )-2)*1+N(RECHTS(A14;2)="PM")*12/24  

Zahlenformate
Zelle Format Wert
B14 'TT.MM.JJ hh:mm 41642,3861111111
B15 'TT.MM.JJ hh:mm 41643,9854166667
B16 'TT.MM.JJ hh:mm 41649,31875
B17 'TT.MM.JJ hh:mm 41650,4930555556
Zellen mit Format Standard werden nicht dargestellt
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 17.12 einschl. 64 Bit



GrußformelHomepage


  

Betrifft: AW: unbekanntes (US-) Datumsformat wandeln von: Wolfango
Geschrieben am: 29.04.2014 16:58:47

...absoluter Hammer! Es funktioniert!

Tausend Dank!

Gruß, Wo


  

Betrifft: Nur Datum, oder Datum + Zeit? (owT) von: EtoPHG
Geschrieben am: 29.04.2014 16:47:52




  

Betrifft: AW: unbekanntes (US-) Datumsformat wandeln von: EtoPHG
Geschrieben am: 29.04.2014 17:00:02

Hallo Wolfango,

Nur Datum:

=DATUM(WERT(TEIL(A1;7;5));MONAT("1."&WECHSELN(WECHSELN(WECHSELN(WECHSELN(LINKS(A1;3);"ar";"rz");"y"; "i"); "ct";"kt");"ec";"ez")&".1900");WECHSELN(TEIL(A1;FINDEN(" ";A1);3);",";"")) 

Nur Zeit:
=ZEITWERT(LINKS(RECHTS(A1;7);5))+(N(RECHTS(A1;2)="PM")/2)

Datum und Zeit:
Die beiden Formeln addieren
=DATUM(....)+ZEITWERT(...)
P.S. Hajos Vorschlag ergibt für alle Datum den Januar und das ist ja wohl nicht Sinn der Sache.

Gruess Hansueli


  

Betrifft: AW: unbekanntes (US-) Datumsformat wandeln von: Wolfango
Geschrieben am: 29.04.2014 17:10:25

....Danke für den Hinweis. Du hast natürlich recht...der Monat stimmte nicht.
Mit Deiner Formel geht's!
(...und ich mach' mich gleich mal daran, sie auch zu verstehen...)

Vielen Dank und Gruß,
Wo


  

Betrifft: Die Hauptgründe, warum dieses Datum für Xl ... von: Luc:-?
Geschrieben am: 29.04.2014 19:48:12

…irregulär ist, sind das fehlende Komma nach dem Monatstag und das ebenso fehlende Leerzeichen vor der a/p-Spezifizierung der Zeit, Wolfango!
Deutlich kürzer wird unter Berücksichtigung dieses Tatbestands eine Umwandlungsfml (hier inkl Uhrzeit) per UDF (benutzerdefinierte Fkt auf VBA-Basis):
1. Fertig als Text formatiert: (A1ff enthält den jeweiligen Datumstext)
=TEval("text";WECHSELN(WECHSELN(A1;" ";", ";2);RECHTS(A1;2);" "&RECHTS(A1;2)); "dd.mm.yyyy, hh:mm")
2. Als Zahl (zum SelberFormatieren) ermittelt:
=TEval("value";WECHSELN(WECHSELN(A1;" ";", ";2);RECHTS(A1;2);" "&RECHTS(A1;2)))
Die UDF TEval kann recht simpel pgmiert sein, zB so:

Function TEval(ByVal Art$, ByVal Text$, Optional ByVal TForm)
    If Not IsMissing(TForm) Then
        TEval = Evaluate(Art & "(""" & Text & """,""" & CStr(TForm) & """)")
    Else: TEval = Evaluate(Art & "(""" & Text & """)")
    End If
End Function
Anmerkender Hinweis: Hierbei kann Arg1 auch mal "" sein, falls ein kompletter FmlText (ohne führendes =!) in US-Original-Notation so ausgewertet wdn soll.

Gruß Luc :-?

Besser informiert mit …


  

Betrifft: AW: Die Hauptgründe, warum dieses Datum für Xl ... von: Wilfried Höttl
Geschrieben am: 30.04.2014 10:37:52

Hallo?

Kürzer

Tabelle1

 AB
4May 15 2014 1:11 PM15.05.2014 13:11
5oder so 
6May 15 2014 1:11 PM15.05.2014 13:11

Formeln der Tabelle
ZelleFormel
B4=DATUM(WERT(TEIL(A4;7;5)); MONAT("1."&WECHSELN(WECHSELN(WECHSELN(WECHSELN(LINKS(A4;3); "ar";"rz"); "y"; "i"); "ct";"kt"); "ec";"ez")&".1900"); WECHSELN(TEIL(A4;FINDEN(" ";A4); 3); ",";""))+ZEITWERT(LINKS(RECHTS(A4;7); 5))+(N(RECHTS(A4;2)="PM")/2)
B6=WECHSELN(TEIL(WECHSELN(A6;" ";"." & INDEX(ZEILE($1:$12); VERGLEICH(LINKS(A6;3); {"JAN"."FEB"."MAR"."APR"."MAY"."JUN"."JUL"."AUG"."SEP"."OCT"."NOV"."DEC"};0)) & ".";2); 5;99); RECHTS(A6;2); "")+(RECHTS(A6;2)="PM")*0,5


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß
Wilfried


  

Betrifft: Kürzer? In Bezug worauf? :-> owT von: Luc:-?
Geschrieben am: 30.04.2014 14:55:11

:-?


 

Beiträge aus den Excel-Beispielen zum Thema "unbekanntes (US-) Datumsformat wandeln"