Comment configurer la mise en forme conditionnelle dans Excel pour les dates

Le Formatage conditionnel dans Excel peut être considéré comme un utilitaire précieux qui peut aider à améliorer visuellement l’analyse et la présentation des données dans la feuille de calcul en appliquant un formatage particulier basé sur des règles ou des conditions prédéfinies. Cet article expliquera comment configurer la mise en forme conditionnelle dans Excel pour les dates. Nous avons également inclus des exemples détaillés que vous pouvez essayer en lisant le guide.

Comment configurer la mise en forme conditionnelle dans Excel pour les dates

La mise en forme conditionnelle permet non seulement de mettre en évidence les cellules à l’aide de codes de couleur, mais également de formater les lignes, les colonnes et les cellules constituées de dates d’une manière qui peut être considérée comme visuellement significative et attrayante.

1. Pré-défini
2. Défini par l’utilisateur
3. Créer une règle de formatage personnalisée
4. Mettre en évidence les jours fériés dans Excel
5. Formatage conditionnel des dates basé sur plusieurs conditions
6. Mise en évidence des dates et des retards à venir

Assurez-vous de parcourir attentivement les détails dans Excel. Il offre de nombreuses options, dont certaines peuvent dépasser le cadre de ce guide.

1] Règles de date de mise en forme conditionnelle prédéfinies avec des dates

La mise en forme conditionnelle avec des dates peut être bénéfique lors du traitement d’ensembles de données contenant des informations relatives à la date. Excel propose près de dix options différentes pour formater les cellules en fonction des dates, comme illustré ci-dessous :

• Pour formater une cellule avec la date comme type de données, cliquez sur Accueil > Mise en forme conditionnelle > Mettez en surbrillance les règles de cellule >Une date se produisant

• Les options de formatage conditionnel peuvent être choisies dans les menus déroulants de la fenêtre suivante. La première liste déroulante nous permet de sélectionner la plage de jours et les options de formatage peuvent être choisies dans la seconde.

• Cliquez OK pour appliquer les règles de formatage choisies.

2] Règles de date de mise en forme conditionnelle définies par l’utilisateur avec des dates

Outre un ensemble de règles prédéfinies, Excel offre également la possibilité de personnaliser ces règles en fonction des besoins des utilisateurs. Il existe plusieurs façons de définir les personnalisations, comme illustré ci-dessous :

Utilisation d’un format personnalisé

1. Ouvrez la fenêtre de format A Date Occuring en suivant les étapes mentionnées ci-dessus.
2. Après avoir sélectionné la plage de jours, sélectionnez Format personnalisé dans le menu déroulant des options de formatage.
3. La fenêtre Formater les cellules apparaîtrait à cause de ce qui précède, où nous pouvons choisir la couleur, la bordure, la police et les effets de notre choix, puis cliquez sur OK pour appliquer les modifications.

Créer de nouvelles règles

Cette méthode implique la création d’une toute nouvelle règle pour le formatage conditionnel, contrairement à la méthode ci-dessus, dans laquelle nous pouvons choisir parmi quelques règles prédéfinies et créer uniquement un format personnalisé. La création d’une nouvelle règle implique :

• Sélection des cellules pour lesquelles la mise en forme doit être appliquée.
• Dans l’onglet Accueil, cliquez sur Mise en forme conditionnelle > Nouvelle règle, qui devrait ouvrir la fenêtre Nouvelle règle de formatage.

• Dans ladite fenêtre, nous pouvons choisir parmi les Types de règles disponibles, puis modifier la description de la règle pour définir les critères de conditionnalité. formatage.
• Une fois terminé, vous pouvez cliquer sur le bouton Format dans le coin inférieur droit de la fenêtre pour choisir le formatage de ladite règle.
• Dans la fenêtre Formater les cellules qui s’ouvre, nous pouvons choisir la police, la bordure, la couleur des cellules, les effets, etc., selon choisissez et cliquez une fois sur OK dans cette fenêtre, suivi de la fenêtre Nouvelle règle de formatage pour que les modifications soient appliquées.

