Tần suất chúng tôi nhận được ngày trong bảng tính được cung cấp cho chúng tôi là 12.26.2016 hoặc 26/12/2016 (định dạng của Vương quốc Anh), chỉ để được thông báo ngày không hợp lệ hoặc không có tháng 26? Bài viết này khám phá việc sửa ngày bằng VBA, sử dụng các hàm TRIM, LEFT, RIGHT và MID.
Bài viết giả định rằng người đọc đã hiển thị dải băng Nhà phát triển và quen thuộc với Trình soạn thảo VBA. Nếu không, hãy Google “Excel Developer Tab” hoặc “Excel Code Window”.
Có thể tải xuống xlsm trong bài tập này đây.
Không phải vấn đề của chúng tôi!
Nơi tốt nhất để giải quyết vấn đề là tại nguồn. Tuy nhiên, không có sức thuyết phục nào có thể thuyết phục bộ phận tính lương trong trường hợp này rằng ngày 12.26.1994 không phải là một ngày hợp lệ (trừ khi được cấu hình như vậy trong Bảng điều khiển của máy tính đối với một số quốc gia Đông Âu).
Trên thực tế, chúng tôi có thể chứng minh rằng nó không thể đọc được bằng máy. Ví dụ: thêm 7 ngày vào một ngày:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
trong khi…
"=2017-01-01 + 7" = 2017/01/08.
Hãy giả sử họ cho rằng đó không phải là vấn đề của họ.
Định dạng ngày
Điều đầu tiên chúng ta phải xác định là ngày ở định dạng Hoa Kỳ hay Quốc tế.
Ví dụ của chúng tôi cho thấy rõ rằng chúng tôi đang xem xét cách sử dụng của Hoa Kỳ, tức là MDY chứ không phải định dạng quốc tế DMY.
Khi chúng tôi đã thiết lập nguồn, chúng tôi cần thay đổi định dạng dữ liệu để Excel có thể hiểu được chúng, cho dù ở quốc tế hay ở Hoa Kỳ.
Cách tốt nhất để thực hiện việc này là thay đổi ngày thành yyyymmdd, một định dạng không cần trình độ chuyên môn.
Quy Trình Chế Biến
Chúng tôi sẽ duyệt qua từng hàng trong tài liệu, gọi một hàm để “sửa” ngày theo quốc gia nguồn. Khi ngày được sửa, chúng tôi sẽ tính tuổi của Nhân viên.
Mật mã
Sao chép đoạn mã sau vào một mô-đun mới:
Option Explicit
Sub Main()
Dim strNewFormat As String
Dim strDate As String
Sheets("Main").Range("B4").Select
'Cycle through the sheet rows, using IDNumber as an anchor
'to prevent a premature halt caused by a blank date of birth
Do While ActiveCell > ""
If ActiveCell.Offset(0, 2) > "" Then
strDate = ActiveCell.Offset(0, 2)
'Remove leading or trailing spaces
strDate = Trim(strDate)
'Call the function
strNewFormat = ReformatDate(strDate, "USA")
'Write the result from the function ReformatDate to a new column
ActiveCell.Offset(0, 3) = strNewFormat
'Determine age by subtracting the previous column from today's date
ActiveCell.Offset(0, 4) = "=(NOW()-RC[-1])/365.25"
'Convert to intger, thus lopping off decimal places
ActiveCell.Offset(0, 4) = Int(ActiveCell.Offset(0, 4))
End If
Range("B" & ActiveCell.Row + 1).Select
Loop
End Sub
Function ReformatDate(sDate As String, sSource As String)
Dim yyyy, mm, dd As String
yyyy = Right(sDate, 4)
If sSource = "USA" Then
mm = Left(sDate, 2)
dd = Mid(sDate, 4, 2)
Else
mm = Mid(sDate, 4, 2)
dd = Left(sDate, 2)
End If
ReformatDate = yyyy & "-" & mm & "-" & dd
End Function
Thêm một nút vào biểu mẫu của bạn và gán nó cho Sub Main.
Báo trước
Trước khi thêm quá nhiều mã phức tạp vào mô-đun của bạn, hãy lưu ý rằng Excel không phải lúc nào cũng ổn định trong quá trình phát triển ứng dụng chính và thường không thể tự khôi phục mã bị hỏng. Kết quả có thể là bản sao duy nhất của bạn bị hỏng vì lỗi xảy ra trong “Lưu”.
Sao lưu thường xuyên và sử dụng công cụ để khắc phục Tệp Excel bị hỏng.
Giới thiệu tác giả:
Felix Hooker là một chuyên gia phục hồi dữ liệu trong DataNumen, Inc., công ty hàng đầu thế giới về công nghệ khôi phục dữ liệu, bao gồm sửa rar hỏng tập tin và các sản phẩm phần mềm phục hồi sql. Để biết thêm thông tin, hãy truy cập www.datanumennăm