Datumsformatierung in Excel

...komplette Frage anzeigen

4 Antworten

=DATUM(RECHTS(A1;4);VERGLEICH(TEIL(A1;5;3);{"Jan"."Feb"."Mar"."Apr"."May"."Jun"."Jul"."Aug"."Sep"."Oct"."Nov"."Dec"};0);TEIL(A1;9;2))+TEIL(A1;12;8)*1

Was wird beim 6 Juni angezeigt? 06 oder 6?

Format: Benutzerdefiniert: TTTT, TT.MM.JJJJ, hh:mm:ss

Vergleich!: das ist die gesuchte Lösung, die die vielen Wechseln-Formeln und damit die Überschreitung der erlaubten Verschachtelungen im xls-Format vermeidet!
Ich hab immer bei den Datumsformeln geschaut! DH!

0

Der Vollständigkeit halber hier noch die Variante, bei der es egal ist ob der String führende und folgende Leerzeichen hat (beim Import öfters mal der Fall) und bei der es egal ist ob der Tag einstellig oder zweistellig dargestellt wird.

=DATUM(RECHTS(GLÄTTEN(A1);4);VERGLEICH(TEIL(GLÄTTEN(A1);5;3);{"Jan"."Feb"."Mar"."Apr"."May"."Jun"."Jul"."Aug"."Sep"."Oct"."Nov"."Dec"};0);TEIL(GLÄTTEN(A1);FINDEN(" ";GLÄTTEN(A1);6)+1;2)*1)+TEIL(GLÄTTEN(A1);FINDEN(" ";GLÄTTEN(A1);9)+1;8)*1

Alles andere muss so stehen:

3 Zeichen (Wochentag), Leerzeichen, 3 Zeichen (Monat englisch), Leerzeichen, 1 oder 2 Zeichen (Tag), Leerzeichen, 8 Zeichen (Zeit), Leerzeichen, 4 Zeichen (Jahr)

1

Hat Super Funktioniert! Ich musste lediglich noch die Uhrzeit entfernen um die Daten auf Tage hoch zu aggregieren.

Besten Dank! War mir eine sehr große Hilfe.

0
@ShinTwo

dazu lässt du einfach alles nach dem Pluszeichen weg.

=DATUM(RECHTS(GLÄTTEN(A1);4);VERGLEICH(TEIL(GLÄTTEN(A1);5;3);{"Jan"."Feb"."Mar"."Apr"."May"."Jun"."Jul"."Aug"."Sep"."Oct"."Nov"."Dec"};0);TEIL(GLÄTTEN(A1);FINDEN(" ";GLÄTTEN(A1);6)+1;2)*1)

1

Es geht mit einer Formel, auch wenn diese ein Monster wird:
in A1 Dein Import, dann Formel sonstwo (nur in xlsx-Dateien, sonst sind es zu viele Verschachtelungsebenen!):

=DATUM(TEIL(A1;21;4);TEIL(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN
(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN
(WECHSELN(WECHSELN(A1;"Jan";"01");"Feb";"02");"Mar";"03");
"Apr";"04");"May";"05");"Jun";"06");"Jul";"07");"Aug";"08");"Sep";"09");
"Okt";"10");"Nov";"11");"Dez";"12");5;2);TEIL(A1;9;2))+ZEITWERT(TEIL(A1;11;8))

Die Zelle musst du auf das Ben.def. Format

TTT, TT.MM.JJ hh:mm:ss

bringen, dann liefert sie Di, 11.06.13 08:36:03

Das Ergebnis ist eine Zahl, also rechenbar. Willst du es als Text, dann (Die Umformung stehe in B1):
=Text(B1;"TTT, TT.MM.JJ hh:mm:ss")
Du kannst das natürlich auch direkt machen und anstelle B1 das Monster einfügen!

xl stört diese lange Formel übrigens nicht, die wird sehr schnell auch in vielen Zellen durchgerechnet, Du kannst sie aber natürlich später auch zu Wert umsetzen.

Anm: vllt kennt jemand eine Methode, um zB Jun zu 6 oder 06 umzuwandeln? Dann ginge es viiieel kürzer und auch in xls2003 !

DH!

Klappt!
Allerdings gibt es - bei mir - beim Kopieren der Formel ein kleines Problem, weil zwei Leerzeichen vor den (WECHSEL-Klammern nicht automatisch von Excel entfernt werden (was sonst oft klappt).
Ich versuche jetzt mal Deine Formel hier kopierfähig einzustellen:

=DATUM(TEIL(A1;21;4);TEIL(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(A1;"Jan";"01");"Feb";"02");"Mar";"03"); "Apr";"04");"May";"05");"Jun";"06");"Jul";"07");"Aug";"08");"Sep";"09"); "Okt";"10");"Nov";"11");"Dez";"12");5;2);TEIL(A1;9;2))+ZEITWERT(TEIL(A1;11;8))

Sieht so zwar sehr unschön aus, klappt aber jetzt - bei mir - mit dem Kopieren.

0
@Oubyi

...und vielleicht noch Okt durch Oct und Dez durch Dec ersetzen, um die englischen Kürzel abzufragen, wie Du es schon mit Mar und May getan hast.

1
@Oubyi

@Oubyi:: Die Leerzeichen musste ich setzen, um die Umbrüche hinzukriegen, dachte eigentlich, dass GF die -wie sonst auch immer- entfernt! (und ich mag immer alles sehen und nicht erst nach rechts scrollen müssen!) Aber stimmt, EIN Leerzeichen ohne zusätzlichen Umbruch hätte auch gereicht!
Und das Oct, Dec : ja, klar! ich mach zu selten was in Englisch!

1
@Iamiam

War nicht böse gemeint, ich hab nur irgendwann den Überblick bei der ganzen Wechselei verloren. :-)

1

Zeitwert find ich cool, habs aber über *1 gelöst.

1
  1. Sind die Tage immer mit drei Buchstaben?
  2. Sind die Monate immer mit drei Buchstaben?
  3. Ist zum Beispiel der "9 Juni" als "Sun Jun 09" oder als "Sun Jun 9" formatiert?

Versuche es damit:

(angenommen dein Datum steht in A1)

=TEXT(TEXT(DATUM(RECHTS(GLÄTTEN(A1);4);MONAT("1. "&RECHTS(LINKS(GLÄTTEN(A1);7);3));RECHTS(LINKS(GLÄTTEN(A1);10);2));"TT.MM.JJJJ")&" "&LINKS(RECHTS(GLÄTTEN(A1);13);8);"TT.MM.JJJJ hh:mm:ss")
0
@ShitzOvran

DH!

Den Trick mit
MONAT("1."& "Monatskürzel")
kannte ich auch noch nicht!
Ich hatte es vergeblich ohne das "1." probiert.

Ich befürchte allerdings, dass man doch noch ein paar WECHSEL einbauen muss, da ich davon ausgehe, dass die Monatskürzel auch - wie die Wochentagskürzel - auf englisch sind.
Oder kennst Du noch einen Trick, um die englischen Kürzel zu erkennen?

0

Ah gutefrage formatiert um: Die Beispiele sind jeweils nach der Jahreszahl getrennt und befinden sich alle in einer Spalte

Der GF-Editor braucht zwei Leerzeichen+Enter für einen Umbruch
(Leider!, vergess ich auch immer wieder mal, obwohl ichs inzwischen gewöhnt sein müsste!). Also:
Tue Jun 11 06:36:04 2013
Tue Jun 11 08:36:36 2013
Tue Jun 11 11:50:43 2013
Die Leerzeichen verschwinden aber in der Textvorschau/Antwort!

1

Was möchtest Du wissen?