Il existe de nombreuses façons de convertir des dates formatées sous forme de texte en dates réelles dans Excel et ce didacticiel vise à toutes les explorer.
Lorsque vous importez des données dans Excel, elles peuvent se présenter sous de nombreuses formes différentes qu’Excel ne reconnaît pas. Lorsque cela se produit, il y a de fortes chances que les dates soient formatées sous forme de texte. Même s’ils ressemblent à des dates, mais ne se comportent pas comme des dates.
Il est également important de savoir qu’Excel applique les formats de date en fonction des paramètres de la région de votre système. Ainsi, lorsque vous importez dans Excel des données provenant d’un pays différent, Excel peut ne pas les reconnaître et les stocker sous forme d’entrées de texte.
Si vous êtes confronté à ce problème, il existe de nombreuses façons de convertir du texte en date dans Excel et nous les couvrirons toutes dans ce didacticiel.
Méthodes de conversion de texte en date
Si votre feuille de calcul contient des dates au format texte, plutôt que des dates réelles, vous ne pouvez pas les utiliser à des fins de calcul. Nous devons donc les reconvertir en dates.
Vous pouvez souvent voir si une date est mal formatée en tant que texte car elle est alignée à gauche par défaut. Et le nombre et les dates sont alignés à droite par défaut.
Il existe quelques méthodes différentes que vous pouvez utiliser pour convertir du texte en date dans Excel:
- Option de vérification des erreurs
- Fonction de texte Excel vers les colonnes
- Rechercher et remplacer
- Outil spécial Collage
- Formule Excel et fonctions
Convertir le texte en date à l’aide de l’option de vérification des erreurs
Excel dispose d’une fonction intégrée de vérification des erreurs qui détecte des erreurs évidentes dans vos données. Lorsque cela se produit, vous verrez un petit triangle vert (un indicateur d’erreur) dans le coin supérieur gauche de la cellule et lorsque vous sélectionnez la cellule, un signe d’avertissement avec un point d’exclamation jaune apparaît. Survolez ce signe et Excel vous informera du problème potentiel.
Par exemple, lorsque vous entrez l’année au format à deux chiffres dans votre date, Excel suppose cette date sous forme de texte et la stocke sous forme de texte. Et si vous sélectionnez cette cellule, un point d’exclamation apparaîtra avec un avertissement:”Cette cellule contient une chaîne de date représentée avec seulement 2 chiffres de l’année”.
Si vos cellules affichent cet indicateur d’erreur, cliquez sur le point d’exclamation, et il affichera quelques options pour convertir les représentations textuelles des dates en dates réelles. Excel vous demandera si vous souhaitez le convertir en 19XX ou 20XX (19XX pour 1915, 20XX pour 2015). Sélectionnez l’option appropriée.
Ensuite, le texte sera converti au format de date approprié.
Comment activer l’option de vérification des erreurs dans Excel
En général, l’option Vérification des erreurs est activée par défaut dans Excel. Si la méthode ci-dessus ne fonctionne pas pour vous, vous devez activer la vérification des erreurs dans Excel.
Pour ce faire, cliquez sur l’onglet”Fichier””et sélectionnez”Options”dans le panneau de gauche.
Dans la fenêtre Options Excel, cliquez sur”Formules”dans le panneau de gauche et dans le panneau de droite, activez”Activer la vérification des erreurs d’arrière-plan”dans la section Vérification des erreurs. Et cochez”Cellules contenant des années représentées par 2 chiffres”dans la section Règles de vérification des erreurs.
Conversion de texte en date à l’aide de la fonction Texte Excel en colonne
Le texte en colonne est une fonctionnalité intéressante d’Excel qui vous permet de diviser les données en plusieurs colonnes, et c’est également un outil puissant pour convertir des valeurs de texte en valeurs de date. Cette méthode reconnaît plusieurs formats de données différents et les convertit au format de date approprié.
Conversion de chaînes de texte simples en dates
Supposons que vos dates soient formatées dans des chaînes de texte comme celle-ci:
Vous pouvez utiliser l’assistant Text to Columns pour les reformater rapidement tous en fonction des dates.
Tout d’abord, sélectionnez la colonne d’entrées de texte que vous souhaitez convertir en dates. Passez à l’onglet”Données”dans le ruban et cliquez sur l’option”Texte en colonnes”dans le groupe Outils de données.
L’assistant Texte en colonnes apparaît. À l’étape 1 de l’assistant Texte vers colonne, choisissez l’option”Délimité”sous Type de données d’origine et cliquez sur”Suivant”.
À l’étape 2, décochez toutes les cases”Délimiteurs”et cliquez sur Suivant.
Dans la dernière étape de l’assistant, sélectionnez”Date”sous Format de données de la colonne et choisissez votre format de date dans la liste déroulante”Dates”, puis cliquez sur le bouton”Terminer”. Dans notre cas, nous convertissons les dates de texte au format «01 02 1995» (jour mois année), nous sélectionnons donc «DMY» dans la liste déroulante.
Maintenant, Excel convertit vos chaînes de texte en dates et affiche l’alignement à droite dans les cellules.
Remarque: Lorsque vous utilisez la méthode Texte vers colonne, toutes vos chaînes de texte doivent être formatées de la même manière. Par exemple, si certaines de vos dates de texte sont au format mois/jour/année (MDY) tandis que d’autres sont au format jour/mois/année (DMY), et lorsque vous choisissez «DMY» à l’étape 3, vous obtiendrez des résultats incorrects. Comme le montre l’image ci-dessous.
Conversion d’une chaîne de texte complexe en date
Les fonctionnalités de texte en colonnes sont utiles lorsque vous souhaitez convertir des chaînes de texte complexes en dates. Il vous permet d’utiliser les délimiteurs pour identifier où vos données doivent être divisées et affichées sur 2 colonnes ou plus. Pour cela, vous devrez utiliser à la fois l’assistant de texte en colonnes et la fonction Excel DATE.
Par exemple, si vos dates sont affichées dans des chaînes de texte en plusieurs parties, comme ceci:
Mercredi 1 février 2020
01 février 2020, 16 h 10
Vous pouvez utiliser l’assistant Texte vers colonne pour séparer les informations de jour, de date et d’heure délimitées par une virgule et les afficher sur plusieurs colonnes.
Tout d’abord, sélectionnez toutes les chaînes de texte à convertir en dates. Cliquez sur le bouton”Texte en colonnes”dans l’onglet”Données”. À l’étape 1 de l’assistant Texte vers colonne, choisissez l’option”Délimité”sous Type de données d’origine et cliquez sur”Suivant”.
À l’étape 2 de l’assistant, sélectionnez les délimiteurs contenus dans vos chaînes de texte et cliquez sur «Suivant». Notre exemple de chaînes de texte séparées par une virgule et un espace-«Lundi 1er février 2015, 13h00». Nous devrions choisir «Virgule» et «espaces» comme délimiteurs pour diviser les chaînes de texte en plusieurs colonnes.
Dans la dernière étape, sélectionnez le format”Général”pour toutes les colonnes de la section Aperçu des données. Spécifiez où les colonnes doivent être insérées dans le champ”Destination”, si vous ne le faites pas, cela écrasera les données d’origine. Si vous souhaitez ignorer une partie des données d’origine, cliquez dessus dans la section Aperçu des données et sélectionnez l’option «Ne pas importer de colonne (ignorer)». Cliquez ensuite sur”Terminer”.
L’image suivante montre le résultat, avec les données d’origine dans la colonne A et les données fractionnées dans les colonnes B, C, D, E, F et G.
Enfin, vous devez combiner les parties de date ensemble en utilisant une formule DATE.
La syntaxe de la fonction Excel DATE est explicite:
=DATE (année, mois, jour)
La fonction DATE est explicite. Dans notre cas, l’année est dans la colonne E, le jour dans la colonne D et le mois dans la colonne C.
La fonction DATE ne reconnaît que les nombres et non le texte. Étant donné que nos valeurs de mois dans la colonne C sont toutes des chaînes de texte, nous devons les convertir en nombres. Pour ce faire, vous devez utiliser la fonction Excel MOIS pour changer le nom d’un mois en numéro du mois.
Pour remplacer le nom d’un mois par le nombre d’un mois, utilisez cette fonction MONTH dans la fonction DATE:
=MONTH (1 & C1)
La fonction ci-dessus concatène 1 avec la cellule C2 qui contient le nom du mois afin que la fonction MONTH puisse le convertir en numéro de mois correspondant.
Voici la fonction DATE que nous devons utiliser pour combiner des parties de date de différentes colonnes:
=DATE (E1, MOIS (1 & C1), D1)
Maintenant, utilisez la poignée de recopie dans le coin inférieur de la cellule de formule et appliquez la formule à la colonne.
Convertir le texte en date à l’aide de la méthode Rechercher et remplacer
Cette méthode utilise des délimiteurs pour changer le format du texte en dates. Si le jour, le mois et l’année de vos dates sont séparés par un délimiteur autre qu’un tiret (-) ou une barre oblique (/), Excel ne les reconnaîtra pas comme des dates et les stockera sous forme de texte.
Cependant, vous pouvez résoudre ce problème avec l’outil Rechercher et remplacer. En remplaçant les délimiteurs non standard par des barres obliques (/) ou des tirets (-), Excel identifiera automatiquement les valeurs comme des dates.
Tout d’abord, sélectionnez toutes les dates de texte que vous souhaitez convertir en dates. Dans l’onglet”Accueil”, cliquez sur le bouton”Rechercher et sélectionner”dans le coin le plus à droite du ruban et sélectionnez”Remplacer”. Vous pouvez également appuyer sur Ctrl + H pour ouvrir la boîte de dialogue Rechercher et remplacer.
Dans la boîte de dialogue Rechercher et remplacer, saisissez le délimiteur que contient votre texte (dans notre cas, un point (.) dans le champ «Rechercher» et une barre oblique (/) ou un tiret (-) dans le champ «Remplacer avec le champ”. Cliquez sur le bouton”Tout remplacer”pour remplacer les délimiteurs et cliquez sur”Fermer”pour fermer la fenêtre.
À présent, Excel devrait détecter que votre texte est une date et le formater automatiquement en tant que date. Vos dates seront alignées à droite, comme indiqué ci-dessous.
Convertir le texte en date à l’aide de l’outil spécial Coller
Une autre façon de convertir du texte en dates consiste à ajouter zéro à la méthode spéciale de collage de texte. L’ajout de zéro à la valeur convertit le texte en numéro de série de la date que vous pouvez mettre en forme en tant que date.
Tout d’abord, sélectionnez une cellule vide et copiez-la (sélectionnez-la et appuyez sur Ctrl + C pour copier).
Ensuite, sélectionnez les cellules contenant les dates de texte que vous souhaitez convertir, cliquez avec le bouton droit de la souris et sélectionnez l’option”Collage spécial”.
Dans la boîte de dialogue Collage spécial, sélectionnez”Tout”dans la section Coller, choisissez”Ajouter”dans la section Opération, puis cliquez sur”OK”.
Vous pouvez également effectuer d’autres opérations arithmétiques soustraire/multiplier/diviser la valeur dans la cellule de destination avec la valeur collée (comme multiplier les cellules par 1 ou diviser par 1 ou soustraire zéro).
Lorsque vous sélectionnez”Ajouter”dans l’opération, Excel ajoute zéro à toutes les valeurs de cellule, puisque l’ajout d’un zéro ne change pas la valeur, vous obtenez le numéro de série de la date. Il ne vous reste plus qu’à changer le format des cellules.
Sélectionnez les numéros de série et dans l’onglet”Accueil”, cliquez sur la liste déroulante”Format des nombres”dans le groupe Numéro. Sélectionnez l’option”Date courte”dans la liste déroulante.
Comme vous pouvez le voir maintenant, les nombres sont formatés sous forme de dates et alignés à droite.
Conversion de texte en date à l’aide de formules
Deux fonctions sont principalement utilisées pour convertir du texte en date: DATEVALUE et VALUE.
Utilisation de la fonction Excel DATEVALUE
La fonction DATEVALUE est une méthode de base utilisée pour convertir une date au format texte en un numéro de série de date Excel.
La syntaxe de DATEVALUE d’Excel:
=DATEVALUE (date_text)
date_text est l’endroit où vous utilisez la valeur de texte que vous souhaitez cacher ou référence à la cellule avec une date stockée sous forme de chaîne de texte.
La formule:
=DATEVALUE (A1)
L’image suivante illustre comment la fonction DATEVALUE gère différents formats de date qui sont stockés sous forme de texte.
Vous avez les numéros de série de date, vous devez maintenant appliquer le format de date à ces numéros. Pour ce faire, sélectionnez les cellules avec des numéros de série, puis accédez à l’onglet”Accueil”et choisissez”Date courte”dans la liste déroulante”Format des nombres”.
Vous avez maintenant vos dates formatées dans la colonne C.
Si vous omettez l’année dans la date de votre texte (A8), DATEVALUE choisira l’année en cours sur l’horloge de votre ordinateur.
La fonction DATEVALUE ne convertira que les valeurs de texte qui ressemblent à une date. Il ne peut pas convertir un texte qui ressemble à un nombre à ce jour, ni une valeur numérique à ce jour, pour cela, vous devrez utiliser la fonction Excel VALUE.
Utilisation de la fonction Excel VALUE
La fonction Excel VALUE peut convertir n’importe quelle chaîne de texte qui ressemble à une date ou à un nombre en nombre, elle est donc utile pour corriger n’importe quel nombre, pas seulement des dates.
La syntaxe de la fonction VALUE:
=VALUE (texte)
Où le texte est la chaîne de texte que nous voulons convertir ou référence à la cellule contenant la chaîne de texte de date.
Un exemple de formule:
=VALEUR (A1)
La fonction VALUE peut transformer toute chaîne de texte qui ressemble à une date ou à un nombre en un nombre comme indiqué ci-dessous.
Cependant, la fonction VALUE ne prend pas en charge tous les types de valeurs de date. Par exemple, si les dates utilisent des décimales (A11), il renverra le #VALUE! Erreur.
Une fois que vous avez le numéro de série de votre date, vous devrez formater la cellule avec le numéro de série de la date pour qu’elle ressemble à une date comme nous l’avons fait pour la fonction DATEVALUE. Pour ce faire, sélectionnez les numéros de série et sélectionnez l’option”Date”dans le menu déroulant”Format des nombres”de l’onglet”Accueil”.
Vous avez donc 5 façons différentes de convertir des dates au format texte dans Excel.
