fbpx

 

Tutoriel réalisé avec Excel Microsoft 365

Un calendrier perpétuel est un calendrier qui pourra être utilisé pour tous les jours de l’année, quelle que soit l’année. Vous n’aurez donc pas à recréer votre calendrier tous les ans et les jours se recalculeront automatiquement à chaque changement d’année. Dans ce tutoriel, nous allons créer ensemble un calendrier perpétuel avec Excel avec un affichage des weekends et jours fériés que vous soyez sur Windows ou Mac.

Calendrier perpétuel Excel

Création du modèle de calendrier perpétuel sur Excel

Création et renommage des onglets

Ouvrez un nouveau classeur Excel et renommez l’onglet Calendrier par un double-clic dessus. Créez un autre onglet (que nous utiliserons plus tard) en cliquant sur Nouvelle feuille Nouvelle feuille et nommez-le Jours fériés.

 

Redimensionnement des cellules

Afin que ce calendrier tienne sur une seule page A4 et reste lisible, nous allons redimensionner les colonnes et lignes.

Redimensionnez les colonnes A à AI en les sélectionnant, en faisant un clic droit sur les en‑têtes > Largeur de colonne…, en saisissant 2,25 dans la boîte de dialogue et en cliquant sur OK.

Redimensionnez la première ligne par un clic droit dessus > Hauteur de ligne… et indiquez 40. Faites de même pour les autres lignes :

  • Ligne 2 : 306
  • Ligne 3 : 20
  • Lignes 4 à 29 : 14,25 (qui est la hauteur par défaut)

Pour centrer tout le calendrier en une seule fois, faites un Ctrl + A (ou Cmd + A sur Mac) et cliquez sur Aligner au centre Bouton Aligner au centre et Centrer Bouton Centrer dans le groupe Alignement du menu Accueil. Vous pouvez aussi choisir la Police et la Taille de police de votre choix (Calibri et 11 dans mon exemple).

 

Création de la cellule de l’année à prendre en compte

