Maîtriser l'affichage des dates avec les formats personnalisés
Dans ce tutoriel, je vais vous montrer comment transformer complètement l'affichage de vos dates dans Excel grâce aux formats personnalisés, une fonctionnalité méconnue qui va vous aider à améliorer votre façon de présenter vos données.
Nous découvrirons ensemble le secret bien gardé d'Excel concernant la nature réelle des dates et nous verrons pourquoi cette connaissance peut vous éviter de grosses erreurs de calculs dans vos feuilles de calcul.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous gérons les événements d'une salle de spectacle.
Ce tableau contient différents types d'événements avec leurs dates respectives, ce qui nous permettra d'explorer toutes les possibilités de formatage des dates.
Comme vous pouvez le constater, j’ai laissé les colonnes B, C et D volontairement vides.
En effet, nous allons les remplir progressivement au cours de ce tutoriel en utilisant différentes techniques de formatage de dates.
Ces colonnes nous serviront à extraire séparément l'année, le mois et le jour de nos dates complètes.
2. Comprendre la nature des dates dans Excel
Avant de nous lancer dans les formats personnalisés, il faut comprendre ce qu'est réellement une date dans Excel afin d'utiliser les dates de manière beaucoup plus efficace.
Contrairement à ce que nous pourrions penser, une date dans Excel n'est pas du texte ni un élément spécial.
En réalité, il s’agit simplement d’un nombre déguisé !
Pour nous en rendre compte, nous nous plaçons dans une cellule vide, par exemple F2, et nous saisissons le nombre 1.
Dans la cellule F3, nous saisissons le nombre 2.
Maintenant, nous sélectionnons ces deux cellules et nous appliquons un format de date nous rendant dans le menu « Accueil » du ruban et en sélectionnant la date courte dans le menu de modification rapide du format :
Nous obtenons respectivement « 01/01/1900 » et « 02/01/1900 ».
Cela nous révèle que le système de dates d'Excel commence le 1er janvier 1900, et que chaque nombre entier représente un jour supplémentaire depuis cette date de référence.
Pour approfondir cette découverte, nous pouvons tester avec des nombres plus grands. Si nous saisissons 365 dans une cellule et que nous lui appliquons un format de date, nous obtenons le 31 décembre 1900, ce qui correspond logiquement à la fin de la première année.
Si nous testons avec 36500, nous obtenons approximativement une date située 100 ans plus tard, soit vers l'année 2000.
Aujourd’hui, nous sommes aux environs du nombre 45000, ce qui correspond à environ 45000 jours écoulés depuis le début du 20ème siècle.
Ainsi, si nous sélectionnons la première date de notre tableau (12/03/2024) et que nous lui appliquons ensuite un format de nombre en utilisant le raccourci [Ctrl]+[Maj]+[1], nous obtenons 45002.
C’est ce qui nous explique pourquoi nous pouvons effectuer des calculs avec les dates (additions, soustractions) et pourquoi les formats personnalisés fonctionnent comme ils le font.
La date reste toujours ce nombre dans la mémoire d'Excel, seul l'affichage change selon le format que nous appliquons.
3. Maîtriser les formats de date personnalisés
3.1. Accéder aux formats personnalisés
Pour accéder aux formats personnalisés, nous avons plusieurs méthodes à notre disposition.
La plus rapide consiste à sélectionner les cellules que nous souhaitons formater, puis à utiliser le raccourci clavier [Ctrl]+[1] pour afficher la boîte de dialogue « Format de cellule ».
Nous pouvons également faire un clic droit sur notre sélection et choisir « Format de cellule » dans le menu contextuel.
Une fois la boîte de dialogue ouverte, nous nous rendons dans l'onglet « Nombre » et dans la liste des catégories sur la gauche, nous descendons jusqu'à trouver « Personnalisé ».
Lorsque nous sélectionnons « Personnalisé », nous découvrons une zone très importante appelée « Code ».
C'est dans cette zone que nous allons saisir nos codes de formatage personnalisés.
Nous pouvons également voir une liste de codes préexistants qui nous donne des idées et nous permet de comprendre la logique des formats.
3.2. Extraire l'année avec le format « AAAA »
Commençons par extraire l'année de nos dates.
Nous sélectionnons toute la colonne B (colonne « Année ») en cliquant sur l'en-tête de colonne.
Ensuite, nous copions les dates complètes de la colonne A en sélectionnant la plage, puis en utilisant [Ctrl]+[C] pour copier.
Nous nous plaçons dans la colonne B et nous collons avec [Ctrl]+[V].
À ce stade, nous avons les mêmes dates dans les deux colonnes.
Maintenant, nous sélectionnons à nouveau la plage B2:B11 et nous ouvrons la boîte de dialogue de format de cellule avec [Ctrl]+[1].
Dans la section « Personnalisé », nous effaçons le contenu de la zone « Code » et nous saisissons simplement « AAAA ».
Lorsque nous validons avec « OK », nous observons que nos dates se transforment pour n'afficher que l'année sur quatre chiffres : 2024.
Il est important de noter que nous aurions pu obtenir le même résultat en utilisant la formule « =ANNEE(A2) » puis en l'étendant vers le bas.
Cependant, l'avantage du format personnalisé est que nous conservons la date complète dans la cellule, ce qui peut être utile pour d'autres manipulations.
3.3. Extraire le mois avec les formats « M »
Pour traiter le mois, nous procédons de la même manière.
Nous sélectionnons la colonne C, nous copions à nouveau nos dates complètes depuis la colonne A, et nous les collons en C2. Puis nous sélectionnons la plage C2:C11 et nous ouvrons le format de cellule.
Les formats de mois offrent une grande variété d'options :
- Un seul « M » nous donne le mois sur un chiffre (3, 4, 5...).
- Deux « M » (MM) ajoutent un zéro devant si nécessaire (03, 04, 05...).
- Trois « M » (MMM) nous donnent le mois en abrégé (mar, avr, mai...).
- Enfin, quatre « M » (MMMM) affichent le mois complet (mars, avril, mai...).
Pour notre exemple, choisissons le format « MMMM » qui affichera les mois en toutes lettres.
Nous saisissons donc « MMMM » dans la zone de code et nous validons, nos dates se transforment alors pour afficher « mars », « avril », « mai », « juin » selon les données de notre tableau.
Cette présentation est particulièrement élégante dans un rapport ou un tableau de bord car elle facilite la lecture et donne un aspect professionnel à nos données.
3.4. Extraire le jour avec les formats « J »
Pour la colonne jour, nous répétons le même processus. Nous copions nos dates en colonne D et nous ouvrons le format de cellule.
Les options pour les jours sont tout aussi variées que pour les mois :
- Un seul « J » affiche le jour sur un chiffre (2, 8, 5...).
- Deux « J » (JJ) ajoutent un zéro si nécessaire (02, 08, 05...).
- Trois « J » (JJJ) nous donnent le jour de la semaine en abrégé (mar, lun, mer...).
- Quatre « J » (JJJJ) affichent le jour de la semaine complet (mardi, lundi, mercredi...).
Pour notre salle de spectacle, il serait très utile d'afficher le jour de la semaine complet, car cela aide les clients à se repérer.
Nous choisissons donc le format « JJJJ ». Après validation, nous obtenons « mardi », « lundi », « mercredi », etc., selon les jours réels de nos événements.
3.5. Créer des formats combinés créatifs
La véritable puissance des formats personnalisés se révèle quand nous combinons plusieurs éléments.
Nous pouvons créer des formats très sophistiqués en mélangeant les codes et en ajoutant du texte fixe.
Par exemple, si nous voulons afficher « mardi 12 mars 2024 », nous utilisons le code « JJJJ JJ MMMM AAAA ».
Pour un format plus compact comme « 12/03/24 », nous utilisons « JJ/MM/AA ».
Si nous préférons un style anglophone, « MMMM JJ, AAAA » nous donnera « mars 12, 2024 ».
Nous pouvons même inclure du texte fixe en l'entourant de guillemets. Par exemple, « "Événement du " JJ MMMM AAAA » affichera « Événement du 12 mars 2024 ».
Cette fonctionnalité est particulièrement utile pour créer des libellés automatiques dans nos rapports.
4. Attention au piège des années
Comme nous venons de le voir, les formats de personnalisation des dates sont très puissants et très pratiques, mais il faut toujours garder en tête ce que nous avons vu au début de ce tutoriel : les dates restent des grands nombres auxquels nous appliquons un format avancé.
Ainsi, si nous nous plaçons dans une cellule vide, par exemple F2, et nous essayons de faire un calcul simple qui consiste à reprendre la valeur d’une cellule de la colonne dans laquelle nous avons appliqué le format « AAAA » pour lui ajouter « +1 » :
=B7+1
Nous constatons que le résultat reste identique !
Ce résultat peut-être déroutant, mais en réalité c’est tout à fait logique, car le « +1 » est appliqué à la date complète, et non à l’année.
Pour nous en rendre compte, il suffit maintenant d’appliquer un format « date » classique pour retrouver le résultat complet.
C'est donc là tout l'enjeu des formats personnalisés : ils ne modifient que l'apparence de la cellule, jamais son contenu réel.
Excel stocke toujours la date complète (le fameux nombre que nous avons découvert plus tôt) et ne fait que changer la façon de l'afficher à l'écran.
Cela nous amène à établir des règles claires d'utilisation des format de date personnalisés : ces derniers sont excellents pour la présentation : rapports, tableaux de bord, documents à imprimer. Ils donnent un aspect professionnel et facilitent la lecture pour les utilisateurs finaux. En revanche, ils sont à éviter quand nous devons effectuer des calculs ou utiliser les données dans des formules.
Dans ce cas, il vaut mieux utiliser les vraies fonctions Excel comme « ANNEE() », « MOIS() », et « JOUR() » qui extraient réellement les composants de la date sous forme de nombres.