↑ Retour à MEFC

Imprimer ceci Page

Date en surbrillance

Les dates sont des indicateurs couramment utilisées dans les feuilles de calculs. Grâce aux fonctions Date d’Excel, il est possible de réaliser des calculs d’addition ou de soustraction et ainsi, réaliser des tableaux automatisés ou semi-automatisés (en utilisant la fonction MAINTENANT notamment).

En combinant les fonctions Date avec les mises en forme conditionnelles, vous pouvez dès lors concevoir des tableaux affichant les seuils d’alertes automatiquement quand une date d’échéance approche.

Navigation

Les mises en forme conditionnelles de base

Dans les options des mises en forme conditionnelles d’Excel (menu Accueil / Mise en forme conditionnelle / Règle de mise en surbrillance > Une date se produisant) vous pouvez affichez d’une couleur différente les cellules qui correspondent à un certain écart avec la date du jour.

Mise en forme conditionnelle

Menu – Mise en forme conditionnelle

Menu - Mise en forme conditionnelleLes options de date proposées par Excel sont :

  • Hier
  • Aujourd’hui
  • Demain
  • Dans les 7 derniers jours
  • La semaine dernière
  • Cette semaine
  • Semaine prochaine
  • Le mois dernier
  • Ce mois
  • Le prochain mois

Comme vous le voyez, les options standards sont toutes autour de la date du jour et ne vous permettent pas de colorier des événements au-delà de 1 mois. Donc, pour concevoir des alertes autres que celles proposées, vous allez devoir créer vos propres règles de mise en forme.

Mise en forme sur les week-ends

Quand vous concevez un calendrier mensuel automatique, il ne vous est pas nécessaire de colorier les week-ends après chaque mise à jour. En utilisant les mises en forme conditionnelles, vous pouvez automatiquement modifier les couleurs des week-ends. Prenons l’exemple du calendrier suivant où tous les calculs dépendent de la cellule B5.

Calendrier automatique

Pour modifier la couleur pour les week-ends, vous allez ouvrir le menu Mise en forme conditionnelle > Nouvelle règle MEFC_Date_4

Dans la boîte de dialogue qui s’affiche à l’écran, vous sélectionnez le menu Utiliser une formule pour déterminer pour quelle cellule le format sera appliqué.

MEFC_Date_5

Dans la zone « Appliquer une mise en forme » vous allez saisir la formule JOURSEM pour déterminer si le jour de la cellule est un samedi (6) ou un dimanche (7). Attention, ici il ne faut bloquer que la référence de la ligne alors il ne faut positionner le symbole $ uniquement que devant le 5.

=JOURSEM(B$5;2)>5

Formule sur la semaine

Ensuite, personnalisez le format en cliquant sur le bouton format et choisissez une couleur de remplissage (orange dans le cas présent)

Couleur de remplissage

Après validation, ouvrez le menu Mise en forme conditionnelle > Gérer les règles MEFC_Date_8

Sélectionnez si besoin l’option Cette Feuille de calcul au lieu de l’option par défaut Sélection. Dans la zone S’applique à, modifier la plage qui correspond à votre sélection initiale lors de la création de votre règles pour l’étendre à toute la colonne.

MEFC_Date_9

 

Changez les paramètres des mois ou des années dans le document ci-dessous et votre tableau coloriera systématiquement les week-ends.

 

Les jours fériés.

Pour enrichir votre classeur précédent en changeant la couleur des jours fériés, vous devez avoir dans un coin de votre classeur (mais pas nécessairement dans la même feuille) les jours fériés de votre pays. Nous avons en colonne AH les jours fériés de la France en relation avec l’année en B2.

Nous allons de nouveau activer l’option Formule personnalisée du menu Mise en forme conditionnelle pour utiliser la formule NB.SI :

=NB.SI($AH$4:$AH$16;B$5)

MEFC_Date_11

Puis, dans la fenêtre de gestion des règles, nous sélectionnons de nouveau la plage B4:AF11. Et pour mettre en avant les jours fériés au-dessus des week-ends, nous remontons la règle des jours ouvrés en première position.

 

Afficher les délais

Dans le cas où nous voulons modifier la couleur de nos cellules en fonction de l’approche d’une date, là encore, nous allons utiliser les mises en forme conditionnelles pour réaliser ce travail à notre place.

Dans le document qui suit, nous voulons afficher

  • en jaune les dates comprises entre 1 et 2 mois
  • en orange, les dates comprises entre 2 et 3 mois
  • en mauve, les dates supérieures à 3 mois

Nous allons alors construire 3 règles de mises en forme conditionnelles en utilisant la formule DATEDIF. Avec respectivement pour les 3 cas les formules suivantes :

  • =DATEDIF($B2;$E$2; »m »)>0
  • =DATEDIF($B2;$E$2; »m »)>1
  • =DATEDIF($B2;$E$2; »m »)>2

Nuances de couleurs

Plutôt que de choisir une couleur fixe différente pour chacun de nos délais, nous allons travailler avec l’option de nuances de couleurs pour colorier de façon nuancée nos cellules.

Tout d’abord, nous allons dans une nouvelle colonne (colonne E), calculer l’écart en nombre de jours dans une même année avec de nouveau la formule DATEDIF et le paramètre « yd ».

=DATEDIF($D2;$G$2; »yd »)

Ensuite, nous allons choisir dans le menu Mise en forme conditionnelle > Nouvelle règle l’option Mettre en forme toutes les cellules d’après leur valeur et choisissez les options suivantes :

  • Echelle 3 couleurs
  • Nombre minimal, 0 de couleur rouge
  • Nombre médian, 10 de couleur jaune
  • Nombre maximal, 30 de couleur blanc

MEFC_Echelle_3_couleurs

Le résultat est un dégradé du blanc au rouge en passant par le jaune en nuançant les couleurs jusqu’à atteindre les limites 0 et 10.

Lien Permanent pour cet article : http://www.excel-exercice.com/mefc/date-en-surbrillance/

3 comments

  1. Anonyme

    Bonjour, est-il possible de mettre en surbrillance des dates déjà passées avec la formule datedif? merci.

    1. Anonyme

      Sans mettre la colonne écart.

  2. Shitoryu

    Bonjour,
    Peut-on mettre en couleur un lundi sur deux avec un calendrier automatique ?
    Merci beaucoup,
    Shitoryu

Laisser un commentaire