3] Comment créer une règle de formatage sur mesure avec des dates

Dans l’exemple ci-dessus, nous avons parcouru les étapes de création d’une nouvelle règle de mise en forme conditionnelle basée sur des critères déjà fournis. Excel permet également d’appliquer lesdites règles de formatage sur des critères définis par l’utilisateur.

Par exemple, supposons que nous devions mettre en évidence les cellules dont les dates datent de plus de 30 ou 60 jours. Dans ce cas, la même chose peut également être mise en œuvre en appliquant des formules de date et des fonctions disponibles dans Excel, comme illustré ci-dessous :

A] Mettre en surbrillance les dates actuelles et plus anciennes

Sous Sélectionner une nouvelle règle dans l’option Mise en forme conditionnelle, choisissez Utiliser une formule pour déterminer laquelle cellule à formater, tapez :

=$H4=TODAY() pour mettre en évidence le présent

=$H4>TODAY() pour mettre en évidence les jours à venir

=$H4<TODAY() pour mettre en évidence les jours précédents

=$H4<=(TODAY()-30) pour mettre en évidence les dates datant de plus de 30 jours.

=$H4<=(TODAY()-60) pour mettre en évidence les dates datant de plus de 60 jours.

La formule ci-dessus vérifie la plage de dates sélectionnée pour la date actuelle, et une fois la correspondance trouvée, changez la couleur d’arrière-plan en Violet, comme sélectionné dans l’option Format. Le $ utilisé à côté de H signifie que la colonne resterait constante, mais les lignes seraient variables.

• Une fois la Description de la règle mentionnée, la police, les couleurs et les effets peuvent être choisis en cliquant sur le bouton Format.
• Cliquez sur OK dans la fenêtre Format des cellules, suivi de la même chose dans la fenêtre Nouvelle règle, pour que les modifications soient effectives.< /span>

B] Surbrillance basée sur une plage de dates définie par l’utilisateur

Des méthodes similaires peuvent être appliquées pour formater les plages de dates spécifiées par les utilisateurs. Par exemple, si nous devons mettre en évidence les dates datant de plus de 30 jours mais de moins de 45 jours, la fonction TODAY() peut être utilisée en combinaison avec un opérateur logique comme AND pour spécifier les critères impliquant deux conditions :

• Sélectionnez les cellules pour lesquelles le formatage doit être appliqué et ouvrez la fenêtre Nouvelle règle.
• Dans le champ Modifier la description de la règle, saisissez la formule ci-dessous,

=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))

La formule ci-dessus utilise l’opérateur AND pour mettre en surbrillance les dates en jaune qui ont plus de 30 jours mais moins de 45 jours pour le sélection entière, à partir de la cellule no. H2.

C] Mettre en évidence les week-ends

Sur la même plage, grâce à la formule WEEKDAY(), les dates tombant le week-end peuvent également être mises en évidence :

• Sélectionnez les cellules pour lesquelles le formatage doit être appliqué et ouvrez la fenêtre Nouvelle règle.
• Dans le champ Modifier la description de la règle, saisissez la formule ci-dessous,

=WEEKDAY($H2, 2)>5

La syntaxe de la formule Weekday peut être représentée par WEEKDAY(serial_number, [return_type]), où serial_number représente le numéro de cellule du date que nous essayons de vérifier (SA1 dans ce cas).

Le paramètre return_types représente le type de la semaine commençant du lundi(1) au dimanche(7), en tenant compte du jour de début de la semaine. comme lundi (représenté par 1) et le dernier jour de la semaine comme dimanche (représenté par 7).

Cependant, les return_types sont facultatifs dans la formule et sont représentés entre troisièmes accolades. >5 est utilisé pour mettre en surbrillance samedi(6) et dimanche(7) pour le type de retour spécifié.

D] Mise en surbrillance des lignes en fonction d’une date spécifique

Cette option peut s’avérer pratique lorsque vous traitez de grandes quantités de données pour lesquelles une date particulière doit être mise en évidence. La même chose peut être obtenue en déterminant la valeur numérique de la date ou en utilisant la fonction DATEVALUE(). La valeur numérique doit être déterminée initialement pour appliquer une mise en forme conditionnelle à une date spécifique, car Excel stocke en permanence les dates sous forme de numéros de série séquentiels. Pour trouver la valeur numérique d’une date spécifique,

• Faites un clic droit sur la cellule avec la date, cliquez sur Formater les cellules > Nombre, puis choisissez Général. Une valeur numérique à 5 chiffres serait affichée sur la cellule, qui peut être notée pour référence future.

• Cliquez sur Annuler une fois le numéro noté puisque nous n’avons pas besoin de changer le format de date de la cellule.
• Cliquez sur Mise en forme conditionnelle>Nouvelle règle> Sélectionnez une nouvelle règle> Utilisez une formule pour déterminer quelle cellule formater et tapez la formule suivante,

=$G5=45249

• En utilisant la fonction DATEVALUE() , la même chose peut également être faite en entrant la formule mentionnée ci-dessous,

=$G5=DATEVALUE("11/19/2023")

Le $ utilisé à côté de C signifie que la colonne resterait constante, mais que les lignes seraient variables.

4] Mettre en évidence les jours fériés dans Excel

Étant donné que les jours fériés peuvent varier en fonction des données démographiques, Excel ne dispose d’aucune fonction intégrée pour mettre en évidence les jours fériés. Par conséquent, pour identifier les jours fériés, nous devons enregistrer les dates dans une colonne différente et appliquer la formule countif pour vérifier l’occurrence des dates de vacances. dans la fiche technique.

Par exemple, nous avons quelques dates mentionnées dans la colonne D et une liste de jours fériés dans la colonne G. Grâce au formatage conditionnel, nous pouvons mettre en évidence les jours fériés en ROUGE dans la colonne D, le cas échéant, en les comparant avec les dates de vacances mentionnées dans la colonne G. en suivant les étapes mentionnées ci-dessous :

• Ouvrez la boîte de dialogue Nouvelle règle et choisissez Utiliser une formule pour déterminer les cellules à formater < /span>Sélectionnez le type de règle.sous
• Entrez la formule =COUNTIF($G$3:$G$5,$D3)
• Cliquez sur Format > Remplissez et sélectionnez la couleur ROUGE dans la palette et cliquez sur OK< /span>.

Le countif() vérifie l’occurrence des dates mentionnées dans la cellule G3 – G5 avec les dates mentionnées dans la colonne D à partir de la cellule D3 .

5] Formatage conditionnel des dates basé sur plusieurs conditions

Supposons une feuille de travail dans laquelle nous avons des plaintes soulevées concernant divers articles ménagers. La fiche indique les dates auxquelles les plaintes ont été déposées ainsi que les dates d’échéance et de clôture, le cas échéant.

En utilisant le formatage conditionnel, marquons les réclamations BLANCHES pour celles qui ont été FERMÉES, ROUGES pour celles qui ont dépassé la date d’échéance mais pas encore clôturées et BLEU pour celles qui ont encore atteint la date d’échéance.

Le processus impliquerait de créer deux règles de formatage conditionnel pour les mêmes données, une pour celles qui n’ont pas encore atteint la date d’échéance et une autre pour celles qui ont dépassé la date d’échéance. Faire cela,

• Ouvrez la fenêtre Nouvelle règle de mise en forme conditionnelle et sélectionnez Utiliser une formule pour déterminer les cellules à formater sous Sélectionnez un type de règle.
• Entrez la formule mentionnée ci-dessous

