Milyen gyakran kapunk dátumokat a táblázatokban, amelyeket 12.26.2016. 26. 12. vagy 2016. 26. XNUMX. (Egyesült Királyság formátumban) formájában kapunk meg, csak akkor közöljük, hogy a dátum érvénytelen, vagy nincs XNUMX. hónap? Ez a cikk bemutatja a dátumok rögzítését VBA-val, a TRIM, LEFT, RIGHT és MID funkciók használatával.
A cikk feltételezi, hogy az olvasó megjeleníti a Fejlesztői szalagot, és ismeri a VBA-szerkesztőt. Ha nem, használja a Google „Excel Developer Tab” vagy az „Excel Code Window” szolgáltatását.
A gyakorlatban szereplő xlsm letölthető itt .
Nem a mi problémánk!
A probléma megoldásának legjobb helye a forrásnál van. Ebben az esetben azonban semmiféle meggyőzés nem tudja meggyőzni a bérszámfejtési osztályt arról, hogy 12.26.1994. XNUMX. XNUMX. nem érvényes dátum (hacsak nincs így beállítva a számítógép Vezérlőpultján néhány kelet-európai országban).
Valójában be tudjuk bizonyítani, hogy géppel nem olvasható. Például 7 nap hozzáadása egy dátumhoz:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
míg…
"=2017-01-01 + 7" = 2017/01/08.
Tételezzük fel, hogy azt sugallják, hogy nem az ő problémájuk.
Dátumformátumok
Az első dolog, amit meg kell győződnünk arról, hogy a dátum amerikai vagy nemzetközi formátumban van-e megadva.
Példánk egyértelművé teszi, hogy az USA-beli felhasználást vizsgáljuk, azaz az MDY-t, nem pedig a nemzetközi DMY-formátumot.
Miután megállapítottuk a forrást, módosítanunk kell az adatformátumokat, hogy az Excel értelmezni tudja azokat, akár nemzetközileg, akár az USA-ban.
Ennek legjobb módja, ha módosítja a dátumot ééééhhnn-re, amely formátum nem igényel minősítést.
A folyamat
A dokumentum minden sorát végigfutjuk, és meghívunk egy függvényt a dátum „javításához” a forrásország szerint. A dátum kijavítása után kiszámoljuk a Munkavállaló életkorát.
A kód
Másolja be a következő kódot egy új modulba:
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
Adjon hozzá egy gombot az űrlaphoz, és rendelje hozzá a fő aloldalhoz.
Ellentmondás
Mielőtt túl sok összetett kódot adna a modulhoz, vegye figyelembe, hogy az Excel nem mindig stabil a nagyobb alkalmazásfejlesztés során, és gyakran magát a sérült kódot sem tudja helyreállítani. Az eredmény az egyetlen példány megsérülése lehet, mivel a sérülés a „Mentés” menüben történik.
Rendszeresen készítsen biztonsági másolatot, és használjon eszközt a javításhoz Excel fájl sérülés.
Szerző Bevezetés:
Felix Hooker adat-helyreállítási szakértő DataNumen, Inc., amely világelső az adat-helyreállítási technológiák területén, beleértve javítás rar fájlkorrupció és SQL helyreállítási szoftvertermékek. További információért látogasson el www.datanumen.com