Excel stocke les valeurs de date et d'heure sous forme de numéros de série dans le back-end.
Cela signifie que même si vous pouvez voir une date telle que "10 janvier 2021-2022" ou "01/10/2020" dans une cellule, dans le back-end, Excel la stocke sous forme de nombre. Ceci est utile car cela permet également aux utilisateurs d'ajouter/soustraire facilement la date et l'heure dans Excel.
Dans Microsoft Excel pour Windows, « 01 Jan 1900 » est stocké comme 1, « 02 Jan 1900 » est stocké comme 2, et ainsi de suite.
Ci-dessous, les deux colonnes ont les mêmes numéros, mais la colonne A affiche des chiffres et la colonne B affiche la date représentée par ce nombre.
C'est aussi la raison pour laquelle vous pouvez parfois vous attendre à une date dans une cellule mais finir par voir un nombre. Je vois cela se produire tout le temps lorsque je télécharge des données à partir de bases de données ou d'outils Web.
Cela se produit lorsque le format de cellule est défini pour afficher un nombre sous forme de nombre au lieu d'une date.
Alors comment pouvons-nous convertir ces numéros de série en dates?
C'est de cela qu'il s'agit dans ce tutoriel.
Dans ce tutoriel, je vous montrerais deux façons très simples de convertir des numéros de série en dates dans Excel
Remarque : Excel pour Windows utilise le système de date de 1900 (ce qui signifie que 1 représenterait le 1er janvier 1900, tandis qu'Excel pour Mac utilise le système de date de 1904 (quels hommes que 1 représenterait le 1er janvier 1904 dans Excel sous Mac)Alors, commençons!
Convertir des numéros de série en dates à l'aide du formatage des nombres
Le moyen le plus simple de convertir un numéro de série de date en date consiste à modifier la mise en forme des cellules contenant ces numéros.
Vous pouvez trouver certains des formats de date couramment utilisés dans l'onglet Accueil du ruban
Laissez-moi vous montrer comment faire cela.
Utilisation des options de format de date intégrées dans le ruban
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez convertir tous ces nombres de la colonne A en la date correspondante qu'il représente.
Vous trouverez ci-dessous les étapes à suivre :
- Sélectionnez les cellules qui ont le numéro que vous souhaitez convertir en date
- Cliquez sur l'onglet "Accueil"
- Dans le groupe "Numéro", cliquez sur la liste déroulante Formatage des nombres
- Dans la liste déroulante, sélectionnez l'option "Date longue" ou Date courte" (en fonction du format dans lequel vous souhaitez que ces chiffres soient)
C'est ça!
Les étapes ci-dessus convertiraient les nombres dans le format de date sélectionné.
Les étapes ci-dessus n'ont pas modifié la valeur dans la cellule, mais uniquement la façon dont elle est affichée.
Notez qu'Excel récupère le formatage de la date courte en fonction du paramètre régional de votre système. Par exemple, si vous êtes aux États-Unis, le format de date serait MM/JJ/AAAA, et si vous êtes au Royaume-Uni, le format de date serait JJ/MM/AAAA.
Bien qu'il s'agisse d'une méthode rapide pour convertir les numéros de série en dates, elle présente quelques limitations :
- Il n'y a que deux formats de date - la date courte et la date longue (et cela aussi dans un format spécifique). Donc, si vous souhaitez obtenir uniquement le mois et l'année et non le jour, vous ne pourrez pas le faire en utilisant ces options.
- Vous ne pouvez pas afficher la date ainsi que l'heure à l'aide des options de formatage dans la liste déroulante. Vous pouvez choisir d'afficher la date ou l'heure mais pas les deux.
Ainsi, si vous avez besoin de plus de flexibilité dans la manière dont vous souhaitez afficher les dates dans Excel, vous devez utiliser l'option Formatage des nombres personnalisé (présentée ensuite dans ce didacticiel).
Parfois, lorsque vous convertissez un nombre en date dans Excel, au lieu de la date, vous pouvez voir des signes dièse (quelque chose comme #####). cela se produit lorsque la largeur de la colonne n'est pas suffisante pour contenir toute la date. Dans ce cas, augmentez simplement la largeur de la colonne.Boîte de dialogue Création d'un format de date personnalisé à l'aide de la mise en forme des nombres
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez convertir les numéros de série de la colonne A en dates au format suivant - 01.01.2020
Notez que cette option n'est pas disponible par défaut dans la méthode du ruban que nous avons abordée précédemment.
Vous trouverez ci-dessous les étapes à suivre :
- Sélectionnez les cellules qui ont le numéro que vous souhaitez convertir en date
- Cliquez sur l'onglet "Accueil"
- Dans le groupe Chiffres, cliquez sur l'icône du lanceur de boîte de dialogue (c'est une petite flèche inclinée en bas à droite du groupe)
- Dans la boîte de dialogue "Formater les cellules" qui s'ouvre, assurez-vous que l'onglet "Nombre" est sélectionné
- Dans les options « Catégorie » sur la gauche, sélectionnez « Date »
- Sélectionnez le formatage souhaité dans la zone « Type ».
- Cliquez sur OK
Les étapes ci-dessus convertiraient les nombres dans le format de date sélectionné.
Comme vous pouvez le voir, il y a plus d'options de formatage de date dans ce cas (par rapport à la date courte et longue que nous avons avec le ruban).
Et si vous ne trouvez pas le format que vous recherchez, vous pouvez également créer votre propre format de date.
Par exemple, disons que je souhaite afficher la date au format suivant - 01/01/2020 (ce qui n'est pas déjà une option dans la boîte de dialogue de formatage des cellules).
Voici comment je peux faire ceci :
- Sélectionnez les cellules contenant les nombres que vous souhaitez convertir en date
- Cliquez sur l'onglet Accueil
- Dans le groupe Chiffres, cliquez sur l'icône du lanceur de boîte de dialogue (c'est une petite flèche d'inclinaison en bas à droite du groupe)
- Dans la boîte de dialogue "Formater les cellules" qui s'ouvre, assurez-vous que l'onglet "Nombre" est sélectionné
- Dans les options « Catégorie » sur la gauche, sélectionnez « Personnalisé »
- Dans le champ de droite, entrez
mm/jj/aaaa
- Cliquez sur OK
Les étapes ci-dessus modifieraient tous les nombres dans le format de nombre spécifié.
Avec le format personnalisé, vous obtenez un contrôle total et il vous permet d'afficher la date dans le format de votre choix. Vous pouvez également créer un format où il affiche la date ainsi que l'heure dans la même cellule.
Par exemple, dans le même exemple, si vous souhaitez afficher la date ainsi que l'heure, vous pouvez utiliser le format ci-dessous :
mm/jj/aaaa hh:mm AM/PM
Vous trouverez ci-dessous le tableau qui indique les codes de format de date que vous pouvez utiliser :
| Code de formatage de date | Comment il formate la date |
| m | Affiche le mois sous forme de nombre de 1 à 12 |
| mm | Afficher les mois sous forme de nombres à deux chiffres de 01-12 |
| mmm | Affiche le mois en trois lettres comme en janvier-décembre |
| mmmm | Affiche le nom complet du mois en janvier-décembre |
| mmmmm | Affiche le premier alphabet du nom du mois comme dans J-D |
| ré | Affiche les jours comme 1-31 |
| jj | Affiche les jours comme 01-31 |
| jjj | Affiche les jours sous la forme dim-sam |
| jjjj | Affiche les jours comme dimanche-samedi |
| aa | Affiche les années comme 00-99 |
| aaaa | Affiche les années comme 1900-9999 |
Convertir des numéros de série en dates à l'aide de la formule TEXTE
Les méthodes décrites ci-dessus fonctionnent en modifiant la mise en forme des cellules contenant les nombres.
Mais dans certains cas, vous ne pourrez peut-être pas utiliser ces méthodes.
Par exemple, ci-dessous, j'ai un ensemble de données où les nombres ont une apostrophe de début - qui convertit ces nombres en texte.
Si vous essayez d'utiliser les options intégrées pour modifier la mise en forme des cellules avec cet ensemble de données, cela ne fera rien. C'est parce qu'Excel ne les considère pas comme des nombres, et puisque les dates sont des nombres, Excel refuse votre souhait de les formater.
Dans un tel cas, vous pouvez soit convertir ces textes en nombres, puis utiliser les méthodes de formatage décrites ci-dessus, soit utiliser une autre technique pour convertir les numéros de série en dates - en utilisant la fonction TEXTE.
La fonction TEXTE prend deux arguments - le nombre qui doit être formaté et le code de format.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez convertir tous les nombres et la colonne A en dates.
Ci-dessous la formule qui pourrait le faire:
=TEXTE(A2,"MM/JJ/AAAA")
Notez que dans la formule, j'ai spécifié que le formatage de la date doit être au format MM/JJ/AAAA. Si vous devez utiliser un autre formatage, vous pouvez spécifier le code correspondant comme deuxième argument de la formule TEXTE.
Vous pouvez également utiliser cette formule pour afficher la date ainsi que l'heure.
Par exemple, si vous souhaitez que le résultat final soit au format - 01/01/2020 12:00 AM, utilisez la formule ci-dessous :
=TEXTE(A2,"mm/jj/aaaa hh:mm AM/PM")
Dans la formule ci-dessus, j'ai également ajouté le format de l'heure, donc s'il y a des parties décimales dans les nombres, il serait affiché comme l'heure en heures et minutes.
Si vous ne voulez que la date (et non la formule sous-jacente), convertissez les formules en valeurs statiques en utilisant le collage spécial.
Un grand avantage de l'utilisation de la fonction TEXTE pour convertir les numéros de série en dates est que vous pouvez combiner le résultat de la formule TEXTE avec d'autres formules. Par exemple, vous pouvez combiner la date résultante avec le texte et afficher quelque chose comme - La date d'échéance est le 01-janv-2020 ou alors La date limite est le 01-janv-2020
Remarque : N'oubliez pas que les dates et l'heure sont stockées sous forme de nombres, où chaque nombre entier représenterait une journée complète et la partie décimale représenterait cette partie de la journée. Donc 1 signifierait 1 journée complète et 0,5 signifierait une demi-journée ou 12 heures.
Ce sont donc deux moyens simples que vous pouvez utiliser pour convertir des numéros de série en dates dans Microsoft Excel.
J'espère que vous avez trouvé ce tutoriel utile!
