Berapa kerap kita mendapat tarikh dalam spreadsheet yang dibekalkan kepada kita pada 12.26.2016, atau 26/12/2016 (format UK), hanya diberitahu tarikhnya tidak sah atau tidak ada bulan 26? Artikel ini menerangkan tarikh penetapan dengan VBA, menggunakan fungsi TRIM, LEFT, RIGHT dan MID.
Artikel tersebut menganggap pembaca memaparkan pita Pengembang dan biasa dengan Editor VBA. Sekiranya tidak, sila "Tab Pembangun Excel" Google atau "Tetingkap Kod Excel".
Xlsm dalam latihan ini boleh dimuat turun di sini.
Bukan Masalah Kita!
Tempat terbaik untuk menyelesaikan masalah adalah sumbernya. Walau bagaimanapun, tidak ada pujukan yang dapat meyakinkan jabatan gaji dalam hal ini bahawa 12.26.1994 bukanlah tarikh yang sah (kecuali jika dikonfigurasikan dalam Panel Kawalan komputer untuk beberapa negara Eropah Timur).
Sebenarnya kita dapat membuktikan bahawa ia tidak dapat dibaca oleh mesin. Contohnya, menambah 7 hari ke tarikh:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
sedangkan ...
"=2017-01-01 + 7" = 2017/01/08.
Mari kita anggap mereka menunjukkan bahawa itu bukan masalah mereka.
Format Tarikh
Perkara pertama yang harus kita pastikan adalah sama ada tarikhnya dalam format AS atau Antarabangsa.
Contoh kami menjelaskan bahawa kami melihat penggunaan USA, iaitu MDY dan bukan DMY format antarabangsa.
Sebaik sahaja kami mengetahui sumbernya, kami perlu mengubah format data agar Excel dapat mengetahuinya, sama ada di peringkat antarabangsa atau AS
Cara terbaik untuk melakukannya adalah dengan menukar tarikh menjadi yyyymmdd, format yang tidak memerlukan kelayakan.
Proses
Kami akan menelusuri setiap baris dalam dokumen, memanggil fungsi untuk "membetulkan" tarikh sesuai dengan negara sumber. Setelah tarikhnya diperbetulkan, kami akan mengira umur Pekerja.
Kod ini
Salin kod berikut ke modul baru:
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
Tambahkan butang ke borang anda, dan tetapkan ke Sub Main.
Kaveat
Sebelum menambahkan kod yang terlalu rumit ke modul anda, harap maklum bahawa Excel tidak selalu stabil dalam pengembangan aplikasi utama, dan sering tidak dapat memulihkan kod yang rosak itu sendiri. Hasilnya adalah kerosakan satu-satunya salinan anda kerana kerosakan berlaku di "Simpan".
Sandarkan dengan kerap dan gunakan alat untuk memperbaikinya Rasuah fail Excel.
Pengenalan Pengarang:
Felix Hooker adalah pakar pemulihan data di DataNumen, Inc., yang merupakan pemimpin dunia dalam teknologi pemulihan data, termasuk pembaikan rar memfailkan rasuah dan produk perisian pemulihan sql. Untuk maklumat lebih lanjut, lawati www.datanumen.com