Luis erhält Informationen, in denen Datumsangaben so codiert sind, dass Jahre, Monate und Tage jeweils durch ein einzelnes Zeichen ersetzt werden. Für jedes Feld werden die Zahlen 1 bis 9 verwendet und danach Buchstaben von a = 10, b = 11, …​ v = 31. Beispielsweise bedeutet der Datumscode ‚bc2‘ b = 11 (das Jahr 2011), c = 12 (der Monat) und Tag = 2. Luis fragt sich, ob eine Funktion entwickelt werden kann, um das codierte Datum durch ein allgemeines Datumsformat wie TT / MM / JJJJ zu ersetzen.

Es gibt tatsächlich mehrere Möglichkeiten, dieses Problem zu lösen.

Eine Möglichkeit besteht darin, „Äquivalenztabellen“ in einem Arbeitsblatt einzurichten, wobei die linke Spalte ein Codezeichen enthält und die rechte den numerischen Wert angibt, der diesem Zeichen zugeordnet ist. Sie können dann eine Formel erstellen, die VLOOKUP verwendet, um die Werte zu finden und die Ergebnisse in ein Datum umzuwandeln.

Erstellen Sie beispielsweise eine Äquivalenztabelle in einigen nicht verwendeten Zellen rechts von Ihren Daten. In meinem Fall habe ich die Tabelle in die Spalten P und Q eingefügt. In Spalte P habe ich die Codezeichen 1 bis 9 und a bis z eingefügt. (Stellen Sie sicher, dass Sie den Ziffern 1 bis 9 ein Apostroph voranstellen, damit sie als Text und nicht als Zahlen gespeichert werden.) Geben Sie in Spalte QI die Zahlen 1 bis 35 ein. In diesem gesamten Bereich (P1: Q35) gab ich dann den Namen DateTable an .

Hier ist also die Formel, die ein decodiertes Datum für ein in Zelle A1 gespeichertes codiertes Datum zurückgibt:

=DATE(2000+VLOOKUP(LEFT(A1,1),DateTable,2,0), VLOOKUP(MID(A1,2,1),DateTable,2,0),VLOOKUP(RIGHT(A1,1), DateTable,2,0))

Denken Sie daran, dass dies eine einzelne, kontinuierliche Formel ist. Eine andere Technik besteht darin, die Äquivalenztabellen vollständig zu umgehen und stattdessen eine Formel für die Konvertierung zu verwenden. Das folgende Beispiel dekodiert ein Datum in Zelle A1:

=DATE(2000+FIND(LEFT(A1,1),"123456789abcdefghijklmnopqrstuvwxyz"), FIND(MID(A1,2,1),"123456789abc"),FIND(MID(A1,3,1), "123456789abcdefghijklmnopqrstuv"))

Diese Formel verwendet die FIND-Funktion, um vom Codezeichen in einen Wert zu übersetzen, und diese Werte werden dann in der DATE-Funktion verwendet, um das gewünschte Datum zurückzugeben. Eine andere mögliche Formel basiert stattdessen auf Zeichencodewerten, um das Datum zu erstellen:

=DATE(2000+CODE(MID(A1,1,1))-87+(CODE(MID(A1,1,1))<58)39,CODE(MID(A1,2,1))-87+(CODE(MID(A1,2,1))<58)39,CODE(MID(A1,3,1))-87+(CODE(MID(A1,3,1))<58)*39)

Schließlich können Sie eine benutzerdefinierte Funktion erstellen, um das dekodierte Datum zurückzugeben. Das Folgende ist nur ein einfaches Beispiel; Es betrachtet jedes Zeichen und konvertiert es in einen numerischen Wert, der dann mit der DateSerial-Funktion verwendet wird, um eine Excel-Datums-Seriennummer zu erstellen:

Function DecodeDate(datecode As String)

Const X = "123456789abcdefghijklmnopqrstuvwxyz"

Dim D As Integer     Dim M As Integer     Dim Y As Integer

Application.Volatile     D = InStr(X, Right(datecode, 1))

M = InStr(X, Mid(datecode, 2, 1))

Y = 2000 + InStr(X, Left(datecode, 1))

DecodeDate = DateSerial(Y, M, D)

End Function

Es sollte auch darauf hingewiesen werden, dass unabhängig von dem von Ihnen verwendeten Ansatz ein inhärenter Fehler in Ihren Datumscodes vorliegt. Das Jahr verwendet die Codewerte 1 bis 9 und a bis z. Dies bedeutet, dass der Datumscode einer von 35 möglichen Werten sein kann. Wenn zum Jahr 2000 (dem Basisjahr für die Beschreibung des Codes) hinzugefügt, bedeutet dies, dass der maximale Jahreswert, der codiert werden kann, 2035 beträgt. Ein Datum nach diesem Jahr funktioniert mit dieser Codierung nicht.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (12422) gilt für Microsoft Excel 97, 2000, 2002 und 2003.

Eine Version dieses Tippes für die Multifunktionsleistenoberfläche von Excel (Excel 2007 und höher) finden Sie hier:

Entschlüsseln eines codierten Datums.