Datum-Import (US/DE) ohne Trenner

Import (oder Eingabe) kalendarischer Daten (US oder DE) ohne Trennzeichen zu Datum

Mitunter werden durch einen Import oder auch bei Eingabe per Hand kalendarische Daten erzeugt, die ohne jegliches Trennzeichen sind. Also beispielsweise im Format JJJJMMTT (US) oder TTMMJJJJ (DE). Da stellt sich vielfach die Frage, wie solch ein Datum in die typische, gewohnte Schreibweise umgewandelt werden kann.

Prinzipiell …

Prinzipiell gilt, dass es ohne Makro/VBA keinen direkten Weg zur Umwandlung gibt. Sie werden immer eine Hilfsspalte verwenden, um die Werte neu zu berechnen. Dieses ist in den Beispielen immer die Spalte B; die Ausgangswerte stehen in Spalte A. Die umgewandelten Daten werden im korrekten Datumsformat (als serielle Zahl) in die Zellen geschrieben, damit sind dann auch Berechnungen möglich. Im Anschluss ist es hilfreich, diese Werte in Spalte A als Wert zu kopieren und die Hilfsspalte danach zu löschen.

Beim Import ist es ausgesprochen hilfreich, wenn die Spalte mit dem zu importierenden Datum als Text definiert wird. So wird gewährleistet, dass beim Format TTMMJJJ keine führende Null unterschlagen wird. Der 1. Januar 2015 bleibt dann 01012015 oder 010115 und wird nicht zu einer Zahl 1012015 gekürzt. Sollte das nicht möglich sein, dann ist die TEXT()-Funktion eine große Hilfe. 

Jahr 4-stellig, US

Das bedeutet, dass für den 1.5.2015 die Ziffernfolge 20150501 in A1 steht. Die beste Möglichkeit der Berechnung in B1:
=DATUM(LINKS(A1; 4); TEIL(A1; 5; 2); RECHTS(A1; 2))
Damit wird ein echtes Datum erzeugt, welches richtig formatiert ist und mit dem natürlich auch gerechnet werden kann.

Jahr 4-stellig, DE

Prinzipiell ist hier und bei den weiteren vorgestellten Lösungswegen auf der Tabellen-Basis genau wie eben beschrieben. In diesem Fall ist nur die Reihenfolge eine andere:
=DATUM(RECHTS(A1; 4); TEIL(A1; 3; 2); LINKS(A1; 2))
und Sie haben ein Datum als Ergebnis.

Jahr 2-stellig

Ach hier ist das Vorgehen grundsätzlich identisch. Allerdings sind die Werte in der jeweiligen Funktion etwas angepasst. Hier direkt untereinander die Formeln für US und danach für DE:
=DATUM(LINKS(A1; 2); TEIL(A1; 3; 2); RECHTS(A1; 2))
=DATUM(RECHTS(A1; 2); TEIL(A1; 3; 2); LINKS(A1; 2))

Import als numerische Werte

Wenn es sich denn nicht umgehen lässt und die importierten kalendarischen Daten als Zahl vorliegen, dann werden ja führende Nullen abgeschnitten. Bei US-Datum ist das ja nicht schlimm, weil das vierstellige Jahr keine führende Null haben kann (das zweistellige aber sehr wohl). Beim DE-Datum ist das die Regel, wenn der Tag vor dem 10. liegt. In A1 würde dann stehen: 1052015 wenn der 1. Mai gemeint ist.

Hier bietet sich an, nicht allzu viel mit WENN() zu arbeiten sondern konsequent davon auszugehen, dass die führende Null fehlen kann. Für das eben genannte Datum würde die Formel beim deutschen Datum mit vierstelligem Jahr dann beispielsweise so aussehen:
=DATUM(RECHTS(A1; 4); LINKS(RECHTS(A1; 6); 2); LINKS(RECHTS("0" & A1; 8); 2))
und alternativ eine weitere Formel ohne WENN():
=DATUM(RECHTS(A1; 4); TEIL(A1; 3-N(LÄNGE(A1)=7); 2); LINKS(RECHTS("0" & A1; 8); 2))
… die aber gewiss nicht Jedermanns Sache ist.  😉

Und falls Sie (doch) das WENN() verwenden wollen, dann prüfen Sie die Anzahl der Stellen in A1. Sind es 7 Stellen (bzw. 5 bei kurzem Datum), dann muss bei allen links beginnenden Zählweisen eine 0 voran gesetzt werden.

