Mise en forme conditionnelle 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.
- Pré-défini
- Défini par l'utilisateur
- Créer une règle de formatage personnalisée
- Mettre en évidence les jours fériés dans Excel
- Formatage conditionnel des dates basé sur plusieurs conditions
- 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 > Mettre 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 sur D'ACCORD 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 :
Windows 10 mail no imprime
Utilisation d'un format personnalisé
- Ouvrez le Un rendez-vous qui se produit formater la fenêtre en suivant les étapes mentionnées ci-dessus.
- Après avoir sélectionné la plage de jours, sélectionnez Format personnalisé dans le menu déroulant des options de formatage.
- Le 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 cliquer 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.
- Sur le Maison Onglet, cliquez Mise en forme conditionnelle > Nouvelle règle , ce qui devrait ouvrir le Nouvelle fenêtre Règle de formatage.
- Sur ladite fenêtre, nous pouvons choisir parmi les disponibles Types de règles , puis modifiez la description de la règle pour définir les critères de mise en forme conditionnelle.
- Une fois terminé, le Format Vous pouvez cliquer sur le bouton dans le coin inférieur droit de la fenêtre pour choisir le formatage de ladite règle.
- Sur le Formater les cellules fenêtre qui s'ouvre, nous pouvons choisir la police, la bordure, la couleur des cellules, les effets, etc., selon notre choix et cliquer une fois sur OK dans cette fenêtre, suivi du Nouvelle règle de formatage Fenêtre pour les modifications à appliquer.
Note: Les règles de formatage peuvent être utilisées pour créer et appliquer plusieurs règles pour le même ensemble de données en fonction de différentes conditions/critères.
Lire: Le filtre de date Excel ne regroupe pas par mois
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. sur la base de 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électionnez une nouvelle règle dans l'option Mise en forme conditionnelle, choisissez Utilisez une formule pour déterminer quelle cellule formater , taper:
=$H4=AUJOURD'HUI() pour souligner le présent
=$H4>AUJOURD'HUI() pour souligner les jours à venir
=$H4
=$H4<=(AUJOURD'HUI()-30) pour mettre en évidence les dates datant de plus de 30 jours.
=$H4<=(AUJOURD'HUI()-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é H signifie que la colonne resterait constante, mais les lignes seraient variables.
- Une fois la Description de la règle est mentionné, la police, les couleurs et les effets peuvent être choisis en cliquant sur le bouton Format.
- Cliquer sur D'ACCORD dans la fenêtre Format de cellule, suivi de la même chose dans la fenêtre Nouvelle règle, pour que les modifications soient effectives.
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 Modifier la description de la règle champ, entrez la formule ci-dessous,
=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))
La formule ci-dessus utilise le ET à l'opérateur de surligner en jaune les dates de plus de 30 jours mais de moins de 45 jours pour toute la sélection, à partir de la cellule n°1. 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 Modifier la description de la règle champ, entrez la formule ci-dessous,
=WEEKDAY($H2, 2)>5
Le 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 de la date que nous essayons de vérifier (SA1 dans ce cas).
Le return_types Le paramètre représente le type de la semaine commençant du lundi (1) au dimanche (7), en considérant le premier jour 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 être 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 le DATEVALUE() fonction. 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.
- Cliquer 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 le DATEVALUE() fonction, 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.
Note: La séquence de numéros de série de ces dates dans Excel commence à partir de 1 St Janvier 1900, comme numéro de série 1. Ainsi, si l'on considère la date du 19 novembre 2023, elle serait numérotée 45249 puisqu'elle est 45248 jours après le 1er janvier 1900. St Janvier 1900.
Lire: Comment convertir le numéro de série en date dans Microsoft Excel
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 le compte formule 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 le Nouvelle règle boîte de dialogue et choisissez Utiliser une formule pour déterminer les cellules à formater sous Sélectionnez le type de règle.
- Entrez la formule =NOMBRESI($G :$G,$D3)
- Cliquer sur Format > Remplir et sélectionnez la couleur ROUGE dans la palette et cliquez sur D'ACCORD .
Le compte() 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.
aplicación que evita el cierre
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)
- Cliquez sur Format , sélectionnez la couleur BLEU dans la palette et cliquez sur OK.
- Créer un autre Nouvelle règle avec le même type de règle et entrez la formule mentionnée ci-dessous
=AND($I10="",$H10<$K)
- 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érifiez 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 ) ou dépassé ($H10<$K ) . 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 le Nouvelle règle boîte de dialogue.
- Choisir Formatez uniquement les cellules contenant sous Sélectionnez le type de règle
- Dans Modifier la description de la règle , choisir Valeur de cellule , Plus grand que , =Maintenant()+30 dans les listes déroulantes respectives.
- Cliquer sur Format, sélectionnez VERT dans la palette de couleurs , et cliquez D'ACCORD .
- Encore une fois, ouvrez un Nouvelle règle et choisis le même Type de règle, comme ci-dessus.
- Dans Modifier la description de la règle , choisir Valeur de cellule , Moins que , =Maintenant() dans les listes déroulantes respectives.
- Cliquer sur Format et sélectionnez ROUGE dans la palette de couleurs, puis cliquez sur D'ACCORD .
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.
Lire : Comment mettre en surbrillance les lignes avec des dates à l'aide de la mise en forme conditionnelle dans Excel
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 » > « Mise en forme conditionnelle » > « 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.
