Con quale frequenza otteniamo date nei fogli di calcolo forniti come 12.26.2016 o 26/12/2016 (formato UK), solo per sapere che la data non è valida o non c'è il mese 26? Questo articolo esplora la fissazione delle date con VBA, utilizzando le funzioni TRIM, LEFT, RIGHT e MID.
L'articolo presuppone che il lettore abbia visualizzato il nastro Developer e abbia familiarità con l'editor VBA. In caso contrario, Google "Excel Developer Tab" o "Excel Code Window".
L'xlsm in questo esercizio può essere scaricato Qui..
Non è un nostro problema!
Il posto migliore per risolvere un problema è alla fonte. Tuttavia, nessuna forza di persuasione può convincere l'ufficio paghe in questo caso che il 12.26.1994 non è una data valida (a meno che non sia stata configurata nel pannello di controllo del computer per alcuni paesi dell'Europa orientale).
Infatti possiamo dimostrare che non è leggibile dalla macchina. Ad esempio, aggiungendo 7 giorni a una data:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
mentre…
"=2017-01-01 + 7" = 2017/01/08.
Supponiamo che suggeriscano che non è un loro problema.
Formati data
La prima cosa da verificare è se la data è in formato USA o internazionale.
Il nostro esempio chiarisce che stiamo osservando l'uso USA, cioè MDY piuttosto che il formato internazionale DMY.
Una volta stabilita la fonte, dobbiamo modificare i formati dei dati in modo che Excel possa dar loro un senso, sia a livello internazionale che negli Stati Uniti.
Il modo migliore per farlo è cambiare la data in aaaammgg, un formato che non richiede alcuna qualificazione.
Come funziona
Passeremo in rassegna ogni riga del documento, chiamando una funzione per "correggere" la data in base al paese di origine. Una volta corretta la data, calcoleremo l'età del dipendente.
Il codice
Copia il seguente codice in un nuovo modulo:
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
Aggiungi un pulsante al tuo modulo e assegnalo a Sub Main.
Avvertimento
Prima di aggiungere troppo codice complesso al tuo modulo, tieni presente che Excel non è sempre stabile nello sviluppo di applicazioni importanti e spesso non è in grado di recuperare il codice danneggiato stesso. Il risultato potrebbe essere la corruzione della tua unica copia poiché la corruzione avviene nel "Salva".
Esegui il backup frequentemente e utilizza uno strumento per risolvere il problema Corruzione del file Excel.
Introduzione dell'autore:
Felix Hooker è un esperto di recupero dati in DataNumen, Inc., che è il leader mondiale nelle tecnologie di recupero dati, tra cui riparazione rar corruzione dei file e prodotti software di recupero SQL. Per maggiori informazioni visita www.datanumen.com