Seberapa sering kami mendapatkan tanggal di spreadsheet yang diberikan kepada kami sebagai 12.26.2016, atau 26/12/2016 (format Inggris Raya), hanya untuk diberi tahu bahwa tanggal tersebut tidak valid atau tidak ada bulan 26? Artikel ini membahas memperbaiki tanggal dengan VBA, menggunakan fungsi TRIM, LEFT, RIGHT dan MID.
Artikel ini mengasumsikan bahwa pembaca memiliki pita Pengembang yang ditampilkan dan sudah terbiasa dengan Editor VBA. Jika tidak, silakan Google "Tab Pengembang Excel" atau "Jendela Kode Excel".
Xlsm dalam latihan ini dapat diunduh di sini.
Bukan Masalah Kami!
Tempat terbaik untuk memecahkan masalah adalah di sumbernya. Namun, tidak ada jumlah persuasi yang dapat meyakinkan departemen penggajian dalam kasus ini bahwa 12.26.1994 bukan tanggal yang valid (kecuali dikonfigurasi di Panel Kontrol komputer untuk beberapa negara Eropa Timur).
Faktanya, kami dapat membuktikan bahwa ini tidak dapat dibaca oleh mesin. Misalnya, menambahkan 7 hari ke tanggal:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
sedangkan…
"=2017-01-01 + 7" = 2017/01/08.
Mari kita asumsikan bahwa mereka menyarankan itu bukan masalah mereka.
Format Tanggal
Hal pertama yang harus kita pastikan adalah apakah tanggalnya dalam format USA atau International.
Contoh kami memperjelas bahwa kami melihat penggunaan USA, yaitu MDY daripada format internasional DMY.
Setelah kami menetapkan sumbernya, kami perlu mengubah format data sehingga Excel dapat memahaminya, baik secara internasional atau di AS.
Cara terbaik untuk melakukannya adalah dengan mengubah tanggal menjadi tttt, format yang tidak memerlukan kualifikasi.
Proses
Kami akan menggilir setiap baris dalam dokumen, memanggil fungsi untuk "mengoreksi" tanggal sesuai dengan negara sumber. Setelah tanggal dikoreksi, kami akan menghitung usia Karyawan.
Kode
Salin kode berikut ke dalam 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 tombol ke formulir Anda, dan tetapkan ke Sub Utama.
Surat protes
Sebelum menambahkan terlalu banyak kode kompleks ke modul Anda, perhatikan bahwa Excel tidak selalu stabil dalam pengembangan aplikasi utama, dan seringkali tidak dapat memulihkan kode yang rusak itu sendiri. Hasilnya bisa jadi hanya salinan Anda yang rusak karena korupsi terjadi di "Simpan".
Buat cadangan sering dan gunakan alat untuk memperbaikinya Kerusakan file Excel.
Pengantar Penulis:
Felix Hooker adalah pakar pemulihan data di DataNumen, Inc., yang merupakan pemimpin dunia dalam teknologi pemulihan data, termasuk memperbaiki rar mengajukan korupsi dan produk perangkat lunak pemulihan sql. Untuk informasi lebih lanjut kunjungi www.datanumen.com