Date en surbrillance


Les dates sont des indicateurs couramment utilisés dans les feuilles de calculs et quand des dates importantes doivent être signalées, il est important de les mettre en surbrillance.

Grâce aux fonctions Date d'Excel, il est possible de réaliser des calculs d'addition ou de soustraction et ainsi, de 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 concevoir des tableaux qui affichent les seuils d'alertes automatiquement lorsqu'une date d'échéance approche.

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.

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

  • 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

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


 

 

 

 

 

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  faut bloquer uniquement la référence de la ligne alors il ne faut positionner le symbole $ que devant le 5.

=JOURSEM(B$5;2)>5

Formule sur la semaine

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

 

 

 

 

 

 

 

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ègle 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 sur 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.ENS :

=NB.SI.ENS($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 d'Excel 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 nuance 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 nous rendons dans le menu Mise en forme conditionnelle > Nouvelle règle, l'option Mettre en forme toutes les cellules d'après leur valeur et choisissons 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.

Articles complémentaires


Voici une liste d'articles qui pourrait également vous intéresser sur le même thème.

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


(28 commentaires)

Passer au formulaire de commentaire

    • Laurent CHAUVIER on 25/07/2018 at 18:23
    • Répondre

    Bonjour

    Je voudrais en colonne a une date, en colonne B une date, et qui la date en B est antérieure à la date en A elle se mette en surbrillance.

    Quel format de date dois-je employer ?
    Quel est le process pour mettre ce format conditionnel sur Excel 2007.

    Merci de votre concours

  1. Bonjour,
    J'aimerai mettre en couleur sur un calendrier excel un jour de la semaine "le mardi"1 fois toutes les deux semaines. je n'arrive pas à mettre en application cette formule.
    Pourriez-vous m'aider svp?
    Bien cordialement

  2. Bonjour,

    Je dois mettre en surbrillance une cellule avec oui ou non si sur ma cellule suivante figure une date: j'explique.

    La cellule B2 va afficher oui ou non j'ai indiqué une date dans la cellule B3 (date inf ou egale a aujourd'hui).

    Merci

  3. bonjour,
    je vous expose mon problème
    tous les ans, je doit visité un nombre important de personnes âgées, plus de 200, je voudrais pouvoir, en renseignant la date de la visite dans mon tableau Excel, modifier automatiquement la couleur de la case de la date de visite (pour pouvoir faire un tri par couleur) et comptabilisé le nombre de visite faites par années, dans une autre case...
    je fais ces opérations actuellement de façon manuelle est-t-il possible de réaliser ce que je souhaite ?
    Cordialement, Fred

    1. Bonjour,
      C'est possible mais c'est un travail à faire dans le cadre d'un projet (donc facturation).
      Si vous pensez que l'automatisation de votre classeur peut vous faire gagner plusieurs heures par semaine, c'est vite rentabilisé

      1. Merci de votre réponse

  4. Bonjour,
    J'ai douze colonnes pour chaque mois d'une année à partir de D, quand j'ai une date dans la colonne C je voudrais mettre en gris les cellule de la ligne correspondantes à partir du mois suivant la date de ma cellule.
    Exemple cellule : 17/07/2017 toutes mes cellules des mois suivants (août à décembre) deviennent grises.

    Merci pour votre aide.

  5. J'ai un calcul de délai en jour dans un tableau de suivi de sous-traitance.
    J'aimerais q'en fonction du degré d'urgence ( URGENT ou NORMAL) précisé dans une colonne, le résultat du délai s'affiche en rouge si la définition de délai n'est pas respecté?
    Si urgent, le délai est de 2j, si normal, délai de 20j
    comment puis je faire?
    En espérant que vous pourrez m'apporter votre précieuse aide,
    Bien cordialement

    1. Bonjour,
      En fait il y a 2 approches. Soit les dates que vous voulez mettre en surbrillance sont dans le futur et là je vous renvoie sur cette vidéo que j'ai faite (en anglais) https://youtu.be/d5qmxmY9yx0 , soit les dates sont dépassées et là c'est cette vidéo (toujours en anglais) qu'il faut voir https://youtu.be/QjOYJ9n7jFo
      Les 2 vidéos sont en pay-per-view

  6. Bonjour
    J'ai créé un tableau pour une gestion de cimetières. J'ai une date de début(G2). Je calcule la date de fin avec la fonction =DATE(ANNÉE(G2)+H2;MOIS(G2);JOUR(G2)) où H2 est le nombre d'années (par exemple 30) Cela fonctionne très bien.
    Où j'ai un problème c'est que j'ai une date de renouvellement en K2. J'ai voulu créer une fonction ou je voudrais que si la date de renouvellement (K2) est à 0, la date de fin de concession (M2) est = à 0 et si la date de renouvellement est renseignée (K2, je calcule la date de fin de fin de concession (M2) avec une colonne L2 qui contient le nombre d'années (par exemple 30).
    SI(K20; ANNÉE(K2)+L2;" "). Cela fonctionne je récupère bien une année avec + 30. Mais ce que je n'arrive pas à faire c'est de récupérer le MOIS et le JOUR. A chaque fois que j'essaye de rajouter MOIS(K2) et JOUR(K2) la fonction ne fonctionne plus.
    Quelqu'un a une idée ?
    Merci d'avance pour votre aide
    Dominique

    1. Bonjour,
      Je n'arrive pas à comprendre votre problème. Vous pouvez m'adresser un exemple à l'adresse [email protected]

  7. Bonjour,

    Je souhaiterai sur mon tableau "qui est mon budget familiale", que les cellules des prélèvement change de couleur à une date bien précise, (à savoir la date du prélèvement),
    Par exemple tant que la date du prélèvement n'est pas passé la cellule reste en jaune, et une fois le jour arrivé la cellule passe en vert.

    Merci de m'expliquer en détail si vous le pouvez je ne suis pas une pro en la matière

    Merci beaucoup par avance.

    • Laurent Hendrick on 18/01/2016 at 16:34
    • Répondre

    Bonjour,

    Je souhaiterais effectuer une mise en forme conditionnelle dépendant une autre mise en forme (autrement dit, un gadget purement visuel). Je me demandais si c'était possible en Excel ?

    Je m'explique : j'aimerais qu'Excel détecte, par exemple, quels cases dans le champ ciblé ont un border, et qu'il applique un fond de couleur à ces cases-là uniquement.

    Y a-t-il une formule permettant de détecter des enjolivures comme les bordures ?

    Merci d'avance !

  8. Bonjour,
    Est-il possible de mettre en forme un plage qui permet de mettre en surbrillance les cellules qui ne sont pas des dates? Ou de mettre en surbrillance les cellules qui ne sont pas au format date?
    Merci de vos réponses

    1. Bonjour,
      Dans le menu Accueil>Mises en forme conditionnelle>Nouvelle règle, vous sélectionnez la 2ème option "Appliquer une mise en forme uniquement aux cellules qui contiennent" et vous sélectionnez dans le premier menu déroulant "Date se produisant" Ensuite, il n'y a plus qu'à définir votre range de date

  9. Bonjour, J'ai peut-être pas tout compris mais votre exemple sur les dates d'anniversaires est assez peu utile puisqu'il donne une idée des dates passées et non des dates à venir. Hors, il n'ai pas possible avec Datediff() d'inverser la comparaison puisque date_debut doit être inférieure à date_fin. Une idée?

  10. Je souhaite une mise en forme conditionnelle qui me permet de mettre en jaune une cellule s'il reste moins de 30 jours avant d'arriver à la date écrite dans la cellule

  11. Bonsoir,
    Désolée de vous solliciter mais je souhaiterais obtenir votre aide pour une formule de calcul que je n'arrive pas à trouver, si vous le voulez bien svp:
    Exemple: dans la cellule a1 j'indique la date de transmission du devis au technicien. Dans la cellule b1 la date retour du technicien. Je souhaiterais que, si la cellule b1 est vide (donc pas de retour du technicien) au bout de 4 jours ouvrés alors la cellule Evra apparaître en rouge afin de l'alerter. Le calcul doit se faire en jours ouvrés
    En espérant que vous pourrez m'apporter votre précieuse aide,
    Bien cordialement

  12. Pour mettre en surbrillance les dates déjà passées, il suffit de choisir l'option "valeur de la cellule" "inférieure à" et indiquer "=aujourdhui()"

  13. Bonjour,
    Concernant la mise en forme conditionnelle, est-ce possible de mettre en couleur une semaine sur deux, soit du samedi au vendredi??

    Merci beaucoup

  14. Bonjour,

    En fait j'aimerais bien creer une conditionnel qui surlignerai une cellule en rouge une fois que la date d'aujourd'hui est atteinte voir dépasser. Histoire d'attirer la vue sur les échéance étant arrivé a termes.
    Cependant je ne parviens uniquement qu'à surligner automatiquement la date d'aujourd'hui mais pas celle passée :/

    Merci pour la lecture et la réponse probable 🙂

    1. Pour mettre en surbrillance les dates déjà passées, il suffit de choisir l’option « valeur de la cellule » « inférieure à » et indiquer « =aujourdhui() »

  15. Comment mettre une couleur différente sur toute une ligne, exemple mettre la ligne samedi en bleu. (et non seulement la cellule)
    Merci

  16. Merci beaucoup pour ce tuto très explicite !

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

    1. Sans mettre la colonne écart.

    2. Pour mettre en surbrillance les dates déjà passées, il suffit de choisir l’option « valeur de la cellule » « inférieure à » et indiquer « =aujourdhui() »

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

Laisser un commentaire

Your email address will not be published.