Tenir les soldes de congés d'une équipe à jour, c'est une de ces tâches invisibles où l'on recompte les jours pris à la main sans jamais être tout à fait sûr du total. Dans cet exercice, on va voir ensemble comment faire porter ce décompte par un fichier qui se met à jour dès qu'une absence est saisie.
L'objectif est de relier une fiche collaborateur à un journal d'absences pour que chaque solde se calcule tout seul, et de déclencher une alerte quand la fin de période approche. Une fois ce réflexe de décompte par critères et de calcul d'ancienneté acquis, il ressert sur tout suivi RH où l'on compte des événements datés par personne !
Ce que tu vas construire
Calculer automatiquement l'ancienneté de chaque collaborateur en années complètes avec DATEDIF.- Compter les jours d'absence par type (CP, RTT, maladie) et par collaborateur sur la période en cours.
- Afficher en temps réel le solde de congés restants pour chaque membre de l'équipe.
- Déclencher une alerte automatique quand la fin de période approche et que le solde est encore élevé.
- Distinguer les différents types d'absences pour ne pas mélanger les compteurs CP, RTT et maladie.
À connaître avant de commencer
- Savoir saisir des formules avec des références inter-onglets (Feuille!Colonne).
Voici les données de départ de cet exercice. Copie-les ou télécharge le fichier Excel, puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Nom | Date d'entrée | Poste | Droit CP | Droit RTT |
| 2 | Dupont Marie | 15/09/2020 | Comptable | 27 | 10 |
| 3 | Martin Paul | 03/01/2024 | Dev | 25 | 10 |
| 4 | Bernard Sophie | 12/06/2018 | RH | 28 | 10 |
| 5 | Lefèvre Thomas | 01/03/2022 | Commercial | 26 | 10 |
| 6 | Moreau Julie | 15/07/2019 | Marketing | 28 | 10 |
| 7 | Petit Romain | 10/11/2023 | Dev | 25 | 10 |
| 8 | Garcia Laura | 22/04/2017 | Comptable | 29 | 10 |
| 9 | Dubois Antoine | 08/09/2021 | Logistique | 26 | 10 |
| 10 | Rousseau Camille | 14/02/2025 | Assistante | 25 | 10 |
| 11 | Lambert Hugo | 30/06/2016 | Chef de projet | 30 | 10 |
Exercice guidé
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Utilise DATEDIF pour calculer le nombre d'années complètes entre la date d'entrée et aujourd'hui. Certaines conventions collectives accordent des jours supplémentaires après 5, 10 ou 15 ans. La formule te donne l'ancienneté automatiquement.
=DATEDIF(B2;AUJOURDHUI();"Y")Résultat attendu : Le nombre d'années d'ancienneté (ex: 5 pour quelqu'un entré le 15/09/2020).
Attention
- DATEDIF est une fonction non documentée d'Excel. Elle fonctionne parfaitement mais n'apparaît pas dans l'auto-complétion. Tape-la manuellement.
Astuces pour aller plus loin
Standardise les types d'absence avec une liste déroulante
NB.SI.ENS est sensible à la casse et aux espaces : "cp" et "CP" sont deux valeurs différentes. Crée une liste déroulante dans la colonne Type (Données > Validation des données) pour forcer la saisie des mêmes valeurs. Tu évites les doublons silencieux qui faussent les compteurs.
Adapte les dates d'alerte à ta convention collective
Certaines conventions clôturent la période de référence au 31 mai, d'autres au 31 décembre ou au 31 mars. Dans la formule d'alerte, remplace DATE(2026;5;31) par la date de clôture de ta convention. L'alerte se déclenchera au bon moment.
Ajoute une colonne Report N-1
Si tes salariés peuvent reporter des jours non pris, ajoute une colonne Report dans la fiche collaborateur. Le droit total devient droit annuel + report. Remets le report à zéro une fois par an à la clôture, après avoir validé les jours reportés avec les managers.
Questions fréquentes
Crée deux onglets : une fiche collaborateur avec le droit annuel et une feuille journal avec une ligne par absence. NB.SI.ENS compte les jours pris par type et par personne. Tu soustrais les jours pris du droit annuel pour obtenir le solde restant en temps réel.
=DATEDIF(date_entrée;AUJOURDHUI();"Y") donne le nombre d'années complètes. C'est une fonction non documentée d'Excel : elle fonctionne parfaitement mais n'apparaît pas dans l'auto-complétion, tape-la manuellement. Pour les jours d'ancienneté : =SI(ancienneté>=10;2;SI(ancienneté>=5;1;0)).
Utilise NB.SI.ENS avec trois critères : le nom du collaborateur, le type d'absence ("CP") et la plage de dates de la période. =NB.SI.ENS(Absences!B:B;A2;Absences!C:C;"CP";Absences!A:A;">="&DATE(2026;6;1)). Adapte la date de début à ta période de référence.
=SI(ET(AUJOURDHUI()>DATE(2026;4;1);solde>10);"Attention : "&solde&" jours à poser";""). La fonction ET vérifie deux conditions en même temps : on est après le 1er avril ET il reste plus de 10 jours. Adapte les dates et le seuil à ta convention collective.
Saisis une ligne par jour d'absence, ou utilise NB.JOURS.OUVRES(date_debut;date_fin) pour compter les jours ouvrés d'une période continue. La deuxième approche est plus rapide mais moins souple si tu as des demi-journées.
Télécharge le fichier via le bouton ci-dessus. Change les noms, dates d'entrée et droits dans la fiche collaborateur. Mets à jour les dates de la période de référence dans les formules NB.SI.ENS et dans la formule d'alerte pour coller à ta convention collective.
3 exercices similaires au suivi des congés et absences
Bulletin de paie simplifié : du brut au net
Pars d'un salaire brut, calcule chaque cotisation salariale ligne par ligne avec ARRONDI, totalise-les avec SOMME et déduis le salaire net en une seule passe.
Voir l'exercice
Suivi des notes de frais
Mets en place un suivi des notes de frais sur Excel : total par catégorie, contrôle des plafonds et total remboursable, à partir d'un relevé de dépenses.
Voir l'exercice
Préparer une base de contacts pour un publipostage
Nettoie une base de contacts à la casse incohérente avec NOMPROPRE, puis construis les champs de fusion (formule d'appel, identifiant) prêts à être repris dans un publipostage.
Voir l'exercice
Envie de t'entraîner plus ?
Des dizaines de cas pratiques Excel corrigés pour progresser pour de vrai.
Voir tous les exercices