Calculer un chevauchement de plages horaires

[Wista]

Bonjour à tous,

Je suis bloqué sur une problématique depuis hier et je n'arrive pas à aboutir à une solution satisfaisante.
Je tente ma chance sur le forum en espérant que vous aurez plus de réussite...

Soit un atelier dont le temps d'ouverture est 8:00 AM/17:00PM.
Un fichier xls me renvoie la liste des défaillance d'une des machine de l'atelier avec l'heure/date d'apparition et l'heure/date de disparition de chaque défaut.

Ex :
En A -> Apparition, en B -> Disparition

Je cherche à déduire de la durée total du défaut les tranches horaires situées hors du temps d'ouverture.
Un défaut peut s’étaler sur plusieurs jours et j'ai donc besoin de savoir gérer l'antériorité...

Ex :
A = 16:00 le 26/05/17 B = 3:00 le 27/05/17
Le calcul doit me renvoyer une durée de 1h00 au lieu de 11h.

J'espère avoir été suffisamment clair ! merci d'avance pour votre aide.

 

[vgendron]

Hello
Voir PJ pour début de piste
le résultat final est en C4

 

[Wista]

Hello vgendron,

Merci beaucoup de ta réponse. C'est effectivement un début, mais ces formules que j'avais aussi envisagées ne fonctionnent pas dans le cas ou le défaut est contenu dans le temps d'ouverture.
Ex :
A = 14:00 B = 16:00

 

[Wista]

Voilà ce que j'ai testé pour le moment. Cela semble fonctionner, sauf quand il considère que A = 3:00AM du jour suivant est inférieur à B = 17:00PM du jour précédent :s) :

Avec A = Apparition du défaut, B = Disparition du défaut, O = ouverture, F fermeture.

=SI(ET(A<O;O<B;B<F);B-O;SI(ET(O<A;A<F;B>F);F-A;SI(ET(A>O;F>B);ABS(B-A);0)))

Des idées pour améliorer cette formule ?

 

[Victor21]

Bonsoir Wista, vgendron.

Une proposition en pj :

=SI(B3<A3;"Fin < Début !";MIN(B3;ENT(B3)+B$1)-MAX(A3;ENT(A3)+A$1)-(ENT(B3)-ENT(A3))*(A$1+1-B$1))

Soit la différence entre la date et l'heure de fin (limitée à l'heure de fermeture) et la date et l'heure de début (limitée à l'heure d'ouverture) , différence à laquelle on soustrait autant de fois la durée de fermeture que de jours concernés.

 

[CISCO]

Bonjour à tous, bonjour Victor21, Vgendron

Pour le fun, une autre manière, plus tordue, pour obtenir le nombre d'heures d'arrêt pendant les heures d'ouverture.

@ plus

P.S : B1 doit être inférieur à 24:00

 

[Wista]

Bonjour Victor21, CISCO,

Merci beaucoup pour votre aide ! j'ai trituré vos formules dans tous les sens et elles fonctionnent très bien
J'ai cependant une question subsidiaire : comment feriez-vous évoluer cette formule si le temps d'ouverture était scindé en deux plages horaires ?

Ex 8h00 - 13h00 et 14h00-17h00

A+

 

[CISCO]

Bonjour

C'est justement l'avantage de la méthode que j'ai utilisée dans ma dernière pièce jointe : il ni a pas grand chose à faire pour prendre en compte deux périodes d'ouverture au lieu d'une. Deux parenthèses en plus, un copier-coller, un A transformé en C, un B transformé en D, et c'est fini.

Dans E3, cela donne

=(SOMMEPROD((A$1*24*60<listedesminutes)*(listedesminutes<=B$1*24*60))+SOMMEPROD((C$1*24*60<listedesminutes)*(listedesminutes<=D$1*24*60)))/(24*60)

@ plus

 

[Victor21]

Bonjour, Wista.

Bonjour Victor21, CISCO,

