Excel : créer un tableau de suivi des échéances de contrats avec alertes et renouvellement automatique

Difficulté

Intermédiaire

Le plus simple est de calculer automatiquement trois dates : l’échéance, la date limite de résiliation et la prochaine échéance en cas de renouvellement automatique. Excel le fait bien avec EDATE, FIN.MOIS, SI et une mise en forme conditionnelle.

Ce guide montre un modèle de tableau réutilisable pour suivre vos contrats et voir d’un coup d’œil ceux qui exigent une action avant le préavis.

Prérequis

Vous avez besoin d’Excel avec les fonctions françaises EDATE, FIN.MOIS et SI. Vérifiez aussi que vos colonnes de dates sont bien au format Date, sinon les calculs et alertes seront faux.

Matériel nécessaire

• Excel
• Liste des contrats à suivre

Étapes

1. 1

   Créer la structure du tableau

   

   Saisissez sur la ligne d’en-tête les colonnes suivantes : Contrat, Date de début, Durée (mois), Préavis (mois), Renouvellement auto, Échéance, Date limite résiliation, Prochaine échéance et Alerte.

   Transformez la plage en tableau Excel avec Ctrl + T. Cela facilite la recopie des formules et la mise en forme.

2. 2

   Saisir les données de base

   Remplissez pour chaque contrat la date de début, la durée en mois, le préavis en mois et indiquez Oui ou Non dans la colonne de renouvellement automatique.

   Utilisez des mois entiers pour la durée et le préavis. Si un contrat fonctionne en jours, ce modèle devra être adapté.

3. 3

   Calculer l’échéance du contrat

   Dans la colonne Échéance, entrez une formule du type =EDATE(B2;C2) si B2 contient la date de début et C2 la durée en mois.

   Cette formule ajoute un nombre de mois à la date de départ. Recopiez-la sur toute la colonne.

4. 4

   Calculer la date limite de résiliation

   Dans la colonne Date limite résiliation, saisissez une formule du type =EDATE(F2;-D2) si F2 contient l’échéance et D2 le préavis en mois.

   Vous obtenez la dernière date à surveiller pour résilier dans les temps. C’est cette date qui servira aux alertes.

5. 5

   Ajuster la date à la fin du mois si le contrat le demande

   Si votre contrat prévoit une échéance en fin de mois, remplacez le calcul simple par une formule comme =FIN.MOIS(EDATE(B2;C2)-1;0). Faites la même logique pour la date limite si elle doit aussi tomber en fin de mois.

   N’utilisez FIN.MOIS que si cette règle existe réellement dans le contrat. Sinon, gardez EDATE seul.

6. 6

   Calculer la prochaine échéance en cas de renouvellement automatique

   

   Dans la colonne Prochaine échéance, vous pouvez utiliser une formule du type =SI(E2="Oui";EDATE(F2;C2);""). Elle ajoute une nouvelle période seulement si le contrat se renouvelle automatiquement.

   Si vous voulez afficher l’échéance courante pour les contrats non renouvelables, remplacez la chaîne vide par F2.

7. 7

   Afficher un statut d’alerte avant la date limite

   Dans la colonne Alerte, utilisez une formule basée sur la date du jour, par exemple : =SI(G2<AUJOURDHUI();"Délai dépassé";SI(G2<=AUJOURDHUI()+30;"À traiter sous 30 jours";"RAS")).

   Cette version signale les contrats dont la date limite est déjà passée et ceux à traiter dans les 30 jours. Ajustez le seuil selon votre besoin interne.

8. 8

   Appliquer une mise en forme conditionnelle

   

   Sélectionnez la colonne Alerte ou tout le tableau, puis créez trois règles de mise en forme conditionnelle basées sur le texte ou une formule. Par exemple : rouge pour Délai dépassé, orange pour À traiter sous 30 jours et vert pour RAS.

   Si vous préférez des formules, créez les règles à partir de la colonne Date limite résiliation avec G2<AUJOURDHUI() puis ET(G2>=AUJOURDHUI();G2<=AUJOURDHUI()+30).

9. 9

   Tester le tableau avec un exemple concret

   Prenez un contrat démarrant le 15/03/2024, d’une durée de 12 mois, avec un préavis de 2 mois et renouvellement automatique Oui. Avec =EDATE(B2;C2), l’échéance devient le 15/03/2025, puis la date limite de résiliation avec =EDATE(F2;-D2) devient le 15/01/2025.

   La prochaine échéance calculée par =SI(E2="Oui";EDATE(F2;C2);"") donnera le 15/03/2026. Si la date du jour est dans les 30 jours avant le 15/01/2025, la colonne d’alerte passera automatiquement en surveillance.

Erreurs fréquentes & dépannage

Confondre échéance et date limite de résiliation est l’erreur la plus fréquente. L’action doit souvent être faite avant l’échéance, selon le préavis.

Utiliser FIN.MOIS partout est aussi risqué. Cette fonction ne convient que si la clause contractuelle prévoit un traitement en fin de mois.

Saisir Oui/Non de manière incohérente peut casser la formule de renouvellement. Une validation de données limite ce problème.

Astuces & pour aller plus loin

Ajoutez un filtre sur la colonne Alerte pour n’afficher que les contrats à traiter. Vous pouvez aussi trier par Date limite résiliation pour prioriser les actions.

Si votre suivi devient volumineux, ajoutez une colonne Responsable ou Fournisseur. Le tableau reste simple mais devient exploitable en équipe.

Questions fréquentes

Faut-il utiliser EDATE ou FIN.MOIS ?

Utilisez EDATE pour une date anniversaire classique, par exemple du 15 mars au 15 mars suivant. Utilisez FIN.MOIS seulement si le contrat prévoit explicitement une échéance ou un calcul en fin de mois.

Comment changer le délai d’alerte de 30 jours à 60 jours ?

Remplacez simplement AUJOURDHUI()+30 par AUJOURDHUI()+60 dans la formule de la colonne Alerte. Vous pouvez aussi stocker ce nombre dans une cellule dédiée pour l’ajuster sans modifier la formule.

Pourquoi mes formules ne fonctionnent-elles pas ?

Vérifiez d’abord que les cellules de date contiennent de vraies dates et non du texte. Vérifiez ensuite les séparateurs de formule de votre Excel, souvent ; en français.

Conclusion

Pour un suivi fiable, basez vos alertes sur la date limite de résiliation, pas seulement sur l’échéance. Commencez avec le modèle simple de ce guide, puis adaptez uniquement les règles réellement prévues par vos contrats.