Vous obtenez mystérieusement « 1905 » à la place de l’année réelle dans votre rapport ? Le coupable n’est pas la formule YEAR() mais le format appliqué à la cellule. Découvrez comment identifier, corriger et éviter durablement cette erreur dans Excel, que vous soyez sur Windows, macOS ou Microsoft 365.
Comprendre la logique d’Excel : numéro de série, formule YEAR() et format d’affichage
Chaque date saisie dans Excel est stockée sous la forme d’un nombre de série : « 1 » correspond au 1er janvier 1900 (ou 1904 sur certains anciens Mac). Les fonctions DAY(), MONTH() et YEAR() ne font que renvoyer la partie arithmétique du nombre, sans tenir compte du format d’affichage.
Or, lorsqu’une cellule reste formatée en « Date personnalisée aaaa », Excel interprète le résultat de YEAR() comme un nouveau nombre de série. Exemple : 2024 s’affiche 16 juillet 1905, car le 2 024e jour après le 0 janvier 1900 est précisément cette date.
Scénario courant : calcul d’une “Go‑Live” annuelle à partir d’une plage dynamique
Supposons un tableau structuré (ListObject) avec :
- Colonne C : Project ID
- Colonne J : Task End Date
L’objectif est double :
- Trouver, pour chaque projet, la date de fin la plus tardive (Project End Date).
- N’afficher que l’année de cette date dans la colonne Planned Project Go Live Year.
Étape 1 : dater la fin de projet
=MAXIFS(J$2:J$46; C$2:C$46; C2)
La fonction MAXIFS filtre la plage J selon l’ID en C et renvoie la date la plus récente. Rien à changer ici.
Étape 2 : extraire l’année… sans créer le bug “1905”
=YEAR([@[Project End Date]])
…ou, en ligne directe :
=YEAR(MAXIFS(J$2:J$46; C$2:C$46; C2))
Si la valeur 1905 surgit, le problème est d’ordre mise en forme.
Détecter et corriger le mauvais format
| Symptôme | Cause | Solution rapide |
|---|---|---|
| La cellule affiche 1905 alors que la barre de formule montre 2024. | Le format “Date → personnalisée : aaaa” est appliqué à la cellule. | Sélectionner la colonne. Passer dans Accueil → Nombre à “Standard” ou “Nombre”. Réajuster, si besoin, la largeur et l’alignement. |
Le résultat de YEAR() se convertit en 01/01/1900 + n jours. | Excel tente d’afficher un entier (2024) comme un numéro de série de date. | Utiliser =TEXT( … ; "aaaa") pour forcer un texte, ou laisser le format “Standard”. |
Trois approches robustes pour éviter toute dérive
1. Formule “nombre pur” + format “Standard”
=YEAR(MAXIFS(J$2:J$46; C$2:C$46; C2))
Adoptez le format “Standard” ou “Nombre” immédiatement ; vous disposez alors d’un entier 2024 exploitable pour d’autres calculs (tri numérique, SIERREUR, MIN, etc.).
2. Formule “texte” pour verrouiller la présentation
=TEXT(MAXIFS(J$2:J$46; C$2:C$46; C2); "aaaa")
Vous obtenez “2024” en tant que chaîne ; impossible de la confondre avec une date. Idéal pour un export CSV à destination d’un ERP ou d’un logiciel de Gantt.
3. Colonne calcul + colonne affichage
Pour la maintenance et la lisibilité, séparez :
- Project End Date : formule
MAXIFS, format Date courte “jj.mm.aaaa” ou ISO 8601. - Project End Year [Num] :
=YEAR([@Project End Date]), format Standard. - Project End Year [Txt] :
=TEXT([@Project End Date];"aaaa"), format Texte.
Aller plus loin : conseils de pro
Valider que les dates sont bien numériques
Des importations CSV/XML peuvent insérer du texte “2025‑12‑31” au lieu d’une vraie date. Pour le vérifier :
=ISNUMBER(J2)
Sinon, transformez avec :
=DATEVALUE(J2)
LUTTER contre la base 1900 / 1904 (macOS)
Sur d’anciens classeurs Mac, l’option “Système de dates 1904” reste cochée. Cela décale tout de quatre ans. Pour synchroniser :
- Fichier → Options → Options avancées → En bas : “Utiliser le système de dates 1904”.
- Désactivez-la pour revenir au standard Windows.
- Vérifiez vos formules
DATE()/N().
Automatiser la correction de format avec Power Query
Power Query (Données → Obtenir & transformer) peut forcer la colonnes Task End Date en type “Date/Heure”, puis ajouter une étape “Colonne dérivée” pour l’année. L’avantage : flux reproductible, zéro macros VBA.
Version dynamique avec LET() et LAMBDA()
=LET(
projID; [@[Project ID]];
fullDate; MAXIFS(Task_End_Date; Project_ID; projID);
YEAR(fullDate)
)
Disponible depuis Microsoft 365, LET évite plusieurs recalculs d’une même expression et clarifie la lecture.
Utiliser un style « Tableau » pour immuniser les références
Convertir votre plage en Tableau (Ctrl + T) renomme les formules et adapte automatiquement les références. Ainsi :
=MAXIFS(Table1[Task End Date]; Table1[Project ID]; [@[Project ID]])
Plus besoin de verrouiller les colonnes avec $ et moins de risque d’erreur lors d’un tri.
Contrôler l’affichage via le format de nombre
Souvenez‑vous : YEAR() n’influence jamais la mise en forme. Tout se passe dans :
- Format de cellule : Ctrl + 1 → Nombre.
- Catégorie : Standard, Nombre ou Personnalisée.
- Typage du Tableau : “Nombre entier” permet des filtres numériques.
Vous pouvez même conserver le résultat brut (2024) et, en parallèle, créer un format personnalisé dans la cellule voisine : aaaa" (Go Live)".
Contrôler la qualité des données avec la Mise en forme conditionnelle
Repérez instantanément les projets qui n’ont pas encore de date de fin :
- Sélectionner la colonne Project End Date.
- Accueil → Mise en forme conditionnelle → Règles de surbrillance des cellules → Égal à… → rester vide.
- Choisir un remplissage rouge pâle.
Dès qu’une MAXIFS retourne #N/A, vous visualisez le manque de données.
FAQ express
Pourquoi 1900 et pas 1899 ?
Le calendrier d’Excel commence au 0 janvier 1900. Un bug historique : 1900 est traité comme année bissextile, alors qu’elle ne l’est pas. Ce choix a été conservé pour compatibilité avec Lotus 1‑2‑3.
Le texte “2024” peut‑il redevenir une date ?
Oui, via =DATEVALUE("31/12/"&A2) si A2 contient 2024. Mais vous perdrez le jour exact tant que vous ne l’enregistrez pas séparément.
Puis‑je concaténer l’année dans un intitulé ?
Bien sûr : ="Pré‑Go Live "&YEAR(B2) affichera “Pré‑Go Live 2024”.
Bons réflexes à adopter
- Un calcul, un affichage : séparez les colonnes.
- Nommez les plages clairement (ex. Task_End_Date).
- Normalisez les données dès l’import : date ISO 8601, fuseau UTC.
- Documentez vos formules avec Notes de cellule (Shift + F2).
- Testez systématiquement le format de sortie avant d’envoyer un fichier au directeur financier !
Conclusion
La fameuse “année 1905” n’est pas un bug de YEAR(), mais une question de format. En choisissant le bon type de cellule ou en transformant la sortie en texte, vous sécurisez votre reporting et évitez des semaines d’incompréhension. Appliquez ces astuces, et vos tableaux croisés, graphiques et exports Power BI afficheront enfin la bonne année !