Tutoriel Excel : comment convertir un nombre ou un texte en date

Ce tutoriel explique comment utiliser les fonctions Excel pour convertir des nombres en date et du texte en date, mais aussi comment transformer des chaînes de texte en dates de manière non formulée. Vous apprendrez également à modifier rapidement un format de nombre en date.

Comme Excel n'est pas la seule application avec laquelle nous travaillons, nous nous retrouvons parfois avec des dates importées dans une feuille de calcul Excel à partir d'un fichier .csv ou d'une autre source externe. Lorsque cela se produit, il est probable que les dates seront exportées en tant qu'entrées de texte. Même si elles ressemblent à des dates, Excel ne les reconnaîtra pas comme telles.

Il existe de nombreuses façons de convertir du texte en date dans Excel et ce tutoriel vise à les couvrir toutes, afin que vous puissiez choisir une technique de conversion de texte en date la plus appropriée à votre format de données. Vous aurez ainsi le choix d’utiliser une méthode avec ou sans formule.

Table des matières

Comment convertir un nombre en date dans Excel ?

Méthode 1 : convertir le numéro de série en date avec la fonction de formatage des cellules

Méthode 2 : Convertir le numéro de série en date avec une formule

Convertir un nombre à 8 chiffres en une date reconnaissable par Excel

Comment convertir du texte en valeurs de date ?

Utiliser la fonction DATEVAL pour convertir un texte en date

Utilisation de la fonction CNUM

Opérations mathématiques pour convertir du texte en date

Comment convertir des chaînes de texte avec des délimiteurs personnalisés en dates ?

Assistant Convertir - moyen sans formule de convertir du texte en date

Comment convertir un nombre en date dans Excel ?

Étant donné que toutes les fonctions Excel qui modifient le texte en date renvoient un nombre en conséquence, examinons de plus près la conversion des nombres en dates en premier.

Méthode 1 : convertir le numéro de série en date avec la fonction de formatage des cellules

Comme vous le savez probablement, Excel stocke les dates et les heures sous forme de numéros de série et seule la mise en forme d'une cellule force l'affichage d'un nombre sous forme de date. Par exemple, le 1er janvier 1900 est stocké sous le numéro 1, le 2 janvier 1900 est enregistré sous la forme 2 et le 1er janvier 2015 est enregistré sous le numéro 42005.

Lors du calcul de dates dans Excel, le résultat renvoyé par différentes fonctions de date est souvent un numéro de série représentant une date. Pour convertir un numéro de série en date dans Excel, tout ce que vous avez à faire est de modifier la mise en forme des cellules.

Pour cela, sélectionnez une cellule ou une plage de cellules avec les nombres que vous souhaitez convertir en dates et appuyez sur Ctrl + 1 pour ouvrir la boîte de dialogue Format de cellule. Dans l'onglet Nombre, choisissez Date, sélectionnez le format de date souhaité sous Type et cliquez sur OK.

Méthode 2 : Convertir le numéro de série en date avec une formule

Vous pouvez également convertir le numéro de série en date avec une formule dans Excel. Pour ce faire, veuillez procéder comme suit :

1. Sélectionnez une cellule vide (ici la cellule B2) adjacente à la cellule du numéro de série que vous devez convertir en date. Entrez la formule=TEXTE(A2; "m/j/aaaa")dans la barre de formule et appuyez sur la touche Entrée.

2. Appliquez la formule aux autres cellules. Ensuite, vous pouvez voir tous les numéros de série sont convertis en dates correspondantes dans la colonne B2 :

Convertir un nombre à 8 chiffres en une date reconnaissable par Excel

Avez-vous déjà rencontré une situation où la date est entrée sous forme de nombre à 8 chiffres au lieu de la valeur de date correcte dans Excel ?

Vous ne pouvez pas effectuer de calculs / fonctions liés à la date appropriée avec le nombre à 8 chiffres dans Excel, car ce n'est pas la bonne façon d'entrer une date.

