Formule générique
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Sommaire
Pour convertir du texte dans un format de date non reconnu en une date Excel appropriée, vous pouvez analyser le texte et assembler une date appropriée avec une formule basée sur plusieurs fonctions: DATE, GAUCHE, MOYEN et DROITE. Dans l'exemple illustré, la formule en C6 est:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Cette formule extrait les valeurs de l'année, du mois et du jour séparément et utilise la fonction DATE pour les assembler dans la date du 24 octobre 2000.
Contexte
Lorsque vous travaillez avec des données d'un autre système, vous pouvez vous retrouver dans une situation où les dates ne sont pas correctement reconnues par Excel, qui les traite plutôt comme du texte. Par exemple, vous pourriez avoir des valeurs de texte comme ceci:
| Texte | Date représentée |
| 20001024 | 24 octobre 2000 |
| 20050701 | 1 juillet 20115 |
| 19980424 | 24 avril 1998 |
| 28.02.2014 | 28 février 2014 |
Lorsqu'Excel a évalué une valeur de date sous forme de texte, une option consiste à utiliser une formule pour analyser le texte en ses composants (année, mois, jour) et à les utiliser pour créer une date avec la fonction DATE. Comme indiqué ci-dessus, je vous recommande d'essayer d'abord les solutions ci-dessous (en ajoutant zéro et en utilisant du texte dans les colonnes) avant d'utiliser une formule. Les deux solutions de contournement sont plus rapides et nécessitent moins d'efforts.
Explication
La fonction DATE crée une date valide à l'aide de trois arguments: année, mois et jour:
=DATE(year,month,day)
Dans la cellule C6, nous utilisons les fonctions LEFT, MID et RIGHT pour extraire chacun de ces composants d'une chaîne de texte et introduire les résultats dans la fonction DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
La fonction GAUCHE extrait les 4 caractères les plus à gauche pour l'année, la fonction MID extrait les caractères des positions 5 à 6 pour le mois et la fonction DROITE extrait les 2 caractères les plus à droite comme jour. Chaque résultat est renvoyé directement à la fonction DATE. Le résultat final est une date Excel appropriée qui peut être formatée comme vous le souhaitez.
Cette approche peut être personnalisée selon les besoins. Par exemple, le format de date non reconnu à la ligne 8 est jj.mm.aaaa et la formule en C8 est:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Texte long
Parfois, vous pouvez avoir des dates sous une forme plus longue comme "11 avril 2020 08:43:13" qu'Excel ne reconnaît pas correctement. Dans ce cas, vous pourrez peut-être ajuster la chaîne de manière à permettre à Excel de reconnaître correctement la date avec la fonction SUBSTITUTE. La formule ci-dessous remplace la deuxième instance d'un espace ("") par une virgule et un espace (","):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Une fois que nous ajoutons la virgule après le nom du mois, Excel comprendra la date, mais il a encore besoin d'un petit «coup de pied». C'est pourquoi nous ajoutons zéro à la fin. L'opération mathématique oblige Excel à essayer de convertir la chaîne en un nombre. En cas de succès, cela entraînera une date Excel valide. Notez que vous devrez peut-être appliquer le formatage du numéro de date pour afficher correctement la date.
Sans formules
Avant d'utiliser une formule pour analyser et construire manuellement une date à partir de texte, essayez l'un des correctifs ci-dessous. La première option utilise une opération mathématique pour "pousser" Excel un peu et le forcer à essayer d'évaluer le texte comme un nombre. Comme les dates Excel sont en fait des nombres, cela peut souvent faire l'affaire. Vous devrez peut-être appliquer un format de date si les opérations réussissent.
Ajouter zéro pour fixer les dates
Parfois, vous rencontrerez des dates dans un format texte qu'Excel devrait reconnaître. Dans ce cas, vous pourrez peut-être forcer Excel à convertir les valeurs de texte en dates en ajoutant zéro à la valeur. Lorsque vous ajoutez zéro, Excel essaiera de forcer les valeurs de texte en nombres. Étant donné que les dates ne sont que des nombres, cette astuce est un excellent moyen de convertir des dates au format texte qu'Excel devrait vraiment comprendre.
Pour convertir les dates en place en ajoutant zéro, essayez Collage spécial:
- Entrez zéro (0) dans une cellule inutilisée et copiez dans le presse-papiers
- Sélectionnez les dates problématiques
- Collage spécial> Valeurs> Ajouter
- Appliquer un format de date (si nécessaire)
Vous pouvez également ajouter zéro dans une formule comme celle-ci:
=A1+0
où A1 contient une date non reconnue.
Texte dans les colonnes pour fixer les dates
Un autre moyen pour qu'Excel reconnaisse les dates consiste à utiliser la fonctionnalité Texte dans les colonnes:
Sélectionnez la colonne de dates, puis essayez Données> Texte dans les colonnes> Fixe> Terminer
Si Excel reconnaît les dates, il les corrigera toutes en une seule étape.
