À quelle fréquence recevons-nous des dates dans les feuilles de calcul qui nous sont fournies comme le 12.26.2016/26/12 ou le 2016/26/XNUMX (format britannique), pour se faire dire que la date n'est pas valide ou qu'il n'y a pas de mois XNUMX ? Cet article explore la fixation des dates avec VBA, en utilisant les fonctions TRIM, LEFT, RIGHT et MID.
L'article suppose que le lecteur a affiché le ruban Développeur et est familiarisé avec l'éditeur VBA. Si ce n'est pas le cas, veuillez Google "Excel Developer Tab" ou "Excel Code Window".
Le xlsm de cet exercice peut être téléchargé ici.
Ce n'est pas notre problème !
Le meilleur endroit pour résoudre un problème est à la source. Cependant, aucun degré de persuasion ne peut convaincre le service de la paie dans ce cas que le 12.26.1994 n'est pas une date valide (sauf si cela est configuré dans le panneau de configuration de l'ordinateur pour certains pays d'Europe de l'Est).
En fait, nous pouvons prouver qu'il n'est pas lisible par machine. Par exemple, ajouter 7 jours à une date :
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
alors que…
"=2017-01-01 + 7" = 2017/01/08.
Supposons qu'ils suggèrent que ce n'est pas leur problème.
Formats de date
La première chose que nous devons vérifier est si la date est au format américain ou international.
Notre exemple montre clairement que nous examinons l'utilisation aux États-Unis, c'est-à-dire MDY plutôt que le format international DMY.
Une fois que nous avons établi la source, nous devons modifier les formats de données afin qu'Excel puisse les comprendre, que ce soit à l'international ou aux États-Unis.
La meilleure façon de le faire est de changer la date en aaaammjj, un format qui ne nécessite aucune qualification.
Le processus
Nous parcourrons chaque ligne du document, en appelant une fonction pour "corriger" la date en fonction du pays source. Une fois la date corrigée, nous calculerons l'âge de l'employé.
Le code
Copiez le code suivant dans un nouveau 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
Ajoutez un bouton à votre formulaire et attribuez-le à Sub Main.
Avertissement
Avant d'ajouter trop de code complexe à votre module, sachez qu'Excel n'est pas toujours stable dans le développement d'applications majeures, et souvent incapable de récupérer lui-même le code endommagé. Le résultat pourrait être la corruption de votre seule copie puisque la corruption se produit dans le "Save".
Sauvegardez fréquemment et utilisez un outil pour réparer Corruption de fichier Excel.
Introduction de l'auteur:
Felix Hooker est un expert en récupération de données dans DataNumen, Inc., qui est le leader mondial des technologies de récupération de données, y compris réparation rar corruption de fichiers et produits logiciels de récupération sql. Pour plus d'informations, visitez www.datanumen.com