Ne pensez jamais que vous pouvez simplement changer le format de cellule en Date (comme JJ/MM/AAAA) et le faire fonctionner. Essayez de transformer la valeur 20140401 en format de date, vous obtiendrez ########## car la valeur est bien au-delà de la limite Excel pour la date.

Pour convertir un tel nombre, vous devrez utiliser la fonction DATE en combinaison avec les fonctions DROITE, GAUCHE et STXT. Malheureusement, il n'est pas possible de créer une formule universelle qui fonctionnera dans tous les scénarios car le numéro d'origine peut être entré dans une variété de formats différents.

Par exemple:

Lorsque vous recevez un fichier avec des dates écrites sous forme de nombre à 8 chiffres, vous devez d'abord reconnaître les formats de date du pays (YMD, DMY, MDY). Si vous avez de nombreux exemples, il est facile de le comprendre.

Si vous copiez et collez simplement le tableau, vous obtiendrez ce résultat.

Les cellules B4 et C3 n'ont pas de zéros en tête. C'est parce qu'Excel les supprime par défaut. Même si vous copiez des cellules sous forme de texte, les zéros seront supprimés. Vous pouvez formater les cellules sous forme de texte et ajouter des zéros à la main, mais il existe un meilleur moyen de le faire. Avant de copier les cellules, sélectionnez les cellules vides où les valeurs seront copiées, cliquez avec le bouton droit et choisissez Format de cellule.

Dans la fenêtre Catégorie, sélectionnez Texte.

Cliquez sur OK.

Copiez le tableau et collez-le dans la feuille de calcul Excel avec l'option de mise en forme de destination.

Après avoir collé ces valeurs, n'oubliez pas de convertir les autres au format général. Maintenant, les zéros de tête sont en place et nous pouvons créer nos formules. Nous allons convertir le texte en date en utilisant les fonctions DATE, STXT, DROITE et GAUCHE.

La fonction STXT

Dans cette formule, nous allons utiliser la fonction STXT pour séparer les valeurs en jours, mois et années :

Les fonctions GAUCHE, STXT et DROITE

Dans la deuxième méthode, nous utiliserons deux fonctions supplémentaires: GAUCHE et DROITE. Cette formule est plus lisible car ces deux fonctions prennent deux paramètres au lieu de trois.

Donc, ce que vous devez faire est d'extraire une année, un mois et une date du numéro d'origine et de les fournir comme arguments correspondants à la fonction Date.

Par exemple, voyons comment convertir le numéro 10032016 (stocké dans la cellule A1) en date du 3/10/2016 :

Extraire l'année. Ce sont les 4 derniers chiffres. Nous utilisons donc la fonction DROITE pour choisir les 4 derniers caractères: =DROITE(A1;4).

Extraire le mois. Il s'agit des 3e et 4e chiffres. Nous utilisons donc la fonction STXT pour les obtenir STXT(A1;3;2). Où 3 est le numéro de départ et 2 est le nombre de caractères à extraire.

Extraire la journée. Ce sont les 2 premiers chiffres. Nous avons donc la fonction GAUCHE pour renvoyer les 2 premiers caractères: GAUCHE(A2;2).

Enfin, intégrez les ingrédients ci-dessus dans la fonction Date, et vous obtenez une formule pour convertir un nombre en date dans Excel :

=DATE(DROITE(A1;4);STXT(A1;3;2);GAUCHE(A1;2))

Comment convertir du texte en valeurs de date ?

Vous pouvez avoir des données dans votre feuille de calcul Excel qui ressemblent à une date, mais qui ne sont pas représentées comme une valeur de date Excel. Par exemple, si vous entrez 01-24-18 dans une cellule, Excel n'aurait aucun moyen de savoir s'il s'agit du 24 janvier 2018 ou du code de votre serrure à combinaison. S’il s’agit d’une date, vous pouvez utiliser l’une des méthodes suivantes pour la convertir en une valeur de date Excel.

