Troisième et avant-dernière vidéo consacrée au calendrier dynamique sur Excel créé il y a bientôt trois ans.
Au fil de son utilisation à titre personnel et des commentaires sur la chaîne YouTube et des mails reçus, j’ai décidé de vous proposer d’apporter des améliorations au calendrier.
Récupération du calendrier dynamique
Afin d’éviter une perte de temps, si vous découvrez cet article sans avoir lu les précédents, je vous invite à lire les articles :
Pour que tous soient sur un pied d’égalité, on va commencer à partir du fichier disponible à la fin du 2nd article.
Libre à vous d’utiliser votre propre calendrier si vous l’avez personnalisé.
Gestion des types d'événements
Sur notre version actuelle, nous avons deux types d’événements :
- Congé
- RTT
J’avoue que le choix est limité. Moi-même, j’en utilise d’autres. De votre côté, vous en avez probablement.
Nouveaux événements
On va en ajouter qui pourraient concerner une majorité d’entre vous :
- Arrêt (pour les arrêts maladie)
- Télétravail
- Pour cela, rendez-vous dans la feuille base.
- Complétez la liste avec les deux nouvelles options.
Libre à vous d’en rajouter en fonction de vos besoins individuels.
Mise en forme conditionnelle
On va désormais mettre en forme les cellules du planning en fonction de leur valeur :
- Férié : déjà configuré en texte blanc sur fond gris foncé
- W-E : déjà configuré en texte gris sur fond gris gris
- Congé : déjà configuré en texte noir sur fond vert clair
- RTT : déjà configuré en texte noir sur fond bleu clair
J’ai choisi les couleurs suivantes pour les nouvelles options :
- Télétravail : texte violet sur fond rose clair
- Arrêt : texte rouge foncé sur fond rouge clair
Par ailleurs, je vais modifier les couleurs de Férié en texte noir sur fond orange clair. Je fais cela car je vais me servir du texte blanc sur fond gris foncé pour nouvelle option que l’on verra dans la prochaine partie de l’article : les jours de repos.
Je fais utiliser pour cela la règle Égal à…
Si c’est pour vous la première fois, je vous invite à lire l’article consacré à la mise en forme conditionnelle
Définir ses jours de repos
La première amélioration fait suite à des demandes d’habitués de May6 pour gérer les événements les week-end car il sont susceptibles d’y travailler.
Ainsi, on va pouvoir choisir quels jours de la semaine nous sommes en repos de deux manières :
- choisir les jours de semaine s’il sont fixes dans l’année (comme les jours ouvrés)
- saisir manuellement les dates de repos si elles sont variables.
Avec cette amélioration, les cas les plus courants seront pris en compte :
- travail en jours ouvrés traditionnel
- travail le week-end
- contrat de travail ou convention collective prévoit un planning variable
Toutefois, les situations de temps partiel, rotation d’équipe (3×8), horaires décalés ne peuvent être appliquées car trop compliqué à mettre en place dans un calendrier dynamique sans créer des conflits avec les autres fonctionnalités (suivi des congés et RTT et une fonctionnalité que je vous réserve dans les mois à venir).
Commençons.
Saisie manuelle des jours de repos
Cette feuille va nous saisir les jours de repos. Elle contiendra tout d’abord un tableau d’une colonne où l’on va saisir nos dates de repos.
- En bas de la fenêtre Excel, cliquez sur le +
- Double-cliquez sur le nom de la nouvelle feuille et renommez la repos
- En A1, saisir Dates repos
- De A2 à A4, saisissez des dates à votre convenance
5. Sélectionnez une des cellule sde la plage A1:A4 puis faites Ctrl+L pour le transformer en tableau structuré.
6. Vérifiez que la plage est correcte puis cochez Mon tableau comporte des en-têtes
Puis, cliquez sur OK
7. Dans l’onglet Création du tableau du ruban, modifiez le nom du tableau en Tabl_repos
Jours de repos fixes
Cette fois-ci, on va s’occuper des situations pour lesquelles les jours de repos sont fixes tout au long de l’année.
On va s’en occuper à l’intérieur de la feuille repos
- En C1, écrivez OU et modifier les propriétés de police pour qu’il apparaisse bien (gras, taille de police, etc…).
C’est pour faire une séparation entre notre tableau de dates en colonne A et notre tableau des jours de semaine à partir de la colonne D. - De D1 à D7 on va afficher les jours de la semaine. C’est uniquement pour avoir des en-têtes de ligne. Vous pouvez saisir les noms manuellement ou faire comme moi : en D1 je mets une date dont je suis sûr qu’il s’agit d’un lundi (ex : 24/11/2025). Je recopie jusqu’à la cellule D7 qui doit afficher le 30/11/2025.
- Je sélectionne la plage de cellules D1: D7 ou l’ensemble de la colonne D puis je change le format de cellule en jjjj.
- De E1 à E7, saisissez les nombres 1 à 7
- manuellement
- ou avec une fonction SEQUENCE
- ou saisir 1 en E1 puis maintenir la touche Ctrl et utiliser la poignée de recopie en E1 jusqu’en E7
- La plage de cellules F1:F7 sert à sélectionner les jours de semaine. Pour simplifier au maximum la formule de notre calendrier, j’ai opté pou le système des celles vides/remplies : si la cellule, Excel considère que c’est un jour de repos. Vous pouvez :
- soit saisir la cellule manuellement
- soit mettre en place un liste déroulante
A titre, personnel, je saisirai manuellement d’un x.
Mise à jour du planning
Il est temps d’appliquer nos améliorations sur le planning. On va d’abord commencer, par notre formule de calcul à l’intérieur du calendrier.
Actuellement, elle est :
=SI(C4="";"";SI(NB.SI(base!$B$2:$B$27;C4);"Férié";SI(OU(JOURSEM(C4;2)=6;JOURSEM(C4;2)=7);"W-E";FILTRE(saisies!$C$2:$C$24;(C4>=saisies!$A$2:$A$24)*(C4<=saisies!$B$2:$B$24);""))))
On fonctionne avec le système des SI imbriqués. Je vous préviens d’avance que la formule finale est 2x plus longue pour tenir compte des améliorations.
Cela complexifie la lecture, c’est pour cela qu’on va passer à SI.CONDITIONS qui offre une meilleure visibilité de notre formule.
Avant toute chose, il faut réfléchir à l’ordre de priorité de nos conditions car cela va impacter fortement notre calendrier.
Ordre d'affichage
Vous avez pu constater que la première version du calendrier dynamique posait problème pour les week-end, d’où la création des repos. De plus, je prépare pour une version future avec une nouvelle amélioration.
Commençons à poser nos conditions par ordre de priorité pour l’affichage :
- Quoiqu’il arrive les jours fériés passe en premier
- Ensuite, viennent nos jours de repos qu’ils soient du lundi au vendredi ou durant le week-end
C’est une double condition : ceux qui ont des jours de repos variables et ceux qui ont des fixes. - Passons ensuite aux événements (congés, RTT, arrêt, etc…). Vu qu’on ne les a pas encore cités, ils ont la priorité sur les week-end. Cela n’a plus d’importance car ils ne seront pas affichés pendant les jours de repos (qui remplacent au final les W-E).
- On envient donc aux fameux week-end. Pourquoi je les laisse vu qu’ils n’ont plus de réelle utilité ? Je veux quand même les apercevoir visuellement dans le calendrier. Mais libre à vous d’enlever cette condition.
- Pour le reste, on affiche rien
Modification de la formule
On va s’appuyer sur les 5 conditions ci-dessus pour rédiger notre formule.
Avant tout, je vais rajouter une condition en premier lieu expliqué dans le tableau ci-dessous :
| Test Logique | Valeur si Vrai | Condition/Explication |
| C4="" | "" | Corrige bug des fins de mois |
| NB.SI(Base_jrferies[Date];C4) | "Férié" | 1ere condition : afficher les jours fériés |
| NB.SI(Tabl_repos[Dates repos];C4) | "Repos" | 2e condition : afficher les jours de repos variables |
| INDEX(repos!$F$2:$F$8;EQUIV(JOURSEM(Y4;2); repos!$E$2:$E$8;0))<>"" | "Repos" | 2ème condition : afficher les jours de repos variables |
| NB.SI.ENS(Tabl_saisies[DateDébut];"<="&C4;Tabl_saisies[DateFin];">="&C4) | FILTRE(Tabl_saisies[TypeEvt];(C4>=Tabl_saisies[DateDébut])*(C4<=Tabl_saisies[DateFin])) | 3e condition : afficher les événements |
| JOURSEM(C4;2)>=6 | "W-E" | 4e condition : afficher les week-end |
| VRAI | "" | 5e condition : ne rien afficher pour le reste |
Explication de la fonction INDEX :
- JOURSEM(C4;2) : calcule le jour de semaine en C4.
Il renvoie un chiffre entre 1 et 7 - EQUIV(JOURSEM(C4;2); repos!$E$2:$E$8; 0) : recherche le n° de jour dans la colonne E de la feuille repos.
Si trouvé, il renvoie la position de ligne sinon il renvoie l’erreur #N/A - INDEX(repos!$F$2:$F$8 ; …) : cherche la valeur correspondante dans la colonne F sur la ligne trouvée par EQUIV.
- …<> » » : Si la cellule contient quelque chose, cela renvoie VRAI.
Si elle est vide, renvoie FAUX.
Maintenant qu’on a nos conditions, on peut finaliser notre formule :
=SI.CONDITIONS(Y4="";"";NB.SI(Base_jrferies[Date];Y4);"Férié";NB.SI(Tabl_repos[Dates repos];Y4);"Repos";INDEX(repos!$F$2:$F$8;EQUIV(JOURSEM(Y4;2); repos!$E$2:$E$8;0))<>"";"Repos";NB.SI.ENS(Tabl_saisies[DateDébut];"<="&Y4;Tabl_saisies[DateFin];">="&Y4);FILTRE(Tabl_saisies[TypeEvt];(Y4>=Tabl_saisies[DateDébut])*(Y4<=Tabl_saisies[DateFin]));JOURSEM(Y4;2)>=6;"W-E";VRAI;"")
Recopier là sur les autres cellules du tableau
Dorénavant, notre tableau dynamique gère les événements pendant les week-end et les jours de repos où qu’ils soient.
Autres améliorations
Dans cette dernière partie, on va peaufiner notre calendrier.
Sélection du 1er mois
Tout d’abord, je vais déplacer la liste déroulante de sélection de 1er mois de la période pour deux raisons :
- préférene personnelle
- normalisation avec la version présentée sur la chaîne YouTube
Vous êtes libre de conserver l’ancienne version. Mais sachez que notre version finale et les versions futures auront cette modification.
- En A4, je crée la liste déroulante. Le plus simple est de copier-Coller D1 en A4.
- En D1, je supprime la validation de données (Autoriser tout) puis je saisis la formule =DATE(A1;MOIS(A4);1)
Raccourcis des feuilles
Comme pour la modification précédente, elle est facultative.
Elle consiste à ajouter des raccourcis des autres feuilles sur notre feuille calendrier.
C’est juste du gri-gri car cela revient au même que de cliquer sur les noms des feuilles en bas de fenêtre Excel.
Je ne vais pas dévoiler comment je procède. Cela fera des déçus, mais rassurez vous. Cette manipulation fera l’objet d’un prochain article.
Télécharger le fichier
Je rappelle que c’était un projet personnel pour obtenir un calendrier dynamique, utilisable sur plusieurs années, qui permet de déterminer les congés à prendre avant la fin de période prévue (mai ou décembre).
J’espère qu’il vous soit profitable. N’hésitez pas à me contacter si vous souhaitez proposer des améliorations.
