Calculer les jours fériés en fonction de l'année

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Calculs automatiques des jours fériés

Jusqu'alors, lorsque nous avions construit des plannings et calendriers, nous avions mis en valeur les jours fériés en prenant appui sur une source importée. Mais ces jours fériés peuvent être générés automatiquement, y compris pour les fêtes religieuses qui se déduisent du Lundi de Pâques à calculer.



Dans l'exemple proposé par la capture, nous travaillons sur un calendrier annuel et perpétuel. Il se reconstruit intégralement à chaque changement d'année, choisie grâce à une liste déroulante. Nous l'avions conçu à l'occasion d'une formation. Ici, il est donc offert dans sa version parfaitement fonctionnelle afin de concentrer l'étude sur ces fameux jours fériés. Et précisément, dans cette version finalisée, ils sont repérés dynamiquement en vert. Mais cette fois, ils se recalculent automatiquement en fonction du choix de l'année spécifiée par l'utilisateur.

Source et présentation
Pour réaliser cette étude, nous devons commencer par récupérer ce calendrier.

• Télécharger le classeur calculer-jours-feries.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Si vous déployez la liste déroulante en cellule C4 et que vous changez d'année, le calendrier se recompose effectivement automatiquement. De fait, les repérages des jours de Week-End se déplacent pour s'adapter aux nouvelles dates. C'est une règle de mise en forme conditionnelle en vigueur qui permet cette identification dynamique. Mais à ce stade, les jours fériés restent muets et pour cause, ils ne sont pas encore connus.

• En bas de la fenêtre Excel, cliquer sur l'onglet Feries pour activer sa feuille,

En colonne G, vous apercevez l'énumération des années servant à nourrir la liste déroulante de la feuille Calendrier. A ce titre, cette liste déroulante n'est pas limitée à cette énumération. Vous êtes autorisé à inscrire en C4 une année non prévue par la liste.

Mais la partie qui nous intéresse est la zone située entre les colonnes B et D. En cellule B2, l'année choisie pour le calendrier est extraite et restituée par un calcul d'équivalence : =Calendrier!C4. C'est à partir de cette information dynamique que tous les jours fériés de l'année doivent être calculés. Mais deux types de jours fériés se distinguent. Les jours fériés fixes, soit identiques d'une année sur l'autre, doivent être calculés entre les cellules B3 et B10. Les jours fériés variables doivent être calculés dans la partie jaune du dessous, entre les cellules B11 et B13. Il s'agit du Lundi de Pâques, du Jeudi de l'Ascension et du Lundi de Pentecôte. Tous trois consistent en une incrémentation fixe du Dimanche de Pâques. C'est donc cette date qui varie chaque année et qui doit être calculée en cellule D3.

Jours fériés fixes
Pour reconstruire la date des jours fériés fixes en fonction de l'information variable sur l'année, il suffit d'exploiter la fonction Excel Date : Date(Année; Mois; Jour).

• En cellule B3, pour le 1^er Janvier, saisir la formule suivante : =DATE(B2;1;1),

L'année variable est passée en premier paramètre. Le mois et le jour sont fixes. Il s'agit du premier dans les deux cas et sont passés respectivement en deuxième et troisième paramètre. Comme vous pouvez le voir, la date est automatiquement restituée dans la cellule du calcul. C'est un format date longue prédéfini sur la plage qui permet de l'afficher avec un niveau de détail amélioré.

• En cellule B4 pour le 1^er Mai, construire la formule suivante : =DATE(B2;5;1),
• En cellule B5 pour le 8 Mai, construire la formule suivante : =DATE(B2;5;8),
• En cellule B6 pour le 14 Juillet, bâtir le calcul suivant : =DATE(B2;7;14),
• En cellule B7 pour le 15 Août, inscrire la syntaxe suivante : =DATE(B2;8;15),
• En cellule B8 pour le 1^er Novembre, construire le calcul suivant : =DATE(B2;11;1),
• En cellule B9 pour le 11 Novembre, utiliser la formule suivante : =DATE(B2;11;11),
• En cellule B10 pour le 25 Décembre, ajouter le calcul suivant : =DATE(B2;12;25),

Nous obtenons bien le calcul de tous les jours fériés fixes en fonction de l'année choisie. Vous pouvez le constater en modifiant cette année avec la liste déroulante depuis la feuille Calendrier. Les jours de semaine attachés évoluent fort naturellement.

Jours fériés variables
Les jours fériés variables sont donc au nombre de trois et se déduisent du calcul pour le Dimanche de Pâques. Le lundi de Pâques est naturellement le jour férié suivant. Il suffit d'incrémenter la date trouvée d'une unité. Pour les deux suivants, il s'agit de les incrémenter respectivement de 39 et 50 unités. La syntaxe de la formule pour le Dimanche de Pâques est relativement complexe. Et comme elle est spécifique à ce calcul, nous proposons de la livrer telle quelle. Pour toute application exploitant les jours fériés, il suffira de la répliquer à l'identique.

• En cellule D3 pour le Dimanche de Pâques, construire la formule suivante :

=ARRONDI(DATE(B2; 4; MOD(234-11*MOD(B2; 19); 30))/7; 0)*7-6

• En cellule B11, pour le Lundi de Pâques, créer le calcul suivant : =D3+1,
• En cellule B12, pour le Jeudi de l'Ascension, ajouter le calcul suivant : =D3+39,
• Enfin, en cellule B13 pour le Lundi de Pentecôte, inscrire le calcul suivant : =D3+50,

Nous obtenons bien une liste dynamique des jours fériés calculés dynamiquement par rapport à l'année choisie. Nous devons maintenant exploiter cette source de données.

Repérer les jours fériés dans le calendrier

Ces jours fériés dynamiques doivent désormais être recoupés avec les dates du calendrier. Lorsque les dates coïncident, la case concernée doit ressortir dans une mise en forme explicitement différente. La méthode la plus simple consiste à exploiter la fonction de dénombrement conditionnel Nb.Si dans une règle de mise en forme conditionnelle.

• En bas de la fenêtre Excel, cliquer sur l'onglet Calendrier pour revenir sur sa feuille,
• Sélectionner toutes les dates du calendrier, soit la plage de cellules C8:N38,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie juste en-dessous, bâtir la syntaxe suivante :

=Nb.Si(Feries!$B$3:$B$13;C8)>0

Sur la plage figée des jours fériés, nous cherchons à identifier la présence de la date en cours d'analyse. Cette analyse débute à partir de la première date en C8. Cette cellule ne doit pas être figée pour que toutes les autres dates soient analysées tour à tour et cherchées dans la plage des jours fériés. Si la fonction de dénombrement retourne une valeur positive (>0), cela signifie que les dates concordent. Dans ces conditions, la case incriminée doit ressortir explicitement.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert pâle,
• Activer alors l'onglet Police de cette même boîte de dialogue,
• Avec la liste déroulante, choisir un vert foncé pour le texte,
• Puis, valider ces attributs de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, créer la règle avec le bouton Ok,

Comme vous pouvez le voir, les jours fériés surgissent parfaitement. Et bien sûr, si vous changez d'année, ils s'adaptent automatiquement à la nouvelle organisation, grâce aux calculs dynamiques opérés dans la feuille Feries.