Comment regrouper les dates d’un TCD Excel par année, mois et jour

Auditer et préparer tes dates avant le TCD

Le réflexe le plus coûteux consiste à créer le TCD avant de vérifier les dates. Excel peut afficher une valeur qui ressemble à 31/01/2025 alors qu'elle est enregistrée comme texte. Visuellement, tout paraît correct, mais le regroupement temporel échoue, car Excel ne reconnaît pas un véritable numéro de série de date.

Vérifier la nature réelle de la colonne

Ajoute une colonne temporaire à côté de ta colonne Date, puis saisis la fonction ESTNUM :

=ESTNUM(A2)

Si le résultat est VRAI, Excel stocke la valeur comme un nombre, donc comme une date exploitable. Si le résultat est FAUX, tu as probablement une date importée comme texte, un espace invisible ou un format non reconnu.

Contrôle aussi les formats qui arrivent souvent depuis un CSV ou un ERP. Une colonne peut mélanger AAAA/MM/JJ, JJ/MM/AAAA, des valeurs avec une heure et des cellules vides. Le format d'affichage ne suffit pas à garantir que le contenu est homogène.

Règle terrain : ne corrige pas seulement l'apparence. Vérifie le type réel de la donnée, puis teste quelques lignes représentatives.

Nettoyer les textes et les incohérences

Pour repérer des caractères invisibles, examine la longueur de la cellule avec :

=NBCAR(A2)

Tu peux ensuite utiliser SUPPRESPACE pour retirer les espaces superflus, ou reconstruire la date à partir de ses composants si l'import a séparé l'année, le mois et le jour. Si la conversion reste instable, Power Query est souvent plus fiable qu'une succession de formules, notamment pour des fichiers réimportés régulièrement. Tu peux consulter ce guide pratique sur Power Query pour structurer ce nettoyage.

Vérifie également les cellules vides. Une seule valeur absente dans un champ Date peut empêcher le regroupement ou générer une catégorie inattendue. Dans une source professionnelle, remplace la valeur manquante par une correction métier documentée, plutôt que par une date arbitraire qui fausserait les indicateurs.

Contrôler le système de dates du classeur

Excel utilise deux systèmes de dates, 1900 et 1904. Dans le système 1900, le 1er janvier 1900 correspond au numéro de série 1. Dans le système 1904, le point de départ est le 1er janvier 1904. Microsoft précise que l'écart entre ces deux systèmes est de 1 462 jours dans sa documentation sur les systèmes de dates Excel.

Ce piège apparaît surtout quand tu copies des feuilles entre des classeurs issus d'environnements différents. Si toutes les dates semblent décalées de façon systématique, vérifie les options avancées du classeur avant de modifier chaque cellule.

Créer le TCD et activer le regroupement temporel

Une fois la colonne Date fiabilisée, transforme idéalement la source en tableau structuré. Sélectionne une cellule de la base, utilise Insertion > Tableau, puis donne un nom explicite au tableau. Cette structure facilite l'extension de la plage quand les contrôleurs de gestion ajoutent de nouvelles lignes.

Construire le tableau croisé dynamique

Suis cette séquence :

1. Sélectionne la source. Vérifie que les en-têtes sont uniques et qu'aucune ligne vide ne coupe la base.
2. Ouvre Insertion > Tableau croisé dynamique. Microsoft documente la création d'un TCD depuis une table ou une plage existante, avec placement sur une nouvelle feuille ou une feuille existante dans la procédure officielle de création d'un tableau croisé dynamique.
3. Place Date dans la zone Lignes. Les dates apparaissent alors comme niveaux de détail.
4. Place ton indicateur dans Valeurs. Il peut s'agir d'un montant de ventes, d'un effectif ou d'un total de dépenses.
5. Ajoute éventuellement un axe métier dans Filtres ou Colonnes. Par exemple, Service pour les RH ou Centre de coûts pour la finance.

Pour approfondir la construction générale d'un TCD, consulte ce tutoriel sur la création d'un tableau croisé dynamique.

Regrouper par année, mois et jour

Clique sur une date dans le TCD, puis fais clic droit > Grouper. Dans la boîte de dialogue, sélectionne Années, Mois et, si nécessaire, Jours, puis valide avec OK.

Le choix dépend de la question métier. Pour un suivi mensuel de chiffre d'affaires, expose Années et Mois. Pour une analyse de présence RH, conserve parfois le niveau Jour. Pour une revue annuelle, Années suffit et évite de surcharger la lecture.

