Créer un calendrier perpétuel Excel : La formule magique pour ne plus jamais vous tromper !

Qu'est-ce qu'un calendrier perpétuel Excel et pourquoi en créer un ?

Un calendrier perpétuel Excel est un outil qui affiche un calendrier pour n'importe quelle année. Contrairement à un calendrier statique, il se met à jour automatiquement en fonction de l'année que vous entrez. C'est un outil précieux pour la planification, la gestion de projet, ou simplement pour avoir une vue d'ensemble des dates importantes.

Les avantages d'un calendrier perpétuel Excel :

• Gain de temps : Plus besoin de refaire votre calendrier chaque année.
• Précision : Évite les erreurs de dates et de jours de la semaine.
• Personnalisation : Adaptez le calendrier à vos besoins (jours fériés, événements, etc.).
• Professionnalisme : Impressionnez vos collègues avec un outil Excel sophistiqué.
• Centralisation : Regroupez toutes vos dates importantes en un seul endroit.

Étape 1 : Préparer votre feuille de calcul Excel

Avant de commencer à écrire des formules, il est important de structurer votre feuille de calcul Excel.

1. Ouvrez Excel et créez une nouvelle feuille de calcul.
2. Nommez la feuille (par exemple, "Calendrier Perpétuel").
3. Créez une cellule pour l'année : Dans la cellule A1, tapez "Année :". Dans la cellule B1, entrez l'année de votre choix (par exemple, 2024). Cette cellule sera la référence pour toutes nos formules. Vous pouvez la mettre en forme pour la rendre plus visible (couleur, police, etc.).
4. Créez l'en-tête des jours de la semaine : Dans la ligne 3, à partir de la colonne B, entrez les jours de la semaine : "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi", "Dimanche". Vous pouvez abréger les jours si vous le souhaitez (Lun, Mar, Mer, etc.).
5. Préparez les lignes pour les dates : Réservez 6 lignes sous les jours de la semaine (lignes 4 à 9). Cela permettra d'afficher tous les jours de chaque mois, même ceux qui commencent un dimanche et se terminent un samedi.

Étape 2 : La formule magique pour afficher les dates

C'est ici que la magie opère ! Nous allons utiliser une formule Excel pour calculer et afficher les dates de chaque mois en fonction de l'année entrée dans la cellule B1.

La formule principale :

La formule que nous allons utiliser est une combinaison des fonctions DATE, JOURSEM, et SI. Elle peut paraître complexe au premier abord, mais nous allons la décortiquer ensemble.

Voici la formule pour la cellule B4 (le premier jour du calendrier) :

=SI(JOURSEM(DATE($B$1;MOIS(DATEVALEUR("1/"&1&$B$1));1);2)=1;DATE($B$1;MOIS(DATEVALEUR("1/"&1&$B$1));1);"")

Explication de la formule :

• DATE($B$1;MOIS(DATEVALEUR("1/"&1&$B$1));1) : Cette partie de la formule crée une date correspondant au premier jour du mois de janvier de l'année spécifiée en B1. MOIS(DATEVALEUR("1/"&1&$B$1)) permet de récupérer le numéro du mois (1 pour janvier). DATE($B$1;MOIS(...);1) construit ensuite la date complète.
• JOURSEM(DATE($B$1;MOIS(DATEVALEUR("1/"&1&$B$1));1);2) : Cette partie détermine le jour de la semaine du premier jour du mois de janvier (1 pour lundi, 2 pour mardi, ..., 7 pour dimanche). L'argument 2 dans JOURSEM spécifie que la semaine commence le lundi.
• SI(JOURSEM(...)=1;DATE(...);"") : Cette fonction SI vérifie si le premier jour du mois de janvier est un lundi. Si c'est le cas, elle affiche la date du premier jour du mois (calculée par DATE(...)). Sinon, elle affiche une chaîne vide (""), ce qui signifie que la cellule reste vide.

Comment l'adapter pour les autres cellules :

Pour les cellules suivantes (C4, D4, etc.), nous devons ajouter 1 à la date précédente. Par exemple, la formule pour la cellule C4 sera :

=SI(B4="";"";SI(MOIS(B4+1)=MOIS(DATEVALEUR("1/"&1&$B$1));B4+1;""))

Explication de la formule :

• SI(B4="";"";...) : Cette partie vérifie si la cellule précédente (B4) est vide. Si c'est le cas, la cellule actuelle reste également vide.
• SI(MOIS(B4+1)=MOIS(DATEVALEUR("1/"&1&$B$1));B4+1;"") : Cette partie vérifie si le mois de la date précédente + 1 jour est le même que le mois de janvier. Si c'est le cas, elle affiche la date précédente + 1 jour. Sinon, elle affiche une chaîne vide, ce qui signifie que nous avons atteint la fin du mois.

Copiez et collez les formules :

1. Entrez la première formule dans la cellule B4.
2. Entrez la seconde formule dans la cellule C4.
3. Copiez la formule de la cellule C4 et collez-la dans les cellules D4 à H4.
4. Pour les lignes suivantes (5 à 9), adaptez les formules en remplaçant B4 et C4 par les cellules correspondantes de la ligne précédente (par exemple, B5 et C5). La logique reste la même.
5. Copiez et collez les formules pour tous les mois de l'année, en modifiant le numéro du mois dans la fonction DATEVALEUR.

Important : N'oubliez pas de verrouiller la cellule B1 (l'année) avec des $, comme dans $B$1, pour que la référence à l'année reste fixe lorsque vous copiez les formules.

Étape 3 : Mettre en forme votre calendrier

Maintenant que les dates s'affichent, il est temps de mettre en forme votre calendrier pour le rendre plus lisible et agréable à utiliser.

Mettre en forme les dates :

1. Sélectionnez toutes les cellules contenant les dates (B4 à H9 pour le premier mois).
2. Cliquez avec le bouton droit et choisissez "Format de cellule...".
3. Dans l'onglet "Nombre", choisissez la catégorie "Date".
4. Sélectionnez un format de date qui affiche uniquement le jour du mois (par exemple, "1").

Masquer les zéros :

Pour éviter d'afficher des zéros dans les cellules vides, vous pouvez utiliser un format de cellule personnalisé.

1. Sélectionnez toutes les cellules contenant les dates.
2. Cliquez avec le bouton droit et choisissez "Format de cellule...".
3. Dans l'onglet "Nombre", choisissez la catégorie "Personnalisée".
4. Dans le champ "Type", entrez le format suivant : 0;;. Cela affichera les nombres positifs sans décimale, masquera les nombres négatifs et les zéros.

Ajouter des bordures et des couleurs :

Pour améliorer la lisibilité, vous pouvez ajouter des bordures aux cellules et utiliser des couleurs pour distinguer les jours de la semaine ou les week-ends.

1. Sélectionnez toutes les cellules du calendrier.
2. Dans l'onglet "Accueil", utilisez les outils de mise en forme (bordures, couleurs de remplissage, etc.) pour personnaliser votre calendrier.

Mettre en évidence les week-ends :

Vous pouvez utiliser la mise en forme conditionnelle pour mettre en évidence les week-ends (samedi et dimanche).

1. Sélectionnez toutes les cellules contenant les dates.
2. Dans l'onglet "Accueil", cliquez sur "Mise en forme conditionnelle" > "Nouvelle règle...".
3. Choisissez "Utiliser une formule pour déterminer quelles cellules seront mises en forme".
4. Entrez la formule suivante : =OU(JOURSEM(B4;2)=6;JOURSEM(B4;2)=7) (en remplaçant B4 par la première cellule de votre sélection).
5. Cliquez sur "Format..." et choisissez une couleur de remplissage pour les week-ends.

Étape 4 : Personnaliser votre calendrier avec les jours fériés

Un calendrier perpétuel ne serait pas complet sans la possibilité d'afficher les jours fériés. Voici comment faire :

1. Créez une nouvelle feuille de calcul nommée "Jours Fériés".
2. Dans la colonne A, entrez les dates des jours fériés pour l'année en cours (par exemple, 01/01/2024, 01/05/2024, etc.).
3. Dans la colonne B, entrez le nom du jour férié (par exemple, "Jour de l'An", "Fête du Travail", etc.).

Utiliser la fonction RECHERCHEV pour afficher les jours fériés :

Dans votre feuille "Calendrier Perpétuel", vous pouvez utiliser la fonction RECHERCHEV pour rechercher les dates des jours fériés dans la feuille "Jours Fériés" et afficher le nom du jour férié correspondant.

Par exemple, dans la cellule B10 (sous la date B4), entrez la formule suivante :

=SIERREUR(RECHERCHEV(B4;'Jours Fériés'!$A:$B;2;FAUX);"")

Explication de la formule :

• RECHERCHEV(B4;'Jours Fériés'!$A:$B;2;FAUX) : Cette partie recherche la date de la cellule B4 dans la colonne A de la feuille "Jours Fériés". Si elle trouve une correspondance, elle renvoie le nom du jour férié correspondant (colonne B).
• SIERREUR(RECHERCHEV(...);"") : Cette fonction SIERREUR gère les erreurs. Si la date n'est pas un jour férié, la fonction RECHERCHEV renvoie une erreur. La fonction SIERREUR remplace l'erreur par une chaîne vide (""), ce qui signifie que la cellule reste vide.

Copiez et collez cette formule dans toutes les cellules sous les dates pour afficher les jours fériés correspondants.

Conseils et astuces pour un calendrier perpétuel Excel parfait

• Utilisez des noms définis : Pour faciliter la lecture et la maintenance de vos formules, vous pouvez définir des noms pour les cellules ou les plages de cellules que vous utilisez fréquemment (par exemple, "Annee" pour la cellule B1). Pour ce faire, sélectionnez la cellule, allez dans l'onglet "Formules", puis cliquez sur "Définir un nom".
• Automatisez la mise à jour des jours fériés : Vous pouvez créer une macro VBA pour automatiser la mise à jour des jours fériés en fonction de l'année sélectionnée. Cela vous évitera de devoir modifier manuellement la liste des jours fériés chaque année.
• Protégez votre feuille de calcul : Pour éviter que des utilisateurs modifient accidentellement vos formules, vous pouvez protéger votre feuille de calcul en allant dans l'onglet "Révision", puis en cliquant sur "Protéger la feuille".
• Explorez les modèles de calendrier Excel : Si vous manquez de temps ou si vous préférez partir d'une base existante, vous pouvez explorer les modèles de calendrier Excel disponibles en ligne. De nombreux modèles gratuits ou payants sont disponibles et peuvent être adaptés à vos besoins.
• Pensez à Google Sheets : Les mêmes principes et formules peuvent être appliqués à Google Sheets, vous permettant de créer un calendrier perpétuel accessible et partageable en ligne.

Erreurs courantes à éviter

• Oublier de verrouiller les références de cellules : Assurez-vous d'utiliser des références absolues ($B$1) pour les cellules qui doivent rester fixes lorsque vous copiez les formules.
• Se tromper dans les formules : Vérifiez attentivement vos formules avant de les copier et de les coller. Une petite erreur peut entraîner des résultats incorrects.
• Ne pas tester votre calendrier : Testez votre calendrier avec différentes années pour vous assurer qu'il fonctionne correctement et qu'il affiche les bonnes dates.
• Négliger la mise en forme : Une mise en forme claire et lisible est essentielle pour rendre votre calendrier facile à utiliser.

En suivant ces conseils et en évitant ces erreurs, vous serez en mesure de créer un calendrier perpétuel Excel performant et adapté à vos besoins.