Excel – US Datum und Uhrzeit konvertieren

Dieser Artikel zeigt mittels verschiedener Excelfunktionen, wie Sie auch solche Daten mit unterschiedlichen Längen in ein anderes Format bzw. in das deutsche Datumsformat umwandeln können.

Beispiele für US-Daten

8/31/2012 8:15:57 AM

8/31/2012 9:31:16 AM

8/31/2012 12:30:23 PM

Verwendete Formeln

Zur Konvertierung des US-Datumsformats in das deutsche Format nutzen wir folgende Formeln:

DATE(year; month; day)

TIME(hour; minute; second)

LEFT(text; [num_chars])

MID(text; start_num; num_chars)

RIGHT(text; [num_chars])

FIND(find_text; within_text; [start_num])

AND(logical1; [logical2]; ...)

IF(logical_test; [value_if_true]; [value_if_false])

Erläuterungen und Besonderheiten

Im folgenden möchte ich nun einen Teil der vollständigen Formel (welche am Ende des Artikels zu finden ist) erläutern und auf eine Besonderheit eingehen.

Als ersten Parameter empfängt die Funktion DATE (siehe unten bei vollständiger Formel) das jeweilige Jahr. Um dieses Jahr zu ermitteln, ist folgende Formel notwendig:

=MID(A1;FIND("/";A1;FIND("/";A1)+1)+1;4)

Der Text welcher durchsucht wird, steht in Zelle A1. Die zweite FIND-Funktion

=MID(A1;FIND("/";A1;FIND("/";A1)+1)+1;4)

liefert als Zahl die Stelle, wo das Zeichen „/“ zum ersten Mal gefunden wird. In unserem Beispiel kommt dieses Zeichen bereits an Stelle 2 vor. Zu dieser Stelle wird anschließend noch die Zahl 1 addiert. Setzt man nun anstatt der Formel das Ergebnis ein, sieht die weitere Formel wie folgt aus:

=MID(A1;FIND("/";A1;3)+1;4)

Die übrig gebliebene FIND-Funktion startet nun ab der Stelle 3 die Suche in der Zelle A1 und findet das nächste „/“-Zeichen auf Position 5 der Zeichenkette. Zu dieser Zahl wird nun wiederum die Zahl 1 addiert, wodurch wir – theoretisch – folgende Formel erhalten:

=MID(A1;6;4)

Diese Formeln schneidet nun ab der 6ten Stelle 4 Zeichen (die länge der Jahresangabe) aus und gibt diese zurück. In unserem Beispiel (im Screenshot oben) ist das Ergebnis „2012“.

Eine weitere Besonderheit der gleich aufgeführten vollständigen Formel ist, dass auch das amerikanische Format 12:30:23 PM korrekt als 12:30:23 Uhr (CET) mithilfe einer expliziten IF-Abfrage konvertiert wird.

Vollständige Formel (englisch)

=DATE(MID(A1;FIND("/";A1;FIND("/";A1)+1)+1;4);LEFT(A1;FIND("/";A1)-1);
 MID(A1;FIND("/";A1)+1;FIND("/";A1;FIND("/";A1)+1)-FIND("/";A1)-1)) + TIME(IF(AND(RIGHT(A1;2)="PM";(MID(RIGHT(A1;11);1;2)<>"12"));
 MID(RIGHT(A1;11);1;2)+12;IF(AND(RIGHT(A1;2)="AM";(MID(RIGHT(A1;11);1;2)="12"));0;MID(RIGHT(A1;11);1;2)));MID(RIGHT(A1;8);1;2);
 MID(RIGHT(A1;5);1;2))

Vollständige Formel (deutsch)

=DATUM(TEIL(A1;FINDEN("/";A1;FINDEN("/";A1)+1)+1;4);LINKS(A1;FINDEN("/";A1)-1);
TEIL(A1;FINDEN("/";A1)+1;FINDEN("/";A1;FINDEN("/";A1)+1)-FINDEN("/";A1)-1)) + ZEIT(WENN(UND(RECHTS(A1;2)="PM";(TEIL(RECHTS(A1;11);1;2)<>"12"));
TEIL(RECHTS(A1;11);1;2)+12;WENN(UND(RECHTS(A1;2)="AM";(TEIL(RECHTS(A1;11);1;2)="12"));0;TEIL(RECHTS(A1;11);1;2)));TEIL(RECHTS(A1;8);1;2);
TEIL(RECHTS(A1;5);1;2))

Fazit

Im Internet finden sich sehr viele Beiträge zu diesem Thema mit den unterschiedlichsten Formeln. Ich habe versucht Ihnen eine möglichst flexible Formel an die Hand zu geben, welche auch bei unterschiedlichen Längen der Daten funktioniert und die richtigen Ergebnisse liefert.

Getestet unter: Microsoft Office Professional Plus 2010 (englisch) und Excel für Mac 2011 (deutsch)