Dans certaines versions récentes d'Excel, le regroupement automatique propose directement Années, Trimestres et Mois. Ce comportement est pratique pour un reporting trimestriel, mais il ne correspond pas toujours au besoin d'un service comptable qui travaille au jour. Dans ce cas, refais clic droit > Grouper, puis sélectionne explicitement les niveaux nécessaires.

La chronologie Excel offre une autre approche. Clique dans le TCD, ouvre Analyse du tableau croisé dynamique > Insérer une chronologie, sélectionne Date, puis choisis l'affichage par années, trimestres, mois ou jours. Tu obtiens un filtre visuel qui permet de sélectionner une période sans ouvrir les menus du champ.

Extraire année, mois et jour avec les bonnes méthodes

Le regroupement du TCD suffit pour explorer une période, mais il ne répond pas à tous les besoins. Dans un fichier RH, tu peux avoir besoin d'une colonne Année d'embauche. Dans un suivi budgétaire, tu peux vouloir trier les mois dans l'ordre chronologique ou construire une clé de période stable.

Trois approches sont utiles. Les fonctions Excel conviennent à une source simple, les colonnes calculées servent à enrichir rapidement un TCD, et Power Query devient préférable quand le nettoyage doit se répéter.

Choisir selon ton contexte

Méthode	Cas d'usage idéal	Avantages	Limites
Fonctions ANNEE, MOIS, JOUR	Source stable et volume maîtrisé	Rapide à mettre en place, lisible, facile à contrôler	Les formules doivent être recopiées et surveillées
Colonnes calculées dans le TCD	Besoin ponctuel d'analyse	Aucun enrichissement permanent de la source	Moins flexible pour des règles métier complexes
Power Query	Imports CSV, ERP ou fichiers récurrents	Nettoyage reproductible, étapes mémorisées, meilleure robustesse	Demande une conception initiale plus structurée

Pour extraire les composants d'une date située en A2, utilise :

=ANNEE(A2)

=MOIS(A2)

=JOUR(A2)

La fonction ANNEE renvoie l'année, MOIS le numéro du mois et JOUR le numéro du jour. Tu trouveras une présentation dédiée dans cette fiche sur la fonction ANNEE dans Excel.

Le numéro du mois est pratique pour trier, mais moins lisible dans un tableau destiné à une direction. Ajoute alors un libellé, par exemple avec TEXTE, tout en conservant une colonne numérique pour l'ordre de tri. Évite de remplacer complètement la date native par du texte, sinon tu perdras une partie des capacités de filtrage temporel.

Reconstruire une date avec DATE

La fonction DATE attend trois arguments, année, mois et jour. Sa syntaxe est :

=DATE(année;mois;jour)

Microsoft précise que l'argument jour peut être un entier positif ou négatif compris entre 1 et 31, ce qui permet de construire des échéances à partir de calculs séparés dans la documentation de la fonction DATE.

Pour décaler un contrat à partir d'une date en A2, avec une année en plus, deux mois supplémentaires et un jour calculé, tu peux utiliser :

=DATE(ANNEE(A2)+B2;MOIS(A2)+C2;JOUR(A2)+D2)

Microsoft fournit cette logique avec la formule =DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)+D2) dans son aide consacrée à l'ajout et à la soustraction de dates. Excel normalise ensuite les composants selon le calendrier, ce qui évite de gérer manuellement chaque changement de mois.

Ajouter des slicers et hiérarchies pour naviguer dans le temps

Un TCD peut être exact et rester pénible à utiliser. Pour un comité mensuel, personne ne veut ouvrir plusieurs menus, cocher des périodes et vérifier à chaque fois que le filtre est bien appliqué. Les segments et les chronologies rendent la navigation plus directe.

Créer une navigation temporelle

Clique dans le TCD, puis choisis Analyse du tableau croisé dynamique > Insérer une chronologie. Sélectionne le champ Date et règle l'unité d'affichage sur Mois, Trimestres ou Années selon le niveau de lecture attendu.

Pour filtrer un segment classique, utilise Analyse du tableau croisé dynamique > Insérer un segment, puis sélectionne Année, Service, Entité ou toute autre dimension utile. Le segment est particulièrement efficace pour les responsables RH qui veulent passer d'un établissement à l'autre, tandis que la chronologie sert à restreindre la période.

La combinaison fonctionne bien dans un tableau de bord de contrôle de gestion. La chronologie filtre la période, le segment filtre le périmètre, et le TCD conserve une lecture synthétique.

Organiser une hiérarchie lisible

