Qui n’a pas compté combien de jours de congés il lui reste ? Grâce à ce tutoriel, vous pourrez suivre vos congés et vos RTT (bonus) grâce à un fichier Excel.
Pré-requis
Concevoir un tel planning risque de prendre beaucoup de temps. Or, nous avons déjà réaliser un modèle de calendrier dans un précédent article.
Je vous propose de télécharger le fichier qui a été utilisé dans notre article.
Période de calcul des congés payés
Légalement, la période de prise en compte et du 1er juin de l’année précédente au 31 mai de l’année en-cours.
Toutefois, les pouvoirs publics autorisent d’adapter cette période par par dispositions conventionnelles, convention collective, accord collectif d’entreprise.
On va en tenir compte pour notre planning afin de s’adapter à tous.
1er mois de la période
Plutôt que d’avoir une date en dur, nous allons mettre en place une liste déroulante pour choisir notre 1er mois.
La 1ère chose est de créer une base recensant tous les mois de l’année.
Dans notre feuille base, saisissons les mois de l’année dans la colonne D. Or, pour être utilisable dans notre calendrier, il faut qu’elle soit au format date (JJ/MM/AAAA) en sachant qu’on possède l’année en cellule A1 dans la feuille calendar.
En D1, saisissez la formule :
=DATE(calendar!A1;1;1)
EN D2, on va mettre la formule :
=MOIS.DECALER(D1;1)
On recopie ensuite sur les lignes suivantes pour atteindre décembre.
Maintenant qu’on a notre base, on peut mettre en place une liste déroulante en D1 sur la feuille calendar.
Comme source, saisissez la formule :
=base!$D$1:$D$12
Modification des jours fériés
Quand votre période n’est pas une année civile, une partie du planning sera dans l’année suivante. Du coup, votre base de jours fériés n’est plus adaptée.
On aurait pu modifier les formules de calcul pour trouver les jours fériés. Or, c’est extrêmement compliqué pour Pâques.
Après des heures de recherche, miracle ! J’ai trouvé une solution simple à mettre en oeuvre. Il suffit que dans notre base, on calcule les jours fériés de l’année en A1 sur la feuille calendar mais aussi l’année suivante.
Nous avons déjà les congés pour l’année N. Calculons pour l’année N+1.
| Jour férié | formule |
|---|---|
|
Jour An |
=DATE(calendar!$A$1+1;1;1) |
|
Pâques |
=PLANCHER(JOUR(MINUTE((calendar!$A$1+1)/38)/2+56)&"/5/"&calendar!$A$1+1;7)-34 |
|
Lundi de Pâques |
=$B$16+1 |
|
Fête du travail |
=DATE(calendar!$A$1+1;5;1) |
|
Armistice 39/45 |
=DATE(calendar!$A$1+1;5;8) |
|
Ascension |
=$B$16+39 |
|
Pentecôte |
=$B$16+49 |
|
Lundi de Pentecôte |
=$B$16+50 |
|
Fête nationale |
=DATE(calendar!$A$1+1;7;14) |
|
Assomption |
=DATE(calendar!$A$1+1;8;15) |
|
Toussaint |
=DATE(calendar!$A$1+1;11;1) |
|
Armistice 14/18 |
=DATE(calendar!$A$1+1;11;11) |
|
Noël |
=DATE(calendar!$A$1+1;12;25) |
J’en ai profité pour changer le nom du jour férié en concaténant avec l’année.
Événements
Base
Maintenant, on a besoin de définir les différents événements. Pour notre article, nous avons les congés payés et les RTT.
Dans la feuille base, créons une nouvelle liste avec ces 2 options sur la colonne E.
Feuille de saisie
La prochaine étape est de créer une nouvelle feuille qu’on renomme saisies.
Elle va nous servir à recenser tous les jours pris et servira de source pour l’affichage dans le planning.
Afin de tester notre fichier, on va rentrer quelques exemples comme ci-dessous. Pour simplifier nos futures formules de calcul, on transforme en tableau structuré qu’on renomme Tabl_saisies.
Pour éviter des erreurs de saisies pour le type, créez une liste déroulante, maintenant vous savez comment faire ^^
Affichage dans le planning
On approche de la fin. Pour chaque jour de notre planning, notre formule est actuellement :
=SI(C4="";"";SI(NB.SI(Base_jrferies[Date];C4);"Férié";SI(OU(JOURSEM(C4;2)=6;JOURSEM(C4;2)=7);"W-E";"")))
Pour trouver si le jour dans le planning correspond à une date dans notre tableau de saisies, on va utiliser la fonction FILTRE.
FILTRE(Tabl_saisies[Type];*(C4>=Tabl_saisies[DateDébut])*(C4<=Tabl_saisies[DateFin]);"")
On obtient la formule définitive :
=SI(C4="";"";SI(NB.SI(Base_jrferies[Date];C4);"Férié";SI(OU(JOURSEM(C4;2)=6;JOURSEM(C4;2)=7);"W-E";FILTRE(Tabl_saisies[Type];(C4>=Tabl_saisies[DateDébut])*(C4<=Tabl_saisies[DateFin]);""))))
Pour résumer la formule, on vérifie d’abord si c’est un jour férié, ensuite si c’est un samedi/dimanche et enfin s’il apparaît dans nos saisies (RTT ou congé).
Mise en forme conditionnelle
Pour mettre en évidence nos jours de repose, on va utiliser la mise en forme conditionnelle en définissant une couleur de remplissage distincte entre congés et RTT.
Comme nous avons créé plusieurs règles de mise en forme conditionnelle, vérifions si l’ordre de priorité est respecté :
- Férié
- Week-end
- Congé/RTT
Pour cela, dans le ruban Accueil, cliquez sur Mise en forme conditionnelle puis Gérer les règles…
Dans la nouvelle fenêtre, sélectionnez Dans cette feuille de calcul dans la liste déroulante.
Si ce n’est pas dans le bon ordre, utilisez les flèches directionnelles. Une fois les règles rangées, cliquez sur OK.
Synthèse
=NB.SI(D4:Z34;"Congé")Pour les RTT pris en AC14, on utilise la même méthode
=NB.SI(D4:Z34;"RTT")Les soldes sont la simple soustraction entre jours possible+reliquat-jours pris
Vous pouvez égayer cette synthèse en appliquant une mise en forme conditionnelle selon le nombre de jours restants (vert si positif et rouge si négatif).
Fichier support
Nous en avons fini avec ce tutoriel. Si vous préférez avoir l’outil prêt à l’emploi ou avez des difficultés à réaliser vous-même le fichier, n’hésitez pas à télécharger ma version.
