Besoin d’obtenir la date la plus récente d’une plage Excel en ignorant les vides (et sans que 31/12/9999 vienne tout fausser) ? Voici des méthodes fiables — MAXIFS, alternatives pour anciennes versions, formules dynamiques 365 et TCD — avec exemples concrets et pièges à éviter.
Vue d’ensemble
Dans Excel, la recherche de la date la plus récente paraît simple : on pense spontanément à MAX. Mais, selon le contenu de la plage (cellules réellement vides, vides renvoyés par formule "", dates saisies en texte, valeurs « sentinelles » comme 31/12/9999, présence d’horodatages), le résultat peut être faux ou formaté comme un nombre. Ce guide présente des solutions éprouvées, prêtes à copier-coller, pour récupérer la date maximale en ignorant les cellules vides, avec ou sans critères, dans toutes les versions d’Excel.
Solution recommandée : MAXIFS / MAX.SI.ENS
Si vous disposez d’Excel 2019 ou Microsoft 365, privilégiez MAXIFS (EN) / MAX.SI.ENS (FR). L’idée est d’indiquer à Excel de ne considérer que les cellules non vides dans la plage de dates.
Cas simple : ignorer uniquement les vides
Plage de dates dans la colonne A :
- FR :
=MAX.SI.ENS(A:A; A:A; "<>")
- EN :
=MAXIFS(A:A, A:A, "<>")
Important : si le résultat s’affiche comme un nombre (par ex. 45212), appliquez un format de cellule de type Date.
Cas avec critères supplémentaires (par ID, Type, Statut, etc.)
Exemple : on veut la date max pour l’ID indiqué en F3 tout en ignorant les vides.
- FR :
=MAX.SI.ENS($D$3:$D$8; $A$3:$A$8; F3; $D$3:$D$8; "<>")
- EN :
=MAXIFS($D$3:$D$8, $A$3:$A$8, F3, $D$3:$D$8, "<>")
Ici :
$D$3:$D$8est la plage de dates à maximiser.$A$3:$A$8contient l’ID (critère principal).$D$3:$D$8; "<>"exclut explicitement les cellules vides de la plage de dates.
Exclusion d’une valeur sentinelle (ex. 31/12/9999)
Si un système importe une date « placeholder » comme 31/12/9999, combinez « non vide » et « différent de 31/12/9999 ».
- FR :
=MAX.SI.ENS(A:A; A:A; "<>"; A:A; "<>"&DATE(9999;12;31))
- EN :
=MAXIFS(A:A, A:A, "<>", A:A, "<>"&DATE(9999,12,31))
Anciennes versions : alternatives sans MAXIFS
Formule matricielle classique (CSE)
Fonctionne dans Excel 2016 et antérieur. Cette formule ignore les vides :
- FR :
=MAX(SI(A3:A1000<>""; A3:A1000))
- EN :
=MAX(IF(A3:A1000<>"", A3:A1000))
Validation : dans Excel ≤2016, validez avec Ctrl+Maj+Entrée. Dans 365/2021, Entrée suffit.
Performance : préférez un intervalle borné (A3:A1000) plutôt que A:A dans les vieilles versions.
AGREGAT / AGGREGATE (sans CSE)
Une alternative robuste sans saisie matricielle explicite :
- FR :
=AGREGAT(14; 6; A3:A1000/(A3:A1000<>""); 1)
- EN :
=AGGREGATE(14, 6, A3:A1000/(A3:A1000<>""), 1)
Explication rapide : 14 = LARGE/GRANDE.VALEUR (on récupère la 1re plus grande), 6 = ignorer erreurs, et l’expression A3:A1000/(A3:A1000<>"") produit des erreurs pour les vides, donc écartées.
Excel 365 : formule dynamique simple
Avec Excel 365, les fonctions dynamiques permettent une solution très lisible :
- FR :
=MAX(FILTRE(A:A; A:A<>""))
- EN :
=MAX(FILTER(A:A, A:A<>""))
Pour éviter d’afficher 0 ou une date « 00/01/1900 » quand tout est vide :
- FR :
=SIERREUR(MAX(FILTRE(A:A; A:A<>"")); "")
- EN :
=IFERROR(MAX(FILTER(A:A, A:A<>"")), "")
Version dynamique avec exclusion d’une sentinelle
- FR :
=MAX(FILTRE(A:A; (A:A<>"")*(A:A<>DATE(9999;12;31))))
- EN :
=MAX(FILTER(A:A, (A:A<>"")*(A:A<>DATE(9999,12,31))))
Tableau croisé dynamique : obtenir le max par groupe
Le TCD est idéal pour calculer la date la plus récente par ID / Type / Statut, sans écrire de formule complexe.
- Sélectionnez vos données et insérez un Tableau croisé dynamique.
- Placez les champs d’identification (par ex. ID, Type) en Lignes.
- Placez le champ Date en Valeurs, puis définissez l’agrégat sur Max.
- Appliquez un format Date à la valeur résultante.
C’est une approche performante pour obtenir le maximum par groupe, tout en gérant aisément des jeux de données volumineux.
Jeu d’exemple complet
Supposons la table suivante (les guillemets simples indiquent un texte) :
| ID | Type | Date | Commentaire |
|---|---|---|---|
| A01 | Commande | 15/03/2024 | Valide |
| A01 | Commande | Cellule réellement vide | |
| A01 | Livraison | 31/12/9999 | Sentinelle à exclure |
| A02 | Commande | ’15/07/2024 | Date stockée en texte |
| A02 | Livraison | 15/07/2024 16:45 | Horodatage (date + heure) |
| A01 | Facture | 02/04/2024 | Valide |
Objectif 1 : date max globale en ignorant les vides :
=MAX.SI.ENS(C:C; C:C; "<>")
Objectif 2 : date max pour l’ID A01 en ignorant vides et 31/12/9999 :
=MAX.SI.ENS($C:$C; $A:$A; "A01"; $C:$C; "<>"; $C:$C; "<>"&DATE(9999;12;31))
Objectif 3 : convertir les « dates texte » avant de prendre le max (utile si la colonne mélange texte et dates) :
- FR (CSE avant 365) :
=MAX(SIERREUR(--C3:C1000;0))
- EN (CSE before 365) :
=MAX(IFERROR(--C3:C1000,0))
Le double signe moins -- force la conversion texte→nombre (date série). SIERREUR/IFERROR met 0 quand la conversion échoue.
Cas particuliers et astuces
Cellules « vides » issues de formules ("")
Une formule telle que =SI(condition; "OK"; "") renvoie une chaîne vide "". Elle n’est pas techniquement « vide », mais MAXIFS avec critère "<>" n’en souffre pas puisque le calcul du maximum ignore de toute façon les textes. Vous pouvez donc utiliser les solutions précédentes sans précaution supplémentaire.
Dates stockées comme texte
Si certaines « dates » sont en texte (alignées à gauche, apostrophe visible à l’édition), convertissez-les avant le calcul ou encapsulez une conversion :
- FR :
=MAX(SIERREUR(--A3:A1000;0))(CSE avant 365) - EN :
=MAX(IFERROR(--A3:A1000,0))(CSE before 365)
Alternative robuste 365 :
- FR :
=MAX(SIERREUR(--FILTRE(A:A;A:A<>"");0)) - EN :
=MAX(IFERROR(--FILTER(A:A,A:A<>"");0))
Si votre jeu de données mélange de vrais nombres de série (dates) et des textes, cette technique uniformise tout en nombres de date.
Aucun résultat non vide
Pour éviter d’afficher 0 (qui correspond à 00/01/1900) quand aucune date valide n’est trouvée :
- FR :
=SIERREUR(MAX(FILTRE(A:A;A:A<>"")); "") - EN :
=IFERROR(MAX(FILTER(A:A, A:A<>"")), "")
Horodatages : ignorer l’heure et ne garder que la date
Si la colonne contient des date‑heure et que vous voulez uniquement la date (sans tenir compte de l’heure), vous avez deux options :
- Après coup :
=ENT( MAX.SI.ENS(A:A; A:A; "<>") )(FR) ou=INT(MAXIFS(A:A, A:A, "<>"))(EN) - Filtrer sur la date tronquée :
=MAX.SI.ENS(ENT(A:A); A:A; "<>")(FR 365)
Pourquoi ça marche ? Dans Excel, une date‑heure est un nombre : partie entière = date, décimales = heure. ENT/INT supprime les décimales.
Plusieurs colonnes de dates
Si vos dates sont dispersées sur plusieurs colonnes (ex. B:D), vous pouvez chercher la plus récente en ignorant les vides :
- Matriciel (toutes versions) :
=MAX(SI(B2:D1000<>""; B2:D1000))
(Validez en CSE si nécessaire.)
Plages structurées (Tables)
Avec un tableau Excel nommé Ventes et une colonne Date :
- FR :
=MAX.SI.ENS(Ventes[Date]; Ventes[Date]; "<>") - EN :
=MAXIFS(Ventes[Date], Ventes[Date], "<>")
Les références structurées se mettent à jour automatiquement lors de l’ajout de lignes.
Séparateur d’arguments : point‑virgule vs virgule
Selon vos paramètres régionaux, Excel emploie ; (FR) ou , (EN) pour séparer les arguments. Adaptez les exemples en conséquence. Dans l’article, « FR » utilise ; et « EN » utilise ,.
Formatage et système de dates
- Si vous voyez un nombre au lieu d’une date, appliquez un format Date.
- Sur d’anciens fichiers Mac, le système « 1904 » peut décaler les dates ; vérifiez Fichier > Options > Options avancées > Lors du calcul de ce classeur.
Comparatif des méthodes
| Méthode | Version idéale | Force | Limite |
|---|---|---|---|
| MAXIFS / MAX.SI.ENS | 2019 / 365 | Lisible, critères multiples, gère vides facilement | Indisponible dans 2016 et antérieur |
| MAX + SI (matriciel) | Toutes | Compatible anciennes versions | CSE requis avant 365, attention aux grandes plages |
| MAX + FILTRE | 365 | Très lisible, dynamique, facile à imbriquer | Réservé aux versions récentes |
| AGREGAT / AGGREGATE | 2010+ | Évite CSE, ignore erreurs | Syntaxe moins intuitive |
| TCD (Max) | Toutes | Rapide par groupe, grande volumétrie | Moins flexible pour enchaîner dans d’autres formules |
Recettes prêtes à l’emploi
Max global en ignorant les vides
- FR :
=MAX.SI.ENS(A:A; A:A; "<>") - EN :
=MAXIFS(A:A, A:A, "<>")
Max avec critères (par ID)
- FR :
=MAX.SI.ENS($D$3:$D$8; $A$3:$A$8; F3; $D$3:$D$8; "<>") - EN :
=MAXIFS($D$3:$D$8, $A$3:$A$8, F3, $D$3:$D$8, "<>")
Exclure 31/12/9999
- FR :
=MAX.SI.ENS(A:A; A:A; "<>"; A:A; "<>"&DATE(9999;12;31)) - EN :
=MAXIFS(A:A, A:A, "<>", A:A, "<>"&DATE(9999,12,31))
Anciennes versions (matriciel)
- FR :
=MAX(SI(A3:A1000<>""; A3:A1000)) - EN :
=MAX(IF(A3:A1000<>"", A3:A1000))
Excel 365 dynamique
- FR :
=MAX(FILTRE(A:A; A:A<>"")) - EN :
=MAX(FILTER(A:A, A:A<>""))
Max sans l’heure
- FR :
=ENT(MAX.SI.ENS(A:A; A:A; "<>")) - EN :
=INT(MAXIFS(A:A, A:A, "<>"))
FAQ rapides
Pourquoi j’obtiens un nombre au lieu d’une date ?
Parce que les dates sont stockées comme nombres de série. Appliquez un format Date à la cellule.
La formule renvoie 0 : que faire ?
Enveloppez avec SIERREUR/IFERROR pour renvoyer "" si rien n’est trouvable, ou vérifiez la présence de texte non converti.
Les cellules « vides » créées par une formule perturbent‑elles le calcul ?
Non : MAX et MAXIFS ignorent le texte. Le critère "<>" suffit.
Dois‑je éviter A:A ?
Sur d’anciennes versions ou gros fichiers, préférez A3:A100000 pour de meilleures performances.
Le TCD est‑il actualisé automatiquement ?
Actualisez‑le après import ou modifiez les options d’actualisation si nécessaire.
Bonnes pratiques de performance
- Dimensionnez vos plages sur anciennes versions (
10 000lignes plutôt qu’une colonne entière). - Utilisez des Tables : elles se redimensionnent automatiquement et simplifient les références.
- Évitez les conversions répétées (
--A:A) sur de très grandes plages ; pré‑convertissez une fois dans une colonne auxiliaire si besoin. - Centralisez les constantes avec
LET(365) pour lisibilité et maintenance.
Erreurs courantes et comment les éviter
- Oublier d’exclure 31/12/9999 : ajoutez le critère
"<>"&DATE(9999;12;31). - Mélanger dates et textes : convertissez avec
--ouDATEVALUE/DATEVALavant le calcul. - Confondre « vide » et
"": souvenez‑vous que""est du texte, mais MAX l’ignore. - Formatage manquant : si le résultat est numérique, appliquez un format Date.
- Négliger l’heure : si nécessaire, tronquez avec
ENT/INT.
Méthode conseillée en pratique
Dans la plupart des cas, la solution la plus simple et robuste est : MAXIFS / MAX.SI.ENS avec le critère "<>" sur la même plage de dates, puis ajoutez vos critères habituels (ID, Type, etc.) et, si présent, l’exclusion de 31/12/9999. Cette approche combine lisibilité, compatibilité avec les « vides » de formule, et fiabilité face aux données réelles.
Guides pas‑à‑pas selon votre version
Microsoft 365 / 2019+
- Placez la formule
=MAX.SI.ENS(plageDate; plageDate; "<>"). - Ajoutez des paires plage/critère pour filtrer (ID, Type, Statut).
- Si besoin, ajoutez
plageDate; "<>"&DATE(9999;12;31)pour la sentinelle. - Formatez en Date.
Excel 2016 et antérieur
- Utilisez
=MAX(SI(plageDate<>""; plageDate))et validez en CSE. - Si « dates texte » :
=MAX(SIERREUR(--plageDate;0)). - Formatez en Date.
Avec TCD
- Insérez le TCD > Lignes : ID/Type > Valeurs : Date (Max).
- Appliquez le format Date et mettez à jour au besoin.
Checklist rapide
- ✅ Plage correcte et bornée si nécessaire.
- ✅ Critère
"<>"sur la même plage de dates. - ✅ Exclusion de 31/12/9999 le cas échéant.
- ✅ Conversion des « dates texte » si présentes.
- ✅ Format Date appliqué au résultat.
Conclusion
Pour obtenir la date maximale d’une plage en ignorant les vides, partez de MAXIFS / MAX.SI.ENS avec "<>". Ajoutez vos critères (ID/Type) et, si nécessaire, l’exclusion de la valeur sentinelle. Sur anciennes versions, utilisez la variante matricielle ou AGREGAT. En 365, la combinaison FILTRE + MAX offre une syntaxe compacte et très lisible. Avec ces recettes, vous couvrez la quasi‑totalité des cas concrets rencontrés en production.
Annexe : conversions et formats utiles
- Forcer une conversion « texte → date » :
--A2ouDATEVALUE/DATEVAL(A2). - Récupérer uniquement la date depuis un horodatage :
ENT(A2)/INT(A2). - Empêcher l’affichage de 0 : encapsuler avec
SIERREUR/IFERROR(…, "").
Fichiers lourds ? Pensez également à Power Query pour nettoyer les « dates texte » ou retirer 31/12/9999 à la source, puis laissez une formule MAXIFS simple faire le reste.