Excel : mettre en forme conditionnelle les valeurs dates et temps

Introduction

Dans ce tutoriel, vous apprendrez à appliquer des mises en forme conditionnelles aux dates dans Excel. Vous apprendrez également des techniques de mise en forme pour différencier les jours de la semaine, les jours fériés, les week-ends…etc.

Dans la plupart des sociétés, on donne une très grande importance aux dates. Par exemple, lors de la réalisation d’un projet, il est important d’attirer l’attention sur les dates d’échéance qui tombent après une période donnée. Avec la mise en forme conditionnelle, vous pouvez facilement créer un rapport mettant en évidence les éléments ou les tâches en retard.

La mise en forme conditionnelle dans Excel vous permet de mettre en forme une ou plusieurs cellules en se basant sur les valeurs contenues dans ces cellules. Excel intègre un ensemble d'options de mise en forme conditionnelle standard. Vous pouvez également utiliser des formules personnalisées pour appliquer ou non une règle de mise en forme spécifique à une plage de cellules. En effet, l'utilisation des formules dans les règles de mise en forme conditionnelle est incroyablement puissante, car elles vous permettent de pousser cette fonctionnalité un peu plus loin pour formater les dates sous condition comme vous le souhaitez.

Table des matières

Introduction

Mise en forme conditionnelle pour les dates (règles intégrées)

Formules de mise en forme conditionnelle des dates

Comment mettre en surbrillance les week-ends dans Excel ?

Mettre en évidence les dates en fonction de la date d'échéance dans Excel

Mettre en surbrillance les dates égales, supérieures ou inférieures à aujourd'hui

Mettre en forme conditionnellement les dates dans Excel en fonction de plusieurs conditions

Comment mettre en évidence des dates entre deux dates dans Excel ?

Mise en forme conditionnelle pour les dates (règles intégrées)

Microsoft Excel fournit plusieurs options pour mettre en forme les cellules sélectionnées en fonction de la date actuelle.

Pour utiliser les règles de mise en forme conditionnelle intégrées dans Excel, suivez les étapes suivantes :

  1. Sélectionnez les données et accédez à l'onglet « Accueil».
  2. Dans le groupe « Styles», cliquez sur le bouton « Mise en forme conditionnelle ». Dans la capture d'écran suivante, vous pouvez voir les options disponibles pour la mise en forme conditionnelle de différents types de données :
  1. Sélectionnez « Règles de mise en surbrillance des cellules» dans le menu contextuel.
  1. Le menu contextuel affiche les options disponibles, mais ici nous souhaitons créer une règle de mise en forme des dates. Sélectionnez l’option « Une date se produisant».
  1. En cliquant sur cette option, la boîte de dialogue « Une date se produisant» s'ouvrira en vous montrant quelques options pour mettre en évidence les dates particulières. Ici, vous avez une option disponible pour mettre en surbrillance la date d'hier au mois prochain, et vous avez également la possibilité de choisir la façon avec laquelle vous voulez que ces dates soient mises en évidence. Comme la couleur du texte et le remplissage de la cellule, il existe des options fixes et des options personnalisées. Veuillez voir la capture suivante pour la référence des options disponibles.
  1. Sélectionnez l'une des options de date dans la première liste déroulante. Par exemple, nous allons choisir « Hier».
  1. Enfin, choisissez l'un des formats prédéfinis.

Vous pouvez aussi configurer votre format personnalisé en sélectionnant « Format personnalisé » puis en effectuant les modifications souhaitées dans les onglets « Police », « Bordure » et « Remplissage ». Si la palette standard Excel ne suffit pas, vous pouvez toujours cliquer sur le bouton « Autres de couleurs… ».

  1. Cliquez sur OK une fois terminé.

Cette méthode est simple et vous permet de mettre en forme les dates rapidement. Cependant, elle présente deux limitations importantes : elle ne fonctionnera que pour les cellules que vous avez sélectionnées et le format conditionnel est toujours appliqué en fonction de la date actuelle.

En utilisant des règles intégrées dans Excel, vous pouvez mettre en surbrillance les dates d'hier, d'aujourd'hui au mois prochain. Si les options intégrées ne sont pas utilisables pour vous, vous pouvez créer vos propres règles et mettre en forme les données comme vous le souhaitez en utilisant des formules comme JOURSEM, AUJOURDHUI, DATE, MAINTENANT et bien d'autres.

Formules de mise en forme conditionnelle des dates

Si vous souhaitez mettre en forme des cellules ou des lignes entières en fonction de la date d’une autre cellule, ou créer des règles pour des intervalles de temps plus longs par exemple plus d'un mois à partir de la date actuelle, vous devez créer votre propre règle de mise en forme conditionnelle en utilisant des formules.

