Comment créer un calendrier dynamique ?

Je vais vous montrer dans cet article comment créer un calendrier dynamique en utilisant plusieurs formules et outils. 

Dans ce calendrier vous aurez une liste d’années vous permettant de sélectionner l’année que vous voudriez et automatiquement, les noms des jours se réorganiseront pour s’adapter à chaque changement d’année ainsi que les jours du weekend seront distingués d’une couleur personnalisée quel que soient leurs positions sur le calendrier.

En plus vous allez voir comment faire pour que ce calendrier affichera le 29^ème jour du mois Février lorsque vous choisissez une année bissextile.

Avant de commencer, je vous présente cette image du calendrier pour vous donner une idée sur l’exemple du calendrier que vous allez obtenir à la fin de cet article.

Alors pour créer ce calendrier, commencez par créer un nouveau classeur Excel de trois feuilles de calcule, et donnez-leur ces noms : Calendrier, Base et Année.

• Sélectionnez la feuille Calendrier et tapez les noms des mois dans les cellules de la plage A3:L3. Puis copiez-les dans la plage de cellules A1:L1 de la feuille Base.

• Sélectionnez maintenant la feuille Année, et créez une liste d’années de votre choix, par exemple de 2019 à 2029.

Revenez à la feuille Calendrier pour créer une liste déroulante qui va contenir ces années.

• Pour ce faire, sélectionnez la cellule K1 et cliquez sur Validation de données sous l’onglet Données.
• Dans la fenêtre qui s’affiche choisissez Liste sous la catégorie Autoriser...

• Puis cliquez sur la zone Source et sélectionnez votre liste d’années et cliquez enfin sur OK.

Vous voyez maintenant une flèche à la droite de la cellule K1, cliquez sur cette flèche pour afficher la liste déroulante créée:

Sélectionnez à présent la feuille Base et sélectionnez la cellule A2 se trouvant sous JANVIER, puis tapez la formule suivante :

=DATE(Calendrier!$K$1;COLONNE(A1);1)

Cette formule va vous permettre d’entrer la première date du premier jour du mois de janvier.

Vous savez que la syntaxe de la fonction Date est Date(année ;mois ;jour)

• L’argument année correspond alors à l’année sélectionnée dans la feuille Calendrier, c’est-à-dire le contenu de la cellule K1, que j’ai figée ici en ajoutant des signes $.
• Pour l’argument mois, j’ai intégré une autre fonction appelée COLONNE que son rôle est de renvoyer le numéro de la colonne référencée. Il me suffit donc d'utiliser la référence de la cellule A1 qui appartient à la première colonne JANVIER pour que cette fonction me renvoie le numéro 1. 

Pour vous rappeler, la syntaxe de la fonction COLONNE est : COLONNE(référence).

J'ai évité donc de taper directement 1 dans l'argument mois de la fonction Date car lorsque je vais copier cette formule de date dans les autres premières cellules des autres mois, Excel va, automatiquement, incrémenter le numéro de la colonne en incrémentant la référence de la cellule A1. Ceci va m’éviter donc de modifier à chaque fois l’argument mois dans ces cellules.

• Dans le dernier argument de la fonction Date, j’ai tapé 1. c-à-d le premier jour.

Copiez ensuite, cette formule de date dans les premières cellules des mois restants : Du B1 à L1. 

• Sélectionnez une année différente  et regardez ce que vont afficher ces cellules.

Insérons maintenant toutes les dates qui nous restent pour remplir notre calendrier

Pour ne pas procéder à saisir les 353 dates une par une, je vous donne une technique très simple pour gagner tout cet effort :

• Dans la cellule A3 tapez =A2+1 puis copiez la formule dans le reste des cellules de la colonne JANVIER jusqu’à ce que vous atteigniez le jour 31 du mois janvier. Cette dernière date sera placée dans la cellule A32.

• Sélectionnez la plage A3:A32 (si ce n’est déjà fait), puis cliquez sur la poignée de recopie en bas et faites glisser la souris jusqu’à la dernière colonne : DECEMBRE.

• Faites après, une petite correction dans ce tableau: Supprimez les dates qui dépassent les limites dans le mois Février et les mois de 30 jours.

Afficher ou masquer le 29 février !

Le problème qui se pose ici c’est le jour 29 du mois février. En effet, si nous voulons afficher la date du 29 février dans la cellule B30  dans le cas d'une année bissextile sélectionnée, nous allons remarquer que cette cellule va afficher la date du 1er mars dans l'autre cas où une année ordinaire est sélectionnée.
Pour résoudre ce problème donc, nous allons utiliser une formule pour demander à Excel de ne faire apparaître la date  du 29 février que si une année bissextile est sélectionnée, et dans le cas contraire, laisser la cellule B30 vide.

Cette formule se composera de trois fonctions intégrées dans la fonction SI, qui sont: Date, FIN.MOIS et JOUR.

Tout d’abord, nous allons vérifier si le mois Février contient 28 ou 29 jours. Nous utiliserons donc la fonction FIN.MOIS

Utilisation de la fonction FIN.MOIS

La fonction FIN.MOIS permet d’obtenir la date du dernier jour du mois en cours, du mois précédant ou suivant la date du mois indiqué.

Sa syntaxe est : FIN.MOIS(date_départ;mois)

• Date_départ est la date du début et doit être entrée en utilisant la fonction Date.
• Mois est le nombre de mois avant ou après la date indiquée.

Note: Nous devons définir le format Date pour la cellule contenant la fonction FIN.MOIS pour qu'elle affiche la date correctement.

Par exemple, la formule FIN.MOIS(DATE(2019;2;1);1) donne 31/03/2019 ; car ici je veux obtenir la date du dernier jour du mois suivant le mois février, c'est pour cela que j’ai tapé 1 pour le deuxième argument de la fonction FIN.MOIS.

Et si je souhaite obtenir la dernière date du mois en cours (indiqué dans le premier argument) qui est février, j’écrirai 0

FIN.MOIS(DATE(2019 ;2 ;1);0) ce qui donne 28/02/2019

Dans notre cas, nous allons remplacer 2019 par Calendrier!$K$1 pour rendre notre formule dynamique. Voici la formule qui sera utilisée :

FIN.MOIS(DATE(Calendrier!$K$1;2;1);0)

Ensuite, je vais utiliser la fonction JOUR qui va me renvoyer le nombre du jour correspondant à la date renvoyée par la fonction FIN.MOIS de la façon suivante :

JOUR(FIN.MOIS(DATE(Calendrier!$K$1;2;1);0))

Enfin, je vais utiliser la fonction SI pour qu’Excel n’affiche la date du 29^ème jour du février que si l'année sélectionnée est bissextile. C’est-à-dire dans le cas où la fonction JOUR renvoie 29.

Alors, ma formule finale que je vais insérer dans la cellule B30 est la suivante :

SI(JOUR(FIN.MOIS(DATE(Calendrier!$K$1;2;1);0))=29;DATE(Calendrier!$K$1;2;29);"")

Faites maintenant un test et sélectionnez une année bissextile puis une année ordinaire et remarquez ce que ça donnera.

Personnaliser le format date

Voilà, jusqu’ici notre travail sur le calendrier de base est fait, nous allons maintenant passer à la feuille Calendrier pour lier les dates des mois de cette dernière feuille aux dates de la feuille Base tout en procédant à une technique qui va nous permettre d’obtenir cet affichage :

Comme vous le remarquez, les dates ont un format personnalisé. Par exemple la première date du janvier est sous forme de : Mar 01 pour l’année 2019.

Pour obtenir ce format donc, nous allons utiliser une formule qui associera les deux fonctions NOMPROPRE et TEXTE.

Pourquoi ces deux fonctions ? parce que Excel ne dispose pas ; dans son outil Format de cellule ; d’option pour définir un format qui met la première lettre du nom du jour en Majuscule.

Pour cela, nous allons utiliser la fonction  NOMPROPRE, mais, elle exige que son argument soit un texte pour qu’elle fonctionne correctement. Dans ce cas nous devons convertir nos dates en texte, et c’est pour cela encore que nous allons faire appel à la fonction TEXTE.

La fonction TEXTE convertit un nombre au format texte dans un format spécifié. Sa syntaxe est TEXTE(nombre ; format )

Pour nos dates donc, nous allons appliquer le format jjj jj. Par exemple, pour le premier janvier utilisons la formule : TEXTE(Base!A2;"jjj jj")

Base!A2 référence la date du 1^er janvier se trouvant dans la cellule A2 de la feuille Base.

Cette formule renvoie donc ce format de date: mar 01, qui est de type texte.

Et pour afficher la première lettre en majuscule, nous allons utiliser cette formule comme paramètre de la fonction NOMPROPRE de la façon suivante :

NOMPROPRE(TEXTE(Base!A2;"jjj jj"))

Voici le résultat obtenu :

Faisons ensuite, copier-coller de la formule dans les autres cellules.

Sélectionnez une autre année et voyez ce qui va se passer.

Pourquoi ne pas se contenter d’une seule feuille pour notre calendrier au lieu de deux?

La réponse est toute simple : car nos fonctions utilisées dans la feuille Base comme Date, FIN.JOUR ne fonctionneront pas si elles contiennent des données de type texte.

Mettre en forme le calendrier

Nous arrivons maintenant à l’étape où nous allons donner un bon design à notre calendrier :

