Calcul de différence en heure en deux dates - Avec plage horaire

[lucifer71]

Bonjour à tous.

Je me permet de venir poser une question sur le forum, suite à un problème rencontré pour le calcul de différence de temps entre 2 dates.
J'ai fouillé le forum, fait pas mal de tests de formules, mais j'avoue que je bloque un peu (voir beaucoup :-/ )

Voici ce que je voudrais réaliser:
- Je reçois des demandes de clients, auxquelles je dois répondre. Demandes dont je stocke les informations dans un fichier excel (date d'entrée, heure d'entrée, date de réponse, heure de réponse).
- Je voudrais compter la différence en heure entre 2 dates.
- Je possède une plage horaire fixe, du lundi matin 8h au vendredi soir 17h, mais tous les autres jours sont comptés en entier. En clair:
Lundi : 08h->24h
Mardi / Mercredi / Jeudi: 00h->24h
Vendredi: 00h -> 17h
- pour toute demande reçue pendant le week-end (ou après le vendredi 17h), le démarrage du comptage heures commence le lundi matin 08h.
- une demande peut prendre plus de 2/3 semaines à être répondue.
- je possède un fichier de 8000 lignes environ, donc la solution la moins "lourde" serait la meilleure (pas de formule matricielle si possible). Pas de contrainte sur le nombre de colonnes intermédiaires.

J'ai fait quelques essais avec comptage des jours ouvrés, exclusion des lundi et vendredi...etc, mais j'avoue que je bloque toujours, et que je me retrouve soit avec des heures négatives, soit avec des heures fausses. Bref, ce n'est pas un grand succès.

Est-ce que vous pourriez me dire comment faire et/ou m'apporter une solution ?

Je vous joins un exemple excel du fichier que j'utilise (simplifié, bien entendu).

d'avance merci pour votre aide.
Bonne Journée.

 

[lucifer71]

Re : Calcul de différence en heure en deux dates - Avec plage horaire

Ehhhh non, je ne les compte pas.

Clair que ça complique, et que du coup, je galère franchement à faire ce fichier. Mais j'essaye aussi d'autres pistes en parallèle.

Effectivement, les lundis et vendredis ne comptent jamais comme 24h.

Merci en tout cas de bien vouloir prendre du temps pour m'aider, c'est super sympa !

 

[eriiic]

Re : Calcul de différence en heure en deux dates - Avec plage horaire

Re,

J'ai ajouté le correctif pour les WE inclus et pour une fin le vendredi.

Sur le principe : je compte toujours x jours de 24h après avoir ôté les jours début et fin.
Puis je soustrais 63h par WE inclus (-2*24h - 7h(vendredi) - 8h(lundi))
Les heures des jours début et fin sont comptés sur les heures corrigées (en K:L)

Les cellules oranges sont à considérer comme des constantes. Laisses-les ainsi (tu peux les déplacer, elles sont nommées) pour qu'elles ne soient évaluées qu'une fois et non à chaque ligne.

J'ai laissé N:Q séparés pour la compréhension. Si tu veux tu peux en faire une formule unique, ça ne devrait pas pénaliser en temps.
A contrôler à fond bien sûr.

eric

 

[lucifer71]

Re : Calcul de différence en heure en deux dates - Avec plage horaire

Bonjour,

Déjà un grand merci de t'être penché sur mon problème.
Je comprend le principe des formules que tu utilise, sauf quelques détails qui sont encore un peu flous, mais je persiste et j'essaye de comprendre.
J'ai fait quelques test pour voir ce que ça donne, et j'ai trouvé un léger "couac" dans le calcul de la plage horaire du vendredi soir, qui en fin de compte n'est pas prise en compte (??) : étonnant car de ce que j'ai compris de tes formules, ça devrait être le cas....
Je m'explique: demande reçue le mardi à 12:00, et répondue le vendredi à 18:00.
Le calcul me donne une plage horaire de 76h au lieu des 75h théorique (car "arret" du compteur le vendredi à 17h)

J'avoue ne pas vraiment comprendre comment changer ceci... ??

Merci encore pour le temps que tu passe à résoudre ce casse tête !

 

[eriiic]

Re : Calcul de différence en heure en deux dates - Avec plage horaire

Bonjour,

Je m'explique: demande reçue le mardi à 12:00, et répondue le vendredi à 18:00.
Le calcul me donne une plage horaire de 76h au lieu des 75h théorique (car "arret" du compteur le vendredi à 17h)

Heuuu, moi je lis 78:00 en résultat, pas 76:00. Fait un effort de précision et d'attention pour ne pas embrouiller tout stp.
Et ce n'est pas plutôt : 12+24+24+17=77 qu'il faut ?
Quand tu détectes une anomalie prend l'habitude de détailler le résultat pour expliquer le pourquoi du résultat.

J'avoue ne pas vraiment comprendre comment changer ceci... ??

Comme les colonnes n'étaient pas limitées (et c'est à utiliser pour accélérer les calculs, à chaque fois qu'un résultat doit être utilisé par plusieurs formules il faut le ressortir qu'il ne soit évalué qu'une seule fois) c'est une fusée à plusieurs étages.
Les calculs sont effectués par rapport à des dates et heures corrigées :
-en J: date de début corrigée (attention, parfois on a besoin de la date réelle)
-en K : heure de début corrigée
- en L: heure de clôture corrigée
Il faut donc que tu te penches sur L.
Effectivement il y avait une erreur, j'utilisais le n° de semaine de date début au lieu de fin.
Mettre en L2 :
=SI(JOURSEM($D2;2)=5;MIN($E2;_h17);$E2)
mais ça donne 77h... Si tu dois trouver 75h explique comment, qcq chose m'échappe.

eric

 

[lucifer71]

Re : Calcul de différence en heure en deux dates - Avec plage horaire

77, pas 75. Tu as tout à fait raison -> je reprends un café, merci pour la correction ;-)

Haaaaa, ok. Je comprends du coup beaucoup mieux la formule que tu utilise pour le calcul, et ca me parait plus logique maintenant

Un imense merci pour tout ce travail !!!!!!!!
Si jamais j'ai des corrections à apporter, je vais essayer de me débrouiller tout seul maintenant que j'ai compris le principe de calcul. Plus qu'à adapter ça à mon fichier complet.

Le résultat est parfait, je peux pas vraiment demander mieux !
Encore merci pour tout.

Je te souhaite une excellente journée !!!