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

Sourzat

XLDnaute Nouveau
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
 

Fichiers joints

Roblochon

XLDnaute Accro
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
 

Fichiers joints

Sourzat

XLDnaute Nouveau
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...
 

Roblochon

XLDnaute Accro
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

XLDnaute Nouveau
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
 

Roblochon

XLDnaute Accro
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
 

Sourzat

XLDnaute Nouveau
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
 

Roblochon

XLDnaute Accro
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

XLDnaute Nouveau
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
 

Roblochon

XLDnaute Accro
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
 

Fichiers joints

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas