Comment détecter des dates ou des plages horaires qui se chevauchent dans Excel ?
Dans Excel, des dates ou des plages horaires chevauchantes peuvent provoquer des conflits d’emploi du temps, des problèmes d’allocation de ressources ou des erreurs compromettant l’intégrité des données. Détecter efficacement ces chevauchements est essentiel pour gérer des plannings, organiser des événements, administrer des systèmes de réservation ou suivre des calendriers de projet où aucune période ne doit se superposer à une autre. Cet article vous propose un guide étape par étape présentant plusieurs méthodes pratiques pour identifier des dates ou des plages horaires chevauchantes dans Excel, comme le montre la capture d’écran ci-dessous.
Vérifier les dates ou Plage horaire chevauchantes à l’aide d’une formule
Vérifier les dates ou Plage horaire chevauchantes à l’aide d’une formule
Lorsque vous devez vérifier systématiquement si des dates ou des plages horaires se chevauchent, les formules Excel offrent une solution rapide et flexible. Cette approche convient parfaitement aux petits et moyens jeux de données, ainsi qu’aux cas où vous avez besoin d’une sortie logique (VRAI ou FAUX) indiquant, ligne par ligne, la présence éventuelle de chevauchements.
Cas d’usage typiques : Planification des horaires des employés, réservations d’événements, suivi des phases de projet ou gestion locative, où chaque ligne représente un intervalle avec une date ou une heure de début et une date de fin.
Limitations : Bien qu’efficace pour des listes de taille modérée, cette méthode peut se révéler moins adaptée aux très grands jeux de données ou à la génération de rapports complets sur les chevauchements entre plusieurs enregistrements.
1. Sélectionnez toutes les cellules contenant vos dates de début. Avec la plage mise en surbrillance, cliquez dans la Zone de nom (le champ situé à gauche de la Barre de formule) et saisissez un nom explicite, tel que datedebut. Appuyez sur Entrée pour confirmer. Cette étape vous permet de faire facilement référence à l’ensemble de la liste dans vos formules. Voir la capture d’écran :
2. De la même manière, sélectionnez les cellules de Date de fin, saisissez un nom de cellule dans la Zone de nom, tel que datefin, puis appuyez à nouveau sur Entrée. Nommer des plages rend vos formules plus lisibles et réutilisables.
3. Cliquez sur une cellule vide située sur la même ligne que votre premier enregistrement – par exemple, C2 – là où vous souhaitez afficher les résultats de chevauchement, puis saisissez la formule suivante :
=SUMPRODUCT((A2<enddate)*(B2>=startdate))>1Remplacez A2 par la cellule contenant la date de début de votre enregistrement, et B2 par celle de sa date de fin. datefin et datedebut utilisent les noms que vous avez définis. Cette formule vérifie si l’intervalle actuel chevauche au moins un autre dans la liste. Appuyez sur Entrée, puis faites glisser la poignée de recopie vers le bas pour appliquer la formule à toutes les lignes à vérifier. Pour chaque ligne, VRAI signifie que la plage correspondante chevauche au moins une autre ; sinon, aucun chevauchement n’a été détecté.
Assurez-vous que datedebut et datefin font référence aux colonnes entières contenant respectivement les valeurs de début et de fin. Ajustez les références de cellules si nécessaire, en fonction de la disposition de vos colonnes ou de la présence d’en-têtes.
Remarques importantes et dépannage :
- Si vous obtenez une erreur #VALEUR!, vérifiez que le nom de votre cellule et vos références sont corrects, et que vos colonnes de dates ne contiennent ni texte ni données date/heure mal formatées.
- Cette approche gère les cas de chevauchement où les périodes ne sont pas entièrement distinctes. Les intervalles qui se touchent uniquement aux extrémités — c’est-à-dire lorsque la date de fin de l’un coïncide exactement avec la date de début d’un autre — ne sont généralement pas considérés comme chevauchants, mais vous pouvez adapter ce comportement en modifiant l’inégalité dans la formule.
- Pour les plages horaires (y compris heures et minutes), la formule fonctionne de la même manière que pour les dates, à condition que toutes les cellules soient formatées de façon cohérente en tant qu’heures ou dates.
Code VBA – Détecter automatiquement les dates ou Plage horaire chevauchantes dans les grands jeux de données ou pour Générer le rapport
Si vous travaillez régulièrement avec de grands jeux de données et que vous recherchez une méthode plus automatisée pour détecter les chevauchements – notamment afin de générer des rapports synthétiques ou de marquer simultanément toutes les entrées conflictuelles – VBA peut considérablement simplifier le processus. Cette approche élimine les vérifications manuelles, s’adapte à des centaines, voire des milliers d’intervalles, et peut être personnalisée pour mettre en évidence ou lister l’ensemble des paires chevauchantes.
Quand l’utiliser : Recommandé pour les utilisateurs avancés qui gèrent de grandes bases de planification, des ressources partagées, ou toute personne souhaitant générer des journaux listant tous les chevauchements détectés, plutôt qu’un simple indicateur VRAI/FAUX par ligne.
Inconvénients potentiels : Nécessite l’activation des macros, une certaine familiarité avec VBA et une sauvegarde prudente des données avant la première exécution afin d’éviter toute surcharge accidentelle.
1. Cliquez sur Outils de développement > Visual Basic pour ouvrir la fenêtre Microsoft Visual Basic pour Applications. Ensuite, cliquez sur Insertion > Module et collez le code ci-dessous dans la fenêtre du module :
Sub FindOverlappingDateRanges()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim lastRow As Long
Dim overlapList As String
Dim msg As String
Dim Start1, End1, Start2, End2
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assumes data starts in row 2
overlapList = ""
For i = 2 To lastRow
Start1 = ws.Cells(i, 1).Value
End1 = ws.Cells(i, 2).Value
If Start1 <> "" And End1 <> "" Then
For j = 2 To lastRow
If i <> j Then
Start2 = ws.Cells(j, 1).Value
End2 = ws.Cells(j, 2).Value
If Start2 <> "" And End2 <> "" Then
If Start1 < End2 And End1 > Start2 Then
overlapList = overlapList & "Row " & i & " overlaps with Row " & j & vbCrLf
End If
End If
End If
Next j
End If
Next i
If overlapList <> "" Then
msg = "The following rows have overlapping date/time ranges:" & vbCrLf & overlapList
Else
msg = "No overlapping date/time ranges found."
End If
MsgBox msg, vbInformation, "KutoolsforExcel"
End Sub2. Après avoir saisi le code, cliquez sur Exécuter ou appuyez sur Entrée pour lancer le code. La macro analyse les paires d’intervalles de dates situées dans les colonnes A (Début) et B (Fin) et signale tout chevauchement détecté. Elle affiche une boîte de message listant toutes les lignes en conflit, facilitant ainsi l’audit ou l’investigation.
- Assurez-vous que les dates de début et de fin figurent respectivement dans les colonnes A et B, à partir de la ligne 2 (la ligne 1 étant réservée aux en-têtes). Ajustez les plages si vos données sont organisées différemment.
- Toutes les cellules doivent contenir des valeurs de date/heure valides, sans aucune cellule vide dans la plage comparée.
- Sauvegardez vos fichiers importants avant d’exécuter ou de modifier du code VBA pour éviter toute perte de données.
Conseil : Vous pouvez améliorer le code VBA pour marquer directement les chevauchements dans la feuille de calcul en coloriant les lignes ou en inscrivant les résultats dans une colonne adjacente.
Utiliser la mise en forme conditionnelle – Mettre visuellement en évidence les plages chevauchantes directement dans la feuille de calcul pour une identification plus facile
Utiliser la mise en forme conditionnelle est une méthode pratique pour signaler visuellement les intervalles de dates ou d’heures chevauchants directement dans votre classeur. Cette solution est particulièrement utile dans les emplois du temps chargés, les Diagramme de Gantt ou les calendriers d’événements, lorsque vous souhaitez repérer immédiatement les enregistrements en conflit.
Idéal pour : Les utilisateurs qui souhaitent un retour visuel immédiat ou des indices colorés, sans avoir à saisir de formules sur chaque ligne ni exécuter de code. Particulièrement efficace pour les vérifications interactives et les présentations.
Limitations : Les jeux de données volumineux peuvent ralentir la réactivité ; bien que les chevauchements soient mis en évidence, les paires précises et les décomptes détaillés ne sont pas générés.
Procédure d’application :
- Sélectionnez la plage des dates de début (par exemple,)A2:A100) et celle des dates de fin (B2:B100), ou sélectionnez les deux colonnes ensemble si les plages sont côte à côte.
- Dans l’onglet Accueil, cliquez sur Utiliser la mise en forme conditionnelle > Nouvelle règle.
- Sélectionnez Utiliser une formule pour déterminer les cellules à mettre en forme.
- Saisissez cette formule dans la zone dédiée (en supposant que votre sélection commence à la ligne 2) :
=SUMPRODUCT(($A2<$B$2:$B$100)*($B2>$A$2:$A$100))>1 - Cliquez sur Format…, choisissez une couleur de remplissage pour mettre en évidence les plages chevauchantes, puis cliquez sur OK pour appliquer.
Une fois la règle appliquée, toute ligne dont l’intervalle sélectionné chevauche un autre dans votre plage est mise en évidence visuellement, facilitant ainsi la détection des problèmes sans avoir à examiner chaque entrée une par une.
Conseil : Ajustez $A$2:$A$100 et $B$2:$B$100 pour qu’ils correspondent à votre vraie plage de données, et assurez-vous que les références pointent vers la première ligne de votre sélection.
Précautions : Si vous souhaitez mettre en évidence uniquement l’une des deux colonnes (par exemple, uniquement les dates de début), utilisez tout de même la logique de formule correspondante. Tenez compte des chevauchements aux limites selon vos besoins logiques spécifiques.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition 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é de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels