Comment mettre en surbrillance/avec mise en forme conditionnelle les dates de plus de 30 jours dans Excel ?
Lorsque vous travaillez avec une liste de dates dans Excel, il est souvent nécessaire de mettre en surbrillance les dates qui sont antérieures de plus de 30 jours à la date du jour. Identifier manuellement ces dates peut être chronophage et sujet aux erreurs, surtout avec de grands ensembles de données. Ce guide présente diverses méthodes efficaces pour mettre en surbrillance ou gérer les dates de plus de 30 jours : de l'utilisation de la mise en forme conditionnelle pour un surlignage automatique, des formules d'assistance pour le tri et le marquage, des macros VBA pour des plages plus larges ou dynamiques, et même l'utilisation d'outils spécialisés pour des flux de travail rationalisés. La compréhension de ces options vous aidera à identifier rapidement les dates échues, surveiller les délais et gérer les données sensibles au temps facilement.
Mettre en surbrillance les dates de plus de 30 jours avec la mise en forme conditionnelle
Sélectionnez et mettez en surbrillance facilement les dates antérieures à une date spécifique avec un outil incroyable
Mettez en surbrillance automatiquement les dates de plus de 30 jours avec une macro VBA
Utilisez une formule dans une colonne d'assistance pour marquer les dates de plus de 30 jours
Mettre en surbrillance les dates de plus de 30 jours avec la mise en forme conditionnelle
La fonctionnalité Mise en forme conditionnelle d’Excel permet aux utilisateurs de mettre en surbrillance automatiquement les dates de plus de 30 jours dans une plage sélectionnée. Cela est particulièrement utile pour suivre les tâches en retard, gérer les délais ou prioriser les éléments en fonction de leur ancienneté. Suivez les étapes détaillées ci-dessous :
1. Sélectionnez la plage contenant vos dates, puis accédez à Accueil > Mise en forme conditionnelle > Nouvelle règle. Voir capture d'écran :
2. Dans la boîte de dialogue Nouvelle règle de mise en forme, configurez ce qui suit :
- 2.1) Choisissez Utiliser une formule pour déterminer les cellules à formater sous les options de type de règle.
- 2.2) Entrez cette formule dans la zone intitulée Formater les valeurs où cette formule est vraie: =A2<AUJOURDHUI()-30
- 2.3) Cliquez sur Format pour spécifier la couleur de remplissage pour mettre en surbrillance les anciennes dates.
- 2.4) Cliquez sur OK pour confirmer et appliquer la règle. Voir capture d'écran :
Remarque : Dans cette formule, A2 doit être la cellule supérieure (coin supérieur gauche) de votre plage sélectionnée, et 30 représente le seuil en jours. Vous pouvez ajuster les deux selon vos besoins. Si vos données ne commencent pas en A2, mettez à jour la référence de cellule en conséquence. Assurez-vous que la référence pointe vers la première ligne de votre plage.
Une fois configuré, Excel mettra en surbrillance toutes les dates de la plage qui sont antérieures de plus de 30 jours à aujourd'hui en utilisant la couleur que vous avez spécifiée. Cela attire immédiatement votre attention sur les éléments qui peuvent nécessiter une action immédiate.
Astuce : Cette formule compare la date de chaque cellule à AUJOURDHUI() moins 30. Si vous souhaitez mettre en surbrillance différentes périodes (par exemple, 60 jours), changez simplement « 30 » par le nombre souhaité.
Si votre liste de dates inclut des cellules vides, vous remarquerez peut-être que celles-ci sont également mises en surbrillance. Pour éviter cela :
3. Resélectionnez votre plage de dates et allez dans Accueil > Mise en forme conditionnelle > Gérer les règles.
4. Dans le Gestionnaire de règles de mise en forme conditionnelle, cliquez sur Nouvelle règle pour ajouter une nouvelle règle pour gérer les cellules vides.
5. Dans la boîte de dialogue Modifier la règle de mise en forme :
- 5.1) Choisissez Utiliser une formule pour déterminer les cellules à formater.
- 5.2) Entrez la formule suivante (remplacez A2 si votre plage commence ailleurs) : =ESTVIDE(A2)=VRAI
- 5.3) Confirmez en cliquant sur OK.
6. Dans le Gestionnaire de règles, assurez-vous de cocher la case Arrêter si vrai pour la nouvelle règle afin que les cellules vides soient exclues des autres règles de mise en forme. Cliquez sur OK pour terminer.
Le résultat : seules les valeurs réelles de dates de plus de 30 jours seront mises en surbrillance, les cellules vides étant ignorées comme prévu.
Scénario & Astuces : La mise en forme conditionnelle est idéale pour les tableaux de bord interactifs ou les rapports où la visualisation rapide des éléments en retard est importante. Cependant, notez que des plages très grandes ou des formats complexes peuvent impacter les performances du classeur. Vérifiez toujours les formats de date : la règle s'applique uniquement si les cellules sont reconnues par Excel comme des dates.
Mettez en surbrillance facilement les dates antérieures à une date spécifique avec un outil incroyable
Si vous avez besoin d'un moyen rapide et convivial de sélectionner et mettre en surbrillance les dates antérieures à une date particulière (par exemple, pour des rapports personnalisés ou des opérations groupées manuelles), Sélectionner des cellules spécifiques dans Kutools pour Excel offre une solution efficace. En quelques clics, vous pouvez sélectionner toutes les cellules de date antérieures à n'importe quelle date spécifiée, puis les mettre en surbrillance ou les traiter comme requis.
1. Sélectionnez les cellules de date, cliquez sur Kutools > Sélectionner > Sélectionner des cellules spécifiques.
2. Dans la boîte de dialogue Sélectionner des cellules spécifiques, vous devez :
- 2.1) Sélectionnez Cellule dans la section Type de sélection.
- 2.2) Choisissez Inférieur à dans la liste déroulante Type spécifique et entrez la date limite (par exemple, 30 jours avant ou une date spécifique) dans la zone.
- 2.3) Cliquez sur OK pour sélectionner toutes les cellules de date correspondantes.
- 2.4) Confirmez le nombre de sélections et poursuivez en cliquant sur OK dans la boîte de dialogue d'information.
3. Avec les dates pertinentes sélectionnées, vous pouvez appliquer une couleur de remplissage en allant dans Accueil > Couleur de remplissage pour les mettre en surbrillance visuellement si nécessaire.
Si vous souhaitez bénéficier d'un essai gratuit (30 jours) de cet utilitaire, veuillez cliquer pour le télécharger, puis aller appliquer l'opération selon les étapes ci-dessus.
Mettez en surbrillance automatiquement les dates de plus de 30 jours avec une macro VBA
Si vous travaillez avec de grands ensembles de données ou avez fréquemment besoin de mettre en surbrillance des dates par rapport à aujourd'hui, une macro VBA peut automatiser efficacement le processus. Cette méthode est particulièrement précieuse lorsque vous avez des plages très vastes, souhaitez mettre à jour la mise en surbrillance de manière répétée, ou devez effacer le formatage précédent avant d'appliquer de nouvelles mises en surbrillance.
1. Ouvrez le classeur Excel dans lequel vous souhaitez appliquer la mise en surbrillance. Accédez à l'éditeur VBA en cliquant sur Outils développeur > Visual Basic. Si "Développeur" n'est pas visible, activez-le depuis les Options Excel. Dans la fenêtre VBA, cliquez sur Insérer > Module.
Sub HighlightOldDates()
Dim WorkRng As Range
Dim Rng As Range
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to check for old dates:", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
' Optional: Clear previous background coloring
WorkRng.Interior.ColorIndex = xlNone
For Each Rng In WorkRng
If IsDate(Rng.Value) Then
If Rng.Value < Date - 30 Then
Rng.Interior.Color = vbYellow ' Or choose any other color you prefer
End If
End If
Next
Application.ScreenUpdating = True
MsgBox "Highlighting complete.", vbInformation, xTitleId
End Sub2. Exécutez la macro en sélectionnant Exécuter (le bouton triangle vert dans l'éditeur VBA) ou appuyez sur F5 après avoir sélectionné le module. Une boîte de dialogue vous demandera de sélectionner la plage de dates que vous souhaitez analyser. La macro effacera automatiquement toute couleur de remplissage précédente et mettra en surbrillance en jaune les cellules avec des dates de plus de 30 jours (vous pouvez modifier la couleur si nécessaire).
Notes pratiques : - Cette solution VBA fonctionne bien pour les tâches récurrentes ou lors de l'analyse de grands tableaux. - Enregistrez toujours votre classeur avant d'exécuter du code VBA, surtout si vous utilisez des macros qui modifient le formatage. - Les macros VBA nécessitent des classeurs compatibles avec les macros (.xlsm) et des paramètres activant les macros. Pour les classeurs partagés ou en ligne, envisagez d'utiliser d'autres méthodes discutées ci-dessus.
Résolution des problèmes : Si la macro ne semble pas fonctionner, assurez-vous que les cellules de date sont correctement formatées et revérifiez la plage sélectionnée. Si des valeurs non-datées sont présentes, elles sont ignorées.
Utilisez une colonne d'assistance avec une formule pour marquer les dates de plus de 30 jours
Pour plus de flexibilité dans l'identification des anciennes dates – telles que le filtrage, le tri ou le déclenchement d'actions supplémentaires – vous pouvez utiliser une colonne d'assistance avec une formule Excel. Cette méthode est particulièrement utile lorsque vous devez traiter ou analyser les résultats signalés au-delà de la simple coloration des cellules.
1. Insérez une nouvelle colonne à côté de votre liste de dates (par exemple, si vos dates commencent dans la colonne A, ajoutez la nouvelle colonne B et nommez-la "Drapeau Échu"). Dans la première ligne de la colonne d'assistance (par exemple, B2), entrez la formule suivante :
=A2<TODAY()-30Cette formule vérifie si la date dans la cellule A2 est antérieure de plus de 30 jours à aujourd'hui. Si c'est vrai, elle renvoie VRAI, sinon FAUX.
2. Appuyez sur Entrée pour appliquer la formule, puis copiez-la pour toutes les lignes de votre plage de données. Pour faire cela rapidement, sélectionnez la cellule B2, faites glisser la poignée de remplissage vers le bas, ou double-cliquez dessus si des données adjacentes sont présentes.
3. Une fois terminé, vous pouvez filtrer ou trier par les valeurs VRAI/FAUX. Les lignes avec VRAI sont celles avec des dates de plus de 30 jours.
Application pratique : Vous pouvez maintenant filtrer les données, appliquer d'autres règles de formatage, ou utiliser la colonne signalée dans d'autres calculs ou processus automatisés. Cette approche est très efficace lorsque vous devez prendre des mesures supplémentaires en fonction de la date échue, comme générer des rapports ou envoyer des notifications.
Astuce : Ajustez le 30 dans la formule pour définir un seuil différent. Assurez-vous toujours que les références de cellule de la formule correspondent à votre plage de données réelle.
Où cela fonctionne le mieux : Cette méthode offre un contrôle granulaire et une traçabilité, la rendant idéale pour les grands ensembles de données ou lorsqu'on travaille avec des flux de travail automatisés.
Lors du choix de la méthode de mise en surbrillance appropriée, tenez compte de vos besoins : la mise en forme conditionnelle fonctionne mieux pour les indices visuels dynamiques ; les colonnes d'assistance permettent un traitement avancé ; le filtre/tri est optimal pour une revue rapide sans altérer la feuille ; le VBA est parfait pour les tâches récurrentes ou à volume élevé ; et Kutools pour Excel offre une sélection rapide et flexible pour les opérations manuelles ou groupées. Soyez toujours conscient des contraintes de formatage de date et de partage de classeur, et enregistrez votre fichier avant d'appliquer des modifications, surtout lors de l'utilisation de VBA ou d'outils complémentaires. Combiner des méthodes peut donner des solutions puissantes pour des flux de travail complexes.
Articles connexes :
Mise en forme conditionnelle des dates inférieures/supérieures à aujourd'hui dans Excel
Ce tutoriel vous montre comment utiliser la fonction AUJOURDHUI dans la mise en forme conditionnelle pour mettre en surbrillance les dates d'échéance ou futures dans Excel en détail.
Ignorer les cellules vides ou à zéro dans la mise en forme conditionnelle dans Excel
Supposons que vous ayez une liste de données avec des cellules à zéro ou vides, et que vous souhaitiez appliquer une mise en forme conditionnelle à cette liste tout en ignorant les cellules vides ou à zéro, que feriez-vous ? Cet article vous sera utile.
Copier les règles de mise en forme conditionnelle vers une autre feuille de calcul/classeur
Par exemple, vous avez mis en évidence des lignes entières en fonction des cellules en double dans la deuxième colonne (colonne Fruit), et coloré les trois valeurs les plus élevées dans la quatrième colonne (colonne Montant) comme indiqué dans la capture d'écran ci-dessous. Et maintenant, vous souhaitez copier la règle de mise en forme conditionnelle de cette plage vers une autre feuille de calcul/classeur. Cet article propose deux solutions de contournement pour vous aider.
Mettre en surbrillance les cellules en fonction de la longueur du texte dans Excel
Supposons que vous travailliez avec une feuille de calcul contenant une liste de chaînes de texte, et que vous souhaitiez mettre en surbrillance toutes les cellules dont la longueur du texte est supérieure à 15. Cet article discute de certaines méthodes pour résoudre cette tâche dans Excel.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels