Home Menü

Excel – US Datum und Uhrzeit konvertieren

Durch das benutzerdefinierte Format von Zellen bietet die Standardsoftware Microsoft Excel bereits große Möglichkeiten bei der Konvertierung/Umwandlung von verschiedenen Datumsformaten. Versucht man allerdings die Konvertierung eines US-Datums inklusive Uhrzeit im Format 8/12/2012 6:39:32 PM mit unterschiedlicher Zeichenlänge, so stößt auch diese Funktion an ihre Grenzen.

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])
Hinweis: Es handelt sich hierbei um die Formeln der englischsprachigen Microsoft Excel 2010 Version. Um die jeweils deutschen Formelnamen herauszufinden, nutzen Sie bitte folgende Webseite: Link

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)

Veröffentlicht von Josef Seidl

Josef Seidl hat an der TU München und der Stanford University Wirtschaftsinformatik studiert, bevor er mit INNOSPOT sein eigenes Unternehmen gründete. Er ist begeistert von Technik, schätzt performante Webseiten und ist gerne in den Bergen unterwegs. Zu finden ist er auch bei LinkedIn und privat bei Twitter.

5 Kommentare » Schreibe einen Kommentar

      Schreibe einen Kommentar

      Pflichtfelder sind mit * markiert.