Merci beaucoup pour votre aide ! j'ai trituré vos formules dans tous les sens et elles fonctionnent très bien
J'ai cependant une question subsidiaire : comment feriez-vous évoluer cette formule si le temps d'ouverture était scindé en deux plages horaires ?
Ex 8h00 - 13h00 et 14h00-17h00
A+

Je rechercherais une nouvelle solution, mais je n'en ai actuellement aucune envie :
-vous ne revenez que 7 jours après la réponse, et uniquement parce que vous avez à nouveau besoin d'aide, à cause d'une mauvaise analyse du problème au départ.

Une piste cependant : déduisez le nombre de fois où la période contient les plages (entières et partielles) entre 13:00 et 14:00

Edit : Bonjour, CISCO Monsieur est bien bon

 

[Wista]

Merci à vous deux pour votre aide. Le problème était bien identifié, je voulais simplement commencer avec un cas de figure simple, avant de m'assurer d'avoir bien compris le mécanisme me permettant de faire évoluer le calcul.

@CISCO : Pas moyen d'intégrer votre méthode à mon fichier... j'ai systématiquement des erreurs de type #REF qui sont apparemment liées aux cellules contenant les dates/heures.
Par ailleurs, je n'arrive pas à comprendre l'utilisation de la fonction INDIRECT que je n'avais jamais vue avant...
Pouvez-vous expliciter un peu plus ?
Je joins mon fichier dépersonnalisé au cas ou vous remarqueriez quelque chose.

 

[CISCO]

Bonjour

Tes données en colonnes A et B contiennent des dates avec des secondes (ex 26/05/2017 09:10:23), ce qui n'était pas le cas dans le fichier initial (ex 26/05/2017 09:10:00). Je regarde ce que je peux faire pour contourner ce problème.

@ plus

 

[CISCO]

Bonjour

Il faut faire les calculs en seconde au lieu de les faire en minute, donc, rajouter des * 60 dedans.
Dans le cas avec une seule période de travail, essayes dans L2 avec

SOMMEPROD((M$1*24*60*60<MOD(LIGNE(INDIRECT(ARRONDI((A2-ENT(A2))*24*60*60+1;1)&":"&(ARRONDI((B2-ENT(A2))*24*60*60;1))));24*60*60))*(MOD(LIGNE(INDIRECT(ARRONDI((A2-ENT(A2))*24*60*60+1;1)&":"&(ARRONDI((B2-ENT(A2))*24*60*60;1))));24*60*60)<=N$1*24*60*60))/(24*60*60)

, puis tire cette formule vers le bas.

Comme la formule utilise la liste des lignes, de la ligne X à la ligne Y, X correspondant à l'heure du début de l'indisponibilité exprimée en seconde, et Y à la fin de la durée exprimée en seconde, cela ne fonctionne que si ces nombres X et Y sont inférieurs au nombre de lignes max de la feuille utilisée, 1048576 sur Excel 2010, soit approximativement 291 heures. Autrement dit, si l'arrêt dure approximativement plus de 291 heures, tu obtiendras un #REF car la ligne Y demandée n'existe pas. Pour le vérifier, il suffit de rajouter un mois à une des dates de la colonne B, et tu obtiendras sur la ligne correspondant #REF.

Est-ce très gênant ?

@ plus

 

[Wista]

Quel idiot, j'aurais du voir cette histoire de secondes ! merci énormément tu m'enlève une sacré épine du pied et tu m'as appris plein de choses !
Il y a très peu de chance que mon système s'arrête plus de 291h, ce n'est donc pas un problème très gênant.
Les formules sont costaudes il faut un bon PC

Bonne soirée

 

[CISCO]

Bonjour

...
Les formules sont costaudes il faut un bon PC
Bonne soirée

Une fois qu'on a compris comment ça fonctionne, ce n'est pas très dur, mais, par contre, cela demande beaucoup de calculs car on ne fait pas par exemple 100 - 10 = 90 mais 1+1+1+1+1+1......+1 = 90. En faisant le calcul en seconde, on a environ 60 fois plus de calculs !!!

@ plus