papouclo

Considérons deux périodes p1 , débutant en d1 et se finissant en f1 , et p2 , débutant en d2 et se finissant en f2 . On cherche le début d et la fin f de la période commune à p1 et p2 avec l’objectif de calculer la durée de cette période commune.

Remarque :
d1 , d2 , f1 , f2 peuvent être des dates ou des temps. On peut les écrire dans des cellules dont les références seront utilisées dans les formules qui suivent ou les écrire directement dans ces formules (bien que cette méthode soit à éviter sauf pour des données dont on est sûr qu’elles ne varieront pas) à l’aide des fonctions:
- DATE ou DATEVAL s’il s’agit de dates,
- TEMPS ou TEMPSVAL ou des fractions de dénominateur 24 (ou multiples de 24) s’il s’agit de temps.

Bornes de l’intersection
Des schémas visualisant quatre des cas possibles ( sur six ) donnent l’idée que d et f se déduisent des données d1 et d2 d’une part, f1 et f2 d’autre part à l’aide des fonctions MAX et MIN.

Périodes disjointes

Calcul de la durée de l’intersection
- Si d et f sont des temps (hh :mm :ss) , la durée commune (en hh : mm : ss) aux périodes p1 et p2 est donnée par f – d donc directement par la formule :
= MIN ( f1 ; f2 ) - MAX ( d1 ; d2 )
- Si d et f sont des dates , la durée commune ( en jours) aux périodes p1 et p2 est donnée par la même différence f – d à laquelle il faut rajouter 1 car il faut compter le jour d dans la durée d’où la formule:
= MIN ( f1 ; f2 ) - MAX ( d1 ; d2 ) + 1
Problème :
Dans les deux cas de périodes disjointes , le deuxième terme d de la différence f – d étant supérieur au premier terme f, le résultat est négatif et qu’il s’agisse de jours ou d’heures, il y aura un problème d’affichage (########). Or nous aurions envie de répondre 0 dans les deux cas.
D’où une transformation des deux formules précédemment données pour interdire des réponses négatives :
- Pour une durée en heures, on aura la formule :
= MAX ( MIN ( f1 ; f2 ) - MAX ( d1 ; d2 ) ; 0 )
- Pour une durée en jours, on aura la formule :
= MAX ( MIN ( f1 ; f2 ) - MAX ( d1 ; d2 ) + 1 ; 0 )

Nombre de jours ouvrés communs à deux périodes
En appelant fériés la plage des dates de jours fériés correspondant aux deux périodes p1 et p2, le nombre de jours ouvrés (samedi et dimanche non ouvrés) communs aux deux périodes pourra s’ obtenir avec la formule :
= MAX ( NB.JOURS.OUVRES (MAX ( d1 ; d2 ) ; MIN ( f1 ; f2 ) ; fériés ) ; 0 )
On emploiera, à partir de la version Excel 2010, la fonction NB.JOURS.OUVRES.INTL si les jours non ouvrés de la semaine sont autres que le samedi et le dimanche.

Période à cheval sur deux jours consécutifs
d1 et f1 étant des temps donnant le début et la fin d’une période à cheval sur deux jours consécutifs. En toute rigueur, le mieux serait d’entrer ces données (donc toutes les données du même type dans la feuille) sous le format jj/mm/aaaa hh : mm , ce qui respecterait la chronologie. Mais le souci de rapidité, une certaine facilité, font que l’on se contente seulement d’entrer les heures au format hh : mm pour traduire des situations telles que :
- L’intervention s’est faite de 23:00 à 03:00.
- Les heures de nuit dans cette entreprise vont de 19:00 à 05:00.
A la lecture, l’intellect humain rétablit la chronologie mais malgré toute sa puissance de calcul, Excel est incapable d’une telle interprétation et il faut lui donner un coup de pouce pour qu’il n’y ait plus l’incohérence de l’heure de fin d’action inférieure à l’heure du début.
N’oublions pas que l’unité de mesure des temps pour Excel est le jour. Il suffit de rajouter 1 à f1 pour rétablir cette chronologie mais seulement dans le cas où l’on a : f1 < d1.
On peut :
- Soit rentrer par exemple dans une cellule pour f1 27:00 au lieu de 3:00 si d1 est 23:00
- Soit d’écrire dans les formules f1 + (f1<d1) à la place de f1.