Microsoft 365 Différence d'heures entre deux dates avec deux plages horaires + jours fériés

[ksebf]

Bonjour à tous,

J'espère que tout le monde se porte bien en ce début d'année 2022 !

Je cherche à créer une cellule me calculant la différence entre deux dates en heures. J'ai parcouru le forum et j'ai trouvé une formule qui ma va bien, mais il me manque la façon de rajouter deux plages horaires dans la journée et en prenant en compte les jours fériés.

Actuellement, avec cette formule, j'ai les heures entre deux dates en prenant en compte les jours ouvrés, une heure de début et une heure de fin.

=SI(ET(D2<>"";E2<>"");(NB.JOURS.OUVRES(D2;E2)-1)*(L$2-I$2)+SI(NB.JOURS.OUVRES(E2;E2);MEDIANE(MOD(E2;1);L$2;I$2);L$2)-MEDIANE(NB.JOURS.OUVRES(D2;D2)*MOD(D2;1);L$2;I$2);0)

Ce que j'aimerais rentrer dans cette formule, c'est une prise en compte des heures seulement entre :
8:00 et 12:00 (I2;J2)
14:00 et 18:00 (K2;L2)

Le but pour moi est que si la date de début commence à 11:30 et la date de fin est à 14:30, j'obtienne un résultat d'une heure (et pas trois comme c'est déjà le cas !)

et les jours fériés (N2:N7)
18/04/2022
26/05/2022
06/06/2022
14/07/2022
15/08/2022
01/11/2022

Si vous avez une idée, je suis preneur

 

[djidji59430]

Bonjour à tous,

Je n'ai d'idée, mais des choses m'interpellent.
Par exemple
Ta formule doit changer en fonction des priorités ?
Critique, on ne mange pas, mineure ou évolution , on peut aller à la cantine ?
Critique a 17h50, qu'est ce qui se passe ?
Mineure a 17h50, qu'est ce qui se passe ?
Prenons ton exemple d'anomalie mineure
Il a une heure pour la prendre en compte et ce n'est pris en compte que le lendemain ?
C'est un tableau pour des municipaux ?

Crdlmt

 

[ksebf]

Bonjour à tous,

Je n'ai d'idée, mais des choses m'interpellent.
Par exemple
Ta formule doit changer en fonction des priorités ?
Critique, on ne mange pas, mineure ou évolution , on peut aller à la cantine ?
Critique a 17h50, qu'est ce qui se passe ?
Mineure a 17h50, qu'est ce qui se passe ?
Prenons ton exemple d'anomalie mineure
Il a une heure pour la prendre en compte et ce n'est pris en compte que le lendemain ?
C'est un tableau pour des municipaux ?

Crdlmt

Hello Djidji, merci pour ton message. Nan, ma formule n'a pas à changer selon les priorités. C'est pour un suivi de ticketing dans le cadre d'un projet. En gros j'ai 1 heure max pour prendre en compte une anomalie mineure. Si je ne le fais pas, je ne respecte pas ma ce pour quoi on m'a engagé, c'est traité en lieu et temps cette anomalie. Lors de réunion de suivi, il faut que je montre si mon équipe respecte ou pas les délais sur lesquels nous nous sommes engagés. Et si le délai court sur la pause de midi (12/14 heures), il ne faudrait pas que ça soit pris en compte dans le calcul du délai de prise en compte

 

[djidji59430]

Oui, mais t'as pas répondu a toutes mes questions

Crdlmt

 

[ksebf]

La formule doit changer en fonction des priorités ? Nope
Critique, on ne mange pas, mineure ou évolution , on peut aller à la cantine ? Tout le monde est en arrêt de 12:00 à 14:00
Critique a 17h50, qu'est ce qui se passe ? 10 minutes décomptés le jour même jusqu'à 18h00, 20 minutes le jour d'après à partir de 9:00
Mineure a 17h50, qu'est ce qui se passe ? 10 minutes décomptés le jour même jusqu'à 18h00, 50 minutes le jour d'après à partir de 9:00
Prenons ton exemple d'anomalie mineure
Il a une heure pour la prendre en compte et ce n'est pris en compte que le lendemain ? 10 minutes décomptés le jour même jusqu'à 18h00, 50 minutes le jour d'après à partir de 9:00
C'est un tableau pour des municipaux ? Pour une maintenance applicative du déploiement d'un ERP de mon entreprise J'essaye d'améliorer nos suivis

 

[djidji59430]

Critique, on ne mange pas, mineure ou évolution , on peut aller à la cantine ? Tout le monde est en arrêt de 12:00 à 14:00

alors comment on peut avoir un pris en compte entre 12 14 h ?

 

[ksebf]

Humm on peut pas, mais je vois ou tu veux en venir, dans mon exemple je l'ai mis à 13h00

Fichier de base corrigé

 

[djidji59430]

re
Ta formule me semble inexacte…

 

[ksebf]

Salut Djidji, c'est exactement ça que je cherchais !

Par contre, je suis désolé, ça doit sûrement être élémentaire, mais je ne sais pas comment tu as pu configurer le "repas" dans ta formule :
=SI(ET(MOD(D2;1)<M;MOD(E2;1)>ApM);F2-(repas*(JOURS(E2;D2)+1));SI(ET(MOD(D2;1)<M;MOD(E2;1)<M);F2-(repas*(NB.JOURS.OUVRES.INTL(D2;E2;1;fériés)-1));SI(ET(MOD(D2;1)>ApM;MOD(E2;1)>ApM);F2-(repas*(NB.JOURS.OUVRES.INTL(D2;E2;1;fériés)-1)))))

Comment as tu pu faire ça ?!?

En tout état de cause, je te remerciement grandement !

 

[ksebf]

Ok je viens juste de comprendre avec le gestionnaire des noms

Cet après-midi, en rentrant d'un brunch, j'étais en train de me demander si je pouvais pas faire ma formule sur les 10 heures (donc de 8 heures à 18 heures) et refaire une formule du même genre, mais que sur la pause du midi (donc de 12 heures à 14 heures), et faire une différence entre les deux valeurs résultantes !

Mais ta solution est plus propre !

C'est parfait, merci énormément Djidji !

 

[djidji59430]

et ta formule sur la cellule rouge, c'est bon ? je n'avais pas le meme résultat

 

[ksebf]

Hello Djidji, oui, je viens de vérifier, il y a un petit souci dans la formule, les 2 heures des repas sont comptées même les week-end !

Ce qui fait que si un ticket est ouvert un vendredi, et répondu un lundi, les heures du week-end ne seront pas additionnés, mais les heures de repas de ce même week-end seront soustraits, donc on a une différence de 4 heures pour chaque week-end "traversé"

 

[ksebf]

Ce que je vois comme solution (dont je n'ai pas trouvé la formule), ce serait de rajouter deux heures par jour de week end (et surement par jour férié) qu'il y a entre les deux dates.

 

[djidji59430]

re

 

[ksebf]

Re, ta formule fonctionne Djidji avec une erreur d'heure dans la journée il me semble. J'ai remplacé le 6/24 par un 8/24 pour avoir la bonne valeur sur l'ensemble

Du coup j'ai fonctionné aussi sur une période avec un jour férié, et tout fonctionne correctement !

=SI(ET(MOD(D7;1)<M;MOD(E7;1)>ApM);(NB.JOURS.OUVRES.INTL(D7;E7;1;fériés)-1)*8/24+MOD(E7-D7;1)-repas;SI(ET(MOD(D7;1)<M;MOD(E7;1)<M);(NB.JOURS.OUVRES.INTL(D7;E7;1;fériés)-1)*8/24+MOD(E7-D7;1)-SI(JOUR(D7)=JOUR(E7-1);0;repas);SI(ET(MOD(D7;1)>ApM;MOD(E7;1)>ApM);(NB.JOURS.OUVRES.INTL(D7;E7;1;fériés)-1)*8/24+MOD(E7-D7;1))))