Excel : vos dates ne se trient pas ? Distinguer texte, vraie date et heure cachée

Vous triez une colonne de dates du plus ancien au plus récent, mais le 9 septembre reste sous le 17 septembre. Certaines lignes se regroupent par année dans le filtre, d’autres restent isolées. Deux cellules affichent le même jour, mais une recherche ou une formule les considère comme différentes.

La bonne première action n’est pas de changer le format d’affichage. Il faut identifier ce que contient réellement chaque cellule : du texte, une vraie date Excel, ou une date accompagnée d’une heure invisible. Ensuite seulement, convertissez dans une nouvelle colonne et contrôlez le résultat avant de remplacer les données d’origine.

Cette distinction évite trois erreurs fréquentes : obtenir un tri seulement « joli » mais faux, inverser le jour et le mois lors d’une conversion, ou supprimer une heure qui avait une utilité métier.

Pourquoi deux dates identiques à l’écran peuvent être différentes

Excel enregistre normalement une date comme un nombre séquentiel, puis applique un format pour l’afficher comme une date. Microsoft précise aussi que DATEVAL convertit une date stockée comme texte en un numéro de série reconnu comme date. Le réglage de date du système peut toutefois modifier l’interprétation du texte (documentation Microsoft sur DATEVAL).

Une cellule qui affiche 17/09/2026 peut donc contenir :

1. le texte « 17/09/2026 » ;
2. un nombre correspondant au 17 septembre 2026, affiché avec un format de date ;
3. un nombre correspondant au 17 septembre 2026 à 14 h 30, alors que le format masque l’heure.

Changer le format d’une cellule ne convertit pas nécessairement son contenu. C’est comme changer l’étiquette d’un dossier sans vérifier ce qu’il contient.

Trois indices utiles, mais aucun verdict à lui seul

• Une date-texte est souvent alignée à gauche et une date numérique à droite. Microsoft signale cet indice dans sa procédure de conversion, mais l’alignement peut avoir été modifié manuellement (convertir des dates stockées comme texte).
• Le petit triangle vert peut signaler certaines dates stockées comme texte, mais son absence ne prouve rien.
• Le filtre qui propose des groupes « Années » et « Mois » sur certaines valeurs seulement indique souvent un mélange de types.

Le test le plus robuste reste une colonne de diagnostic.

La grille « valeur, type, origine, conversion, contrôle »

Ne corrigez pas directement la colonne source. Ajoutez cinq colonnes temporaires :

Cette grille est l’outil central : elle oblige à connaître l’origine avant de choisir une conversion. Sans cette information, 03/04/2026 reste ambigu : 3 avril ou 4 mars ?

Étape 1 : tester le type réel de la cellule

Si la valeur suspecte est en A2, saisissez dans une nouvelle colonne :

=ESTNUM(A2)

VRAI signifie qu’Excel voit un nombre. Comme une vraie date Excel est numérique, c’est un bon signal. FAUX signifie que la cellule n’est pas numérique : elle peut notamment contenir du texte ou une erreur. Les fonctions de type EST testent la valeur sans convertir automatiquement un texte en nombre (documentation Microsoft des fonctions IS/EST).

Le test ne suffit pas à lui seul : le nombre 123 est numérique sans être forcément une date métier valide. Vérifiez aussi l’origine, la plage attendue et le résultat après conversion.

Compter les valeurs à examiner

Dans une colonne de 100 lignes, vous pouvez compter les cellules qu’Excel ne voit pas comme des nombres :

=NB.SI(B2:B101;FAUX)

Ici, la colonne B contient le résultat de ESTNUM. Le total obtenu est un indicateur de travail, pas une preuve que toutes les autres dates sont correctes.

Étape 2 : détecter une heure cachée

Une date et une heure partagent le même nombre : la partie entière représente le jour et la partie décimale l’heure. Si A2 est bien numérique, utilisez dans une colonne de contrôle :

=SI(ESTNUM(A2);A2-ENT(A2);"texte")

• résultat 0 : aucune heure n’est enregistrée ;
• résultat supérieur à 0 : une heure existe, même si elle n’est pas affichée ;
• résultat texte : la cellule doit d’abord être examinée comme texte.

Deux cellules affichant toutes deux 17/09/2026 peuvent ainsi être différentes : l’une vaut le 17 septembre à 00 h 00, l’autre à 14 h 30. Cela peut empêcher une correspondance exacte ou créer des regroupements inattendus.

Si l’heure ne sert réellement à rien, créez une nouvelle colonne :

=ENT(A2)

Ne faites pas cette suppression par réflexe. Dans un suivi d’interventions, de livraisons ou d’appels, l’heure peut être une donnée utile. Décidez d’abord si votre unité de travail est le jour ou l’instant précis.

