calcul d'une durée avec plage horaire en condition

[lefenec]

Bjr à tous,

Utilisateur depuis lontemps d'Excel pour divers usages, j'ai toujours eu du mal avec les formules "complexes" et encore plus avec les dates.... alors si vous pouvez me donner un petit coup de main sur mon problème ?

Voila, il faut que je détermine la durée d'une action entre deux dates. Mais que cette durée tienne compte de mes horaires d'ouverture de mon service.

Je m'explique :

- un usager me fait une demande à une date et une heure (exemple: 02/01/2009 10:34:46 ) que je stock dans une colonne A de mon fichier Excel

- je traite sa demande à une date et heure (exemple 06/01/2009 22:00:03 ) que je retrouve colonne B

Dans mon exemple, ma demande est faite le vendredi 2 janvier et je traite la demande le mardi 6 janvier.

Mon service est ouvert tous les jours de 9h à 17h du lundi au vendredi (fermé le samedi et dimanche).

Mon souhait est de connaitre la durée "contractuelle" que j'ai mis pour traiter la demande de l'usager. En ne comptabilisant pas mes horaires de fermeture de 17h à 9h et le WE.

J'essai depuis 2jrs à trouver le bon format de cellule et la formule qui va bien.... mais rien à faire je n'y arrive pas.....

Si vous pouviez m'aider ?

Merci d'avance à vous tous.

 

[hoerwind]

Re : calcul d'une durée avec plage horaire en condition

Bonjour,

Cela demande une petite explication.

Comment le traitement de la demande peut-il être fait à 22:00:03 alors que les service est fermé à 17:00:00 ?

Faut-il en conclure qu'une demande peut également être faite avant 9:00:00 ?
Et qu'une demande ou son traitement peuvent être fait durant le weekend ?
Ne faut-il pas tenir compte des jours fériés et des périodes de congé ?

Un petit exemple en pièce jointe reprenant tous les cas de figure et les résultats souhaités aiderait fortement à la définition des contraintes.

 

[lefenec]

Re : calcul d'une durée avec plage horaire en condition

Merci pour votre retour,

En effet un usager peut faire une demande quand il veut. En règle général c'est entre le lundi 7h et le vendredi soir 22h, rarement le WE.

De mon coté j'ai un "engagement" de prise en charge d'une demande pendant mes horaires de mon service, soit de 9h à 17h du lundi au vendredi.

C'est pour cela que mon souhait est de ne calculer que le délais écoulé pendant mes horaires de mon service.

exemple 1 : un usager fait une demande le matin à 7h, je la prend en compte à 10h et je la traite le jour même à 17h ==> mon délai de prise en compte est de 9h à 10h soit 1h et mon délai de traitement est de 9h à 17h, soit 8h

exemple 2 : idem exemple 1 mais je traite la demande à 19h, mon délai de traitement reste le même, soit 8h... car je souhaite vraiment ramener cela à un horaire "contractuel"

exemple 3 : idem que exemple 1, mais je traite la demande le lendemain à 10h, ma durée est donc calculé depuis 9h jusqu'au lendemain fin de traitement à 10h moins ma fermeture de la nuit de 17h à 9h, soit le jour J de 9h à 17h + J+1 de 9h à 10h = 9h

Pour le moment je me contenterai juste des jours ouvrées dans l'année.

je ne sais pas comment joindre mon fichier avec mes données

 

[Gael]

Re : calcul d'une durée avec plage horaire en condition

Bonjour Lefennec, Hoerwind,

Un essai ci-joint.

En E1 et B1 j'ai mis les heures d'ouverture et de fermeture.

Le début est ramené à l'heure d'ouverture s'il est inférieur et la fin ramenée à l(heure de fermeture si elle est supérieure.

Si tu as une plage de dates pour les jours fériés, il suffit de rajouter cette plage en 3ème paramètre de la fonction "NB.JOURS.OUVRES".

@+

Gael

 

[Gael]

Re : calcul d'une durée avec plage horaire en condition

Re,

Petite (et même grosse) erreur dans les calculs.

Ci-joint rectification.

@+

Gael

 

[lefenec]

Re : calcul d'une durée avec plage horaire en condition

Grand grand merci Gael, (Hoerwind aussi),

Pour m'avoir aidé. Ca marche super bien. J'ai contrôlé et franchement c'est super.
J'espère pouvoir aussi contribuer "humblement" à aider d'autres forumeur un jour comme vous le faites.

Milles mercis encore.

 

[ROGER2327]

Re : calcul d'une durée avec plage horaire en condition

Bonjour à tous

Une autre proposition :

[COLOR="DarkSlateGray"]=NB.JOURS.OUVRES($A2;$B2;FERIES)*($H$1-$G$1)-MAX(0;MOD(A2;1)-$G$1)+MAX(0;MOD(A2;1)-$H$1)-MAX(0;$H$1-MOD(B2;1))+MAX(0;$G$1-MOD(B2;1))[/COLOR]

avec 9:00 en G1, 17:00 en H1. FERIES est un plage de jours fériés (argument facultatif).
J'ai quelques résultats différents de ceux de Gael : à vérifier, donc.​

ROGER2327

 

[ROGER2327]

Re : calcul d'une durée avec plage horaire en condition

Suite...

La formule donnée ci-dessus est incomplète.
Formule corrigée :

[COLOR="DarkSlateGray"]=NB.JOURS.OUVRES($A2;$B2;FERIES)*($I$3-$I$2)-NB.JOURS.OUVRES($A2;$A2;FERIES)*(MAX(0;MOD(A2;1)-$I$2)-MAX(0;MOD(A2;1)-$I$3))-NB.JOURS.OUVRES($B2;$B2;FERIES)*(MAX(0;$I$3-MOD(B2;1))-MAX(0;$I$2-MOD(B2;1)))[/COLOR]

avec 9:00 en I2, 17:00 en I3. FERIES est un plage de jours fériés (argument facultatif).
Ci-joint, classeur avec calcul du délai de prise en charge et calcul de la durée de traitement.​

ROGER2327