Années bissextiles et calendrier automatique sous Excel

Sous Excel, découvrez comment automatiser un calendrier à l’aide de formules et automatiser l’affichage conditionnels des jours et dates. Et, plus complexe, ajoutez plusieurs formules pour gérer les années bissextiles.

Trois étapes pour créer un calendrier qui gère les années bissextiles

Créez et testez, sous Excel 2010, Excel 2013, Excel 2016 ou Excel 2019 un calendrier automatique qui affiche, sous condition, les 29e, 30e et 31e jours des mois concernés, années bissextiles ou pas.

#1 – Créer la structure du calendrier

Sous Excel, et dans une feuille vierge, saisissez les premiers éléments de votre futur calendrier automatique : à savoir, une cellule pour accueillir l’année et une seconde pour préciser le mois.

Dans l’exemple ci-dessous, les cellules B1 (AnneeCalendrier) et B2 (NumMois) accueillent l’année et le numéro du mois.

>>Nommer les cellules Année et Mois

Utile pour simplifier l’écriture et la recopie des formules, l’option Définir un nom est simple à mettre en œuvre.

  • Sélectionnez la cellule.
  • Ouvrez le menu contextuel (clic-droit).
  • Cliquez sur l’option Définir un nom.
  • Nommez la cellule dans la zone Nom (ici : AnneeCalendrier).
  • Cliquez sur le bouton OK.
  • Nommez, de la même façon, la cellule B2 (NumMois).

Besoin d’aide sur les cellules nommées ? C’est ici…

1-nommer une cellule sous excel - calendrier annees bissextiles

>>Relier la cellule NumMois à une liste de choix

Pratique pour éviter les erreurs de saisie, l’option Liste s’active via la boîte de dialogue Validation des données.

  • Sélectionnez la cellule B2 (nommée NumMois).
  • Activez, sur le Ruban Excel, l’onglet Données.
  • Repérez le groupe de commandes Outils de données.
  • Cliquez sur l’icône Validation des données.
  • Activez, dans l’onglet Options, l’option Liste.
  • Saisissez, dans la zone Source, la chaîne de mois (de 1 à 12) en séparant les nombres par un point-virgule.
  • Cliquez sur le bouton OK pour valider la liste de choix.

#2- Saisir les formules DATE et TEXTE(JOURSEM(DATE…

Tout calendrier mensuel contient, par défaut, au moins 28 jours. La formule de base est donc assez simple à créer. A partir de 29 et jusqu’à 31, la formule doit s’adapter à la date saisie :

  • 29 jours en février quand il s’agit d’une année bissextile ;
  • 30 jours pour les mois d’avril, juin, septembre et novembre ;
  • 31 jours pour les autres mois.

2-liste de validation des donnees - excel calendrier automatique annees bissextiles

>>Afficher les dates automatiques du 1er au 28 de chaque mois

L’affichage des 28 premiers jours de chaque mois repose sur la saisie puis la recopie de la fonction DATE dont les arguments sont =DATE(année;mois;jour).

Pour éviter de créer deux formules au lieu d’une, l’option la plus simple est privilégiée. Elle s’applique aux cellules B1 à B3 (du 1er au 28e jour du mois).

  • La formule débute par =DATE(
  • La cellule nommée AnneeCalendrier correspond à l’argument ANNEE;
  • NumMois reprend le numéro du MOIS choisi dans la Liste de choix de la cellule B2.
  • L’argument JOUR se trouve en colonne A (à partir de A4 à A31).

3- saisir formule date

>>Afficher le jour de la semaine (en jour tronqué : lun, mar, mer…)

Il s’agit ici d’imbriquer les formules TEXTE, JOURSEM et DATE.

La formule =TEXTE repose sur deux arguments VALEUR et Format_texte

  • VALEUR ne pouvant être renseigné, la formule JOURSEM est appliquée
  • Format_texte précise le format d’affichage retenu (ici : « jjj » pour les 3 premières lettres de chaque jour de semaine)

La formule =JOURSEM repose sur les arguments Numéro_de_série et Type_retour

  • Numéro_de_série s’appuie sur la formule DATE
  • Type_retour renvoie un chiffre entre 1 (dimanche) et 7 (samedi) désignant le jour de la semaine. Le numéro de « retour » est ici 1

La formule =DATE est identique à celle décrite précédemment : ANNEE, MOIS, JOUR

  • ANNEE correspond ici à la cellule nommée AnneeCalendrier
  • MOIS s’appuie sur la cellule nommée NumMois
  • JOUR affiche le numéro de jour de 1 à 28 saisi de A4 à A31

Ce qui donne : =TEXTE(JOURSEM(DATE(AnneeCalendrier;NumMois;A4);1); »jjj »)

#3- Saisir les formules de gestion des années bissextiles et des mois de 30 et/ou 31 jours

L’idée ici est d’afficher le numéro de jour, le jour et le nom du jour toute l’année sauf en février quand il s’agit d’une année bissextile.

>>Gérer l’affichage du 29e jour du mois

En A32, la formule imbrique SI, SI(OU(MOD…ET(MOD… MOD… Pour mémoire, Excel résoud les formules par palier. S’il peut résoudre le premier palier, il s’arrête…

=SI(NumMois<>2;A31+1;SI(OU(MOD(AnneeCalendrier;400)=0;ET(MOD(AnneeCalendrier;4)=0;MOD(AnneeCalendrier;100)<>0));A31+1; » »))

  • =SI( vérifie le mois. S’il est différent de 2 (février) ; ALORS Excel ajoute 1 jour au numéro inscrit en A31 ; SINON est placé à la fin de la formule.
  • La formule SI(OU(MOD(AnneeCalendrier;400)=0;ET(MOD(AnneeCalendrier;4)=0;MOD(AnneeCalendrier;100)<>0));A31+1 affiche ou masque le 29e jour des mois de février
  • Enfin, l’argument SINON « » (guilemets vides) correspond au 3e argument de la formule =SI initiale

4- saisir formule imbriquee texte-joursem-date

En B32, la formule repose sur le même postulat : la date s’inscrit si et seulement s’il ne s’agit pas d’une année bissextile.

 

En C32, la formule vérifie, là encore, s’il s’agit d’une année bissextile et affiche alors le 29e jour. La répétition de la formule TEXTE est nécessaire pour afficher le 29e jour quand le mois contient 30 ou 31 jours.

6- formule imbriquee jour annee bissextile

>>Gérer l’affichage du 30e jour du mois

En A33, une formule conditionnelle affiche le nombre 30 quand NumMois est différent de 2 (février). La même logique s’applique à la cellule B33. Mais la cellule C33 contient, quant à elle, une formule qui imbrique une condition (SI) et la formule imbriquée TEXTE(JOURSEM(DATE décrite précédemment.

9-10-11- formule calendrier automatique mois avril etc>>Gérer l’affichage du 31e jour du mois, quand il y en a…

En A34, une formule imbriquée SI(OU analyse le numéro du mois de la cellule B2 nommée NumaMois. Le nombre 31 s’applique en mars, mai, juillet, août, octobre et décembre.

En B34 et C34 les formules affichent également la date et le jour de semaine si le mois saisi correspond à la condition fixée (mois de 31 jours).

12- formule calendrier automatique mois de 31 jours#3- Masquer le message d’erreur renvoyé dans le supposé 29e « jour » de février des années non bissextiles

Plusieurs techniques sont possibles, mais la plus simple et là encore privilégiée. Elle repose sur une Mise en forme conditionnelle. Pour l’appliquer :

  • Réglez votre calendrier sur une année non bissextile (ex. 2021) et sur le mois de février (2). Un message d’erreur #VALEUR ! apparaît car Excel ne trouve pas la valeur 29 en cellule A32. C’est normal. Comme la formule SI(ESTVIDE ne peut être utilisée (la cellule contient une formule, dont elle n’est pas vide), il faut ruser.
  • Sélectionnez (1) la cellule C32, activez l’onglet ACCUEIL puis repérer le groupe Styles.
  • Cliquez sur l’icône Mise en forme conditionnelle puis sur le menu Nouvelle règle (2).
  • Sélectionnez l’option Appliquer une mise en forme uniquement aux cellules qui contiennent (3).
  • Repérer la zone Modifier la description de la règle et choisir Erreurs (4).
  • Cliquez sur Format et modifiez la couleur de la police (passer de Noir à Blanc) (5) puis fermez la boîte de dialogue
  • Enfin, cliquez sur OK pour valider la règle. Les seules fois où le message d’erreur #Valeur s’affiche, la mise en forme conditionnelle s’applique.

13- mise en forme conditionnelle pour masquer  #valeur

BONUS : le saviez-vous ? Il est possible de masquer les cellules contenant les numéros des jours, sans masquer la colonne d’accueil !

Pas à pas de l’astuce : sélectionnez les cellules à « masquer », et appliquez-leur le Format personnalisé de Type ;;;

5- masquer le contenu des cellules inutiles sans masquer la colonne