Hur ofta får vi datum i kalkylblad som skickas till oss som 12.26.2016/26/12 eller 2016/26/XNUMX (UK-format), bara för att få veta att datumet är ogiltigt eller att det inte finns någon månad XNUMX? Den här artikeln utforskar fixeringsdatum med VBA med hjälp av TRIM, VÄNSTER, HÖGER och MID-funktioner.
Artikeln förutsätter att läsaren har utvecklarbandet och är bekant med VBA Editor. Om inte, vänligen googla "Excel Developer Tab" eller "Excel Code Window".
Xlsm i denna övning kan laddas ner här..
Inte vårt problem!
Det bästa stället att lösa ett problem är källan. Ingen övertygelse kan dock övertyga löneavdelningen i det här fallet att 12.26.1994 inte är ett giltigt datum (såvida det inte är konfigurerat i datorns kontrollpanel för vissa östeuropeiska länder).
Vi kan faktiskt bevisa att det inte är maskinläsbart. Till exempel lägga till 7 dagar till ett datum:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
medan ...
"=2017-01-01 + 7" = 2017/01/08.
Låt oss anta att de föreslår att det inte är deras problem.
Datumformat
Det första vi måste ta reda på är om datumet är i USA eller internationellt format.
Vårt exempel gör det tydligt att vi tittar på USA-användning, dvs. MDY snarare än internationellt format DMY.
När vi väl har etablerat källan måste vi ändra dataformaten så att Excel kan förstå dem, vare sig internationellt eller i USA.
Det bästa sättet att göra detta är att ändra datumet till yyyymmdd, ett format som inte behöver någon behörighet.
Processen
Vi går igenom varje rad i dokumentet och kallar en funktion för att ”korrigera” datumet efter källland. När datumet har korrigerats beräknar vi anställdens ålder.
Koden
Kopiera följande kod till en ny 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
Lägg till en knapp i ditt formulär och tilldela den till Sub Main.
Caveat
Innan du lägger till för mycket komplex kod i din modul bör du tänka på att Excel inte alltid är stabil i större applikationsutveckling och ofta inte kan återställa själva den skadade koden. Resultatet kan bli korruption av din enda kopia eftersom korruptionen sker i "Spara".
Säkerhetskopiera ofta och använd ett verktyg för att fixa Excel-filkorruption.
Författarintroduktion:
Felix Hooker är en dataåterställningsexpert i DataNumen, Inc., som är världsledande inom teknik för återställning av data, inklusive reparation rar arkivera korruption och mjukvaruprodukter för SQL-återställning. För mer information besök www.datanumen.com