Excel : créer un planning automatique de locations avec détection des chevauchements

Difficulté

Intermédiaire

Vous pouvez créer un planning automatique de locations dans Excel avec quatre éléments : une table de réservations, un calcul de durée, un test de chevauchement et un statut Disponible ou Réservé. Le point clé est de comparer, pour une même ressource, si l’heure de début d’une réservation est antérieure à la fin d’une autre et si l’heure de fin est postérieure à son début.

Le tutoriel ci-dessous s’applique à des salles, des véhicules ou tout autre bien réservable. Les formules proposées utilisent NB.SI.ENS, MAX, MIN et la mise en forme conditionnelle.

Prérequis

Préparez un tableau Excel avec une ligne par réservation. Utilisez de vraies dates et heures Excel, pas du texte saisi librement.

Dans l’exemple, les colonnes sont : Ressource, Début, Fin, Durée, Chevauchement et Statut.

Matériel nécessaire

• Excel avec fonctions de base et mise en forme conditionnelle
• Un tableau de réservations avec dates/heures valides

Étapes

1. 1

   Créer le tableau de réservations

   

   Saisissez vos en-têtes en ligne 1 : Ressource en A, Début en B, Fin en C, Durée en D, Chevauchement en E et Statut en F. Transformez ensuite la plage en tableau via Insertion > Tableau pour recopier les formules plus proprement.

   Entrez une ligne par réservation, par exemple une salle ou un véhicule, avec un début et une fin sur la même ligne. Vérifiez que la colonne Fin contient bien une date et une heure complètes, même si la réservation dure moins d’un jour.

2. 2

   Calculer la durée de chaque location

   Dans la colonne Durée, soustrayez la date de début à la date de fin. Si votre première réservation est en ligne 2, entrez en D2 : =C2-B2.

   Appliquez ensuite un format adapté, par exemple [h]:mm pour afficher des durées supérieures à 24 heures. Vous obtenez ainsi une durée exploitable sans formule plus complexe.

3. 3

   Détecter les chevauchements pour une même ressource

   

   Dans la colonne Chevauchement, comptez les réservations de la même ressource dont l’intervalle recoupe la ligne en cours. En E2, utilisez : =NB.SI.ENS($A:$A;A2;$B:$B;"<"&C2;$C:$C;">"&B2)-1.

   Cette formule renvoie le nombre de lignes en conflit avec la réservation courante. Le -1 retire la ligne elle-même du comptage.

4. 4

   Afficher un statut disponible ou réservé

   Ajoutez un statut lisible selon le contenu de la ligne. Si une ligne correspond à une réservation saisie, entrez en F2 : =SI(ET(A2<>"";B2<>"";C2<>"");"Réservé";"Disponible").

   Ce statut est utile si vous tenez aussi une grille de créneaux à remplir manuellement. Dans un simple journal de réservations, chaque ligne complète sera logiquement marquée Réservé.

5. 5

   Calculer la durée de chevauchement si vous en avez besoin

   Si vous voulez mesurer combien de temps deux réservations se recoupent, utilisez MAX et MIN entre deux lignes. Par exemple, entre la ligne 2 et la ligne 3 : =MAX(0;MIN(C2;C3)-MAX(B2;B3)).

   Le résultat donne une durée positive seulement si les deux intervalles se croisent. Cette formule sert surtout pour des contrôles ciblés ou des analyses complémentaires, pas pour compter tous les conflits d’un grand tableau.

6. 6

   Colorer automatiquement les conflits

   

   Sélectionnez les lignes de votre tableau, puis ouvrez Accueil > Mise en forme conditionnelle > Nouvelle règle. Choisissez une règle basée sur une formule et entrez une formule liée à la colonne Chevauchement, par exemple : =$E2>0.

   Appliquez un remplissage rouge ou orange pour faire ressortir les réservations en conflit. Vous pouvez ajouter une deuxième règle, par exemple =$E2=0, pour colorer les lignes sans conflit en vert clair.

7. 7

   Tester le planning avec un exemple simple

   Saisissez trois lignes pour la même ressource. Exemple : Salle A, du 10/06/2026 09:00 au 10/06/2026 11:00 ; Salle A, du 10/06/2026 10:30 au 10/06/2026 12:00 ; Salle A, du 10/06/2026 12:00 au 10/06/2026 13:00.

   Les deux premières lignes doivent afficher un chevauchement supérieur à 0. La troisième ne doit pas être signalée en conflit avec la deuxième si elle commence exactement à l’heure où l’autre se termine.

Erreurs fréquentes & dépannage

La première erreur fréquente est de mélanger des dates texte et de vraies dates Excel. Les formules de durée, de comparaison et de mise en forme conditionnelle deviennent alors incohérentes.

La deuxième erreur est d’oublier le critère sur la ressource dans NB.SI.ENS. Sans ce filtre, Excel compare toutes les salles ou tous les véhicules entre eux et crée de faux conflits.

La troisième erreur est d’utiliser des heures sans date pour des réservations sur plusieurs jours. Dans ce cas, deux créneaux situés à des dates différentes peuvent sembler se chevaucher à tort.

Astuces & pour aller plus loin

Ajoutez une liste déroulante pour la colonne Ressource avec la validation des données. Cela évite les variantes de saisie du type Salle A, salle A ou Salle-A.

Si vous gérez beaucoup de créneaux, filtrez d’abord par ressource et par période. La lecture des conflits devient plus simple, même avant d’automatiser davantage le classeur.

Questions fréquentes

Peut-on utiliser ce modèle pour des véhicules au lieu de salles ?

Oui. Il suffit que chaque véhicule soit identifié de façon unique dans la colonne Ressource, puis que chaque ligne porte un début et une fin valides.

La formule détecte-t-elle les réservations qui se touchent sans se chevaucher ?

Non. Avec les critères Début < Fin courante et Fin > Début courant, deux réservations consécutives qui se suivent exactement ne sont pas considérées comme en conflit.

Faut-il absolument utiliser MAX et MIN dans la détection globale ?

Non. Pour repérer les conflits dans tout le tableau, NB.SI.ENS suffit. MAX et MIN sont surtout utiles pour calculer la durée exacte d’un recouvrement entre deux réservations précises.

Conclusion

Pour un planning fiable, gardez une structure simple : une ressource, un début, une fin, puis des colonnes calculées. Si vous devez choisir une seule automatisation prioritaire, commencez par NB.SI.ENS et la mise en forme conditionnelle, car ce sont elles qui rendent les conflits visibles immédiatement.