=AND($I10="",$H10>$K$6)

• Cliquez sur Format, sélectionnez la couleur BLEUE dans la palette, puis cliquez sur OK.
• Créez une autre Nouvelle règle avec le même type de règle et saisissez la formule mentionnée ci-dessous

=AND($I10="",$H10<$K$6)

• Cliquez sur Format, sélectionnez la couleur ROUGE dans la palette et cliquez sur OK.

Dans cette formule, nous vérifions d’abord si la colonne Date de clôture est VIDE ou non pour déterminer que la plainte est toujours ouverte ET puis vérifions si la DATE D’ÉCHÉANCE est supérieure à la date actuelle ou inférieure pour déterminer si la date d’échéance approche ($H10>$K$6) ou dépassée ($ H10<$K$6). La date dans la cellule K6 représente la date actuelle.

6] Mise en évidence des dates et des retards à venir

Pour comprendre comment mettre en évidence les retards par rapport aux dates dans une feuille Excel, prenons un exemple dans lequel nous avons une liste de titres avec leurs noms et dates d’expiration. Grâce au formatage conditionnel, si nous devons marquer ceux qui doivent expirer en VERT et ceux qui ont déjà expiré en ROUGE,

• Sélectionnez la plage de données et ouvrez la boîte de dialogue Nouvelle règle.
• Choisissez Formater uniquement les cellules qui contiennent sous Sélectionner le type de règle
• Dans Modifier la description de la règle, choisissez Valeur de la cellule, Supérieur à, =Now()+30 dans les listes déroulantes respectives.
• Cliquez sur Format, sélectionnez VERT dans la palette de couleurs, et cliquez sur OK.

• Encore une fois, ouvrez une Nouvelle règle et choisissez le même Type de règle, < a i=4>comme ci-dessus.
• Dans Modifier la description de la règle, choisissez Valeur de la cellule, Moins de, =Now() dans les listes déroulantes respectives.Moins de, =Now() dans les listes déroulantes respectives.Moins de, =Now() dans les listes déroulantes respectives.
• Cliquez sur Format et sélectionnez ROUGE dans la couleur palette, puis cliquez sur OK.

Conclusion

Ce didacticiel vise à couvrir de manière exhaustive les options et fonctions pouvant être utilisées pour appliquer la mise en forme conditionnelle dans les feuilles de calcul Excel en fonction des dates. Il peut aider à comprendre comment appliquer des règles de formatage pour améliorer la visualisation et l’analyse des données. La maîtrise de la mise en forme conditionnelle peut également aider à identifier les tendances, à vérifier les délais et à définir les priorités des tâches, aidant ainsi à prendre des décisions éclairées.

Comment créer une mise en forme conditionnelle dans Excel basée sur des dates ?

Tout d’abord, sélectionnez vos cellules de date pour créer une mise en forme conditionnelle dans Excel basée sur les dates. Cliquez sur « Accueil » > « Formatage conditionnel » > ‘Nouvelle règle’. Choisissez « Formater les cellules qui contiennent », sélectionnez « Dates se produisant » sous la règle, choisissez les critères de date spécifiques, puis définissez votre format préféré en cliquant sur « Format ». Postulez en appuyant sur « OK ». Cela met rapidement en évidence les dates clés dans vos données.

Comment mettre en forme conditionnelle les dates d’échéance dans Excel ?

Tout d’abord, sélectionnez vos cellules avec des dates aux dates d’échéance au format conditionnel dans Excel. Ensuite, cliquez sur « Mise en forme conditionnelle » dans l’onglet « Accueil ». Choisissez « Nouvelle règle », sélectionnez « Formater uniquement les cellules qui contiennent », définissez la règle sur « Valeur de cellule » et « inférieure à » et entrez votre date d’échéance ou une formule. Cliquez sur « Format », choisissez votre style de formatage et appuyez sur « OK » pour appliquer.