Vous trouverez ci-dessous quelques exemples de mises en forme conditionnelles des dates les plus utilisées.

Comment mettre en surbrillance les week-ends dans Excel ?

Dans cet exemple, nous devons explorer la création de nouvelles règles car nous n’avons pas d'option intégrée disponible pour mettre en évidence les dates du week-end. Dans la capture ci-dessous nous avons des données sur des produits avec leurs dates de livraison.

  1. Commencez par sélectionner la plage de cellules où vous souhaitez mettre en évidence les week-ends. Dans notre cas, il s'agit de la plage B2:B7.
  2. Sous l'onglet « Accueil», cliquez sur le bouton « Mise en forme conditionnelle » et sélectionnez « Nouvelle règle ».
  1. Créez une nouvelle règle de mise en forme basée sur une formule en sélectionnant « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué».
  1. Dans la zone « Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie», entrez la formule « JOURSEM » suivante qui déterminera quelles cellules sont des samedis et dimanches :

=JOURSEM(B2; 2)> 5

Remarque : Notez que dans la formule, vous excluez les symboles dollar de référence absolue ($) pour la cellule cible (B2). Si vous cliquez sur la cellule B2 au lieu de taper sa référence, Excel rendra automatiquement votre référence de cellule absolue en ajoutant les signes de dollar. Il est important que vous n'incluez pas les symboles de dollar de référence absolus dans votre cellule cible, car vous avez besoin d'Excel pour appliquer cette règle de mise en forme en fonction de la propre valeur de chaque cellule.

  1. Sélectionnez ensuite le format en cliquant sur le bouton « Format » en bas de la boite de dialogue.
  2. La boîte de dialogue « Format de cellule» s'ouvrira et vous pourrez choisir n'importe quelle couleur que vous voulez. Ici, nous avons choisis la couleur verte.
  1. En cliquant sur « OK», vous aurez vos dates de week-end affichées avec un remplissage vert comme illustré dans la capture ci-dessous.

Maintenant, expliquons brièvement la formule JOURSEM que nous avons utilisé, afin que vous puissez l'ajuster rapidement à vos propres feuilles de calcul :

  • La syntaxe de la fonction JOURSEM est comme suit : JOURSEM(numéro_de_série ; [type_retour])
  • Le paramètre « numéro_de_série » représente la date que vous voulez trouver. Vous fournissez une référence à votre première cellule contenant une date, B2 dans notre cas.
  • Le paramètre [type-retour] détermine le type de semaine, les crochets signifient qu'il s’agit d’un argument facultatif. Vous entrez 2 comme type de retour pour une semaine du lundi (1) au dimanche (7).
  • Enfin, vous écrivez > 5 pour ne mettre en évidence que les samedis (6) et dimanches (7).

Mettre en évidence les dates en fonction de la date d'échéance dans Excel

Dans cette section nous avons un scénario dans lequel les dates de livraison en retard de plus de 10 jours doivent être mises en forme en rouge .

  1. Sélectionnez les cellules de données dans votre plage cible (cellules C2:C7dans cet exemple),
  2. Accédez à l'onglet « Accueil» du ruban Excel, puis cliquez sur « Mise en forme conditionnelle » et sélectionnez « Nouvelle règle ».
  1. Dans la zone de liste en haut de la boîte de dialogue, cliquez sur l'option « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué». Cette option demande à Excel d’évaluer les valeurs en fonction d'une formule que vous spécifiez. Si la formule donne « VRAI » pour une cellule particulière, la mise en forme conditionnelle est appliquée à cette cellule.
  1. Dans la zone de saisie de formule, copiez et collez la formule suivante :

=C2 - B2 > 10

Dans cette formule, vous déterminez si la date de livraison est supérieure à 10 jours après la date d’échéance. Si tel est le cas, la mise en forme conditionnelle serait appliquée.

  1. Cliquez sur le bouton « Format ». Cela ouvre la boîte de dialogue « Format de cellule», dans laquelle vous disposez d'un ensemble complet d'options pour la mise en forme de la police, de la bordure et du remplissage de votre cellule cible.
  1. Après avoir choisi vos options de mise en forme, cliquez sur le bouton « OK» pour confirmer vos modifications et revenir à la boîte de dialogue « Nouvelle règle de mise en forme ».
  2. De retour dans la boîte de dialogue « Nouvelle règle de mise en forme», cliquez sur le bouton « OK » pour confirmer votre règle de mise en forme.

Ainsi le résultat est comme suit :

Mettre en surbrillance les dates égales, supérieures ou inférieures à aujourd'hui

