Luis nhận được thông tin trong đó ngày tháng được mã hóa sao cho năm, tháng và ngày được thay thế bằng một ký tự duy nhất. Đối với mỗi trường, các số từ 1 đến 9 được sử dụng và sau các chữ cái đó, từ a = 10, b = 11, …​ v = 31. Ví dụ: mã ngày ‘bc2’ có nghĩa là b = 11 (năm 2011), c = 12 (tháng) và ngày = 2. Luis tự hỏi liệu có thể nghĩ ra một hàm để thay thế ngày được mã hóa bằng một định dạng ngày chung, chẳng hạn như dd / mm / yyyy.

Thực ra có một số cách bạn có thể giải quyết vấn đề này.

Một cách là thiết lập “bảng tương đương” trong trang tính, trong đó cột bên trái bao gồm một ký tự mã và cột bên phải chỉ ra giá trị số được liên kết với ký tự đó. Sau đó, bạn có thể tạo một công thức sử dụng hàm VLOOKUP để tìm các giá trị và chuyển đổi kết quả thành ngày tháng.

Ví dụ: tạo cho bạn bảng tương đương trong một số ô không sử dụng ở bên phải dữ liệu của bạn. Trong trường hợp của tôi, tôi đặt bảng trong cột P và Q. Trong cột P, tôi đặt các ký tự mã, từ 1 đến 9 và a đến z. (Đảm bảo bạn đặt trước các chữ số từ 1 đến 9 bằng dấu nháy đơn để chúng được lưu trữ dưới dạng văn bản thay vì số.) Trong cột QI, hãy đặt các số từ 1 đến 35. Toàn bộ phạm vi này (P1: Q35) Sau đó, tôi đặt tên là DateTable .

Đây là công thức, sau đó, sẽ trả về một ngày được giải mã cho một ngày được mã hóa được lưu trữ trong ô 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))

Hãy nhớ rằng đây là một công thức duy nhất, liên tục. Một kỹ thuật khác là bỏ qua hoàn toàn các bảng tương đương và thay vào đó sử dụng một công thức để thực hiện chuyển đổi. Sau đây là một ví dụ sẽ giải mã một ngày trong ô A1:

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

Công thức này sử dụng hàm FIND để dịch từ ký tự mã sang một giá trị, sau đó các giá trị này được sử dụng trong hàm DATE để trả về ngày mong muốn. Một công thức khả thi khác dựa vào các giá trị mã ký tự để tạo ngày:

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

Cuối cùng, bạn có thể tạo một hàm do người dùng xác định để trả về ngày đã giải mã. Sau đây chỉ là một ví dụ đơn giản; nó xem xét từng ký tự và chuyển nó thành giá trị số, sau đó được sử dụng với hàm DateSerial để tạo số sê-ri ngày tháng trong 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

Cũng cần phải chỉ ra rằng bất kể phương pháp tiếp cận bạn sử dụng là gì, vẫn có một lỗ hổng cố hữu trong mã ngày của bạn. Năm sử dụng các giá trị mã từ 1 đến 9 và a đến z. Điều này có nghĩa là mã ngày có thể là một trong 35 giá trị có thể. Khi được thêm vào năm 2000 (năm cơ sở cho cách bạn mô tả mã), điều đó có nghĩa là giá trị năm tối đa có thể được mã hóa là 2035. Bất kỳ ngày nào sau năm đó sẽ không hoạt động với mã này.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12422) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: