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.

 

[Jocelyn]

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

Bonjour le Forum,
Bonjour lucifer71,

un essai en se servant de plusieurs colonnes pour découper le calcul.

je pense malgré tout qu'une solution par macro serait la bien venu, malheureusement je suis une bille a ce jeu la alors juste pour dépanner

Cordialement

 

[lucifer71]

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

Merci beaucoup pour cette réponse.
C'est effectivement bien ce que je cherche à faire.

2 "petites" choses:
- je sais que je vais paraître pénible, mais les 5 formules, dupliquées sur les 8000 lignes, me donne un fichier vraiment très très lourd, et il est du coup assez difficile de travailler avec (fichier en partage sur un réseau).
- Les macros ne sont pas vraiment envisageables, trop contraignant pour l'organisation que nous avons...

Merci en tout cas pour ton aide.
PS: J'avoue que j'ai un peu de mal à comprendre l'intégralité des formules, surtout pour les colonnes "compliquées" (N et/ou P)

 

[lucifer71]

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

Bonjour à tous,

Je suis toujours avec mon problème de formules, et j'avoue que je pédale dans la semoule...
J'ai testé la solution proposée par Jocelyn (encore merci), mais dupliqué sur mes 8000 lignes, il faut bien avouer que excel peine vraiment à faire le calcul...
Je fait des changement dans ce fichier en moyenne toutes les 5 minutes, et ces formules, meme si très efficaces, demandent trop de ressources pour pouvoir présenter une solution viable.

Est-ce que vous aurriez une autre idée de solution, ou une autre piste à explorer pour faire ce type de calcul ?

D'avance merci pour votre aide

 

[lucifer71]

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

Bonjour A tous,

Je me permet de faire remonter le topic
J'essaye toujours de trouver une solution à mon problème. Je combine les formules depsuis quelques jours, mais je n'arrive toujours pas à trouver une solution qui convienne aux critères que j'ai indiqués...

Si une âme charitable veut bien se pencher sur le problème, je lui serait reconnaissant.

Un grand merci par avance.
Bonne journée.

 

[Jocelyn]

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

Bonjour lucifer71,

Hum hum désolé je n'avais pas vu avant que tu étais revenu sur le fil.

A priori ton soucis est maintenant un problème de temps et de lourdeur, alors il n'y plus qu'une solution c'est de passer par VBA.

Par contre je suis une bille a ce jeu la alors je ne pourrai pas t'aider, aussi je te conseil d'ouvrir un nouveau topic en précisant dans le titre que tu souhaites du VBA

Voila désolé je ne peux rien faire de plus

Cordialement

 

[lucifer71]

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

Bonjour

Merci pour cette réponse.
Malheureusement, le VBA n'est pas envisageable pour mon problème.
Je bosse sur un fichier en partage sur réseau, et celà pose trop de problèmes d'utilisation.
En clair, c'est forcément par formule que je dois passer... :-/

Merci encore pour cette réponse.

bonne journée.

 

[lucifer71]

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

Bonjour,

Après quelques tests du fichier proposé (solution que je pense prendre du coup), j'avoue que je fais face à quelques problèmes...
En fait, lors de la réception d'une demande le vendredi, et réponse le mardi, le calcul du nombre d'heures me parait pas/peu logique.
De la même façon, pour le calcul du nombre d'heure pour une entrée de demande et réponse le même jour (un lundi par exemple), le calcul n'est pas correct.

J'ai mis les mains dans le cambouis, et j'ai essayé de décortiquer les formules. J'arrive à comprendre le principe de fond, meme si certaines subtilités m'échappent.
Par contre, une partie d'une des formule reste complètement étanche à ma compréhension:

JOURSEM(LIGNE(INDIRECT(B2+1&":"&D2-1));2)=1)*1)

Est-ce que vous pourriez m'expliquer à quoi celà sert exactement ???

Merci d'avance pour votre aide.
Bonne journée.

 

[eriiic]

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

Bonjour,

je veux bien jouer mais si tu pouvais détailler les résultats à obtenir sur tes exemples stp.
Sur 3 colonnes : 1er jour, jours intermédiaires, dernier jour. Le dernier jour est un peu nébuleux...
eric

edit: non, c'est bon. Je pensais que tu avais mis les résultats des lignes 3:6

 

[lucifer71]

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

Quand tu indique que le dernier jour est un peu nébuleux, j'avoue ne pas comprendre vraiment en fait ??

Est-ce que tu pourais juste me dire ce qui n'est pas clair, que je puisse essayer de détailler/expliquer comme il faut ?

Merci beaucoup

 

[eriiic]

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

J'avais éditer mon post en même temps que ta réponse.
Finalement j'ai quand même une question pour date fin :

mar 18/06/2013

19:00:00

et

ven 21/06/13 18:00 tu comptes combien avec une fermeture à 17h ? eric

 

[lucifer71]

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

Pour le mardi, si tu réponds à 19h, tu compte 19h pour la journée de mardi (car c'est un jour "entier" pour lequel on compte 24h)

Pour le vendredi, je compte 17h seulement, meme si je répond à 18h (je ne suis pas censé répondre après 17h, donc toute réponse faite après cet horaire n'est que "bonus" pour mon comptage en quelque sorte).

Merci en tout cas de te pencher sur mon cas

 

[eriiic]

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

Du coup j'ai rajouté une colonne pour le ven > 17:00
A contrôler bien sûr, et dis ce que ça donne au niveau temps de réaction.

eric

 

[lucifer71]

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

Un grand merci!

Donc, pour les réponses: Niveau temps de réaction, ça me parait très correct sur mon fichier complet. En tout cas, plus que ma solution actuelle.
Par contre, pour les temps de réponses, je ne suis pas sur d'avoir très bien expliqué:

Exemple: Je recoit une demande le mardi à 12h, et je réponds le vendredi à 19h.
Je compte donc:
Mardi 12h + mercredi 24h + Jeudi 24h + vendredi 17h.
17h seulement pour le vendredi, car je "vérouille ma plage horaire du vendredi"

Seconde chose: si la réponse se fait à cheval sur 2/3 semaines, il me semble que ce n'est pas bon non plus.
Par exmeple, je reçoit la demande le 03 juin à 12h, et je réponds le 11 juin à 12h.
Je compte donc:
lundi 3: 12
+ mardi 4: 24
+ mercredi 5: 24
+ jeudi 6: 24
+ vendredi 7 : 17
+ lundi 10: 16
+ mardi 11: 12
Total = 12+24+24+24+17+16+12= 129h. => Le fichier en compte 192 (??)

Par contre, j'avoue que ton approche est vraiment intéressante car très "minimaliste" et donc forcément plus légère...

 

[eriiic]

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

Ah, tu ne comptes pas les we intercalaires, ça va compliquer...

+ jeudi 6: 24
+ vendredi 7 : 17
+ lundi 10: 16
+ mardi 11: 12

Un vendredi et un lundi ne comptent jamais pour 24h même s'ils sont au milieu ????

Je revois ça ce soir.
eric