XL 2010 Formule pour calculer le délais de résolution

[Sourzat]

Bonjour,

Je travaille sur un fichier excel pour gérer le suivi des incidents sur un logiciel. Nous avons besoin de calculer le délais de résolution de l'incident pour attribuer des pénalités.

Pour cela, j'aurais besoin de votre aide pour créer une formule qui me permet de calculer le délais de résolution entre la date de déclaration et la date de résolution (en jour et en heure) en fonction des jours ouvrés (lundi à vendredi) et en fonction des heures de travail du prestataire (8h30/12h00 et 13h30/18h00).

Je n’arrive pas à imbriquer une formule qui me prend toutes les contraintes. Actuellement, j'ai bien le calcul des jours ouvrés mais je ne sais pas comment ajouter le calcul des heures en fonction des horaires d'ouverture.

Merci de votre aide

 

[Hasco]

Bonjour,

En fichier joint un exemple. Je n'ai pas tout concaténer en une seule formule pour que vous puissiez suivre. Ce serait possible mais cela donne toujours des formules tarabiscotées difficiles à démêler en cas de besoin.

voyez ce que vous pouvez prendre.

Pour extraire l'heure d'une cellule (ex: B10) qui contient date et heure: =B10-ENT(B10)
Par contre s'il faut comparer ce résultat à une autre heure, il vaut mieux l'arrondir car dans l'opération
B10-ENT(B10) excel travail sur un grand nombre de décimale après la virgule.
0,647916666668607 = 15:33 après opération B11-ENT(B11)
0,647916666666667 = 15:33 cellule ne contenant que l'heure
Si vous mettez ces deux nombres au format horaire, les deux afficheront 15:33:00 mais en fait seront inégaux.
C'est pour cette raison que vous verrez des arrondis à la 6ème décimale.
Ce sont les imprécisions d'excel qu'il faut connaître et avec lesquelles nous devons jongler.

Cordialement

 

[Sourzat]

Merci de votre retour. Mais je crois que la formule proposée ne prend pas en compte les jours ouvrés ? Quand je fais le calcul manuellement je ne trouve pas le même résultat que vous...

 

[Hasco]

Re,

Si je prends mon calendrier papier on a bien 3 jours ouvrés du 2/1 au 6/1 2020 et 6 jours ouvrés du 10/1 au 17/1 2020

Maintenant, je me suis peu être perdu dans les heures de début et fin.

Je verrai cela demain.

bonne soirée

 

[Sourzat]

Oui nous avons bien trois jours ouvrés mais il ne faut pas les compter entièrement car le jour d'ouverture débute à 9:30 et le jour de résolution finit à 15:30...

Merci de votre aide

 

[Hasco]

Re,

Oui je suis bien d'accord mais là où je me suis mélangé les pinceaux, c'est que j'ai rajouté les heures travaillées le premier et dernier jour au lieu de retrancher les heures non-travaillées.

Il suffit d'inverser la logique.

cordialement

 

[Hasco]

Re,

Voici qui est corrigé.
Si cela ne va pas, dites quel résultats vous obtenez.

Cordialement

 

[Sourzat]

Merci beaucoup pour votre aide, le résultat est correct, il ne me reste plus qu'à imbriquer toutes les formules ensemble !

Bonne journée à vous

 

[Hasco]

Bonjour,

Petit conseil: si vous le pouvez, nommez vos cellules de limites horaire (par exemple: Matin_Debut,Matin_Fin,Soir_Debut,Soir_Fin) la formule finale s'en trouvera plus lisible et compréhensible dans un an à tout utilisateur de votre fichier.

Cordialement

 

[Sourzat]

Je suis désolée de vous déranger encore mais pour moi le deuxième calcul est faux, en calculant à la main je trouve 1j 11h30min au lieu de 1j21h24min...
Avez-vous une idée du problème svp ?

Merci d'avance

 

[Hasco]

Bonjour,

Avez-vous une idée du problème svp ?

Et vous ?

cordialement

 

[Hasco]

Bonjour,

J'avais oublié de retiré les heures d'après midi lorsque Résolution le matin et les heures du matin lorsque Déclaration l'après midi.

Voici qui est corrigé dans le fichier joint.

J'ai définit les noms suivant dans le classeur:

Matin_Début =TEMPS(8;30;0)
Matin_Fin =TEMPS(12;0;0)
Matin_Durée =Matin_Fin-Matin_Début

Soir_Début =TEMPS(13;30;0)
Soir_Fin =TEMPS(18;0;0)
Soir_Durée =Soir_Fin-Soir_Début

Formule du calcul d'heures non travaillées le jour de déclaration : =SI(ARRONDI(B10-ENT(B10);6)<Matin_Fin;ARRONDI(B10-ENT(B10);6)-Matin_Début;Matin_Durée+ARRONDI(B10-ENT(B10);6)-Soir_Début)

formule du calcul d'heures non travaillées le jour de résolution : =SI(ARRONDI(D10-ENT(D10);6)<Matin_Fin;Matin_Fin-ARRONDI(D10-ENT(D10);6)+Soir_Durée;Soir_Fin-ARRONDI(D10-ENT(D10);6))

ce qui nous donne le résultat que vous avez calculé manuellement.

Bonne journée