Si vous souhaitez mettre en forme conditionnellement des cellules ou des lignes entières en fonction de la date du jour, vous devez utiliser la fonction AUJOURDHUI ainsi :

  • Mettre en évidence les dates qui sont égales à la date d’aujourd'hui : =$B2=AUJOURDHUI()
  • Mettre en évidence les dates qui sont supérieur à la date d’aujourd'hui : =$B2>AUJOURDHUI()
  • Mettre en évidence les dates qui sont inférieur à la date d’aujourd'hui : =$B2

La capture d'écran suivante montre les règles précédentes en action. Veuillez noter qu'au moment de l’écriture de ceslignes, AUJOURDHUI était le 04 septembre 2020.

Mettre en forme conditionnellement les dates dans Excel en fonction de plusieurs conditions

De la même façon, vous pouvez utiliser la fonction AUJOURDHUI en combinaison avec d'autres fonctions Excel pour gérer des scénarios plus complexes. Par exemple, vous pouvez utiliser cette méthode si vous souhaitez colorier les cellules de la colonne « Date livraison » lorsque la date de livraison est égale ou supérieure à la date d’aujourd'hui et que vous souhaitez que cette mise en forme disparaisse lorsque vous entrez le numéro de facture. Pour cette tâche, vous aurez besoin d'une colonne d’assistance avec la formule suivante :

=SI(B2>=AUJOURDHUI(); SI(C2=""; 1; 0); 0)

Où B est votre colonne « Date livraison » et C la colonne « Facture ».

Ainsi, si la date de livraison est supérieure ou égale à la date d’aujourd’hui et qu'il n'y a pas de nombre dans la colonne « Facture », la formule renvoie « 1 », sinon «  ». Les lignes avec un résultat « 1 » doivent être mises en surbrillance puisqu’elles répondent à nos deux conditions. Mais, dans le cas où nous saisissons un nombre dans la facture correspondante à une de ces lignes, la mise en forme doit disparaitre.

Pour mettre en place cette mise en forme conditionnelle :

  1. Sélectionnez vos données, accédez à l’onglet « Accueil », cliquez sur « Mise en forme conditionnelle» et sélectionnez « Nouvelle règle ».
  1. Dans la zone de liste en haut de la boîte de dialogue, cliquez sur l'option « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué».
  2. Entrez la formule suivante : =$D2=1

où D est votre colonne supplémentaire. Bien sûr, vous pourrez masquer cette colonne plus tard.

  1. Sélectionnez le format que vous souhaitez et cliquez sur « Ok ».

Et voilà le résultat.

Dans la capture ci-dessous, nous avons entré un nombre dans la facture de la ligne 5, et comme attendu, la mise en forme est disparue.

Comment mettre en évidence des dates entre deux dates dans Excel ?

Si par exemple vous avez une longue liste de dates dans votre feuille de calcul, vous pouvez mettre en évidence les cellules ou les lignes entières qui appartiennent à une certaine plage de dates. Cela signifie mettre en forme toutes les dates comprises entre deux dates données.

Dans l’exemple de la capture ci-dessous, nous avons une liste de dates dans la plage de cellules A2:A7 :

Nous avons une date de début 28/08/2020 et une date de fin 03/09/2020, et nous souhaitons mettre en évidence toutes les cellules dans lesquelles la date se situe entre ces deux dates. Si vous avez un cas pareil, suivez simplement les étapes suivantes :

  1. Sélectionnez la plage de cellules dont vous souhaitez mettre en évidence les cellules de date. Ici A2 :A7.
  2. Accédez à l'onglet « Accueil », cliquez sur le bouton « Mise en forme conditionnelle» sous le groupe « Styles ».
  3. Sélectionnez l’option « Nouvelle règle» dans la liste du menu contextuel. Ainsi, la boîte de dialogue « Nouvelle règle de mise en forme » s'ouvrira.
  1. Cliquez sur l’option « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué» dans la liste « Sélectionnez un type de règle ».
  2. Tapez la formule suivante dans la zone de texte « Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie» :

=ET($A2>=$D$2; $A2

  1. Cliquez sur le bouton « Format » dans la boîte de dialogue « Nouvelle règle de mise en forme» et la boîte de dialogue « Format de cellule » s'ouvrira.
  2. Passez à l'onglet « Remplissage» dans la boîte de dialogue « Format de cellule » et choisissez une couleur selon vos besoins.
  1. Cliquez sur le bouton « OK» pour revenir à la boîte de dialogue « Nouvelle règle de mise en forme ».
  2. Cliquez encore une fois sur le bouton « Ok ». Vous verrez que les cellules ont été mises en évidence.

Télécharger le fichier Excel de ce tutoriel

Article publié le 09 Septembre 2020par Hanane Mouqqadim