Utiliser la fonction DATEVAL pour convertir un texte en date

En pratique, tout format de date standard entré dans une cellule est reconnu par Excel comme une date et converti en conséquence. Cependant, il peut y avoir des cas, tels que lorsque des dates de texte sont importées à partir d'une source de données externe ou que des données sont copiées et collées dans Excel. Pour cela ,vous avez besoin de DATEVAL.

La fonction Excel DATEVAL convertit une représentation textuelle d'une date en un numéro de série de date Excel.

Notez qu'après avoir utilisé la fonction pour convertir une chaîne de texte en une date, vous devez vous assurer que la cellule contenant la fonction est formatée en tant que date.

Les cellules A1 à A3 de la feuille de calcul ci-dessous contiennent trois représentations textuelles différentes de la date du 1er janvier 2016.

La fonction Excel DATEVAL est utilisée dans la colonne B de la feuille de calcul, pour convertir les valeurs de texte dans la colonne A en numéros de série de date.

La colonne B est formatée avec le type de format «Général». Par conséquent, le numéro de série de la date 42370 est affiché sous forme d'entier.

Si vous souhaitez afficher le contenu de la colonne B sous forme de dates, vous devez modifier la mise en forme de ces cellules pour avoir un format de date.

La façon la plus simple de procéder consiste à sélectionner la ou les cellules à formater, puis à sélectionner l'option de formatage des cellules Date dans le menu déroulant du groupe Nombre de l'onglet Accueil (voir ci-dessous):

La feuille de calcul résultante, avec les cellules de la colonne B au format dates, est illustrée ci-dessous :

Utilisation de la fonction CNUM

Lorsque votre cellule contient une date mais qu'Excel pense qu'il s'agit d'une chaîne de texte, vous pouvez (dans la plupart des cas) utiliser la fonction CNUM pour la convertir. Cela prend la valeur du texte et la convertit en un horodatage Unix à cinq chiffres, qui peut ensuite être converti en une valeur de date typique que vous reconnaîtrez visuellement.

Vous pouvez utiliser CNUM pour convertir n'importe quelle chaîne de texte en nombre. Excel, dans la plupart des cas, est capable de reconnaître une valeur de date probable et, en utilisant CNUM, il peut la convertir en valeur de date à la place.

Pour convertir une chaîne de texte en valeur de date, tapez =CNUM(A2), où la cellule A2 contient la chaîne de texte que vous convertissez. Dans l'exemple ci-dessous, nous utilisons le format de date jour-mois-année, mais cela fonctionnera également pour d'autres formats de date.

Cela ne fonctionne que pour certains types de valeurs de date probables. Comme vous pouvez le voir sur l'image ci-dessus, il ne prend pas en charge les dates qui utilisent des décimales (par exemple, 12.05.1990). Il renverra une erreur #VALUE pour les cellules qu'il ne peut pas convertir.

Une fois que vous avez un horodatage Unix converti, vous pouvez le modifier en utilisant le formatage des cellules Excel en sélectionnant les cellules de date et en sélectionnant l'option Date dans le menu déroulant Nombre de l'onglet Accueil.

Opérations mathématiques pour convertir du texte en date

Outre l'utilisation de fonctions Excel spécifiques telles que CNUM et DATEVAL, vous pouvez effectuer une opération mathématique simple pour forcer Excel à effectuer une conversion de texte en date pour vous. La condition requise est qu'une opération ne modifie pas la valeur de la date (numéro de série). Cela semble un peu flou ? Les exemples suivants vous faciliteront la tâche !

En supposant que la date de votre texte se trouve dans la cellule A1, vous pouvez utiliser l'une des formules suivantes, puis appliquer le format de date à la cellule :

• ·Addition: = A1 + 0
• ·Multiplication: = A1 * 1
• ·Division: = A1 / 1
• ·Double négation: = - A1

