Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube 
Google Sheets : créer un calendrier perpétuel avec les jours ouvrés et les jours fériés.
La fonction NB.JOUR.OUVRES permet de calculer le nombre de jours ouvrés entre deux dates mais elle permet également de repérer les jours ouvrés à des dates précises. Je vous propose ici de créer en trois […]
Ce que vous allez découvrir
- Construction du tableau :
- Application des jours ouvrés et jours fériés :
- Mise en forme conditionnelle
- Conclusion
Google Sheets : créer un calendrier perpétuel avec les jours ouvrés et les jours fériés.
La fonction NB.JOUR.OUVRES permet de calculer le nombre de jours ouvrés entre deux dates mais elle permet également de repérer les jours ouvrés à des dates précises. Je vous propose ici de créer en trois minutes chrono un calendrier perpétuel dynamique par application de cette fonction associée à une mise en forme conditionnelle :
Dans cette feuille de calcul, vous remarquez que les weekends sont identifiés par un fond orangé et les jours fériés par une couleur rose. Ce tableau est dynamique et s’adapte à l’année en cours ! A vos claviers pour découvrir pas à pas la réalisation de ce calendrier perpétuel avec mise en avant des jours fériés.
Construction du tableau :
Pour commencer vous indiquez en A1 l’année souhaitée. Ici nous mettrons « 2022 ».
Puis en B1 vous entrez la formule
=sequence(1;12;1;1)
et en A2 la formule
=sequence(31;1;1;1)
Nous avons maintenant une ligne avec les numéros de mois et une colonne avec les numéros de jours.
En B2 vous entrez la formule :
=date($A$1;B$1;$A3)
, remarquez bien la position du $.
Vous pouvez ensuite recopier cette formule vers le bas et vers la droite; et appliquer le format de date qui convient :
Le calendrier est construit. (il ne nous reste plus que deux minutes ….)
Il faut juste nettoyer les cellules des mois de moins de 31 jours par l’application d’une mise en forme conditionnelle avec la formule personnalisée :
Ainsi les jours en excédent sont masqués
Application des jours ouvrés et jours fériés :
Il nous faut maintenant mettre en couleur les jours non-ouvrés. Vous allez pour cela utiliser la fonction NB.JOURS.OUVRES. Cette fonction attend trois arguments : une date de début, une date de fin et une plage renseignée des jours fériés. En indiquant une date de début et fin identique vous obtiendrez une valeur égale à 0 ou 1 selon que la date est un jour ouvré ou pas.
En O1 de notre tableau vous allez renseigner les jours fériés :
- Pour les jours fériés fixes c’est assez simple : la formule est =date($A$1;1;1) pour le 1er janvier puis =date($A$1;5;1) pour le premier mai et ainsi de suite pour les 8 mai, 14 juillet, 15 août, 1er novembre, 11 novembre et 25 décembre.
- Cependant nous avons dans notre calendrier des jours fériés à des dates variables et notre calendrier doit être dynamique, c’est-à-dire qu’en modifiant l’année en A1 tout le tableau doit se recalculer y compris les jours fériés.
La seule date à récupérer est le jour de Pâques, les autres sont calculées.
Comme tout le monde le sait, le jour de Pâques est le premier dimanche qui suit la pleine lune après le 21 mars… Seulement voilà on ne connaît pas les cycles de lune (oui, ça se calcule et cela pourra faire l’objet d’un autre article).
Alors nous allons demander l’aide d’un ami et avec un importHTML pour récupérer le lundi de Pâques grâce à la formule :
=query(importhtml("https://www.lecalendrier.fr/jours-feries-"&$A$1;"table";1);"Select Col1 where Col2 contains 'Lundi de Pâques'";0)
L’importhtml vous retourne la liste des jours fériés pour l’année en A1 et le QUERY permet d’extraire le lundi de Pâques.
Ensuite c’est facile : le dimanche de Pâques est égal à Lundi -1 , le jeudi de l’ascension est égal à Dimanche de Pâques + 38 et le lundi de Pentecôte à Ascension + 11
Et vous avez ainsi la liste des jours fériés pour l’année indiquée en A1 :
La plage des jours fériés doit bien être placée dans la même feuille que le calendrier, la formule personnalisée de la mise en forme conditionnelle que vous allez utiliser ensuite ne peut pas faire appel à une autre feuille ou à une plage nommée (c’est une fonctionnalité attendue 😉)
Mise en forme conditionnelle
Il ne vous reste plus qu’à appliquer une mise en forme conditionnelle sur les cellules B2:M32 avec la formule personnalisée :
=NB.JOURS.OUVRES(B2;B2;$O$2:$O$13)=0
C’est à dire : si le nombre de jours ouvrés sur la date est égale à 0, alors c’est soit un jour férié soit un weekend, alors nous appliquons une couleur de fond.
Vous pouvez également appliquer une mise en forme spéciale pour les jours fériés avec la formule personnalisée :
=NB.SI($O$1:$O$13;B2)>0
Si la date est incluse dans la plage O1:O13 alors c’est bien un jour férié.
Et enfin s’il vous reste quelques secondes la mise en forme conditionnelle suivante permettra d’afficher en rouge et gras la date d’aujourd’hui :
Personnalisation des jours ouvrés
Voilà votre calendrier est construit, seulement tout le monde ne travaille pas du lundi au vendredi : certaines professions travaillent du mardi au samedi, d’autres ne travaillent pas le mercredi (la gestion des vacances scolaires c’est aussi pour un autre article …)
Alors il y a la fonction NB.JOURS.OUVRES.INTL qui fonctionne comme la précédente mais accepte un argument supplémentaire soit sous forme d’un nombre ou sous forme d’une chaîne de caractère dans laquelle on peut personnaliser les jours ouvrés.
Exemple en deuxième argument : “0010011” chaque caractère indique un jour de la semaine, ici les mercredis, samedis et dimanches ne sont pas travaillés.
Ainsi en remplaçant dans la mise en forme conditionnelle NB.JOURS.OUVRES par NB.JOURS.OUVRES.INTL avec en deuxième argument la cellule N1 qui comprend la formule = »0010011″ vous pouvez personnaliser le calendrier.
Conclusion
Vous ne disposez pas des trois minutes pour construire le calendrier ? je vous donne le lien (oui c’est cadeau) : Calendrier annuel
Vous pouvez maintenant modifier à volonté l’année et vérifier si les années futures nous réservent de meilleurs jours fériés que 2022 (1er mai, 8 mai et Noël un dimanche).
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets- Articles connexes
- Plus de l'auteur
Passionné par l'univers Google Workspace, Formateur interne, je suis toujours à la recherche de nouvelles solutions de productivité et d'automatisation à mettre en oeuvre et à partager.
Merci Thierry ! super aide pour les jours fériés !
B2 vous entrez la formule : =date($A$1;B$1;$A2)
Bonjour,
Bien vu , effectivement c’est bien $A2 , c’était pour voir qui suivait