Per Makro (VBA)

Eigentlich viel universeller geht es per Makro. Wobei „Makro“ nicht in jedem Fall der korrekte Begriff ist, es handelt sich um eine benutzerdefinierte Funktion (UDF). Wie Sie solch eine Funktion in Ihr Projekt einbinden, können Sie an dieser Stelle nachlesen.

Option Explicit

Function DatumOhneTrenner(Datum, Optional LC As Variant)
   Dim x As Variant
   
   If IsMissing(LC) Then LC = "US"
   If Len(Datum) < 5 Or Len(Datum) > 8 Then
      MsgBox "Der Wert kann nicht in ein Datum umgewandelt werden!", _
       vbCritical + vbOKOnly, "Fehler"
      DatumOhneTrenner = ""
      Exit Function
   End If
   
   If UCase(LC) = "US" Then
      x = Left(Datum, 4) & "/" & Mid(Datum, 5, 2) & "/" & Mid(Datum, 7, 2)
      If Not IsDate(x) Then
         MsgBox "Der Wert kann nicht in ein Datum umgewandelt werden!", _
          vbCritical + vbOKOnly, "Fehler"
         DatumOhneTrenner = ""
         Exit Function
      End If
      DatumOhneTrenner = CDate(x)
      Exit Function
   Else
      If Len(Datum) = 5 Or Len(Datum) = 7 Then Datum = "0" & Datum
      x = Left(Datum, 2) & "." & Mid(Datum, 3, 2) & "." & Mid(Datum, 5, 4)
      If Not IsDate(x) Then
         MsgBox "Der Wert kann nicht in ein Datum umgewandelt werden!", _
          vbCritical + vbOKOnly, "Fehler"
         DatumOhneTrenner = ""
         Exit Function
      End If
      DatumOhneTrenner = CDate(x)
      Exit Function
   End If
   'Platz für weitere Prüfungen oder Routinen (darum auch das Exit Function hierüber)
End Function

Das sieht auf den ersten Blick vielleicht recht umfangreich aus, aber dafür ist der Code möglichst transparent gehalten. Und eines ist dabei ganz anders als bei der Formel-Lösung im Datenblatt: Hier wird grundlegend geprüft, ob dort eventuell ein fehlerhaftes Datum in der entsprechenden Zelle existiert.

Der Aufruf erfolgt so:
=DatumOhneTrenner(Datum ; ["US"]/["DE"])
wobei das erste Argument ein direkt eingegebenes Datum (natürlich ohne Trenner) oder eine Zelladresse sein kann. Der zweite Parameter ist ein optionaler Text und stellt die Landeskennung dar. Beispiel für das DE-Format:
=DatumOhneTrenner(A1; "DE")
Geben Sie zweite Argument nicht ein,  dann setzt die Funktion automatisch das US-Format voraus.

 Natürlich wäre es möglich, ein Makro zu verwenden, welches Zelle für Zelle in einem benannten oder markierten Bereich solch eine Umwandlung direkt vornimmt, also ohne Hilfsspalte. Wir lehnen das ab, da so jegliche Kontrollmöglichkeit fehlt, ob die Umwandlung korrekt ist. Angenommen in A1 steht 101112. Und der Aufruf der Funktion erfolgt hier so: 
=DatumOhneTrenner(A1)
dann wird das Ergebnis natürlich 12.11.2010 sein. Vielleicht sollte es aber doch der 10.11.2012 sein, weil es eigentlich das deutsche Datumsformat war? Und wenn das bei einer ganzen markierten Spalte passiert, dann wird unter Umständen mit schöner Regelmäßigkeit die Fehlermeldung aufploppen und die Zellen sind dann auch noch leer. Da ist es doch besser, die Werte rasch zu überprüfen und dann zu kopieren.

Ach ja, falls Ihnen der Name der Funktion zu lang ist, können Sie ihn nach Belieben ändern. Beispielsweise DoT (als Abkürzung). In dem Fall müssen Sie aber jedes Vorkommen des Funktionsnamens ersetzen.

▲ nach oben …

Dieser Beitrag wurde unter Datum und Zeit, Excel-Funktionen, Mit VBA/Makro, Ohne Makro/VBA, Tabelle und Zelle, Text, Tipps und Tricks abgelegt und mit , , , , , , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.