Table des matières
L'une des tâches typiques d'un utilisateur de Microsoft Excel. Nous avons deux plages de dates de type « début-fin ». Le défi consiste à déterminer si ces plages se chevauchent et, si oui, de combien de jours.
Intersection ou pas ?
Commençons par résoudre la question de savoir s'il existe en principe une intersection d'intervalles ? Supposons que nous ayons un tableau des quarts de travail pour les employés comme celui-ci :
On voit clairement que les quarts de travail de Yaroslav et Elena se croisent, mais comment calculer cela sans recourir à la construction d'un calendrier et à un contrôle visuel? La fonction nous aidera SUMPRODUCT (SOMMEPROD).
Insérons une autre colonne dans notre tableau avec une formule qui donne la valeur booléenne TRUE si les dates se croisent :
Combien de jours dure la traversée ?
S'il n'est fondamentalement pas facile de comprendre si nos intervalles se croisent ou non, mais de savoir exactement combien de jours se situent exactement dans l'intersection, alors la tâche devient plus compliquée. En toute logique, il faut « pomper » jusqu'à 3 situations différentes dans une formule :
- les intervalles ne se chevauchent pas
- l'un des intervalles absorbe complètement l'autre
- les intervalles se croisent partiellement
De temps en temps, je vois la mise en œuvre de cette approche par d'autres utilisateurs en utilisant un tas de fonctions IF imbriquées, etc.
En fait, tout peut être fait magnifiquement en utilisant la fonction MEDIAN (MÉDIAN) de la catégorie Statistique.
Si nous désignons conditionnellement le début du premier intervalle comme N1, et la fin pour K1, et le début de la seconde N2 et fin pour K2, alors en termes généraux notre formule peut s'écrire :
=MÉDIANE(N1;K1+ 1 ;K2+1)-MÉDIANE(N1;K1+ 1 ;N2)
Compact et élégant, n'est-ce pas ? 😉
- Comment Excel fonctionne-t-il réellement avec les dates ? Comment calculer le nombre de jours calendaires ou ouvrés entre les dates ?
- Comment construire un calendrier calendrier (vacances, formations, quarts de travail…) dans Excel en utilisant la mise en forme conditionnelle ?
- Vérification d'une ou plusieurs conditions avec les fonctions IF (IF)