Cik bieži mēs saņemam datumus izklājlapās, kas mums tiek piegādātas kā 12.26.2016. gada 26. janvāris vai 12. gada 2016. decembris (Apvienotās Karalistes formāts), tikai lai paziņotu, ka datums nav derīgs vai nav 26. mēneša? Šis raksts pēta datumu fiksēšanu ar VBA, izmantojot funkcijas TRIM, LEFT, LIGHT un MID.
Rakstā tiek pieņemts, ka lasītājam ir parādīta izstrādātāja lente un viņš ir iepazinies ar VBA redaktoru. Ja nē, lūdzu, Google “Excel izstrādātāja cilne” vai “Excel koda logs”.
Šī uzdevuma xlsm var lejupielādēt šeit.
Nav mūsu problēma!
Labākā vieta problēmas risināšanai ir tās avots. Tomēr šajā gadījumā nekāda pārliecināšana nevar pārliecināt algu nodaļu, ka 12.26.1994. Nav derīgs datums (ja vien tas nav konfigurēts datora vadības panelī dažām Austrumeiropas valstīm).
Patiesībā mēs varam pierādīt, ka tas nav mašīnlasāms. Piemēram, pievienojot datumam 7 dienas:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
tā kā…
"=2017-01-01 + 7" = 2017/01/08.
Pieņemsim, ka viņi iesaka, ka tā nav viņu problēma.
Datuma formāti
Vispirms mums jāpārliecinās, vai datums ir norādīts ASV vai starptautiskā formātā.
Mūsu piemērs skaidri parāda, ka mēs aplūkojam ASV lietojumu, ti, MDY, nevis starptautiskā formāta DMY.
Kad avots ir izveidots, mums jāmaina datu formāti, lai programma Excel varētu tos saprast, neatkarīgi no tā, vai tas notiek starptautiskā mērogā vai ASV.
Labākais veids, kā to izdarīt, ir mainīt datumu uz ggggmmdd - formātu, kam nav nepieciešama kvalifikācija.
Process
Mēs pārlūkosim katru dokumenta rindu, izsaucot funkciju, lai “labotu” datumu atbilstoši avota valstij. Kad datums būs izlabots, mēs aprēķināsim Darbinieka vecumu.
Kodekss
Kopējiet šo kodu jaunā 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
Pievienojiet savai veidlapai pogu un piešķiriet to apakšpozīcijai.
Iebildums
Pirms modulim pievienojat pārāk daudz sarežģīta koda, ieteicams informēt, ka programma Excel ne vienmēr ir stabila galveno lietojumprogrammu izstrādē un bieži vien nespēj atkopt bojāto kodu. Rezultāts varētu būt jūsu vienīgā eksemplāra bojājums, jo korupcija notiek sadaļā “Saglabāt”.
Bieži dublējiet un izmantojiet rīku, lai to labotu Excel failu korupcija.
Autora ievads:
Fēlikss Hukers ir datu atkopšanas eksperts DataNumen, Inc., kas ir pasaules līderis datu atkopšanas tehnoloģiju, tostarp remonts rar failu korupciju un SQL atkopšanas programmatūras produkti. Lai iegūtu vairāk informācijas, apmeklējiet vietni www.datanumen. Ar