Calcul d'une durée avec contraintes horaires

[shingo94]

Bonjour à vous tous,

Je viens chercher de l'aide car mon expérience Excel est peu développé et j'espère pouvoir m'améliorer grâce à vous et un jour, pourquoi pas, apporté également mon aide.

Exposé du problème.

Je cherche une formule qui pourrait me calculer la durée qu'il y a entre deux dates/heures en prenant en compte une plage horaire.

A1=27/12/2007 14:56:48
A2=04/01/2008 14:47:14
Plage horaire : 6h00-23h00

exemple1:
A1= 23/12/2007 14:30:00
A2= 26/12/2007 15:00:00

la durée est de :
sur 23/12/2007 ===> 23:00:00 - 14:30:00 = 08:30:00
sur 24/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 25/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 26/12/2007 ===> 15:00:00 - 06:00:00 = 11:00:00

===> total de durée = 53:30:00



De plus A1 et A2 peuvent etre dans la plage horaire (23h à 6h)


exemple2:
A1= 23/12/2007 05:30:00 (donc ds le calcule on prendra a 06:00:00)
A2= 26/12/2007 15:00:00

la durée est de :
sur 23/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 24/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 25/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 26/12/2007 ===> 15:00:00 - 06:00:00 = 11:00:00

===> total de durée = 62:00:00


exemple3:
A1= 23/12/2007 14:30:00
A2= 26/12/2007 03:00:00

la durée est de :
sur 23/12/2007 ===> 23:00:00 - 14:30:00 = 08:30:00
sur 24/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 25/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 26/12/2007 ===> 03:00:00 inferieur a 06:00:00 = 00:00:00

===> total de durée = 42:30:00


exemple4:
A1= 23/12/2007 05:30:00
A2= 26/12/2007 03:00:00

la durée est de :
sur 23/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 24/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 25/12/2007 ===> 23:00:00 - 06:00:00 = 17:00:00
sur 26/12/2007 ===> 03:00:00 inferieur a 06:00:00 = 00:00:00

===> total de durée = 51:00:00



Voila j'ai pas mal détaillé avec des exemples en esperant avoir été clair


j'ai trouvé une fonction qui traite a peu pret mon probleme mais cela ne marche pas completement, parce que ça traite les jours ouvrés (donc "supprime les samedis et dimanches") et si A1 ou A2 en dehors de la Plage horaire : 6h00-23h00, le resultat ne correspond pas

(NB.JOURS.OUVRES(A1;A2)-2)*("23:00"-"6:00")+"23:00"-MOD(A1;1)+MOD(A2;1)-"6:00"
au format [hh]:mm:ss

D'avance merci pour toute l'aide que vous pourrez me donner.

en PJ: un fichier test XD

 

[Denis132]

Re : Calcul d'une durée avec contraintes horaires

Bonsoir shingo94 et bienvenue sur le forum,

J'avais fini ma formule lorsque je me suis aperçu que je ne tenais pas compte du mois et ouf, il est trop tard. Je te l'envoie tout de même, peut-être que ça peut t'inspirer ou quelqu'un d'autres.

@+

Denis

P.S. Dans tes 2 premiers exemples, tu écris:

15:00:00 - 06:00:00 = 11:00:00

 

[abcd]

Re : Calcul d'une durée avec contraintes horaires

Bonjour raymonizuka,

Assez difficile d'obtenir les résultats demandés.
Normalement 15:00 - 6:00 = 9:00 et non 11:00

Vois quand même si la formule en pièce jointe peut te convenir.

abcd

 

[shingo94]

Re : Calcul d'une durée avec contraintes horaires

merci à vous 2 pour votre aide

effectivement : 15:00 - 6:00 = 9:00 et non 11:00. Hereusement que je ne repasse pas le BAC S

Je jette un coup d'oeil sur vos propositions

Merci,

 

[shingo94]

Re : Calcul d'une durée avec contraintes horaires

B2 = 23/12/2007 14:30:00
B3 = 26/12/2007 15:00:00

=(DATEDIF(B2;B3;"md")-1)*17/24+(MOD(B2;1)<23/24)*(23/24-MAX(MOD(B2;1);6/24))+(MOD(B3;1)>6/24)*(MIN(MOD(B3;1);23/24)-6/24)

apres avoir etudier les fonctions:

(DATEDIF(B2;B3;"md")-1) ===> 3-1 = 2 OK

mais j'ai pas compris la suite du code
MOD(B2;1) renvoi le reste de B2/1 soit B2

(MOD(B2;1)<23/24)*(23/24-MAX(MOD(B2;1);6/24))+(MOD(B3;1)>6/24)*(MIN(MOD(B3;1);23/24)-6/24)

 

[Denis132]

Re : Calcul d'une durée avec contraintes horaires

Salut Shingo, abcd,

N'oublie pas que nous comptons des heures donc mod(b2;1) donnera le nombre d'heures contenu dans b2 divisé par 1( 1 qui équivaut à 1 jour ou 24 heures) comme si tu écrivais b2/24.

@+

Denis

 

[abcd]

Re : Calcul d'une durée avec contraintes horaires

Bonjour,

Autre façon d'expliquer :

18/06/2008 12:14 renvoie en format Standard 39617,50991
MOD(date+heure;1) renvoie 0.50991, c'est à dire la partie décimale.
0,50991 au format hh:mm renvoie 12:14

C'est une méthode pour extraire l'heure d'une chaine contenant la date et l'heure.
Pour extraire la date, c'est la fonction ENT.

abcd

 

[shingo94]

Re : Calcul d'une durée avec contraintes horaires

merci pour vos explications

je comprends mieux le fonctionnement du code

encore merci,