Das Problem
Wir haben Daten welche teilweise oder ganz mit dem Amerikanischem Datum versehen sind (Monat/Tag/Jahr) und wir müssen diese in die Europäische Norm bringen.
In unserer Beispielliste haben wir mehrere Daten welche mit Amerikanischen Datum geliefert wurden.
Die Lösung(en)
Es gibt für das Problem drei Lösungen (ohne VBA):
- Formatierung Lösung
- Formel Lösung
- Text in Spalten Lösung
Lösung 1: Formatierung Lösung
Die Formatierung Lösung ist die schnellste Lösung insofern man sie einsetzen kann. Die Werte müssen als Datum in Excel gespeichert sein.
Ob dies zutrifft kann man herausfinden in dem man eines der Datum anwählt und per rechts klick auf Zellen Formatieren geht. Hier sieht man unter der Lasche Zahlen in welchem Format die Daten gespeichert sind.
Sollte die Rubrik Datum hier erscheinen, so heißt das dass Excel das Datum als fortlaufende Zahl gespeichert hat, und man es deshalb nach belieben formatieren kann. So lässt sich das Problem einfach beheben in dem man das Europäische Format auswählt!
In unserem Beispiel trifft dies aber nicht zu! Wenn das Datum als Text gespeichert wurde so kommen nur noch die nächsten beiden Lösungen in Frage. Hier die Beispiel Datei zum Download:
Lösung 2: Formel Lösung
Die Formel Lösung ist etwas kompliziert wenn man es das erste mal erstellen muss… kann jedoch im nachhinein zigmal verwendet werden ohne weiteren Aufwand und einsetzen.
Das Datum wurde als Text gespeichert. Ob dies zutrifft kann man auf gleiche Art und Weise herausfinden wie oben in Lösung 1 bereits beschrieben.
Dank dem dass die amerikanischen Datum mit / getrennt werden, können wir mit einer Kombination aus verschiedenen Text Formeln und der DATUM Formel den Wert als fortlaufende Zahl eruieren, und im Anschluss nach belieben Formatieren.
Die Formel in Zelle G2 sieht folgendermaßen aus:
=DATUM(RECHTS(F2;4);LINKS(F2;FINDEN(“/”;F2)-1);TEIL(F2;FINDEN(“/”;F2)+1;(FINDEN(“/”;F2;4))-(FINDEN(“/”;F2)+1)))
Diese Formel könnte noch durch eine WENN Formel ergänzt werden um die korrekt formatieren Datum Werte auch in die Spalte G zu kopieren. Dies habe ich aber der Übersicht zu liebe hier weggelassen.
Lösung 3: Text in Spalten Lösung
Wenn man den Aufwand nur einmal betreiben muss, und es einfach schnell gemacht sein muss, so kann man die Amerikanischen Datum auch mit der Text in Spalten Funktion von Excel umwandeln.
Die Tabelle wird so sortiert dass alle Amerikanischen Datum zusammen sind. Diese werden dann in eine neue Tabelle in Spalte A kopiert. Wir bearbeiten nun mit dem Daten Menü>Text in Spalten Werkzeug von Excel die Amerikanischen Datum folgendermaßen:
Der Ursprüngliche Datentyp in Schritt 1. wählen wir als getrennt an und in Schritt 2. benutzen wir als Trennzeichen / . Dann Fertig stellen.
Wir haben nun das Amerikanische Datum in drei separate Spalten geteilt. Diese stehen bekanntermaßen für den Monat, Tag, und das Jahr.
Mit der DATUM Formel fügen wir diese Werte nun wieder zusammen so dass Excel es als fortlaufende Zahl wahrnimmt, welche nun im Europäischen Format dargestellt werden kann.
Nun müssen die Werte wieder in die original Liste zurück kopiert werden.
Hallo,
tolle Lösungsansätze!
Leider benötige ich weitere Hilfe bei der Lösung 2 die bei mir übrigens auch in dieser Form funktioniert: =DATUM(RECHTS(C5;4)*1;LINKS(C5;2);TEIL(C5;4;2))
Wie bekomme ich denn mit Hilfe der Wenn-Funktion oder einer anderen Funktion nicht immer die Fehlermeldungen bei Einträgen, wo das Datum in der korrekten Schreibform angegeben ist?
Ich freue mich über eine kurze Rückmeldung.
Gruß
FRED
Hi Fred,
Versuche es doch mal mit =ZELLE(“typ”;C5). Wenn die Zellen Text enthält bekommst Du das Ergebnis “l”. Somit könntest Du richtige von Falschen Datum unterscheiden.
Hilft das?
Gruss, Lukas
Hallo Lukas,
leider funktioniert es so bei mir nicht.
Ich habe folgendes Problem bzw. benötige folgende Formel:
==> Wenn das Datum korrekt in Zeile A steht, dann übernehme das Datum so wie es ist + Wenn das Datum in amerikanischer Schreibweise ist dann DATUM(RECHTS(C5;4)*1;LINKS(C5;2);TEIL(C5;4;2)) + Wenn in der Zelle nichts steht, dann unternehme nichts.
Hoffe das ist verständlich so.
Hier mal ein Versuch, der leider nicht funktioniert:
=WENN(ISTZAHL(SUCHEN(“/”;C21));DATUM(RECHTS(C21;4)*1;LINKS(C21;2);TEIL(C21;4;2));C21)
–> Das soll heißen, wenn in der Zelle C21 irgendwo ein “/” steht, dann Datumsformel wie oben. Ansonsten soll der Wert aus der Tabelle (korrektes Datum oder auch ein leeres Feld) 1:1 übernommen werden.
Würde mich riesig über einen Lösungsvorschlag freuen.
Noch mal eine Zwischenmeldung.
Ich habe jetzt eine Formel gefunden die funktioniert. Nun besteht nur noch das Problem, dass wenn die Zelle leer ist automatisch das Datum “00.01.1900” angezeigt wird.
Hast du diesbezüglich einen Vorschlag?
Hier die Formel die funktioniert: =WENN(ISTZAHL(SUCHEN(“/”;C31));DATUM(RECHTS(C31;4)*1;LINKS(C31;2);TEIL(C31;4;2));C31)
Hi Fred,
Freut mich dass Du eine Lösung für Dich gefunden hast. Das “00.01.1900” Datum wird angezeigt weil dies die Zahl 0 darstellt als Datum. Es ist nicht die eleganteste Lösung aber eine einfache weitere Prüfung durch eine WENN Formel würde funktionieren:
=WENN(ISTZAHL(SUCHEN(“/”;C32)); _
DATUM(RECHTS(C32;4)*1;LINKS(C32;2);TEIL(C32;4;2)); _
WENN(C32=0;””;C32))
Das ganze muss natürlich zusammen eingegeben werden… hier geteilt weil WordPress keinen Zeilenumbruch macht.
Hi, Lukas;
Bei Pkt 1 besteht das Problem, dass nicht als Text in ein Xl-Blatt geladene bzw kopierte US-Datumsangaben mitunter automatisch in die lokale Datumsform umgewandelt, wenn es möglich ist, was einen Mix aus falschen echten und Text-US-Datumsangaben ergeben kann.
Handelt man lt Pkt 2, was ja US-Datumstexte voraussetzt, gäbe es eine wesentlich kürzere Formel, um echte Datumswerte zu erzeugen, denn Xl versteht auch das internationale UN-Datumsformat YYYY/MM/DD (bzw JJJJ-MM-TT), so dass nur das Jahr vorangestellt werden muss: =–(RECHTS(F2;4)&”/”&LINKS(F2;LÄNGE(F2)-5))
Das dürfte dann ein Vorgehen nach Pkt 3 überflüssig machen.
Gruß, Luc :-?
Wunderbar, Dankeschön!! Per Formel funktionierte es superfix, nachdem ich den Bezug aktualisiert und alle “/” neu getippt hatte. Perfekt .. fehlt nur noch die Uhrzeit-Umwandlung. Warum nur gibt es dafür nicht einfach einen Button oder als Funktion unter “Umwandeln” .. doo
Danke Dorothea!
essentiell ist, dass in den Einstellungen für Windows die Standard-Sprache auf Deutsch festgelegt wird. Wenn nicht benötigt, Englisch in allen Versionen (amerikanisch etc.) entfernen, mindestens aber nach unten rücken!
Hallo, Danke für die Formel, hat mir sehr geholfen.
Nach dem Kopieren der o.a. Formel in mein Excel musste ich die Hochkommas händisch überschreiben, habe ewig gesucht bis ich das gefunden habe. (Wenn man genau hinsieht… die kopierten Hochkommas sind leicht kursiv, was mein Excel nicht erkannt hat).