Kaip dažnai gauname datas skaičiuoklėse, kurios mums pateikiamos kaip 12.26.2016-26-12 arba 2016-26-XNUMX (JK formatu), tik tada, kai pranešama, kad data neteisinga arba nėra XNUMX mėnesio? Šiame straipsnyje nagrinėjamas datų nustatymas naudojant VBA, naudojant TRIM, LEFT, RIGHT ir MID funkcijas.
Straipsnyje daroma prielaida, kad skaitytojas turi rodomą kūrėjo juostelę ir yra susipažinęs su VBA redaktoriumi. Jei ne, naudokite Google „Excel Developer Tab“ arba „Excel Code Window“.
Šio pratimo xlsm failą galima atsisiųsti čia.
Ne mūsų problema!
Geriausia vieta išspręsti problemą yra jos šaltinis. Tačiau jokie įtikinėjimai negali įtikinti darbo užmokesčio departamento šiuo atveju, kad 12.26.1994-XNUMX-XNUMX nėra tinkama data (nebent kai kuriose Rytų Europos šalyse taip sukonfigūruota kompiuterio valdymo skydelyje).
Tiesą sakant, galime įrodyti, kad jis nėra nuskaitomas mašininiu būdu. Pavyzdžiui, 7 dienų pridėjimas prie datos:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
kadangi…
"=2017-01-01 + 7" = 2017/01/08.
Tarkime, jie teigia, kad tai ne jų problema.
Datos formatai
Pirmas dalykas, kurį turime išsiaiškinti, ar data yra JAV ar tarptautiniu formatu.
Mūsų pavyzdys aiškiai parodo, kad žiūrime į JAV naudojimą, ty MDY, o ne tarptautinį DMY formatą.
Kai nustatome šaltinį, turime pakeisti duomenų formatus, kad „Excel“ galėtų juos suprasti tarptautiniu ar JAV.
Geriausias būdas tai padaryti – pakeisti datą į yyyymmdd – formatą, kuriam nereikia jokių sąlygų.
Procesas
Peržiūrėsime kiekvieną dokumento eilutę, iškviesdami funkciją „pataisyti“ datą pagal šaltinio šalį. Pataisę datą, apskaičiuosime Darbuotojo amžių.
Kodeksas
Nukopijuokite šį kodą į naują 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
Pridėkite mygtuką prie formos ir priskirkite jį prie pagrindinio puslapio.
Caveat
Prieš pridėdami per daug sudėtingo kodo į savo modulį, atminkite, kad „Excel“ ne visada stabili kuriant pagrindines programas ir dažnai negali atkurti paties sugadinto kodo. Rezultatas gali būti jūsų vienintelės kopijos sugadinimas, nes sugadinimas įvyksta „Išsaugoti“.
Dažnai kurkite atsargines kopijas ir pataisykite naudodami įrankį Excel failo sugadinimas.
Autoriaus įvadas:
Felixas Hookeris yra duomenų atkūrimo ekspertas DataNumen, Inc., kuri yra pasaulyje duomenų atkūrimo technologijų lyderė, įskaitant remontas rar failo korupcija ir sql atkūrimo programinės įrangos produktai. Norėdami gauti daugiau informacijos, apsilankykite WWW.datanumen.com