Créez votre calendrier avec Excel

Dans cet article, nous allons créer un calendrier perpétuel en partant d'un classeur vierge. Cet exercice va faire appel à différentes fonctionnalités de base d'Excel. Nous les avons déjà abordées dans de précédents articles et c'est un bon moyen de vérifier que ces concepts sont maitrisés !

Réalisons un calendrier comme celui-ci-dessous. Le calendrier est dynamique et se met à jour en fonction de l'année saisie : jours de la semaine, fond gris sur les week-ends, affichage ou non du 29 février...

Apercu calendrier perpetuel

 

Liens utiles pour l'exercice

Avant de commencer, voici les liens vers les articles qui seront utiles à la réalisation de ce calendrier :

Pour ceux qui veulent directement la solution, je vous laisse télécharger le fichier et, si vous le souhaitez, suivre le tutoriel ci-dessous.

logo telecharger fichier
 

Créer la structure du calendrier

On commence par préparer la trame. Une cellule pour rentrer l'année, 12 colonnes pour les mois et 31 lignes pour les jours. On numérote les lignes et colonnes. En réalité, on crée 32 lignes, dont 2 avec le numéro 1, pour afficher le nom du mois en en-tête.

Creation structure calendrier

On utilise une seule formule que l'on va dupliquer dans tout le tableau.
On fige la référence de l'année qui ne doit pas bouger. Pour les mois, la ligne est fixe pas la colonne pour copier vers la droite. Inversement pour les jours, colonne fixe, ligne relative.

Generer calendrier fonction DATE

Après copier-coller dans le tableau, voilà le résultat.

Calendrier non formate

On met en forme la 1ère ligne pour afficher uniquement le nom du mois.

Format en-tete calendrier

Dans le reste du calendrier, on affiche le jour de la semaine et le numéro du jour.

Format jours calendrier
 

Identifier les week-ends

Pour isoler les samedis et dimanches, nous pouvons utiliser la fonction JOURSEM.
Ensuite, une mise en forme conditionnelle basée sur le résultat de cette fonction suffit à les mettre en valeur.

On sélectionne tout le tableau puis dans le ruban Accueil > Mise en forme conditionnelle > Nouvelle règle...
On choisit d'utiliser une formule et on entre la suivante :

Mise en forme conditionnelle week-end

  • Il y a deux cas où la condition doit être vraie : samedi et dimanche. On utilise la fonction OU pour prendre en compte ces deux cas.
  • Avec la méthode 2, la fonction renvoie une valeur entre 1 lundi et 7 dimanche. Les valeurs 6 et 7 sont donc celles qui nous intéressent.
  • La formule est entrée pour C10, cellule sélectionnée dans la plage. Cette référence doit rester relative pour être décalée dans le reste du tableau.
  • Pour la mise en forme, j'ai choisi un remplissage gris.

Et voici après validation.

Format calendrier week end
 

Masquer les dates erronées

Comme je vous l'avais annoncé la dernière fois, le calendrier Excel ne marque pas de rupture. Ainsi, le 30 février 2016 'existe' et correspond au 1er mars. Regardez ci-dessous les dates qui ne devraient pas apparaître.

Calendrier dates erronees

Pour les masquer, nous allons utiliser une nouvelle mise en forme conditionnelle.
Nous allons comparer le mois de chaque jour au chiffre en en-tête, le 30 février calculé renvoie bien le 1er mars donc le mois 3 et non le mois 2.
On sélectionne le tableau et on entre la formule suivante :

Mise en forme conditionnelle dates erronees

  • On évalue le mois de la cellule et on compare à l'en-tête de colonne entrée au début. Attention à la ligne absolue pour que la formule marche dans tout le tableau !
  • Pour le format, j'ai choisi de masquer la valeur avec le format de nombre personnalisé : ;;;

Format dates erronees

Dernière chose, pour un rendu impeccable ! On place cette règle en 1ère position et on interrompt si elle est vraie.

Calendrier gestion regles mise en forme conditionnelle

Sinon on risque d'avoir un fond gris sur des cellules vides. Par exemple, le 31 avril/1er mai 2016 tombe un dimanche.

Calendrier mise en forme conditionnelle interrompre si vrai

Pour terminer, j'ai masqué le quadrillage ainsi que la ligne et la colonne d'en-tête que nous avons utilisées pour construire la formule.

Et voilà un calendrier quasi perpétuel, du moins entre 1900 et 9999. Maintenant, il suffit de modifier l'année pour le mettre à jour automatiquement.

Format definitif calendrier excel perpetuel
 

J'espère que cet exemple vous aura été utile et que vous aurez pu le refaire par vous-mêmes. Posez vos questions en commentaires si tout n'est pas clair.

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , .Mettre en favori le Permaliens.

2 réponses à Créez votre calendrier avec Excel

  1. Slem dit :

    Bonjour,
    le calendrier est très bien fait excepté une petite erreur.
    En effet, l’année 1900 n’est pas bissextile et il n’y a pas de 29 février
    Si vous regardez un calendrier des postes de cette année, vous verrez que l’on passe directement du mardi 28 février au jeudi 1er mars.
    Il n’y a pas de mercredi.
    Cordialement

    • Jean-Rémy dit :

      Bonjour,

      Vous avez tout à fait raison, l’année 1900 n’est pas bissextile.
      Pour être complet et pour ceux que ça intéresse, les années bissextiles sont déterminées de la manière suivante (tiré de l’article Wikipédia) :

      • Années multiples de 4 sauf les années multiples de 100 comme 1900
      • Années multiples de 400 comme 2000 par exemple

      Pour revenir au calendrier joint à cet article, il fait apparaître 1900 comme bissextile parce qu’Excel la considère comme tel.
      En réalité, ce bug existe depuis le lancement d’Excel, et même bien avant. Comme l’explique Microsoft sur cette page (traduite automatiquement depuis l’anglais), ce bug date du début de Lotus 1-2-3 dans lequel 1900 était bissextile. Pour assurer la compatibilité des fichiers Lotus dans Excel, Microsoft a conservé cette erreur. Et ne l’a pas modifié depuis pour ne pas fausser tous les anciens fichiers basés sur ce calendrier.

      Cette erreur a, a priori, peu d’impact, travailler avec les 2 premiers mois de 1900 est maintenant plutôt rare. En revanche, corriger l’erreur et donc imposer la correction des fichiers serait beaucoup plus lourd… D’où le choix de maintenir volontairement ce bug.

      Merci en tout cas pour votre remarque pertinente.
      En espérant vous revoir sur le site !

      Bien à vous
      Jean-Rémy

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.