Comme vous pouvez le voir dans la capture d'écran ci-dessus, les opérations mathématiques peuvent convertir les dates (lignes 2 et 3), les heures (ligne 4) ainsi que les nombres au format texte (ligne 5).

Comment convertir des chaînes de texte avec des délimiteurs personnalisés en dates ?

L'une des erreurs les plus courantes que commettent les débutants lors de la saisie de dates dans Excel consiste à le faire avec le caractère de point final pour séparer le jour, le mois et l'année.

Excel ne reconnaîtra pas cela comme une valeur de date et ira de l'avant et le stockera sous forme de texte. Cependant, vous pouvez résoudre ce problème avec l'outil Rechercher et remplacer. En remplaçant les points par des barres obliques (/).

1-      Sélectionnez les colonnes sur lesquelles vous souhaitez effectuer la recherche et le remplacement.

2-      Cliquez sur Accueil > Rechercher et sélectionner > Remplacer ou appuyez sur Ctrl + H.

3-Dans la fenêtre Rechercher et remplacer, saisissez un point (.) Dans le champ « Rechercher » et une barre oblique (/) dans le champ « Remplacer par ». Cliquez ensuite sur « Remplacer tout ».

Tous les points sont convertis en barres obliques et Excel reconnaît le nouveau format comme une date.

Si vos données des feuilles de calcul changent régulièrement et que vous souhaitez une solution automatisée pour ce scénario, vous pouvez utiliser la fonction SUBSTITUE.

=CNUM(SUBSTITUE(A2;".";"/"))

La fonction SUBSTITUE est une fonction texte. Elle ne peut donc pas convertir seule le texte en une date. La fonction CNUM convertira la valeur du texte en une valeur numérique. La valeur doit être formatée en date.

Vous pouvez le faire en utilisant la liste « Format des nombres » dans l'onglet « Accueil ».

L'exemple -ici- d'un délimiteur (.) est typique. Mais vous pouvez utiliser la même technique pour remplacer tout caractère de délimitation.

Assistant Convertir - moyen sans formule de convertir du texte en date

Si vous n'êtes pas un utilisateur de formule, une fonctionnalité Excel de longue date appelée Convertir sera utile. Elle peut gérer des dates de texte simples ainsi que des chaînes de texte en plusieurs parties.

Si les chaînes de texte que vous souhaitez convertir en dates se présentent comme suit:

1.1.2020

1.2020

01 01 2020

2020/1/1

Vous n'avez pas vraiment besoin de formules, ni d'exporter ou d'importer quoi que ce soit. Il suffit de suivre ces étapes rapides.

Dans cet exemple, nous allons convertir des chaînes de texte comme « 01 01 2020 » (le jour, le mois et l'année sont séparés par des espaces) en dates.

1-      Dans votre feuille de calcul Excel, sélectionnez une colonne d'entrée de texte que vous souhaitez convertir en dates.

2-      Basculez vers l'onglet « Données », groupe « Outils de données », puis cliquez sur  « Convertir ».

3-      À l'étape 1 de l'Assistant Conversion, sélectionnez Délimité et cliquez sur Suivant.

4-      À l'étape 2 de l'Assistant, décochez toutes les cases du délimiteur et cliquez sur Suivant.

5-      À l'étape finale, sélectionnez Date sous Format de données de colonne, choisissez le format correspondant à vos dates, puis cliquez sur Terminer. Dans cet exemple, nous convertissons les dates de texte au format « 01 02 2020 » (jour mois année), nous sélectionnons donc JMA dans la liste déroulante.

Maintenant, Excel reconnaît vos chaînes de texte comme des dates, les convertit automatiquement à votre format de date par défaut et les affiche alignées à droite dans les cellules. Vous pouvez modifier le format de date de la manière habituelle via la boîte de dialogue Format de cellule.

Télécharger le fichier Excel de ce tutoriel