XL 2019 Calcul d'un délai avec une plage horaire simple

[pp62]

Bonjour,

J'ai cherché sur ce forum des discussions similaires mais je n'arrive pas à réutiliser les solutions trouvées.

Voilà mon problème, les utilisateurs utilisent notre système de tickets lorsqu'ils ont des questions.
J'ai besoin de calculer (avec une formule excel) notre délai de réponse pour chaque ticket crée depuis 2017.
Pour cela j'ai la date d'émission du ticket et la date de clôture (=date de réponse), nous avons une plage horaire de travail entre 09h et 18h (mais il est arrivé qu'une réponse a été faite après notre fermeture) et enfin j'aimerais ne pas prendre en compte les weekend et jours fériés.

J'ai trouvé quelques formules mais il y a des erreurs que je n'arrive pas à expliquer:
- mauvais calcul du délai (surligné en rouge)
- pas de résultat si le ticket arrive entre 18h01 et 23h59 (surligné en orange)

Je vous joins un exemple de mon fichier avec mes formules.
Merci d'avance pour votre aide.
PP

 

[CISCO]

Bonsoir

Regarde par exemple sur ce fil. La méthode utilisée est tordue, nécessite pas mal de calculs (les calculs sont faits en additionnant des secondes, et pas en faisant des différences), mais elle est facilement adaptable à diverses situations.

@ plus

 

[pp62]

Bonjour Cisco,

Je te remercie pour ta réponse, en effet je n'avais pas vu ce fil.
Malheureusement j'ai beau me triturer la tête, je n'arrive pas à l'appliquer dans mon tableau, alors que le fichier récupérés sur ce fil fonctionne.
Et puis problème supplémentaire, le calcul ne prend pas en compte les jours ouvrés et les jours fériés.

Avec mes formules, je pense n'être pas vraiment pas loin de ce que je cherche, c'est dommage !
Encore merci en tout cas.

 

[CISCO]

Bonsoir

Qu'est-ce que tu veux exactement : les durées entre la demande et la réponse ou uniquement celles à cheval avec les plages horaires ?

@ plus

 

[pp62]

Bonjour,

J'aimerais calculer les durées entre la demande et la réponse et ce même, si la réponse est faite plusieurs jours après. J'ai besoin de la durée qui est comprise dans ma plage horaires (hors weekend et jours fériés).

Notre plage horaire est de 09h à 18h (soit 9h d'emplitude).
Ex1: Le ticket arrive le 24/02/2020 à 08h00, la réponse est envoyée à 11h00. Le délai de réponse est donc de 2h. Pour l'instant j'arrive à calculer cet exemple.

Ex2: Le ticket arrive le 24/02/2020 à 18h30, la réponse est envoyée le 25/02/2020 à 11h00. Le délai de réponse est de 2h. Et là ma formule ne fonctionne plus car l'heure d'arrivée est après 18h.

Ex3: Le ticket arrive le 24/02/2020 à 08h00, la réponse est envoyée le 28/02/2020 à 17h00. Le délai de réponse est de 44h (=4jours*9h + 8h). Et là pareil, ma formule ne fonctionne plus.

Voilà j'espère avoir été plus clair sur ma demande et je croise les doigts pour que quelqu'un puisse m'aider.

D'avance merci.

 

[CISCO]

Bonjour

Peut-être avec ces formules matricielles, à valider avec Ctrl+maj+entrer, le résultat étant au format [hh]:mm

@ plus

P.S : Cette méthode utilisant la fonction LIGNE, elle n'est pas valable que si la durée, exprimée en minute, dépasse approximativement le nombre de lignes possible sur une feuille Excel. Cela laisse de la marge...

 

[pp62]

Cisco,

Un grand MERCI pour ton travail, en effet cela fonctionne.
Petit hic je n'arrive pas à copier, ni à reproduire ta dernière formule dans mon tableau. Mais ce n'est pas grave je vais repartir de ton fichier et y insérer le reste de mes données.

Encore merci d'avoir pris le temps, cela m'aide beaucoup.
Ce site est vraiment d'une grande aide !

 

[pp62]

Hello,

Avant de clôturer, je me permets de déposer la formule de Cisco, ainsi même si le fichier Test n'est plus dispo, ça aidera quand même d'autres utilisateurs:

Date de création = A6
Date de la réponse = B6
Plage horaire de 09h à 18h (D1=09:00 et E1=18:00)
Jours fériés = de J8 à J18

=SIERREUR(SOMMEPROD(NON(ESTNUM(EQUIV(ENT(ENT(A6)+ligne_min/(24*60));$J$8:$J$18;0)))*(JOURSEM(ENT(A6)+ligne_min/(24*60);2)<6)*(D$1*24*60<MOD(ligne_min;24*60))*(MOD(ligne_min;24*60)<=E$1*24*60))/(24*60);"")

Encore merci.
Bonne journée.