Hoe vaak krijgen we datums in spreadsheets die aan ons zijn geleverd als 12.26.2016/26/12 of 2016/26/XNUMX (UK-formaat), alleen om te horen dat de datum ongeldig is of dat er geen maand XNUMX is? Dit artikel onderzoekt het vastleggen van datums met VBA, met behulp van TRIM-, LEFT-, RIGHT- en MID-functies.
In het artikel wordt ervan uitgegaan dat de lezer het ontwikkelaarslint heeft weergegeven en bekend is met de VBA-editor. Als dit niet het geval is, gebruik dan Google "Excel Developer Tab" of "Excel Code Window".
De xlsm in deze oefening kan worden gedownload hier.
Niet ons probleem!
De beste plaats om een probleem op te lossen, is bij de bron. Geen enkele mate van overtuiging kan de salarisadministratie in dit geval echter overtuigen dat 12.26.1994 geen geldige datum is (tenzij zo geconfigureerd in het configuratiescherm van de computer voor sommige Oost-Europese landen).
We kunnen in feite bewijzen dat het niet machinaal leesbaar is. Bijvoorbeeld: 7 dagen bij een datum optellen:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
terwijl…
"=2017-01-01 + 7" = 2017/01/08.
Laten we aannemen dat ze suggereren dat het niet hun probleem is.
Datumnotaties
Het eerste dat we moeten controleren, is of de datum in het formaat VS of internationaal is.
Ons voorbeeld maakt duidelijk dat we naar het gebruik in de VS kijken, dwz MDY in plaats van het internationale formaat DMY.
Zodra we de bron hebben vastgesteld, moeten we de gegevensindelingen wijzigen zodat Excel ze kan begrijpen, zowel internationaal als in de VS.
De beste manier om dit te doen, is door de datum te wijzigen in jjjjmmdd, een formaat dat geen kwalificatie behoeft.
Het proces
We zullen elke rij in het document doorlopen en een functie aanroepen om de datum te "corrigeren" volgens het bronland. Zodra de datum is gecorrigeerd, berekenen we de leeftijd van de werknemer.
De code
Kopieer de volgende code naar een nieuwe module:
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
Voeg een knop toe aan uw formulier en wijs deze toe aan Sub Main.
Caveat
Voordat u te veel complexe code aan uw module toevoegt, moet u er rekening mee houden dat Excel niet altijd stabiel is bij de ontwikkeling van grote toepassingen en vaak de beschadigde code zelf niet kan herstellen. Het resultaat kan de beschadiging van uw enige kopie zijn, aangezien de beschadiging plaatsvindt in de "Opslaan".
Maak regelmatig een back-up en gebruik een tool om het probleem op te lossen Excel-bestand corruptie.
Auteur Introductie:
Felix Hooker is een expert op het gebied van gegevensherstel DataNumen, Inc., de wereldleider in technologieën voor gegevensherstel, waaronder reparatie rar bestand corruptie en sql-herstelsoftwareproducten. Voor meer informatie bezoek www.datanumen.com