Луис получает информацию, в которой даты закодированы таким образом, что годы, месяцы и дни заменяются одним символом. Для каждого поля используются числа от 1 до 9, а после них буквы от a = 10, b = 11, …​ v = 31. Например, код даты «bc2» означает b = 11 (2011 год), c = 12 (месяц) и day = 2. Луис задается вопросом, можно ли разработать функцию для замены закодированной даты на общий формат даты, такой как дд / мм / гггг.

На самом деле есть несколько способов решения этой проблемы.

Один из способов — создать «таблицы эквивалентности» на листе, где левый столбец включает кодовый символ, а правый указывает числовое значение, связанное с этим символом. Затем вы можете создать формулу, которая использует ВПР для поиска значений и преобразования результатов в дату.

В качестве примера создайте свою таблицу эквивалентности в некоторых неиспользуемых ячейках справа от ваших данных. В моем случае я помещаю таблицу в столбцы P и Q. В столбце P я помещаю символы кода от 1 до 9 и от a до z. (Убедитесь, что вы поставили перед цифрами от 1 до 9 апостроф, чтобы они сохранялись как текст, а не как числа.) В столбце Q я помещаю числа от 1 до 35. Весь этот диапазон (P1: Q35) я затем дал имя DateTable .

Вот формула, которая возвращает декодированную дату для закодированной даты, хранящейся в ячейке A1:

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

Помните, что это единая непрерывная формула. Другой способ — полностью обойти таблицы эквивалентности и вместо этого использовать формулу для преобразования. Ниже приведен пример декодирования даты в ячейке A1:

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

В этой формуле используется функция НАЙТИ для преобразования символа кода в значение, а затем эти значения используются в функции ДАТА для возврата желаемой даты. Вместо этого другая возможная формула полагается на значения кода символа для создания даты:

=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)

Наконец, вы можете создать определяемую пользователем функцию для возврата декодированной даты. Ниже приводится простой пример; он просматривает каждый символ и преобразует его в числовое значение, которое затем используется с функцией DateSerial для создания серийного номера даты Excel:

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

Также следует отметить, что независимо от используемого вами подхода в ваших кодах даты есть врожденный недостаток. Год использует кодовые значения от 1 до 9 и от a до z. Это означает, что код даты может быть одним из 35 возможных значений. При добавлении к 2000 году (базовый год для того, как вы описали код) это означает, что максимальное значение года, которое может быть закодировано, составляет 2035. Любая дата после этого года не будет работать с этим кодированием.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12423) применим к Microsoft Excel 2007, 2010, 2013 и 2016.

Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Deciphering_a_Coded_Date [Расшифровка закодированной даты].