• Mettez en forme les noms des mois et les dates : Police et taille de police, gras, centrer …
• Choisissez des couleurs de remplissage différentes pour les entêtes des colonnes.
• Vous pouvez aussi définir une couleur de remplissage pour les cellules vides qui se trouvent en bas dans les colonnes des mois de 30 jours.
• Pour la colonne du mois Février, sélectionnez la cellule B32 (consacré à la date du 29^ème jour) et appliquez à elle une mise en forme conditionnelle qui va la remplir de la même couleur de remplissage; définie précédemment; dans le cas où février compte 28 jours.

• Pour ce faire, cliquez sur Mise en forme conditionnelle et choisissez Créer une règle.
• Sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué, puis tapez la formule suivante : =$B$32=""
• Cliquez ensuite sur Format et choisissez Remplissage puis sélectionnez votre couleur de remplissage.
• Validez et fermez l’outil Mise en forme conditionnelle.

Sélectionnez dans un cas une année bissextile, et dans l'autre cas une année ordinaire, pour vérifier si tout fonctionne correctement.

Passons à une étape importante aussi, c’est mettre distinguer le ou les jours du weekend  d’une couleur différente.

Pour cela nous allons appliquer également une mise en forme conditionnelle pour remplir les cellules affichant les dates des weekend d'une couleur différente:

• Sélectionnez la plage A4:A34 de la colonne JANVIER.
• Cliquez sur Mise en forme conditionnelle et choisissez Créer une règle.
• Sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué, puis tapez la formule suivante : =JOURSEM(Base!A2;2)>5 (je considère ici que mon weekend est de deux jours :Samedi et Dimanche)
• Cliquez ensuite sur Format et choisissez Remplissage puis sélectionnez votre couleur de remplissage.
• Validez et fermez l’outil Mise en forme conditionnelle.

Faites la même chose pour les autres colonnes en suivant les étapes précédentes :

• FÉVRIER: la formule utilisée est  =JOURSEM(Base!B2;2)>5 ( N’oubliez pas d’intégrer la cellule B32 dans la plage référencée ).
• MARS: la formule utilisée est  =JOURSEM(Base! C2;2)>5
• AVRIL: la formule utilisée est  =JOURSEM(Base!D2;2)>5
• MAI: la formule utilisée est  =JOURSEM(Base!E2;2)>5
• JUIN: la formule utilisée est  =JOURSEM(Base!F2;2)>5
• JUILLET: la formule utilisée est  =JOURSEM(Base!G2;2)>5
• AOÛT: la formule utilisée est  =JOURSEM(Base!H2;2)>5
• SEPTEMBRE: la formule utilisée est  =JOURSEM(Base!I2;2)>5
• OCTOBRE: la formule utilisée est  =JOURSEM(Base!J2;2)>5
• NOVEMBRE: la formule utilisée est  =JOURSEM(Base!K2;2)>5
• DÉCEMBRE: la formule utilisée est  =JOURSEM(Base!L2;2)>5

J’ai traité un exemple pareil et d'autres exemples concernant l'utilisation de la mise en forme conditionnelle dans cet article : Exemples d’utilisationde la mise en forme conditionnelle

Faites un test maintenant! (Sélectionnez des années différentes!)

Nous avons presque terminé, il nous reste d’insérer des espaces vides entre les colonnes des mois :

• Insérez premièrement des colonnes comme c’est illustré dans cette image :

• Supprimez la mise en forme conditionnelle et réduisez la largeur des colonnes.

• Sélectionnez ces colonnes insérées comme vous l’avez vu dans l’image animée précédente.
• Cliquez sur Mise en forme conditionnelle puis sur Effacer les règles, Effacer les règles des cellules sélectionnées.

• Puis, Cliquez avec le bouton droit sur le premier entête et cliquez sur Largeur de colonne puis tapez 0,5 et validez.

• N’oubliez pas de supprimer aussi les autres couleurs de remplissage qui restent dans ces colonnes.

• Sélectionnez la cellule contant la liste déroulante d’années puis coupez-la et collez-la dans son premier emplacement K1.
• Mettez-la en forme
• Tapez enfin dans la cellule I1 : Année. Choisissez pour elle une taille de police et une couleur.

Vous pouvez ajouter une image d’arrière-plan si vous le désiriez. Cliquez donc sur Mise en page puis sur Arrière-plan et cliquez sur A partir d’un fichier pour sélectionner votre image.

Sous Mise en page également, sélectionnez l'orientation paysage.

Bravo à vous ! votre calendrier est prêt, vous pouvez l'utiliser, l’imprimer ou le partager avec vos amis et proches.

PARTAGEZ PARTAGEZ PARTAGEZ