Koliko često dobijamo datume u tabelama koje su nam dostavljene kao 12.26.2016. 26. 12. ili 2016. 26. XNUMX. (format u Ujedinjenom Kraljevstvu), samo da bi nam rekli da je datum nevažeći ili da nema XNUMX. meseca? Ovaj članak istražuje popravljanje datuma pomoću VBA, koristeći funkcije TRIM, LEFT, RIGHT i MID.
Članak pretpostavlja da čitač ima prikazanu traku programera i da je upoznat sa VBA Editorom. Ako ne, molimo Google "Excel Developer Tab" ili "Excel Code Window".
Xlsm u ovoj vježbi možete preuzeti OVDJE.
Nije naš problem!
Najbolje mjesto za rješavanje problema je izvor. Međutim, nikakva količina uvjeravanja ne može uvjeriti odjeljenje za obračun plaća u ovom slučaju da 12.26.1994. XNUMX. XNUMX. nije važeći datum (osim ako nije tako konfigurisan u kontrolnoj tabli računara za neke zemlje istočne Evrope).
U stvari, 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
Prva stvar koju moramo utvrditi je da li je datum u američkom ili međunarodnom formatu.
Naš primjer jasno pokazuje da gledamo na upotrebu u SAD-u, tj. MDY umjesto međunarodnog formata DMY.
Nakon što uspostavimo izvor, moramo promijeniti formate podataka kako bi Excel mogao razumjeti njih, bilo na međunarodnom nivou ili u SAD-u.
Najbolji način da to učinite je da promijenite datum u ggggmmdd, format za koji nije potrebna kvalifikacija.
Proces
Proći ćemo kroz svaki red u dokumentu, pozivajući funkciju da “ispravi” datum prema zemlji izvora. Kada se datum ispravi, izračunaćemo starost Zaposlenog.
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 dugme svom obrascu i dodijelite ga Podglavnom.
naredba za suspendovanje
Prije nego što dodate previše složenog koda u svoj 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 jer se oštećenje dešava u "Sačuvaj".
Često pravite rezervne kopije i koristite alat za popravku Oštećenje Excel datoteke.
Uvod za autora:
Felix Hooker je stručnjak za oporavak podataka DataNumen, Inc., koji je svjetski lider u tehnologijama za oporavak podataka, uključujući Popravak rar datoteka korupcija i sql softverski proizvodi za oporavak. Za više informacija posjetite www.datanumen.com