Luis接收信息,其中编码日期,以便将年,月和日分别替换为一个字符。对于每个字段,使用数字1到9,然后使用字母,从a = 10,b = 11,…​ v = 31开始。例如,日期代码“ bc2”表示b = 11(2011年),c = 12(月)和day = 2。 Luis想知道是否可以设计一个函数来用通用的日期格式(例如dd / mm / yyyy)替换编码日期。

实际上,有几种方法可以解决此问题。

一种方法是在工作表中设置“等效表”,其中左列包含代码字符,而右列指示与该字符关联的数值。然后,您可以创建一个使用VLOOKUP查找值并将其转换为日期的公式。

例如,在数据右侧的一些未使用的单元格中创建等效表。就我而言,我将表放在P和Q列中。在P列中,我将代码字符1到9和a到z放入代码中。 (确保在数字1到9之前加上撇号,以便将它们存储为文本而不是数字。)在QI列中,将数字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"))

此公式使用FIND函数将代码字符转换为一个值,然后在DATE函数中使用这些值以返回所需的日期。相反,另一个可能的公式取决于字符代码值来创建日期:

=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_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(12422)适用于Microsoft Excel 97、2000、2002和2003。您可以在以下位置找到用于Excel功能区界面(Excel 2007及更高版本)的本技巧的版本:

链接:/ excelribbon-Deciphering_a_Coded_Date [解密编码日期]。