Bienvenue en 2023. Qui dit nouvelle année, dit nouveaux fichiers Excel pour son activité ou ses besoins personnels. Parmi les fichiers qui n’échappent pas à ce renouvellement, il y a le calendrier. Ce dernier peut servir à différentes tâches.
Plutôt que repartir de zéro, je vous propose un calendrier qui sera utilisable pour toutes les futures années.
C’est parti !
Les en-têtes
Colonnes : mois de l'année
Dans un précédent article, nous avons vu la fonction SEQUENCE. Vous pensez sûrement qu’elle sera utilisée pour afficher les mois de l’année. Hé bien, ce n’est pas possible car il y aura 2 colonnes sur Excel pour chaque mois. Or, pour que SEQUENCE fonctionne correctement, les cellules doivent être adjacentes.
Nous allons donc faire autrement.
Tout d’abord, en cellule A1, on va indiquer l’année du calendrier. C’est important car ça va nous servir pour plus tard. Ensuite, on passe directement en C1. Dans cette cellule, on va faire une formule :
=DATE($A$1;1;1)
Ainsi, on obtient 1/1/2022 si la cellule est au format date courte (on le modifiera plus tard).
Pour les 11 mois restants, on va utiliser la fonction Excel MOIS.DECALER.
Pour notre exemple, cela donne :
=MOIS.DECALER(C1;1)
Nous pouvons transformer le format de ces cellules avec le format personnalisé mmm.
Sélectionnez les cellules C1 à Y1 (c’est plus rapide de sélectionner avec les cellules qui resteront vides plutôt que des Ctrl+Clic avec seulement les cellules contenant un en-tête).
Faites un clic-droit puis Format de cellule
Dans la nouvelle fenêtre de dialogue, allez dans la catégorie Personnalisée puis tapez mmm dans le champ Type et validez en cliquant sur OK.
On obtient les en-têtes de colonne avec l’abréviation de chaque mois. Tant qu’on y est, mettons un peu en forme ces en-têtes. Faites comme bon vous semble de votre côté.
On peut maintenant passer aux en-têtes de ligne.
Lignes : jours du mois
Pour éviter de saisir les nombres des jours de chaque mois, on va créer une suite de valeurs grâce à la fonction SEQUENCE. Or, avant cela, il nous faut connaître le nombre de jours pour chaque mois.
Dans la cellule C2, cherchons le dernier jour du mois avec la fonction FIN.MOIS.
On obtient :
=FIN.MOIS(C1;0)
Recopier cette formule sous chaque en-tête. Par défaut, les cellules sont au format standard (un nombre), si vous le souhaitez, vous pouvez transformer au format Date courte mais cela a en réalité peu d’importance car c’est juste une ligne intermédiaire qu’on masquera après.
Maintenant qu’on a le 1er et dernier jour du mois, on peut calculer le nombre de jours.
On va utiliser la fonction JOURS.
Comme la fonction ne compte pas le dernier jour, il faut ajouter +1 à notre formule.
On obtient :
=JOURS(C2;C1)+1
Recopier de nouveau pour chaque colonne contenant un en-tête.
On a tout ce qu’il nous faut pour créer notre SEQUENCE.
Voici la formule pour la cellule B4 :
=SEQUENCE(C3;1;C1)
On peut modifier le format de cellule pour indiquer uniquement le nombre du jour : JJ.
Sélectionnez toutes les cellules contenant la formule SEQUENCE puis Clic-droit Format de cellule
Dans la catégorie Personnalisée, saisissez JJ dans le champ Type puis faites OK.
La mise en forme
Avant de copier la colonne B et C à l’ensemble du tableau, au temps mettre en forme ces 2 colonnes :
- j’adapte la largeur des colonnes
- j’ajoute des bordures
Maintenant, je sélectionne les cellules B4 à C34 et je copie (Ctrl+C ou Clic-droit>Copier)
Je sélectionne les cellules D4 à Y34 puis Clic-droit > collage spécial > Conserver les largeurs de colonnes sources.
Avec cette méthode, non seulement vous recopiez la formule de calcul mais aussi la mise en forme (largeur de colonne et bordures).
Nous en avons fini avec la structure du fichier. On peut passer au contenu.
Dans un calendrier annuel, nous avons toujours certains jours qui sont mis en valeur :
- les week-end
- les jours fériés
Les week-end
Il existe plusieurs manières d’afficher les week-end sur un calendrier. J’ai opté sur une méthode combinant formule de calcul et mise en forme conditionnelle.
Pourquoi faire cela ?
En fait, cela m’évitera beaucoup de problèmes ultérieurement quand je ferai une analyse de son contenu (nous verrons cela dans de prochains articles).
On va utiliser la fonction JOURSEM.
Dans notre exemple, nous voulons distinguer les samedis et dimanches. Pour l’argument type_retour, on va utiliser l’organisation traditionnelle d’une semaine (lundi à dimanche) soit 2. On obtient dans la formule en C4 :
=JOURSEM(B4;2)
Excel nous indique le n° de jour dans la semaine.
2ème étape : indiquer qu’on est le week-end s’il trouve 6 (samedi) ou dimanche (7). On utilise un simple SI. Comme il y a deux conditions, je vais imbriquer avec la fonction OU.
=SI(OU(JOURSEM(B4;2)=6;JOURSEM(B4;2)=7);"W-E";"")
On peut recopier cette formule, à l’ensemble des cellules vides de notre tableau.
On peut constater un bug en fin de mois. Quand il n’y a pas le n° du jour, Excel indique qu’il s’agit d’un weekend.
Pour contrer cela, on va ajouter un SI la cellule est vide. On pourrait la mettre sur toutes les cellules, mais on peut tout aussi bien la mettre sur les lignes 32 à 34 uniquement car nous n’aurons jamais de problème du 1er au 28e jour du mois.
=SI(B4="";"";SI(OU(JOURSEM(B4;2)=6;JOURSEM(B4;2)=7);"W-E";""))
Mise en forme conditionnelle
Soyons honnêtes, écrire W-E n’est pas visuellement agréable sur l’ensemble du calendrier.
On va donc appliquer une mise en forme conditionnelle si la valeur de la cellule est égale à W-E.
Pour cela, sélectionnez tout le tableau (ce n’est pas grave si vous prenez aussi les colonnes avec les n° des jours).
Dans le ruban Accueil, cliquez sur Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Égal à…
Dans la boîte de dialogue, renseignez W-E dans le champ de gauche puis sélectionnez Format personnalisé… dans le champ de droite.
Dans la nouvelle fenêtre, appliquez une même couleur pour la police et le remplissage de la cellule. Faites OK jusqu’à revenir sur la feuille de calcul.
Les jours fériés
Maintenant, on va afficher les jours fériés à l’intérieur du calendrier.
Base des jours fériés
Le plus simple est de créer une base dans une feuille à part pour recenser tous les jours fériés en France.
Créons une nouvelle feuille appelée Base. Saisissez le tableau comme ci-dessous.
Maintenant, transformez-le en tableau structuré qu’on renomme Base_jrferies. Ça nous simplifiera notre tâche pour nos futures formules de calcul.
Maintenant, on va calculer dans la colonne B les jours fériés pour l’année. Il existe deux types de jours fériés :
- fixes quelque soit l’année
- variables en fonction du jour de Pâques
Jours fériés fixes
Pour ces jours, on va utiliser une simple formule avec la fonction DATE. car nous connaissons déjà l’année : la cellule A1 de la feuille calendar.
| Jour | Formule |
|---|---|
|
Jour de l'An |
=DATE(calendar!$A$1;1;1) |
|
Fête du travail |
=DATE(calendar!$A$1;5;1) |
|
Armistice 39/45 |
=DATE(calendar!$A$1;5;8) |
|
Fête nationale |
=DATE(calendar!$A$1;7;14) |
|
Assomption |
=DATE(calendar!$A$1;8;15) |
|
Toussaint |
=DATE(calendar!$A$1;11;1) |
|
Armistice 14/18 |
=DATE(calendar!$A$1;11;11) |
|
Noël |
=DATE(calendar!$A$1;12;25) |
Jours fériés variables
Tout est basé sur une date : Pâques. Or, cette date n’est jamais la même tous les ans.
Il existe 2 formules possibles :
=PLANCHER(JOUR(MINUTE(Annee/38)/2+56)&"/5/"&Annee;7)-34 =ARRONDI(DATE(Annee;4;MOD(234-11*MOD(Annee;19);30))/7;0)*7-6
en remplaçant Annee par la cellule correspondante sur notre classeur Excel.
| Jour | Formule |
|---|---|
|
Pâques |
=PLANCHER(JOUR(MINUTE(calendar!A1/38)/2+56)&"/5/"&calendar!A1;7)-34 =ARRONDI(DATE(calendar!A1;4;MOD(234-11*MOD(calendar!A1;19);30))/7;0)*7-6 |
|
Lundi de Pâques |
=$B$3+1 |
|
Ascension |
=$B$3+39 |
|
Pentecôte |
=$B$3+49 |
|
Lundi de Pentecôte |
=$B$3+50 |
Ajout des jours fériés dans le calendrier
Dans nos formules de calcul, nous avons actuellement
=SI(B4="";"";SI(OU(JOURSEM(B4;2)=6;JOURSEM(B4;2)=7);"W-E";""))
Il va falloir ajouter la partie concernant les jours fériés. Maintenant, je vous pose une question :
Si un jour férié tombe durant un week-end, vous préférez laisser la mise en forme conditionnelle d’un week-end ou mettre en priorité une mise en forme spécifique au jour férié ?
Personnellement, je préfère mettre en priorité le jour férié.
Maintenant, réalisons la partie de formule concernant les jours fériés. Pour chaque jour du calendrier, on va regarder si la date apparaît dans notre base de jours fériés.
A cet effet, on va imbriquer deux fonctions : SI et NB.SI. En les cumulant, on peut afficher un texte particulier si un jour férié est trouvé :
SI(NB.SI(Base_jrferies[Date];B4);"Férié")
la formule finale de B4 devient :
=SI(B4="";"";SI(NB.SI(Base_jrferies[Date];B4);"Férié";SI(OU(JOURSEM(B4;2)=6;JOURSEM(B4;2)=7);"W-E";"")))
Recopier la formule sur toute la colonne et formule + mise en forme sur le reste du tableau comme je vous ai montré précédemment.
Mise en forme conditionnelle
Comme pour les week-end, on va appliquer une mise en forme particulière aux jours fériés.
Choisissez une autre couleur pour ce type de jour. Personnellement, je préfère choisir une couleur de police différente de la couleur de remplissage.
Ça y est, nous en avons fini.
Vous pourrez remplacer les cellules vides contenant notre formule de calcul par ce que vous voulez en fonction de vos besoins.
Pensez bien à garder une version de ce fichier comme modèle pour le réutiliser les années suivantes.
Je profite de l’occasion pour vous souhaiter mes meilleurs voeux pour cette nouvelle année !
Ping : Excel : suivre ses congés et RTT - May6