Étape 3 : convertir sans inverser le jour et le mois

Pour un texte non ambigu et compatible avec les paramètres de votre poste, DATEVAL peut convenir :

=DATEVAL(A2)

Microsoft avertit que le résultat dépend des paramètres de date du système. Une valeur comme 03/04/2026 ne doit donc pas être convertie en masse tant que vous ne connaissez pas sa convention d’origine.

Si la source est strictement au format AAAA-MM-JJ

Pour une source documentée comme 2026-09-17, vous pouvez construire la date explicitement :

=DATE(GAUCHE(A2;4);STXT(A2;6;2);DROITE(A2;2))

Testez cette formule sur une copie et ajoutez un contrôle d’erreur si le fichier réel peut contenir des espaces, des cellules vides ou d’autres formats. Les noms de fonctions et séparateurs peuvent varier selon la langue et la version d’Excel.

Si le problème revient chaque semaine ou chaque mois

Une formule ponctuelle peut devenir fragile quand plusieurs exports sont assemblés. Power Query permet de définir le type d’une colonne avec des paramètres régionaux. Microsoft explique qu’une date jour/mois/année peut produire des erreurs si elle est interprétée avec les règles américaines mois/jour/année, et recommande alors « Modifier le type > Utiliser les paramètres régionaux » (types de données et paramètres régionaux dans Power Query).

Dans une requête répétable :

1. conservez la colonne source ;
2. affectez le type Date en choisissant la convention de la source ;
3. isolez les erreurs au lieu de les supprimer silencieusement ;
4. extrayez temporairement année, mois et jour sur un échantillon ;
5. contrôlez le nombre de lignes, le nombre d’erreurs et les dates minimale et maximale ;
6. rechargez seulement après validation.

Cette démarche est plus adaptée qu’une correction manuelle si le même problème revient à chaque import.

Cas fictif : trois exports de suivi de formation

Imaginons un service RH qui rassemble trois fichiers. Le cas est fictif, mais les valeurs illustrent des mélanges courants.

Le service ne devrait pas choisir « français » uniquement parce que l’utilisateur travaille en France. Il doit rattacher chaque fichier à sa source. Pour la valeur ambiguë, la bonne action est de suspendre la conversion, puis de vérifier la convention auprès du producteur du fichier ou dans une valeur impossible à confondre, par exemple un jour supérieur à 12.

Exercice corrigé : classer avant de convertir

Vous trouvez ces six valeurs dans une colonne dont le format d’affichage est jj/mm/aaaa :

Correction : seules les lignes 1 et 3 sont confirmées comme valeurs numériques de date à ce stade. La ligne 6 est numérique, mais son sens métier reste à vérifier. Les lignes 2, 4 et 5 exigent une règle de conversion liée à leur source. La ligne 3 ne doit être tronquée que si le besoin est bien un regroupement par jour.

Les cinq contrôles avant de remplacer la colonne d’origine

Avant de coller des valeurs ou de supprimer la source, vérifiez :

• le même nombre de lignes avant et après traitement ;
• zéro erreur non expliquée ;
• une date minimale et une date maximale plausibles ;
• cinq valeurs tirées de sources différentes, dont une date avec un jour supérieur à 12 ;
• le comportement du tri, du filtre et d’une correspondance exacte.

Conservez également une copie du fichier brut et notez la convention utilisée. Si une conversion donne un résultat plausible mais faux — 4 mars au lieu du 3 avril — aucun message d’erreur ne vous protégera.

Formule, Power Query ou formation : comment choisir ?

La formation Excel débutant de StraFormation couvre notamment la distinction entre texte, nombre et date ainsi que le tri et le filtrage. Elle convient si le besoin concerne la construction et la fiabilisation de tableaux courants. La formation Excel avec Power Query est plus pertinente lorsque le nettoyage doit être rejoué sur des imports réguliers. Les prérequis, la version d’Excel, le format et les modalités doivent être confirmés pour le parcours envisagé.

Pour prolonger le contrôle des données, consultez aussi pourquoi une somme Excel peut être fausse, comment supprimer les doublons sans effacer la bonne ligne et quand ajouter ou fusionner des tables dans Power Query.

Préparer une demande de parcours utile

Pour être orienté vers un parcours adapté, apportez si possible : votre version d’Excel, deux fichiers d’exemple anonymisés, l’origine des dates, leur convention attendue, la fréquence de mise à jour, le volume de lignes et le résultat final à produire. Précisez aussi si l’heure doit être conservée.

Ces éléments permettent de distinguer un besoin ponctuel de correction, un besoin de méthode dans Excel et un besoin d’automatisation avec Power Query, sans promettre qu’une seule formule réglera tous les cas.