Dans la zone Lignes, place les niveaux dans cet ordre :

• Année, pour la vision globale.
• Trimestre, pour les revues intermédiaires.
• Mois, pour le pilotage courant.
• Jour, uniquement quand le détail est réellement utile.

Cette hiérarchie permet de développer une année puis un trimestre sans exposer immédiatement toutes les dates quotidiennes. Elle réduit le bruit visuel et aide ton lecteur à passer du total annuel à l'explication mensuelle.

Si plusieurs TCD utilisent la même source ou le même modèle de données, vérifie les connexions du segment via Connexions de rapport, dans l'onglet du segment. Tu peux alors synchroniser plusieurs tableaux de bord avec une même sélection temporelle. Si un TCD n'apparaît pas dans la liste, contrôle qu'il repose bien sur la même source logique.

Résoudre les erreurs courantes de dates dans les TCD

Le clic droit sur une date n'est pas une solution universelle. Quand le regroupement échoue, répéter l'opération ne change rien. Il faut identifier le symptôme, remonter à la cause et corriger la source.

Lire le symptôme avant de corriger

Symptôme	Cause probable	Correction
Les dates s'affichent comme du texte ou des numéros de série	Type de donnée incorrect ou format mal interprété	Vérifie =ESTNUM(A2), puis convertis la colonne en vraies dates
Le regroupement échoue immédiatement	Cellule vide, date invalide ou sélection non exploitable	Nettoie toute la colonne et sélectionne un seul champ Date
Les mois apparaissent dans un ordre inattendu	Libellés textuels ou tri alphabétique	Conserve un numéro de mois pour trier, puis affiche un libellé séparé
La date semble décalée	Systèmes 1900 et 1904 différents	Compare les paramètres du classeur et sécurise le transfert
Le TCD conserve d'anciennes lignes	Source ou cache non actualisé	Actualise la source, puis actualise le TCD

Les pièges qui demandent un vrai diagnostic

Une date affichée comme 45231 n'est pas forcément fausse. Il peut s'agir du numéro de série interne d'Excel, présenté avec un format Général. Dans ce cas, applique un format de date depuis Accueil > Nombre, puis vérifie que la valeur reste numérique.

À l'inverse, une date qui ressemble à une date peut être du texte. CNUM peut aider quand le contenu est convertible, mais Power Query est plus sûr pour des fichiers issus d'importations répétitives. Les dates mélangées, comme une partie en JJ/MM/AAAA et une autre en AAAA/MM/JJ, exigent une règle explicite avant conversion.

Diagnostic utile : si une correction manuelle règle une ligne mais pas les suivantes, le problème se situe dans la méthode d'importation, pas dans le TCD.

Les cellules vides sont souvent oubliées parce qu'elles se trouvent à la fin de la plage. Pourtant, elles peuvent empêcher la chronologie ou le regroupement de fonctionner correctement. Filtre la source sur les blancs, corrige les lignes concernées, puis actualise le TCD.

Automatiser et optimiser pour des rapports fiables

Un reporting mensuel ne doit pas dépendre d'un nettoyage manuel fragile. Utilise Power Query pour convertir les dates à l'importation, supprimer les espaces et appliquer la même règle à chaque nouveau fichier. Une structure Power Query dédiée à l'actualisation d'un TCD réduit les interventions répétitives.

Conserve la source dans un tableau Excel structuré. Le TCD récupérera plus facilement les nouvelles lignes après actualisation, à condition que les colonnes gardent leurs noms et leur type.

Pour l'affichage français, applique un format cohérent. Microsoft indique que j affiche les jours de 1 à 31 et que aa affiche l'année sur deux chiffres dans son aide sur les formats de dates. Dans un rapport professionnel, préfère souvent une date complète quand l'audit est important, et un libellé Mois suivi d'une Année pour les vues synthétiques.

Astuce bonus : pour récupérer une valeur ciblée dans un tableau de synthèse, combine INDEX et EQUIV avec une clé de période propre. Tu évites ainsi de dépendre d'une position de ligne qui change après un regroupement ou un filtre.

En résumé

1. Audite la colonne Date avec ESTNUM, les filtres et la recherche des cellules vides.
2. Normalise les imports avant de créer le TCD.
3. Regroupe ensuite par Années, Mois, Trimestres ou Jours selon l'usage métier.
4. Ajoute une chronologie pour faciliter la navigation.
5. Actualise la source et le TCD après chaque correction.
6. Protège la structure pour éviter les modifications accidentelles dans les rapports partagés.