Calendrier de suivi des congés, absences, jours fériés, vacances scolaires

🛠️ Introduction

Vous souhaitez piloter le suivi des congés, absences, jours fériés, vacances scolaires, et calculer les droits acquis, restants, voire simuler les congés à venir ? Ce fichier Google Sheets le fait pour chaque membre du personnel via :

• Une feuille Congé Tous & Paramètres (centralise tous les congés et réglages)

• Une feuille dédiée par employé, avec leur agenda et calculs personnalisés

---

📄 Feuille centrale » Congé Tous & Paramètres

Structure des blocs

1. Congés classiques (colonnes B→H) :

   Matricule | Nom | Date | NB JOUR CONGE | Mois | Jour | Année

Congé

utilisez =FILTER(...) pour afficher les congés d'un employé pour sa feuille personnelle (

AY9):

Excel
=SIERREUR( FILTER( 'Congé tous & Parametre'!B$1:H; 'Congé tous & Parametre'!B$1:B = $H$1 ); "")

Absences (colonnes K→R), même logique.

Congés spéciaux / Congés exceptionnels (colonnes U→AB)

Jours fériés (colonnes AF→AH)

Fêtes religieuses (colonnes AK→AO)

Vacances scolaires (colonnes AR→AU)

Ces ensembles doivent être maintenus et utilisés pour colorer automatiquement l’agenda individuel.

📆 Feuille personnelle (agenda + calcul)

Chaque employé a sa propre feuille structurée ainsi :

Formules utiles

• Filtrer les événements pour un employé
  Par exemple, afficher congés dans son agenda :

Excel
=FILTER('Congé tous & Parametre'!$B:$H; 'Congé tous & Parametre'!$B:$B = $H$1)

• Mettre en forme les jours pris
  Dans le calendrier (colonnes R→AP), une mise en forme conditionnelle :

  fait ressortir les jours de congé en jaune.

Excel
=NB.SI($BA$9:$BA$2000; R9)>0

• Colorier les jours fériés
  Même principe avec la plage $CG$2:$CG$2000, format rouge.

CALCULE 

Entête (A1…L3)

• A2 : Année de référence pour le calcul (ex : 2024)

• B2 : Jours acquis à l’entrée (ex : 0)

• B3 : Jours acquis la première année (ex : 9)

• F1 : Date d’embauche, H1 : matricule, J1 : nom & matricule affiché

Table de calcul (lignes 8 à 38)

Colonnes A→M déroulent l’historique annuel :

Année début de Calcule B1 Reste Jour Avant Calcule B2 NB jour Aqui 1ère année B3

date de recrutement F1 / matricule H1 / nom J1 / nom et matricule K1

de A 8 à M 38  il y a 

• B9 à B38 : =$F$1 dates d’embauche (glissées)

• C9 = 0, +1 ensuite jusqu’à 29

• D9 = DATE(ANNÉE(B9);12;31) pour chaque année de l’intervalle

• E9 = ANNÉE(D9) (glissées)

• F9 = D9 − B9 (jours parcourus)

• G9 = (D9 − B9)/365,25 → Nombre d'année

• H9 = formule … qui attribue jours en fonction de l’ancienneté (0–1, 1–5 ans, etc.)

Excel
=SI(ET(C9=0; B3=""); ARRONDI(1,5 * (F9 / 30); 0); SI(ET(C9=0; B3<>""); $B$3; SI(ET(C9>0; G9<>""; G9<5); 18; SI(ET(C9>0; G9>=5; G9<10); 19,5; SI(ET(C9>0; G9>=10; G9<15); 21; SI(ET(C9>0; G9>=15; G9<20); 22,5; SI(ET(C9>0; G9>=20; G9<25); 24; SI(ET(C9>0; G9>=25; G9<30); 25,5; SI(ET(C9>0; G9>=30; G9<35); 27; SI(ET(C9>0; G9>=35); 30; ""))))))))))

• I9 = H9 pour la 1re année, sinon accumulateur

Excel
=SI(ET(ANNEE(D10)<>"";ANNEE(D10)<=ANNEE(AUJOURDHUI()));H10+L9;H10)

• J9 = reste reporté =SI(E9=ANNEE($F$1);"";L8)

• K9 = congés pris cette année (filtrage par année)

Excel
=SI(E9<$B$1;""; SOMME.SI.ENS($BB:$BB; $BE:$BE; E9))

• L9 = I9 − (K9 + M9) reste

• M9 = j. rémunérés si utilisés

Calendrier (colonnes R→AP)

• T2 : Jr. Pris = SOMME.SI.ENS(...) pour total pris

• W2 : Reste via RECHERCHEV année : =SI($R$3<ANNEE($F$1);0;RECHERCHEV($R$3;E:L;8;FAUX()))

• T5 : acquis 

• X5 : crédit

• R6 : NB.J : Congé Rémunéré’, basé sur W6 : =SI(W6<>"";"NB.J : Congé Rémunéré";"")

🧩 Intégration et synchronisation

• Importer les congés d’une autre sheet (Congé tous & Parametre)

Pour les congé dans la colonne AY9 Utilisez :

=FILTER('Congé tous & Parametre'!$B$1:$H; 'Congé tous & Parametre'!$B$1:$B=$H$1) 

Pour les jour ferrier  Utilisez :

Dans la colonne CD 7 =QUERY('Congé tous & Parametre'!$AF$2:$AH;"select *") 

ET  dans CC7 : année CC8=SI(CE8<>"";$R$3;"") tirer glisser 

Et dans CG7 : CG8 Date agenda =SI(CE8<>"";DATE(CC8;CD8;CE8);"") tirer glisser 

• Automatiser les mises en forme conditionnelles pour colorer calendrier en jaune/congé ou rouge/jour férié.

  Congé Sélectionner l'agenda et dans règle mise en forme conditionnel

       =NB.SI($BA$9:$BA$2000; R9)  > 0 et pour couleur j'ai mis jaune 

jour ferrier Sélectionner l'agenda et dans règle mise en forme conditionnel 

=NB.SI($CG$2:$CG$2000; R9) > 0 cloueur rouge

---

📝 Conclusion

• Ce fichier permet de centraliser, calculer, suivre et simuler les congés des employés.

• Chaque composant (formule, agenda, feuille individuelle) s’intègre pour créer un outil complet.

• Vous pouvez l’adapter : formules de calcul à modifier pour les conditions locales, zones à colorer, import/export.

---

✅ À faire maintenant

• Tester avec plusieurs employés

• Mettre à jour les listes de jours fériés et vacances scolaires

• Ajuster pour règles locales (ex : calcul jours acquis après x années)

• Option : automatiser via Apps Script  pour le plus et le moins

Script

function ajoutun() {   var spreadsheet = SpreadsheetApp.getActive();   spreadsheet.getRange('AG3:AM3').activate();   var plusun = spreadsheet.getRange("AG3:AM3").getValue();     spreadsheet.getCurrentCell().setValue(plusun + 1);   }; function moinun() {   var spreadsheet = SpreadsheetApp.getActive();   spreadsheet.getRange('AG3:AM3').activate();   var plusun = spreadsheet.getRange("AG3:AM3").getValue();     spreadsheet.getCurrentCell().setValue(plusun -1);   };

---

Avec ce modèle, vous avez une base solide pour gérer efficacement les congés du personnel en toute transparence.