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 à les explorer toutes.
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. Parfois, lorsque les dates sont importées à partir d'une source externe, elles sont mises en forme sous forme de texte.
Il est également important de savoir qu'Excel applique des formats de date en fonction des paramètres de votre région système. Ainsi, lorsque vous importez des données dans Excel provenant d'un autre pays, Excel peut ne pas les reconnaître et les stocker sous forme d'entrées de texte.
Si vous rencontrez 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 formatées sous forme de texte, au lieu de dates réelles, il n'est pas possible de les utiliser dans des calculs. Vous devez donc les reconvertir en dates réelles.
Si vous voyez vos dates alignées à gauche, cela signifie qu'elles sont formatées en tant que texte car les textes sont alignés à gauche par défaut. Et le nombre et les dates sont toujours alignés à droite.
Il existe différentes manières de convertir du texte en date dans Excel :
- Option de vérification des erreurs
- Fonctionnalité Excel Text to Columns
- Rechercher et remplacer
- Coller l'outil spécial
- Formule et fonctions Excel
Convertir le texte en date à l'aide de l'option de vérification des erreurs
Excel dispose d'une fonction de vérification des erreurs intégrée qui détecte certaines erreurs évidentes dans vos données. S'il trouve une erreur, il vous montrera un petit triangle vert (un indicateur d'erreur) dans le coin supérieur gauche de la cellule, qui contient l'erreur. Si vous sélectionnez cette cellule, un panneau d'avertissement avec un point d'exclamation jaune apparaîtra. Lorsque vous déplacez votre curseur sur ce signe, Excel vous informera du problème possible avec cette cellule.
Par exemple, lorsque vous entrez l'année au format à deux chiffres dans votre date, Excel considère 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 dates formatées en texte en dates réelles. Excel vous montrera les options pour 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 règle générale, l'option Vérification des erreurs est activée dans Excel par défaut. Si la fonction de vérification des erreurs 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 en arrière-plan » dans la section Vérification des erreurs. Et cochez les « Cellules contenant des années représentées par 2 chiffres » dans la section Règles de vérification des erreurs.
Convertir du texte en date à l'aide de la fonctionnalité de texte en colonne d'Excel
Text to Column est une fonctionnalité intéressante d'Excel qui vous permet de diviser les données en plusieurs colonnes. C'est également un outil puissant pour convertir les 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
Disons que vos dates sont formatées dans des chaînes de texte comme ceci :
Vous pouvez utiliser l'assistant Text to Columns pour les reformater rapidement aux dates.
Tout d'abord, sélectionnez la plage d'entrées de texte que vous souhaitez convertir en dates. Ensuite, accédez à l'onglet « Données » dans le ruban et cliquez sur l'option « Texte dans les colonnes » dans le groupe Outils de données.
L'assistant Texte en colonnes apparaîtra. À l'étape 1 de l'assistant Texte en 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 des données de la colonne et choisissez votre format de date dans la liste déroulante à côté de « Dates », puis cliquez sur le bouton « Terminer ». Dans notre cas, nous convertissons les dates de texte représentées par « 01 02 1995 » (jour mois année), nous choisissons donc « JMA » dans la liste déroulante « Date : ».
Maintenant, Excel convertit vos dates de texte en dates réelles et les affiche alignées à droite dans les cellules.
Noter: Avant de commencer à utiliser la fonction Texte en colonne, assurez-vous que toutes vos chaînes de texte sont dans un format identique, sinon les textes ne seront pas convertis. Par exemple, si certaines de vos dates de texte sont formatées 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 il est montré dans l'image ci-dessous.
Conversion d'une chaîne de texte complexe en date
La fonction Texte en colonnes est pratique 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. Et combinez les parties fractionnées des dates en date entière à l'aide de la fonction DATE.
Par exemple, si vos dates sont affichées dans des chaînes de texte en plusieurs parties, comme ceci :
mercredi 01 février 2020
01 février 2020, 16h10
Vous pouvez utiliser l'assistant Texte en 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 que vous souhaitez convertir en dates. Cliquez sur le bouton « Texte dans les colonnes » dans l'onglet « Données ». À l'étape 1 de l'assistant Texte en 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 que contiennent 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 01 février 2015, 13h00". Nous devons 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 dans 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 la colonne (ignorer) ». Ensuite, cliquez sur « Terminer ».
Maintenant, les parties des dates (jours de la semaine, mois, année, heure) sont réparties dans les colonnes B, C, D, E, F et G.
Ensuite, combinez les parties de la date à l'aide d'une formule DATE pour obtenir la date entière.
La syntaxe de la fonction Excel DATE :
=DATE(année,mois,jour)Dans notre exemple, les parties mois, jour et année se trouvent respectivement dans les colonnes C, D et E.
La fonction DATE ne reconnaît que les chiffres 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 MOIS pour remplacer le nom du mois par le numéro du mois.
Pour convertir le nom d'un mois en numéro de mois, utilisez cette fonction MOIS dans la fonction DATE :
=MOIS(1&C1)La fonction MOIS ajoute 1 à la cellule C2 qui contient le nom du mois pour convertir le nom du mois en numéro de mois correspondant.
C'est la fonction DATE que nous devons utiliser pour combiner des parties de date de différentes colonnes :
=DATE(E1,MOIS(1&C1),D1)
Utilisez maintenant la poignée de remplissage dans le coin inférieur de la cellule de formule et appliquez la formule à la colonne.
Convertir du texte en date à l'aide de la méthode Rechercher et remplacer
Cette méthode utilise des délimiteurs pour modifier le format du texte en dates. Si le jour, le mois et l'année dans 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.
Pour résoudre ce problème, utilisez la fonction Rechercher et remplacer. En modifiant les délimiteurs de période non standard (.) avec des barres obliques (/) ou des tirets (-), Excel modifiera automatiquement les valeurs en 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 ». Sinon, appuyez 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 par ») Cliquez sur le bouton « Remplacer tout » pour remplacer les délimiteurs et cliquez sur « Fermer » pour fermer la fenêtre.
Désormais, Excel reconnaît que vos chaînes de texte sont désormais des dates et les formate automatiquement en tant que dates. Vos dates seront alignées comme indiqué ci-dessous.
Convertir du texte en date à l'aide de l'outil spécial Coller
Un autre moyen rapide et facile de convertir des chaînes de texte en dates consiste à ajouter 0 à la chaîne de texte à l'aide de l'option spéciale coller. L'ajout de zéro à la valeur convertit le texte en numéro de série de la date que vous pouvez formater en tant que date.
Tout d'abord, sélectionnez une cellule vide et copiez-la (sélectionnez-la et appuyez sur Ctrl + C copier).
Ensuite, sélectionnez les cellules contenant les dates de texte que vous souhaitez convertir, faites un clic droit et sélectionnez l'option "Collage spécial".
Dans la boîte de dialogue Collage spécial, sélectionnez "Tous" dans la section Coller, choisissez "Ajouter" dans la section Opération et 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 avec 1 ou diviser par 1 ou soustraire zéro).
Lorsque vous sélectionnez « Ajouter » dans Opération, cela ajoute « zéro » à toutes les dates de texte sélectionnées, car l'ajout d'un « 0 » ne modifie pas les valeurs, vous obtiendrez le numéro de série pour chaque date. Il ne vous reste plus qu'à modifier le format des cellules.
Sélectionnez les numéros de série et dans l'onglet « Accueil », cliquez sur la liste déroulante « Format du numéro » 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 en tant que dates et alignés à droite.
Convertir du texte en date à l'aide de formules
Il existe deux fonctions principalement utilisées pour convertir du texte en date : DATEVALUE et VALUE.
Utilisation de la fonction Excel DATEVALUE
La fonction Excel DATEVALUE est l'un des moyens les plus simples de convertir une date représentée sous forme de texte en numéro de série d'une date.
La syntaxe de la fonction DATEVALUE :
=VALEURDATE(texte_date)Argument: date_texte spécifie la chaîne de texte que vous souhaitez convertir ou faire référence à la cellule contenant les dates de texte.
La formule:
=VALEURDATE(A1)L'image suivante illustre comment la fonction DATEVALUE gère quelques formats de date différents qui sont stockés sous forme de texte.
Vous avez les numéros de série de date, vous devez maintenant appliquer le format 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 du nombre ».
Vous avez maintenant vos dates formatées dans la colonne C.
Même s'il n'y a pas de partie d'année dans votre date de texte (A8), DATEVALUE utilisera l'année en cours à partir de 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 modifier une valeur numérique à ce jour, pour cela vous aurez besoin de la fonction VALUE d'Excel.
Utilisation de la fonction Excel VALUE
La fonction Excel VALUE est capable de convertir n'importe quelle chaîne de texte qui ressemble à une date ou à un nombre en une valeur numérique, elle est donc très utile lorsqu'il s'agit de convertir n'importe quel nombre, pas seulement des dates.
La fonction VALEUR :
=VALEUR(texte)texte– la chaîne de texte que nous voulons convertir ou référence à la cellule contenant la chaîne de texte.
L'exemple de formule pour convertir la date du texte :
=VALEUR(A1)La formule VALUE ci-dessous peut changer toutes les chaînes de texte qui ressemblent à une date 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), elle renverra la #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 du numéro » dans l'onglet « Accueil ».
Voilà, ce sont les 5 manières différentes de convertir des dates formatées en texte en dates dans Excel.