Formule générique
=(NETWORKDAYS(start,end)-1)*(upper-lower) +IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)
Sommaire
Pour calculer le nombre total d'heures de travail entre deux dates et heures, vous pouvez utiliser une formule basée sur la fonction JOURS RÉSEAU. Dans l'exemple illustré, E5 contient cette formule:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower) +IF(NETWORKDAYS(C5,C5),MEDIAN(MOD(C5,1),upper,lower),upper) -MEDIAN(NETWORKDAYS(B5,B5)*MOD(B5,1),upper,lower)
où "inférieur" est la plage nommée H5 et "supérieur" est la plage nommée H6.
Remarque: cet exemple a été inspiré par un défi de formule sur Chandoo, et une solution plus complète fournie par le maître de formule Barry Houdini sur le forum MrExcel.
Explication
Cette formule calcule les heures de travail totales entre deux dates et heures, qui se produisent entre une heure «inférieure» et «supérieure». Dans l'exemple illustré, l'heure inférieure est 9h00 et l'heure supérieure est 17h00. Ceux-ci apparaissent dans la formule comme les plages nommées «inférieur» et «supérieur».
La logique de la formule est de calculer toutes les heures de travail possibles entre les dates de début et de fin, inclusivement, puis de reculer toutes les heures à la date de début qui se produisent entre l'heure de début et l'heure inférieure, et toutes les heures à la date de fin qui se produisent entre l'heure de la fin et l'heure supérieure.
La fonction NETWORKDAYS gère l'exclusion des week-ends et des jours fériés (lorsqu'ils sont fournis sous forme de plage de dates). Vous pouvez passer à NETWORKDAYS.INTL si votre emploi du temps comporte des jours ouvrables non standard.
Formatage de la sortie
Le résultat est un nombre qui représente le nombre total d'heures. Comme tous les temps Excel, vous devrez formater la sortie avec un format numérique approprié. Dans l'exemple illustré, nous utilisons:
(h):mm
Les crochets empêchent Excel de se déplacer lorsque les heures sont supérieures à 24. En d'autres termes, ils permettent d'afficher des heures supérieures à 24. Si vous avez besoin d'une valeur décimale pour les heures, vous pouvez multiplier le résultat par 24 et le mettre en forme numéro régulier.
Version simple
Si les heures de début et de fin se produisent toujours entre les heures inférieure et supérieure, vous pouvez utiliser une version plus simple de cette formule:
=(NETWORKDAYS(B5,C5)-1)*(upper-lower)+MOD(C5,1)-MOD(B5,1)
Pas d'heure de début et d'heure de fin
Pour calculer le nombre total d'heures de travail entre deux dates, en supposant que tous les jours sont des jours de travail complets, vous pouvez utiliser une formule encore plus simple:
=NETWORKDAYS(start,end,holidays)*hours
Voir l'explication ici pour plus de détails.
