Formule générique
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Sommaire
Pour calculer les heures de travail entre deux dates avec une planification personnalisée, vous pouvez utiliser une formule basée sur les fonctions WEEKDAY et SUMPRODUCT, à l'aide de ROW, INDIRECT et MID. Dans l'exemple illustré, la formule en F8 est:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Ce qui renvoie 36 heures, selon un horaire personnalisé où 8 heures sont travaillées du lundi au vendredi, 4 heures sont travaillées le samedi et le lundi 3 septembre est un jour férié. Les jours fériés sont fournis dans la gamme nommée G6: G8. L'horaire de travail est entré sous forme de chaîne de texte dans la colonne D et peut être modifié à volonté.
Remarque: Il s'agit d'une formule matricielle qui doit être saisie avec Ctrl + Maj + Entrée. Si vous avez une journée de travail standard de 8 heures, cette formule est plus simple.
Explication
À la base, cette formule utilise la fonction WEEKDAY pour déterminer le jour de la semaine (c'est-à-dire lundi, mardi, etc.) pour chaque jour entre les deux dates données. WEEKDAY renvoie un nombre compris entre 1 et 7. Avec les paramètres par défaut, dimanche = 1 et samedi = 7.
L'astuce de cette formule consiste à assembler un tableau de dates que vous pouvez ajouter à la fonction WEEKDAY. Ceci est fait avec ROW avec INDIRECT:
ROW(INDIRECT(B6&":"&C6))
ROW interprète les dates concaténées comme des numéros de ligne et renvoie un tableau comme celui-ci:
(43346;43347;43348;43349;43350;43351;43352)
Chaque nombre du tableau représente une date. La fonction WEEKDAY évalue ensuite le tableau et renvoie un tableau de valeurs du jour de la semaine:
(2;3;4;5;6;7;1)
Ces chiffres correspondent au jour de la semaine de chaque date. Ils sont fournis à la fonction MID comme argument de numéro de départ, avec la valeur dans D6, "0888884" pour le texte:
MID("0888884",(2;3;4;5;6;7;1),1)
Parce que nous donnons à MID un tableau de nombres de début, il renvoie un tableau de résultats comme celui-ci:
("8";"8";"8";"8";"8";"4";"0")
Ces valeurs correspondent aux heures travaillées chaque jour de la date de début à la date de fin. Notez que les valeurs de ce tableau sont du texte, pas des nombres. Pour convertir en nombres réels, nous multiplions par un deuxième tableau créé pour gérer les vacances, comme expliqué ci-dessous. L'opération mathématique contraint le texte à des valeurs numériques.
Vacances
Pour gérer les vacances, nous utilisons ISNA, MATCH et la plage nommée "vacances" comme ceci:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Cette expression utilise MATCH pour localiser les dates qui sont dans la plage nommée jours fériés en utilisant le même tableau de dates généré ci-dessus avec INDIRECT et ROW. MATCH renvoie un nombre lorsque des vacances sont trouvées et l'erreur # N / A si ce n'est pas le cas. La fonction ISNA "retourne" les résultats de sorte que TRUE représente les vacances et FALSE représente les non-vacances. ISNA renvoie un tableau ou des résultats comme celui-ci:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Enfin, les deux tableaux sont multipliés l'un par l'autre dans SUMPRODUCT. L'opération mathématique contraint TRUE et FALSE à 1 et zéro, et les valeurs de texte du premier tableau à des valeurs numériques (comme expliqué ci-dessus), donc à la fin nous avons:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Après multiplication, nous avons un seul tableau dans SUMPRODUCT contenant toutes les heures de travail dans la plage de dates:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT additionne ensuite tous les éléments du tableau et renvoie un résultat de 36.
