Calcul excel complexe

despi75 - Dernière réponse le 15 févr. 2012 à 18:33

Bonjour,
Bonjour à tous,
Je m'arrache les cheveux je fais donc appel a votre savoir.
Mon prestataire travail de 9h à 19h sans interruption. Nous avons 5 délais d'intervention : délai 1 : 2h, délai 2 : 5h, délai 3 : 10h, délai 4 : 20h et délai 5 : 5 jours (1jrs =9hà19h jours ouvrés)
Je souhaite créer un tableau Excel pour calculer automatiquement les heures limites d'intervention.
Exemple je demande une intervention à 15h00 pour un délai 2, je souhaite que le tableau calcul la date limite à laquelle le prestataire doit intervenir (soit 15h00 + 5h ) en tenant compte du temps de travail qui est de 9H à 19 H)dans ce cas il devra intervenir au plus tard le lendemain à 10 H.
Merci mille fois d'avance à ceux ou celles qui pourront m'aider.

Calcul excel complexe »

Suggestions

Calcul excel complexe
Formules EXCEL de Base 2 » Fiches pratiques
[Excel] Compter des valeurs (Résolu) » Forum
Mode d'emploi excel (Résolu) » Forum
[Excel] Mise à jour automatique des liens (Résolu) » Forum
Plusieurs lignes dans une cellule excel (Résolu) » Forum

Plus

Réponse

seb_067 63Messages postés 6 février 2012Date d'inscription 11 mars 2012Dernière intervention 8 févr. 2012 à 17:04

C'est fait =)

Alors voilà un exemple, j'utilise 5 colonnes (A, B, C, E et F) :

Colonne A :
Date Demande
15/09/2012 15:00
16/09/2012 15:00
17/09/2012 15:00
18/09/2012 15:00
19/09/2012 15:00

Colonne B :
Delai
1
2
5
4
3

Colonne C (réponse) :
Date Limite Intervention
15/09/2012 17:00
17/09/2012 10:00
22/09/2012 15:00
20/09/2012 15:00
20/09/2012 15:00

Colonne E :
Delai
1
2
3
4
5

Colonne F :
Heures
2
5
10
20
50

Les deux dernieres colonnes correspondent à un simple tableau, auquel je fais appel avec la fonction RECHERCHEV =)

La réponse au problème est dans la 3ème colonne, que j'appelle "Date Limite Intervention" ;)

La formule que j'utilise pour la COLONNE C, ligne 2 est :
=SI(HEURE(A2)+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)<19;A2+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)/24;A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))/24)

et il te suffit de la tirer vers le bas ;)

En gros, si l'heure de la date de la demande à laquelle on ajoute le nombre d'heure du délai choisi est inférieure à 19, alors il suffit d'additionner la date de demande par le nombre d'heure/24 (parce que excel compte en jour), et si c'est supérieur alors il faut rajouter 14 heures à chaque fois qu'on dépasse d'un jour... parce qu'il y a 14h où tu ne travailles pas (entre 19h et 9h)

Si t'as une question n'hésite pas =)

Seb.

Ajouter un commentaire

Afficher les 14 commentaires

despi75 - 9 févr. 2012 à 12:16

Seb, je te remercie sincèrement c'est vraiment super. quel temps de gagné !

despi75 - 9 févr. 2012 à 12:23

je viens te solliciter à nouveau, si j'abuse n'hésite pas à me le dire.
pourrais faire en sorte que le calcul exclu le week end qui n'est pas travaillé.
bien à toi

seb_067- 9 févr. 2012 à 18:42

Oui, je peux =) (j'aurais pu y penser... mais non :s)

En revanche j'aurais besoin de savoir si la Demande d'intervention peut être faite n'importe quand (7j/7, 24h/24), comme ce serait par exemple le cas sur internet, ou au contraire si la demande se fait par téléphone, ou autres...

(d'ailleurs, si la demande peut être faite un lundi à 22h par exemple, je dois revoir ma formule...)

Il me faudrait donc les intervalles possibles de l'heure et la date de la Demande =)

De rien ;)
Seb.

despi75 - 9 févr. 2012 à 18:54

super, je ne sais pas comment te remercier du temps que tu me fais gagner.

la demande ne peut être faite que du lundi au vendredi de 9H à 19H

allez au boulot :-)

seb_067- 9 févr. 2012 à 19:05

Ah ok =) ça simplifie la chose un petit peu ;)

Je te fais ça en 2min lol

Par contre, garde bien à l'esprit que la formule ne sera pas valable pour une demande faite en dehors des horaires donnés... Je pourrais y passer plus de temps et te faire une formule qui marche tout le temps, mais je suis pas un génie et puis ça servirait de toute façon presque à rien !

à tt de suite !
Seb.

seb_067- 9 févr. 2012 à 19:12

Voilà ci-dessous ta formule...

Il suffit de rajouter "Si le jour d'intervention est un samedi ou un dimanche, alors rajoute 48h...sinon c'est bon"

=SI(HEURE(A2)+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)<19;A2+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)/24;SI(JOURSEM(A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))/24)<6;A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))/24;A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))+48/24))

C'est un peu long, je sais, c'est sûrement possible de raccourcir, mais ça devrait marcher =)

Tiens moi au courant du moindre bug !

Seb.

seb_067- 9 févr. 2012 à 19:16

Chez moi ça marche sans probleme (tant que la demande est entre 9h et 19h pendant les 5 premiers jours de la semaine bien sur)

despi75 - 9 févr. 2012 à 20:47

Je ne suis pas au bureau, j'essaye demain matin et te tiens au courant
merci beaucoup de ton aide
à demain ! je te souhaite une bonne soirée

despi75 - 10 févr. 2012 à 10:45

Bonjour Seb
j'ai fait les tests ce matin, voilà le résultat.
j'ai fait un tableau qui va du 1er février 2012 au 29 février 2012 et j'ai testé tous les délais. ci-dessous des exemples de dates qui ne collent pas. je suis en office 2010, peut être que cela à son importance. (pas de pb avec les delais 1 et 2)

merci encore de ton aide.

01/02/2012 10:44 3 02/02/2012 10:44
02/02/2012 10:44 3 28/02/2012 10:44
03/02/2012 10:44 3 29/02/2012 10:44

01/02/2012 10:44 4 22/03/2012 10:44
02/02/2012 10:44 4 23/03/2012 10:44
03/02/2012 10:44 4 05/02/2012 10:44

04/02/2012 10:44 5 09/02/2012 10:44
05/02/2012 10:44 5 06/06/2012 10:44
06/02/2012 10:44 5 07/06/2012 10:44

seb_067- 10 févr. 2012 à 12:21

Salut !

Ah ces parenthèses... dsl le 48 à la fin est bien entendu dans la parenthèse, sinon il n'est pas divisé par 24, et excel le compte en jours ! Voilà donc ce que ça donne :

=SI(HEURE(A2)+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)<19;A2+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)/24;SI(JOURSEM(A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))/24)<6;A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))/24;A2+(14+RECHERCHEV(B2;$E$2:$F$6;
2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10)+48)/24))

Continue de me tenir au courant ;)

Au passage, la formule empêche toute intervention après 18h59, on est bien d'accord ? =)

Il est aussi très facile d'ajouter les périodes de congés là dedans... les jours fériés c'est autre chose, mais c'est faisable =)

Seb.

despi75 - 14 févr. 2012 à 19:34

bonsoir Seb, désolée je n'ai pas eu le temps de tester avant, je viens de le faire et j'ai le message suivant dans la cellule : #N/A
argh !

despi75 - 14 févr. 2012 à 19:37

Date Demande Delai Date Limite Intervention Delai Heures
20/12/2011 15:42 5 #N/A 1 2
27/01/2012 10:44 3 2 5
17/09/2012 15:00 1 3 10
18/09/2012 15:00 1 4 20
19/09/2012 19:00 5 5 50

despi75 - 15 févr. 2012 à 12:13

Bonjour Seb,
ne tient pas compte du message precédent cela fonction, c'etait un espace au copier coller.
par contre problème sur délais 4 et 5

10/02/2012 09:00 4 12/02/2012 09:00 ( aurait du etre le 13 a 9.00)
01/02/2012 09:00 5 06/02/2012 09:00 ( aurait du etre le 8 a 9.00)

merci encore de ce que tu pourras faire.

seb_067- 15 févr. 2012 à 18:33

Salut !

Ah oui, je ne prenais pas en compte la durée du week-end à chaque fois... J'ai donc changé : "Si le jour d'intervention est > 5 (donc 6:samedi ou 7:dimanche) ET si il est inférieur au jour de la demande (ce qui veut dire qu'on a dépassé le week-end) ALORS rajoute 48h" =)

Ce qui donne :

=SI(HEURE(A2)+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)<19;A2+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)/24;SI(ET(JOURSEM(A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))/24)<6;JOURSEM(A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))/24)>=JOURSEM(A2));A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10))/24;A2+(14+RECHERCHEV(B2;$E$2:$F$6;2;FAUX)+14*ENT((RECHERCHEV(B2;$E$2:$F$6;2;FAUX)-1)/10)+48)/24))

Désolé c'est long, mais cette fois ça marche... Et du coup dans ton exemple :

10/02/2012 09:00 4 12/02/2012 09:00 ( aurait du etre le 13 a 9.00) J'ai 14 (car la demande est faite un samedi, le 10... je commence à compter le 12 à 9:00, ce qui donne 14, normalement)
01/02/2012 09:00 5 06/02/2012 09:00 ( aurait du etre le 8 a 9.00) C'est bon !

Continue de tester et de me tenir au courant !!!

Seb.

Répondre au sujet

Posez votre question

Dossier à la une

Passage au tout numérique : quel coût pour les particuliers ?

Passage au tout numérique : quel coût pour les particuliers ?

Combien cela coûte-t-il au total ? Quelles aides apportent l'état et les acteurs du marché pour alléger cette charge non choisie ? Tous les détails sur Commentçamarche.net.

Calcul excel complexe

Calcul excel complexe »

Passage au tout numérique : quel coût pour les particuliers ?