Jak často dostáváme data v tabulkách, které nám byly dodány jako 12.26.2016. 26. 12 nebo 2016. 26. XNUMX (formát pro Velkou Británii), jen aby bylo řečeno, že datum je neplatné nebo není XNUMX. měsíc? Tento článek zkoumá data opravy pomocí VBA pomocí funkcí TRIM, LEFT, RIGHT a MID.
V článku se předpokládá, že čtenář má zobrazenou pásku Developer a je obeznámen s editorem VBA. Pokud ne, prosím „Google Developer Card Tab“ nebo „Excel Code Window“.
Soubor xlsm v tomto cvičení lze stáhnout zde.
To není náš problém!
Nejlepší místo k vyřešení problému je u zdroje. Žádná míra přesvědčování však v tomto případě nemůže přesvědčit mzdové oddělení, že 12.26.1994 není platné datum (pokud to není nakonfigurováno v Ovládacích panelech počítače pro některé východoevropské země).
Ve skutečnosti můžeme dokázat, že to není strojově čitelné. Například přidání 7 dnů k datu:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
zatímco…
"=2017-01-01 + 7" = 2017/01/08.
Předpokládejme, že naznačují, že to není jejich problém.
Formáty data
První věc, kterou musíme zjistit, je, zda je datum ve formátu USA nebo v mezinárodním formátu.
Náš příklad objasňuje, že se díváme na využití v USA, tj. Spíše na MDY než na mezinárodní formát DMY.
Jakmile jsme založili zdroj, musíme změnit datové formáty, aby jim Excel mohl rozumět, ať už v mezinárodním měřítku, nebo v USA.
Nejlepší způsob, jak to udělat, je změnit datum na rrrrmmdd, formát, který nevyžaduje žádnou kvalifikaci.
Proces
Procházíme každý řádek v dokumentu a voláme funkci, která „opraví“ datum podle země zdroje. Jakmile bude datum opraveno, vypočítáme věk zaměstnance.
Kodex
Zkopírujte následující kód do nového modulu:
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
Přidejte do formuláře tlačítko a přiřaďte jej k Sub Main.
varování
Než do svého modulu přidáte příliš mnoho složitého kódu, uvědomte si, že Excel není ve vývoji hlavních aplikací vždy stabilní a často nedokáže sám obnovit poškozený kód. Výsledkem může být poškození vaší jediné kopie, protože k poškození dojde v části „Uložit“.
Často zálohujte a opravte pomocí nástroje Poškození souboru Excel.
Úvod autora:
Felix Hooker je odborník na obnovu dat v oboru DataNumen, Inc., která je světovým lídrem v oblasti technologií pro obnovu dat, včetně opravit rar poškození souboru a SQL softwarové produkty pro obnovu. Pro více informací navštivte www.datanumen.com