لوئیس اطلاعاتی را دریافت می کند که در آن تاریخ ها به گونه ای رمزگذاری شده اند که سال ها، ماه ها و روزها با یک کاراکتر جایگزین می شوند. برای هر فیلد از اعداد 1 تا 9 و بعد از آن حروف از a=10، b=11، ... v=31 استفاده می شود. به عنوان مثال، کد تاریخ bc2 به معنای b=11 (سال 2011)، c=12 (ماه) و day=2 است. لوئیس به این فکر می کند که آیا می توان تابعی را برای جایگزینی تاریخ کدگذاری شده با یک قالب تاریخ رایج مانند dd/mm/yyyy ابداع کرد.
در واقع راه های مختلفی برای حل این مشکل وجود دارد. یک راه این است که "جدول معادل" را در یک کاربرگ تنظیم کنید، که در آن ستون سمت چپ شامل یک کاراکتر کد و سمت راست مقدار عددی مرتبط با آن کاراکتر را نشان می دهد. سپس می توانید فرمولی بسازید که از VLOOKUP برای یافتن مقادیر و تبدیل نتایج به تاریخ استفاده می کند.
به عنوان مثال، جدول معادل خود را در برخی از سلول های استفاده نشده در سمت راست داده خود ایجاد کنید. در مورد من، جدول را در ستون های P و Q قرار دادم. در ستون PI کاراکترهای کد، 1 تا 9 و a تا z را قرار دادم. (مطمئن شوید که قبل از ارقام 1 تا 9 با یک آپستروف قرار می گیرید تا به عنوان متن ذخیره شوند نه به صورت اعداد.) در ستون QI اعداد 1 تا 35 را قرار دهید. کل این محدوده (P1:Q35) سپس نامی از Table Table دادم. . در اینجا فرمولی است که تاریخ رمزگشایی شده را برای تاریخ رمزگذاری شده ذخیره شده در سلول 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))
Finally, you could create a user-defined function to return the decoded date. The following is just a simple example; it looks at each character and converts it to a numeric value that is then used with the DateSerial function to create an Excel date serial number:
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
It should be pointed out, as well, that regardless of the approach you use, there is an inherent flaw in your date codes. The year uses the code values 1 through 9 and a through z. This means that the date code can be one of 35 possible values. When added to the year 2000 (the base year for how you described the code), that means that the maximum year value that can be coded is 2035. Any date after that year will not work with this coding.