Fusionnez les cellules A1 à AI1 en les sélectionnant puis en cliquant sur Fusionner et centrer Bouton Fusionner et centrer dans le groupe Alignement du menu Accueil. Puis, nommez-la en saisissant Annee_chosie dans la Zone nom et en validant par la touche Entrée. Enfin, saisissez 2021 dans cette cellule et appliquez-lui la mise en forme de votre choix (dans cet exemple : Calibri en Police, 28 en Taille de police, Gras et couleur #4F81BD).

 

Ajout de l’image

Fusionnez les cellules A2 à AI2 et insérez-y l’image de votre choix en utilisant le bouton Images Icône Images dans le menu Insertion > Illustrations Bouton Illustrations. Redimensionnez l’image à l’aide des poignées en vous aidant du quadrillage pour qu’elle reste dans les cellules que nous venons de fusionner.

Ajout des jours de la semaine

En B5, saisissez L pour Lundi, puis, en B6, M pour Mardi et ainsi de suite jusqu’en H5 où vous devriez avoir un D. Saisissez la même suite de lettres pour les plages suivantes :

  • K5 à Q5 ;
  • T5 à Z5 ;
  • AC5 à AI5 ;
  • B14 à H14 ;
  • K14 à Q14 ;
  • T14 à Z14 ;
  • AC14 à AI14 ;
  • B23 à H23 ;
  • K23 à Q23 ;
  • T23 à Z23 ;
  • AC23 à AI23.

Fusion et coloration des mois

Sélectionnez les cellules A4 à H4 et fusionnez-les avant de leur appliquer une couleur de fond (#4F81BD dans mon exemple) en utilisant le bouton Couleur de remplissage Couleur de remplissage dans le menu Accueil et une couleur de police (blanche dans mon exemple) via le bouton du même nom Couleur de police. Faites de même pour les cellules A13 à H13 et A22 à H22.

Pour gagner du temps, utilisez le pinceau pour reproduire la mise en forme Reproduire la mise en forme dans le menu Accueil en cliquant dessus lorsque votre cellule active est la cellule colorée, en double‑cliquant sur le pinceau pour le bloquer et en cliquant sur A13 et A22. Recliquez sur le pinceau pour le retirer.

Procédez de la même manière pour les autres colonnes en fusionnant et en appliquant une couleur identique aux cellules J4 à Q4, J13 à Q13 et J22 à Q22 (#C0504D dans mon exemple), puis S4 à Z4, S13 à Z13, S22 à Z22 (#1F497D dans mon exemple) et enfin AB4 à AI4, AB13 à AI13, AB22 à AI22 (#F79646 dans mon exemple).

Coloration des jours et semaines

Pour chaque bloc, sélectionnez la ligne des jours et la colonne qui nous servira pour les numéros de semaine pour appliquer une couleur de fond un peu plus clair que le mois et une couleur de police blanche. Ce qui donnerait, pour le premier mois dans mon exemple, une couleur de fond #95B3D7 pour les cellules A5 à A11 et B5 à H5, puis la même couleur pour les cellules A14 à A20, B14 à H14, A23 à A29 et B23 à H23.

Faites de même pour les autres blocs :

  • J5 à J11, K5 à Q5, J14 à J20, K14 à Q14, J23 à J29, K23 à Q23 de couleur #DA9694 ;
  • S5 à S11, T5 à Z5, S14 à S20, T14 à Z14, S23 à S29, T23 à Z23 de couleur #538DD5 ;
  • AB5 à AB11, AC5 à AI5, AB14 à AB20, AC14 à AI14, AB23 à AB29, AC23 à AI23 de couleur #FABF8F.

Définition des noms des mois

Pour simplifier notre travail lors de la création des formules, nous allons nommer les cellules des mois. Pour cela, sélectionnez le 1er mois (A4) et, dans la Zone nom, saisissez Mois_1. Puis, pour les autres mois (en vous déplaçant de gauche à droite, puis vers le bas), nommez-les Mois_2, Mois_3, etc. jusqu’à ce que vous arriviez au dernier bloc qui se nommera Mois_12.

Ajout des fonctions Excel pour calculer automatiquement les dates de chaque jour

Ajout des fonctions pour les mois

Pour les noms des mois

Comme les dates des jours vont être basées sur le mois, on ne peut pas se contenter de l’écrire en toutes lettres : nous allons donc utiliser une fonction.

En A4, saisissez =DATE(Annee_choisie;MOIS(1);1). Cela signifie que nous souhaitons obtenir le 1er jour du 1er mois de l’année indiquée en A1.

En J4, nous allons nous baser sur la fonction précédente pour l’incrémenter d’un mois. Saisissez =MOIS.DECALER(Mois_1;1) qui ajoute 1 mois à la date précédente. Procédez de la même manière pour les autres mois en remplaçant Mois_1 par Mois_2, puis Mois_3, etc. jusqu’au dernier mois qui obtiendra la fonction =MOIS.DECALER(Mois_1;11).

Pour les numéros des jours

Les jours ne vont pas être calculés de la même façon sur toute l’année. En effet, comme les premières et dernières lignes de chaque mois peuvent être vides, il faut prendre en compte cette possibilité.

 

  • Janvier :

En B6, saisissez =SI(JOURSEM(Mois_1)=2;Mois_1;""). Avec cette formule, nous vérifions si le premier jour de l’année est un lundi (2). Si c’est le cas, le premier sera dans cette cellule, sinon rien.

En C6, saisissez =SI(B6<>"";B6+1;SI(JOURSEM(Mois_1)=3;Mois_1;"")). Dans ce cas, c’est la même formule sauf qu’on vérifie si c’est un mardi (3) et, avant, on vérifie si la cellule précédente n’était pas vide (si c’était le cas, on incrémenterait de 1).

Pour les cellules suivantes, on peut se contenter d’utiliser la poignée de recopie et de remplacer 3 par 4, puis 5, 6, 7 et 1.

Pour la 2e ligne, le mois étant commencé, il suffit d’écrire =H6+1 en B7, puis =B7+1 en C7 et d’utiliser la poignée de recopie pour le reste de la ligne. Faites de même pour les 3 autres lignes.

Pour la dernière ligne du mois, en B11, saisissez =SI(H10<FIN.MOIS(Mois_1;0);H10+1;""). Dans ce cas, avant d’incrémenter, on vérifie si H10 n’est pas le dernier jour du mois de janvier et il en est de même pour les autres cellules de la ligne où l’on vérifie si la date précédente n’est pas le dernier jour du mois, auquel cas la cellule active serait vide.

 

  • Les autres mois :

Pour les autres mois, il y a une astuce qui peut vous faire gagner beaucoup de temps. Copiez la plage B6 à H11, puis cliquez droit sur la cellule K6 et, dans les options de collage, choisissez Formules Option de collage Formules. Tout en gardant la sélection faite par Excel, faites un Ctrl + F (ou Cmd + F sur Mac) et, dans l’onglet Remplacer, sur la ligne Rechercher, saisissez Mois_1 et, sur la ligne Remplacer par, Mois_2. Cliquez sur Remplacer tout : Excel vous indique que des remplacements ont été effectués. Cliquez sur Fermer.

Procédez de la même manière pour les autres mois en copiant une plage déjà faite et en remplaçant la plage nommée par celle du mois que vous modifiez.

Pour les numéros des semaines

Pour renvoyer le numéro de la semaine pour chaque ligne, une seule formule sera utilisée (il suffira ensuite de modifier les cellules utilisées ) : =SI(B6<>"";NO.SEMAINE.ISO(B6);SI(H6<>"";NO.SEMAINE.ISO(H6);"")). Cette fonction respecte la norme ISO 8601 applicable en France.

Comme une ligne aura forcément la première ou dernière cellule qui sera non vide (sinon, on n’indique pas de numéro de semaine), c’est de cette manière que l’on conditionne notre formule de calcul.

Modification du format de cellule

Pour les mois

Pour le moment, les mois sont indiqués sous la forme jj/mm/aaaa, ce qui n’est pas très visuel pour un calendrier : nous allons donc modifier le format de cellule. Sélectionnez chaque cellule de nom de mois en maintenant la touche Ctrl enfoncée (ou Cmd sur Mac) lorsque vous cliquez sur chacun d’eux, puis cliquez sur la flèche Flèche de groupe du groupe Nombre du menu Accueil (ou Autres formats numériques… dans la liste déroulante sur Mac). Choisissez la Catégorie Personnalisée et saisissez, sous Type, mmmm. Cliquez sur OK.

Pour les jours

Les jours sont, quant à eux, affichés sous forme de numéro de série, ce qui n’est pas pratique non plus. Sélectionnez donc toutes les cellules où pourrait se trouver une date (même celles qui sont vides à cet instant, car, d’une année à l’autre, cela pourrait changer) et modifiez le format de cellule comme précédemment en saisissant jj.

Création des bordures

Maintenant que nous n’allons plus utiliser la poignée de recopie, nous pouvons ajouter une bordure pour chaque bloc de mois en les sélectionnant 3 par 3 (avec la touche Ctrl enfoncée ou Cmd sur Mac) de façon verticale et en appliquant une bordure de la même couleur que la couleur de remplissage du mois via le bouton Bordures Icône Bordures du groupe Police du menu Accueil > Autres bordures…Autres bordures. Choisissez la couleur de votre choix et cliquez sur Contour Contour avant de cliquer sur OK.

Application des mises en forme conditionnelles aux jours non fériés

Création du tableau des jours fériés

Dans l’onglet Jours fériés que nous avons créé au début de ce tutoriel, créez un tableau en A1 en cliquant sur Tableau Bouton tableau dans le menu Insertion et cliquez sur OK. Remplacez Colonne1 par Dates et saisissez, en B1, Jours fériés.

Dans ce tableau, nous allons référencer tous les jours qui devront apparaître comme fériés (pour la France) dans le calendrier (vous pouvez aussi y saisir vos congés, mais vous devrez créer une ligne pour chaque jour).

Saisissez, à partir de la cellule B2, les jours fériés suivants :

  • Jour de l’an ;
  • Lundi de Pâques ;
  • Fête du Travail ;
  • Victoire des Alliés ;
  • Ascension ;
  • Lundi de Pentecôte ;
  • Fête nationale ;
  • Assomption ;
  • La Toussaint ;
  • Armistice ;
  • Noël.

Modifiez le format de cellule des dates de A2 à A12 pour le format Date longue (directement accessible via la liste déroulante du groupe Nombre).

Enfin, nommez cette plage Dates_jours_feries via la Zone nom.

Cas des jours fériés qui tombent tous les ans à la même date

Le Jour de l’an tombant tous les ans à la même date, il suffit d’écrire en A2 : =DATE(Annee_choisie;1;1). Cela signifie que nous souhaitons obtenir le 1er jour du 1er mois de l’année définie dans la cellule Annee_choisie.

Faites de même pour les autres dates fixes :

  • Fête du Travail : =DATE(Annee_choisie;5;1) (1er jour du 5e mois) ;
  • Victoire des Alliés : =DATE(Annee_choisie;5;8) (8e jour du 5e mois) ;
  • Fête nationale : =DATE(Annee_choisie;7;14) (14e jour du 7e mois) ;
  • Assomption : =DATE(Annee_choisie;8;15) (15e jour du 8e mois) ;
  • La Toussaint : =DATE(Annee_choisie;11;1) (1er jour du 11e mois) ;
  • Armistice : =DATE(Annee_choisie;11;11) (11e jour du 11e mois) ;
  • Noël : =DATE(Annee_choisie;12;25) (25e jour du 12e mois).

Cas des jours fériés qui ne tombent pas tous les ans à la même date

Certaines fêtes religieuses (lundi de Pâques, Ascension et lundi de Pentecôte) ne se déroulent pas tous les ans à la même date, il faut donc effectuer un calcul qui va se baser sur la date du jour de Pâques.

Selon la définition, Pâques est le dimanche qui suit le 14e jour de la Lune qui atteint cet âge le 21 mars ou immédiatement après. Pâques tombe donc entre le 22 mars et le 25 avril.

 

  • Pâques :

Pour simplifier la création de ces fonctions, nous allons placer le calcul pour Pâques dans une cellule à part, mais vous pourriez très bien l’imbriquer dans les 3 formules qui l’utilisent.

En D1, j’écris Pâques : et, en E1, je saisis : =ARRONDI(DATE(Annee_choisie;4;MOD(234-11*MOD(Annee_choisie;19);30))/7;0)*7-6.

Cette fonction permet de calculer la date de Pâques de façon automatique en y mélangeant des fonctions d’Arrondi, de Date et de Mod (calcul du reste d’une division).

Édit : il s’avère que cette formule n’indique pas la bonne date de Pâques pour certaines années. Il vaudrait donc mieux utiliser : =PLANCHER(JOUR(MINUTE(Annee_choisie/38)/2+56) & "/5/" & Annee_choisie;7)-34

 

Enfin, je nomme cette cellule Paques via la Zone nom et je lui attribue le format Date courte via la liste déroulante du groupe Nombre.

 

  • Lundi de Pâques :

Pour le lundi de Pâques, il suffit d’écrire dans le tableau =Paques+1 puisqu’il s’agit toujours du lundi qui suit immédiatement Pâques et que cette date tombe toujours un dimanche.

 

  • Ascension :

Le calcul de l’Ascension est tout aussi simple. Cette date se déroulant 40 jours après Pâques, dans le tableau, saisissez =Paques+39 (pour tomber sur un jeudi).

 

  • Lundi de Pentecôte :

Le lundi de Pentecôte se déroulant 50 jours après Pâques, dans le tableau, saisissez =Paques+50 (il doit tomber un lundi).

 

Création des mises en forme conditionnelles

Pour rendre notre calendrier plus visuel, nous allons le colorer grâce à la mise en forme conditionnelle. Pour les 4 conditions que nous allons créer, nous procéderons toujours de la même manière : cliquer sur Mise en forme conditionnelle Bouton Mise en forme conditionnelle du menu Accueil > Nouvelle règle…Commande Nouvelle règle.

 

Création de la mise en forme conditionnelle pour les cellules vides

Choisissez le type de règle Appliquer une mise en forme uniquement aux cellules qui contiennent et, dans la première liste, choisissez Cellules vides. Puis, cliquez sur Format… pour appliquer le format de votre choix. Dans mon exemple, il s’agit d’une couleur de police et de remplissage grise (#D9D9D9). Pour terminer, cliquez sur OK.

 

Création de la mise en forme conditionnelle pour la date du jour

Choisissez le type de règle Appliquer une mise en forme uniquement aux cellules qui contiennent et, dans la première liste, choisissez Dates se produisant et remplacez Hier par Aujourd’hui. Cliquez sur Format… pour appliquer le format de votre choix. Dans mon exemple, il s’agit d’une couleur de police vert foncé (#006100) et d’une couleur de remplissage vert clair (#C6EFCE). Pour terminer, cliquez sur OK.

 

Création de la mise en forme conditionnelle pour les weekends

Choisissez le type de règle Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué et, dans le champ, saisissez la formule suivante : =OU(JOURSEM(B6)=7;JOURSEM(B6)=1). Cette fonction permet de rechercher les samedis (7) et les dimanches (1). Cliquez sur Format… pour appliquer le format de votre choix. Dans mon exemple, il s’agit d’une couleur de remplissage orange clair (#FDE9D9). Pour terminer, cliquez sur OK.

 

Création de la mise en forme conditionnelle pour les jours fériés

Choisissez le type de règle Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué et, dans le champ, saisissez la formule suivante : =NB.SI(Dates_jours_feries;B6). Cette fonction permet de vérifier si la valeur de la cellule à laquelle est appliquée la mise en forme se trouve dans le tableau des jours fériés. Cliquez sur Format… pour appliquer le format de votre choix. Dans mon exemple, il s’agit d’une couleur de police bordeaux (#9C0006) et d’une couleur de remplissage rose (#FFC7CE). Pour terminer, cliquez sur OK.

 

Application des mises en forme conditionnelles à toutes les cellules concernées

Une fois toutes vos mises en forme conditionnelles créées, cliquez sur le bouton Mise en forme conditionnelle Bouton Mise en forme conditionnelle > Gérer les règles…Commande Gérer les règles. Pour l’une des 4 règles, sélectionnez ce qui se trouve dans le champ S’applique à et choisissez chacun des blocs de dates sur le calendrier (B6 à H11, K6 à Q11, etc.) en maintenant la touche Ctrl enfoncée (ou Cmd sur Mac). Puis, contentez‑vous de copier ce champ pour le coller pour les 3 autres règles avant de cliquer sur Appliquer et, si cela vous convient, de cliquer sur OK.

 

Finalisez votre calendrier perpétuel

Si vous souhaitez imprimer ce calendrier, je vous conseille de cliquer sur la flèche Flèche de groupe du groupe Mise en page dans le menu du même nom (bouton Mise en page Bouton Mise en page sur Mac). Dans l’onglet Marges, indiquez 1 en Haut et en Bas et 0,5 pour les autres valeurs, puis cochez Horizontalement et Verticalement avant de cliquer sur OK (ou Aperçu pour voir le résultat).

Vous pouvez masquer le quadrillage si vous le souhaitez en vous rendant dans le menu Affichage et en décochant la case Quadrillage dans le groupe Afficher.

 

Testez votre calendrier perpétuel

La création de notre calendrier étant à présent terminée, il ne vous reste plus qu’à modifier l’année en A1 pour vérifier que les jours et mises en forme conditionnelles se modifient bien automatiquement et qu’elles sont correctes.

 

Vous pouvez télécharger le fichier finalisé au bas de l’article.

 

Si vous souhaitez aller plus loin dans l’apprentissage des formules d’Excel, découvrez le Master Club Excel qui, chaque mois, vous permet de maîtriser 5 fonctions via des cours en PDF et vidéo et des exercices avec leurs corrigés.

Pour bien démarrer l’année 2021, pour toute inscription au Master Club avant le 5 janvier 2021, je vous offre le Kit du bon démarrage avec les formules Excel.