Koliko često dobivamo datume u proračunskim tablicama koje su nam dostavljene kao 12.26.2016. ili 26. (UK format), samo da bi nam rekli da je datum nevažeći ili da nema 12. mjeseca? Ovaj članak istražuje popravljanje datuma pomoću VBA, korištenjem funkcija TRIM, LEFT, RIGHT i MID.
Članak pretpostavlja da čitatelj ima prikazanu vrpcu za razvojne programere i da je upoznat s VBA uređivačem. Ako ne, molimo proguglajte “Excel Developer Tab” ili “Excel Code Window”.
Xlsm u ovoj vježbi može se preuzeti ovdje.
Nije naš problem!
Najbolje mjesto za rješavanje problema je izvor. Međutim, nikakva količina uvjeravanja ne može uvjeriti odjel za obračun plaća u ovom slučaju da 12.26.1994. XNUMX. XNUMX. nije valjan datum (osim ako nije tako konfigurirano u upravljačkoj ploči računala za neke istočnoeuropske zemlje).
Zapravo možemo dokazati da nije strojno čitljiv. Na primjer, dodavanje 7 dana datumu:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
dok…
"=2017-01-01 + 7" = 2017/01/08.
Pretpostavimo da sugeriraju da to nije njihov problem.
Formati datuma
Prvo što moramo utvrditi je je li datum u američkom ili međunarodnom formatu.
Naš primjer jasno pokazuje da gledamo upotrebu u SAD-u, tj. MDY, a ne međunarodni format DMY.
Nakon što smo ustanovili izvor, moramo promijeniti formate podataka kako bi ih Excel mogao shvatiti, bilo u inozemstvu ili u SAD-u.
Najbolji način da to učinite jest promijeniti datum u ggggmmdd, format koji ne zahtijeva kvalifikacije.
Proces
Kružit ćemo kroz svaki red u dokumentu, pozivajući funkciju za "ispravljanje" datuma prema zemlji izvora. Nakon što se datum ispravi, izračunat ćemo dob Zaposlenika.
Kodeks
Kopirajte sljedeći kod u novi modul:
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
Dodajte gumb svom obrascu i dodijelite ga podglavnom.
Naredba za suspendovanje
Prije dodavanja previše složenog koda u vaš modul, imajte na umu da Excel nije uvijek stabilan u razvoju velikih aplikacija i često ne može sam oporaviti oštećeni kod. Rezultat bi mogao biti oštećenje vaše jedine kopije budući da se oštećenje događa u "Spremi".
Često stvarajte sigurnosne kopije i upotrijebite alat za popravak Oštećenje Excel datoteke.
Uvod za autora:
Felix Hooker je stručnjak za oporavak podataka u DataNumen, Inc., koji je svjetski lider u tehnologijama za oporavak podataka, uključujući popravak rar datoteka korupcija i softverski proizvodi za oporavak sql-a. Za više informacija posjetite www.datanumen.com