Comment convertir du texte en valeurs de date dans Microsoft Excel
L'analyse des données d'entreprise nécessite souvent de travailler avec des valeurs de date dans Excel pour répondre à des questions telles que combien d'argent avons-nous gagné aujourd'hui ou comment cela se compare-t-il au même jour la semaine dernière ? Et cela peut être difficile quand Excel ne reconnaît pas les valeurs comme des dates.
Malheureusement, cela n'est pas inhabituel, en particulier lorsque plusieurs utilisateurs saisissent ces informations, les copient et les collent à partir d'autres systèmes et les importent à partir de bases de données.
Dans cet article, nous décrirons quatre scénarios différents et les solutions pour convertir le texte en valeurs de date.
Dates contenant un point/période
L'une des erreurs les plus courantes que font probablement les débutants lors de la saisie de dates dans Excel est de le faire avec le caractère point pour séparer le jour, le mois et l'année.
Excel ne le reconnaîtra pas comme une valeur de date 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 (/), Excel identifiera automatiquement les valeurs comme des dates.
Sélectionnez les colonnes sur lesquelles vous souhaitez effectuer la recherche et le remplacement.
Cliquez sur Accueil > Rechercher et sélectionner > Remplacer ou appuyez sur Ctrl+H.
Dans la fenêtre Rechercher et remplacer, tapez un point (.) dans le champ Rechercher quoi et une barre oblique (/) dans le champ Remplacer par. Ensuite, cliquez sur Remplacer tout.
Tous les points sont convertis en barres obliques et Excel reconnaît le nouveau format comme une date.
Si les données de votre feuille de calcul changent régulièrement et que vous souhaitez une solution automatisée pour ce scénario, vous pouvez utiliser la fonction SUBSTITUTE.
=VALUE(SUBSTITUTE(A2,".","/"))
La fonction SUBSTITUTE est une fonction de texte, elle ne peut donc pas la convertir en date par elle-même. La fonction VALUE convertira la valeur textuelle en une valeur numérique.
Les résultats sont montrés plus bas. La valeur doit être formatée comme une date.
Vous pouvez le faire en utilisant la liste Format de nombre dans l'onglet Accueil.
L'exemple ici d'un délimiteur point est typique. Mais vous pouvez utiliser la même technique pour remplacer ou substituer n'importe quel caractère de délimitation.
Conversion du format aaaammjj
Si vous recevez des dates dans le format indiqué ci-dessous, cela nécessitera une approche différente.
Ce format est assez standard dans la technologie car il supprime toute ambiguïté sur la façon dont les différents pays stockent leurs valeurs de date. Cependant, Excel ne le comprendra pas initialement.
Pour une solution manuelle rapide, vous pouvez utiliser Text to Columns.
Sélectionnez la plage de valeurs que vous devez convertir, puis cliquez sur Données > Texte en colonnes.
L'assistant Texte vers colonnes s'affiche. Cliquez sur Suivant sur les deux premières étapes afin que vous soyez à l'étape trois, comme indiqué dans l'image ci-dessous. Choisissez Date, puis sélectionnez le format de date utilisé dans les cellules de la liste. Dans cet exemple, nous avons affaire à un format YMD.
Si vous souhaitez une solution de formule, vous pouvez utiliser la fonction Date pour construire la date.
Cela serait utilisé avec les fonctions de texte Left, Mid et Right pour extraire les trois parties d'une date (jour, mois, année) du contenu de la cellule.
La formule ci-dessous montre cette formule en utilisant nos exemples de données.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
En utilisant l'une ou l'autre de ces techniques, vous pouvez convertir n'importe quelle valeur numérique à huit chiffres. Par exemple, vous pouvez recevoir la date au format jjmmaaaa ou mmjjaaaa.
Fonctions DATEVALUE et VALUE
Parfois, le problème n'est pas causé par un caractère délimiteur, mais par une structure de date maladroite simplement parce qu'il est stocké sous forme de texte.
Vous trouverez ci-dessous une liste de dates dans une variété de structures, mais elles sont toutes reconnaissables pour nous en tant que date. Malheureusement, ils ont été stockés sous forme de texte et doivent être convertis.
Pour ces scénarios, il est facile de convertir en utilisant une variété de techniques.
Pour cet article, je voulais mentionner deux fonctions pour gérer ces scénarios. Ce sont DATEVALUE et VALUE.
La fonction DATEVALUE convertira le texte en une valeur de date (probablement vu cela venir), tandis que la fonction VALUE convertira le texte en une valeur numérique générique. Les différences entre eux sont minimes.
Dans l'image ci-dessus, l'une des valeurs contient également des informations temporelles. Et ce sera une démonstration des différences mineures des fonctions.
La formule DATEVALUE ci-dessous convertirait chacun en une valeur de date.
=DATEVALUE(A2)
Remarquez comment l'heure a été supprimée du résultat de la ligne 4. Cette formule ne renvoie strictement que la valeur de date. Le résultat devra toujours être formaté en tant que date.
La formule suivante utilise la fonction VALEUR.
=VALUE(A2)
Cette formule produira les mêmes résultats sauf pour la ligne 4, où la valeur de temps est également conservée.
Les résultats peuvent ensuite être formatés en tant que date et heure, ou en tant que date pour masquer la valeur temporelle (mais pas la supprimer).