Numeriblog › Google Workspace › Google Sheets, le tableur surpuissant › Google Sheets : créer un calendrier perpétuel avec les jours ouvrés et les jours fériés.

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 […]

Laisser une évaluation

7 020
personnes ont consulté cet article

3 minutes

Rédigé par Patrick Tellier - il y a 3 ans et modifié le 07/05/2024 à 11:02

Ce que vous allez découvrir

  • Construction du tableau :
  • Application des jours ouvrés et jours fériés :
  • Mise en forme conditionnelle
  • Personnalisation des jours ouvrés
  • 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 :

 

Calendrier perpétuel avec weekend est jours fériés construit avec Google Sheets.

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 :

TEXTE “Blanc” FOND “Blanc”

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 similaires

  • Articles connexes
  • Plus de l'auteur

Rédacteur

Photo de profil de l'auteur
Patrick Tellier

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.

S’abonner
Notification pour
4 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
Antoine MARTIN

Merci Thierry ! super aide pour les jours fériés !

0
Patrick Tellier
0
le vieil indien

B2 vous entrez la formule : =date($A$1;B$1;$A2)

3
Patrick TELLIER

Bonjour,
Bien vu , effectivement c’est bien $A2 , c’était pour voir qui suivait

2