Calculer Date Fin en fonction de la Date début et Durée (en heures)
- Initiateur de la discussion ghizzz
- Date de début
PMG
XLDnaute Junior
Je reformule ma demande, j'aimerai compléter une formule existante (Hoerwind et James007).
Je souhaite connaitre la date de fin d'une tache en heures ouvrées avec des arrêts qui sont planifier après la date de début de lancement et dont la durée augmente cette dernière.
Avec la formule SERIE.JOUR.OUVRE je n’arrête pas de faire des réfs circulaires.
J'ai besoin d'un coup de pouce svp!
Merci bcp par avance
Je souhaite connaitre la date de fin d'une tache en heures ouvrées avec des arrêts qui sont planifier après la date de début de lancement et dont la durée augmente cette dernière.
Avec la formule SERIE.JOUR.OUVRE je n’arrête pas de faire des réfs circulaires.
J'ai besoin d'un coup de pouce svp!
Merci bcp par avance
PMG
XLDnaute Junior
Bonjour,
Je ne demande pas la solution je vais la trouver à force de chercher, juste qqun qui peut me guider sur la bonne voie!
Merci par avance
Je ne demande pas la solution je vais la trouver à force de chercher, juste qqun qui peut me guider sur la bonne voie!
Merci par avance
CISCO
XLDnaute Barbatruc
PMG
XLDnaute Junior
Merci Cisco, je vais essayer avec ta proposition!
Je vais y arriver!
Je vais y arriver!
CISCO
XLDnaute Barbatruc
Bonjour
J'ai commencé à faire le travail, et j'obtiens le bon résultat, mais cela n'est pas très pratique.
La méthode :
* Dans G29, on a par formule le nombre de minutes de travail nécessaires.
* On liste avec listemin toutes les minutes allant de 1 à une valeur X donnée dans H29. Il faut écrire cette valeur X et la modifer autant de fois que nécessaire en fonction du besoin.
* Dans I29, une formule calcule le nombre de minutes réellement travaillées sur la période début à début + X
* Reste plus qu'à modifier la valeur dans H29 jusqu'à avoir la même valeur dans G29 et I29. J'ai essayé avec la valeur cible (Données --> Analyse de scénarios), mais cela n'a pas fonctionné.
* Dans K29, on obtient automatiquement le début + X min
Comment fonctionne la formule dans I29 ?
* Une première partie permet de ne prendre en compte que les minutes entre C2 et C3, et C5 et C6 (Heures de travail)
* La deuxième partie prend en compte que les minutes ne correspondant pas aux 4 périodes indiquées dans H4:I17
* La dernière partie élimine les minutes des samedi et dimanche affichés dans les colonnes Q:R.
Ce n'est pas pratique pour deux ou trois raisons :
* Dans certains cas, il faudra peut-être davantage de valeurs dans les colonnes P, Q et R.
* Il faut ajuster la valeur dans la colonne H, à la main. Mais bon, comme il s'agit d'une simple somme, ce n'est pas très compliqué.
* Pour le moment, cela ne fonctionne que sur la ligne 29.
Faute de mieux, pour le moment...
@ plus
P.S 1: Ce n'est peut-être pas bon à la minute près (Je m'y perd un peu au niveau des bornes des périodes à prendre en compte). Mais bon, je ne pense pas que tu sois à 10 minutes près...
P.S2 : J'ai vérifié les calculs dans les colonnes A, B et C, tout en bas, jusque vers la ligne 20 000. On obtient 1800 minutes réellement travaillées sur la ligne 17758, après un total de 17699 minutes écoulées. Cela correspond à ton résultat ainsi qu'à ce que donne la formule dans H29.
J'ai commencé à faire le travail, et j'obtiens le bon résultat, mais cela n'est pas très pratique.
La méthode :
* Dans G29, on a par formule le nombre de minutes de travail nécessaires.
* On liste avec listemin toutes les minutes allant de 1 à une valeur X donnée dans H29. Il faut écrire cette valeur X et la modifer autant de fois que nécessaire en fonction du besoin.
* Dans I29, une formule calcule le nombre de minutes réellement travaillées sur la période début à début + X
* Reste plus qu'à modifier la valeur dans H29 jusqu'à avoir la même valeur dans G29 et I29. J'ai essayé avec la valeur cible (Données --> Analyse de scénarios), mais cela n'a pas fonctionné.
* Dans K29, on obtient automatiquement le début + X min
Comment fonctionne la formule dans I29 ?
* Une première partie permet de ne prendre en compte que les minutes entre C2 et C3, et C5 et C6 (Heures de travail)
Code:
(((C2*24*60<=MOD(E29*24*60+listemin;24*60))*(MOD(E29*24*60+listemin;24*60)<C3*24*60))+((C5*24*60<=MOD(E29*24*60+listemin;24*60))*(MOD(E29*24*60+listemin;24*60)<C6*24*60))
Code:
*ESTNA(EQUIV(listemin;LIGNE(INDIRECT(ARRONDI.AU.MULTIPLE((H14-(C29+E29))*24*60+1;1)&":"&ARRONDI.AU.MULTIPLE((I14-(C29+E29))*24*60;1)));0))
*ESTNA(EQUIV(listemin;LIGNE(INDIRECT(ARRONDI.AU.MULTIPLE((H15-(C29+E29))*24*60+1;1)&":"&ARRONDI.AU.MULTIPLE((I15-(C29+E29))*24*60;1)));0))
*ESTNA(EQUIV(listemin;LIGNE(INDIRECT(ARRONDI.AU.MULTIPLE((H16-(C29+E29))*24*60+1;1)&":"&ARRONDI.AU.MULTIPLE((I16-(C29+E29))*24*60;1)));0))
*ESTNA(EQUIV(listemin;LIGNE(INDIRECT(ARRONDI.AU.MULTIPLE((H17-(C29+E29))*24*60+1;1)&":"&ARRONDI.AU.MULTIPLE((I17-(C29+E29))*24*60;1)));0))
Code:
*ESTNA(EQUIV(listemin;LIGNE(INDIRECT(ARRONDI.AU.MULTIPLE((Q1-(C29+E29))*24*60+1;1)&":"&ARRONDI.AU.MULTIPLE((R1-(C29+E29))*24*60;1)));0))
*ESTNA(EQUIV(listemin;LIGNE(INDIRECT(ARRONDI.AU.MULTIPLE((Q2-(C29+E29))*24*60+1;1)&":"&ARRONDI.AU.MULTIPLE((R2-(C29+E29))*24*60;1)));0))
*ESTNA(EQUIV(listemin;LIGNE(INDIRECT(ARRONDI.AU.MULTIPLE((Q3-(C29+E29))*24*60+1;1)&":"&ARRONDI.AU.MULTIPLE((R3-(C29+E29))*24*60;1)));0)))Ce n'est pas pratique pour deux ou trois raisons :
* Dans certains cas, il faudra peut-être davantage de valeurs dans les colonnes P, Q et R.
* Il faut ajuster la valeur dans la colonne H, à la main. Mais bon, comme il s'agit d'une simple somme, ce n'est pas très compliqué.
* Pour le moment, cela ne fonctionne que sur la ligne 29.
Faute de mieux, pour le moment...
@ plus
P.S 1: Ce n'est peut-être pas bon à la minute près (Je m'y perd un peu au niveau des bornes des périodes à prendre en compte). Mais bon, je ne pense pas que tu sois à 10 minutes près...
P.S2 : J'ai vérifié les calculs dans les colonnes A, B et C, tout en bas, jusque vers la ligne 20 000. On obtient 1800 minutes réellement travaillées sur la ligne 17758, après un total de 17699 minutes écoulées. Cela correspond à ton résultat ainsi qu'à ce que donne la formule dans H29.
Dernière édition:
CISCO
XLDnaute Barbatruc
Bonsoir
Grosse simplification de la troisième partie faite pour éliminer les samedi et les dimanche.
@ plus
Grosse simplification de la troisième partie faite pour éliminer les samedi et les dimanche.
Code:
*(JOURSEM((C29+E29+listemin/(24*60));3)<5)@ plus
CISCO
XLDnaute Barbatruc
Rebonsoir
Grosse simplification de la seconde partie de la formule dans I29.
Et avec quelques $ de plus, la formule peut maintenant être tirée vers le bas.
A toi de faire quelques tests en plus.
@ plus
P.S : Si tu as plus de déplacements, plus de congés..., il faudra rallonger la seconde partie, mais ce n'est pas très compliqué à faire.
Grosse simplification de la seconde partie de la formule dans I29.
Et avec quelques $ de plus, la formule peut maintenant être tirée vers le bas.
A toi de faire quelques tests en plus.
@ plus
P.S : Si tu as plus de déplacements, plus de congés..., il faudra rallonger la seconde partie, mais ce n'est pas très compliqué à faire.
Code:
*NON((H$14<(C30+E30+listemin/(24*60)))*((C30+E30+listemin/(24*60))<=I$14))
*NON((H$15<(C30+E30+listemin/(24*60)))*((C30+E30+listemin/(24*60))<=I$15))
*NON((H$16<(C30+E30+listemin/(24*60)))*((C30+E30+listemin/(24*60))<=I$16))
*NON((H$17<(C30+E30+listemin/(24*60)))*((C30+E30+listemin/(24*60))<=I$17))
Dernière édition:
PMG
XLDnaute Junior
Merci bcp de te pencher sur ce problème Cisco, je vais regarder attentivement le travail que tu as effectué!
Je te réponds demain!
A+
Je te réponds demain!
A+
CISCO
XLDnaute Barbatruc
Bonjour
Plus besoin de faire le travail par dichotomie dans la colonne H pour trouver la bonne valeur. Cela se fait tout seul
.
Dans la colonne I, on a un calcul du style SOMME(formule) avec formule une série de 0 et de 1, les 0 correspondant aux minutes non travaillées, les 1 aux minutes travaillées, sur une période allant de début à début +X minutes (X est choisi dans la colonne H).
Dans J, j'ai mis
avec G29 le nombre de minutes nécessaires pour la pièce considérée.
SI(formule;listemin;"") renvoie des "" à la place des 0, et le n° de chaque minute considérée dans listmin à la place des 1.
Et hop, Excel nous renvoie la position de cette petite valeur, donc le temps, en minute, réellement nécessaire, y compris les samedi, les dimanche, les RDV... puisque les "" ne comptent pas comme un nombre (donc ne sont pas pris en compte par la fonction PETITE.VALEUR).
Si on met un nombre trop petit dans la colonne H (=si on prend une période de calcul trop petite), certaines formules renvoient #Nombre. Pour corriger cela, il suffit de relancer le calcul avec un nombre plus grand autant de fois que nécessaire.
@ plus
PS : Fichier changé le 25/03/2020 suite à la simplification de la formule dans la colonne J
Plus besoin de faire le travail par dichotomie dans la colonne H pour trouver la bonne valeur. Cela se fait tout seul
.Dans la colonne I, on a un calcul du style SOMME(formule) avec formule une série de 0 et de 1, les 0 correspondant aux minutes non travaillées, les 1 aux minutes travaillées, sur une période allant de début à début +X minutes (X est choisi dans la colonne H).
Dans J, j'ai mis
VB:
PETITE.VALEUR(SI(formule;listemin;"");G29)SI(formule;listemin;"") renvoie des "" à la place des 0, et le n° de chaque minute considérée dans listmin à la place des 1.
Et hop, Excel nous renvoie la position de cette petite valeur, donc le temps, en minute, réellement nécessaire, y compris les samedi, les dimanche, les RDV... puisque les "" ne comptent pas comme un nombre (donc ne sont pas pris en compte par la fonction PETITE.VALEUR).
Si on met un nombre trop petit dans la colonne H (=si on prend une période de calcul trop petite), certaines formules renvoient #Nombre. Pour corriger cela, il suffit de relancer le calcul avec un nombre plus grand autant de fois que nécessaire.
@ plus
PS : Fichier changé le 25/03/2020 suite à la simplification de la formule dans la colonne J
Dernière édition:
CISCO
XLDnaute Barbatruc
Bonjour
Toujours la même méthode, en supprimant les colonnes intermédiaires.
J'ai laissé la colonne permettant d'imposer la durée de la période de calcul (X, colonne G maintenant, 10000 min correspondant presqu'à une semaine), histoire que cela soit contrôlable ligne par ligne, au cas par cas.
Si désiré, on peut encore modifier cela pour mettre une seule valeur X pour l'ensemble du tableau, dans une cellule, ou directement dans la formule dans la colonne I. Mais dans ce cas, si une ligne donne #Nombre, il faudra changer la valeur dans cette cellule (ce qui relancera le calcul sur toutes les lignes, sans en changer le résultat, sauf pour celle(s) posant problème) ou dans la formule (pas très pratique).
@ plus
P.S : Fichier changé le 24/03/2020
Toujours la même méthode, en supprimant les colonnes intermédiaires.
J'ai laissé la colonne permettant d'imposer la durée de la période de calcul (X, colonne G maintenant, 10000 min correspondant presqu'à une semaine), histoire que cela soit contrôlable ligne par ligne, au cas par cas.
Si désiré, on peut encore modifier cela pour mettre une seule valeur X pour l'ensemble du tableau, dans une cellule, ou directement dans la formule dans la colonne I. Mais dans ce cas, si une ligne donne #Nombre, il faudra changer la valeur dans cette cellule (ce qui relancera le calcul sur toutes les lignes, sans en changer le résultat, sauf pour celle(s) posant problème) ou dans la formule (pas très pratique).
@ plus
P.S : Fichier changé le 24/03/2020
Dernière édition:
PMG
XLDnaute Junior
Bonjour Cisco,
Tout d'abord un grand merci pour tout le travail, ton efficacité et le temps passé!
Si j'ai bien compris (enfin j'espère):
Mon résonnement:
Date début + Tmp de tavail = Date de fin
Si Date de début <= Contraintes <= Date de fin donc Date de fin + Contraintes = Date de fin 2
Si Date de début <= Contraintes <= Date de fin 2 donc Date de fin 2 + Contraintes = Date de fin 3
Si etc.... En boucle et donc compliqué ne sachant pas combien de contraintes il y a réellement.
Je pensais qu'une solution de type SERIE.JOUR.OUVRE était envisageable en remplaçant les féries par les "contraintes" ou en modifiant le temps de départ. (fichier test contrainte heure 01)
Ton résonnement:
Date début + Tmp de tavail = Date de fin
Décompte par minutes et si Contraintes donc Nb de minutes de travail + Nb de minutes de Contraintes nécessaires.
Lorsque Nb de minutes de travail = Tmp de travail on à la Date de fin
J'ai rajouté des taches en partant de la date de fin de la tache antérieur (ref GANTT) les résultats sont bons. Effectivement je ne suis pas à la minute prêt, le but est de planifier au 1/4 heure voir à la 1/2 heure.
J'ai eu une référence circulaire en I29 puis elle a disparu mais mon ordi rame (5 sec) pour calculer ce qui doit être normal vu le nb d'opérations à vérifier.
En tout cas merci pour cette démonstration de simplification.
Questions:
1) Peut on simplifier la formule de calcul des heures, ou au lieu de comparer la date de début et de fin de H14 à I23, je fais 1 tableau congés et feriés avec des jours ouvrés à ajouter et un deuxième avec les déplacements?
2) Faut il penser à transposer ou pas en VBA pour augmenter la vitesse des calculs ?
Merci et A+
Tout d'abord un grand merci pour tout le travail, ton efficacité et le temps passé!
Si j'ai bien compris (enfin j'espère):
Mon résonnement:
Date début + Tmp de tavail = Date de fin
Si Date de début <= Contraintes <= Date de fin donc Date de fin + Contraintes = Date de fin 2
Si Date de début <= Contraintes <= Date de fin 2 donc Date de fin 2 + Contraintes = Date de fin 3
Si etc.... En boucle et donc compliqué ne sachant pas combien de contraintes il y a réellement.
Je pensais qu'une solution de type SERIE.JOUR.OUVRE était envisageable en remplaçant les féries par les "contraintes" ou en modifiant le temps de départ. (fichier test contrainte heure 01)
Ton résonnement:
Date début + Tmp de tavail = Date de fin
Décompte par minutes et si Contraintes donc Nb de minutes de travail + Nb de minutes de Contraintes nécessaires.
Lorsque Nb de minutes de travail = Tmp de travail on à la Date de fin
J'ai rajouté des taches en partant de la date de fin de la tache antérieur (ref GANTT) les résultats sont bons. Effectivement je ne suis pas à la minute prêt, le but est de planifier au 1/4 heure voir à la 1/2 heure.
J'ai eu une référence circulaire en I29 puis elle a disparu mais mon ordi rame (5 sec) pour calculer ce qui doit être normal vu le nb d'opérations à vérifier.
En tout cas merci pour cette démonstration de simplification.
Questions:
1) Peut on simplifier la formule de calcul des heures, ou au lieu de comparer la date de début et de fin de H14 à I23, je fais 1 tableau congés et feriés avec des jours ouvrés à ajouter et un deuxième avec les déplacements?
2) Faut il penser à transposer ou pas en VBA pour augmenter la vitesse des calculs ?
Merci et A+
CISCO
XLDnaute Barbatruc
Bonsoir
Oui, tu as bien compris le raisonnement.
Pour simplifier pour tous les futurs lecteurs :
On impose une période de calcul de X minutes, allant donc de Date début à Date début + X.
Sur cette période, chaque minute est remplacée par un " " si elle correspond à une minute hors heures de travail, ou aux congés, aux RDV, aux samedi et aux dimanche, les autres par leur n° de minute.
Prenons une durée nécessaire, utile, de 15 minutes, sur une période de 30 minutes. Cela donne par exemple {1;2;3;4;5;6;7;8;9;10;11;"";"";"";"";"";"";"";"";20;21;22;23;24;25;26;27;28;29;30}, avec un congé de 8 minutes (!) à partir de la minute 12. A la minute 20, on n'a effectué que 12 minutes de travail, à la 21, cela fait 13... Et la pièce est finie seulement à la minute 23.
Pour trouver cette valeur 23, on fait avec PETITE.VALEUR({1;2;3;4;5;6;7;8;9;10;11;"";"";"";"";"";"";"";"";20;21;22;23;24;25;26;27;28;29;30};15)
Je regarde tes fichiers.
@ plus
Oui, tu as bien compris le raisonnement.
Pour simplifier pour tous les futurs lecteurs :
On impose une période de calcul de X minutes, allant donc de Date début à Date début + X.
Sur cette période, chaque minute est remplacée par un " " si elle correspond à une minute hors heures de travail, ou aux congés, aux RDV, aux samedi et aux dimanche, les autres par leur n° de minute.
Prenons une durée nécessaire, utile, de 15 minutes, sur une période de 30 minutes. Cela donne par exemple {1;2;3;4;5;6;7;8;9;10;11;"";"";"";"";"";"";"";"";20;21;22;23;24;25;26;27;28;29;30}, avec un congé de 8 minutes (!) à partir de la minute 12. A la minute 20, on n'a effectué que 12 minutes de travail, à la 21, cela fait 13... Et la pièce est finie seulement à la minute 23.
Pour trouver cette valeur 23, on fait avec PETITE.VALEUR({1;2;3;4;5;6;7;8;9;10;11;"";"";"";"";"";"";"";"";20;21;22;23;24;25;26;27;28;29;30};15)
Je regarde tes fichiers.
@ plus
Dernière édition:
CISCO
XLDnaute Barbatruc
Bonsoir
Une solution consiste effectivement à noter dans une colonne tous les jours, un par un, correspondant aux RDV, aux congés, aux samedi et aux dimanche, ainsi on n'aurait pas à mettre 10 lignes dans la formule pour tenir compte des RDV et congés inscrits de la ligne 14 à la ligne 23, et une ligne en plus pour éliminer les samedi et les dimanche. Une seule ligne utilisant cette colonne suffirait. Et cela ferait moins de calcul. De plus, cela serait facile de rajouter des RDV ou des congés supplémentaires en bas de la colonne, si tu as plus de 10 RDV/congés à prendre en compte. Mais cela ferait une colonne vraiment très longue.
Il est assez logique que cette méthode prenne beaucoup de temps de calcul, vu qu'on compare chaque minute (10 000 pour une période d'une semaine) à des bornes. Il est certain qu'avec un macro, cela irait plus vite, mais c'est au-dessus de mes compétences actuelles (sauf si je me replonge deux ou trois jours dans mes livres sur le VBA...). Est-ce vraiment gênant pour toi ?
@ plus
P.S : Moins aussi, à un moment, j'ai eu, uniquement dans le fichier 06, des références circulaires. Je suis reparti du 05 (Puisque c'est exactement la même méthode), j'ai contrôlé les $, supprimé très progressivement les colonnes intermédiaires, et depuis, plus de référence circulaire.
Une solution consiste effectivement à noter dans une colonne tous les jours, un par un, correspondant aux RDV, aux congés, aux samedi et aux dimanche, ainsi on n'aurait pas à mettre 10 lignes dans la formule pour tenir compte des RDV et congés inscrits de la ligne 14 à la ligne 23, et une ligne en plus pour éliminer les samedi et les dimanche. Une seule ligne utilisant cette colonne suffirait. Et cela ferait moins de calcul. De plus, cela serait facile de rajouter des RDV ou des congés supplémentaires en bas de la colonne, si tu as plus de 10 RDV/congés à prendre en compte. Mais cela ferait une colonne vraiment très longue.
Il est assez logique que cette méthode prenne beaucoup de temps de calcul, vu qu'on compare chaque minute (10 000 pour une période d'une semaine) à des bornes. Il est certain qu'avec un macro, cela irait plus vite, mais c'est au-dessus de mes compétences actuelles (sauf si je me replonge deux ou trois jours dans mes livres sur le VBA...). Est-ce vraiment gênant pour toi ?
@ plus
P.S : Moins aussi, à un moment, j'ai eu, uniquement dans le fichier 06, des références circulaires. Je suis reparti du 05 (Puisque c'est exactement la même méthode), j'ai contrôlé les $, supprimé très progressivement les colonnes intermédiaires, et depuis, plus de référence circulaire.
Dernière édition:
CISCO
XLDnaute Barbatruc
Bonsoir
Il y a un problème avec ton fichier 07 : Normalement, chaque ligne, à partir de la 29 comprise, est indépendante des autres suivantes.
Dans ton fichier 07, si on met une valeur X trop petite dans G29, tous le reste en-dessous plante ! Cela n'est pas normal et je n'ai pas ce problème avec le fichier 06.
@ plus
Il y a un problème avec ton fichier 07 : Normalement, chaque ligne, à partir de la 29 comprise, est indépendante des autres suivantes.
Dans ton fichier 07, si on met une valeur X trop petite dans G29, tous le reste en-dessous plante ! Cela n'est pas normal et je n'ai pas ce problème avec le fichier 06.
@ plus
PMG
XLDnaute Junior
Bonsoir,
Merci pour tes explications!
Réponse question 1):
Effectivement la colonne risque d'être longue, cela dit le tableau fera 50 lignes max à la fin.
Peut on utiliser ta méthode de décompte des minutes pour les RDV seulement.
Pour les congés et jours feriés une formule du genre:
=SOMMEPROD(NB.SI.ENS(A2:A17;"<="&jours;B2:B17;">="&jours)*(JOURSEM(jours;2)<6))))
jours=LIGNE(INDIRECT(Début&":"&Fin))
afin de calculer le nb de jours ouvrés a ajouter.
Réponse question 2):
Je ne pensais pas que ma question de calcul d'heure allait aller si loin et était aussi compliqué finalement. Je vais avoir une cinquantaine de tâches max normalement donc j'ai bien peur que ça rame bcp. Je ne voudrais pas abusé de ton temps pour le VBA.
Réponse avec fichier 7:
J'ai mis les taches les unes à la suite des autres, la date de début correspond à la date de fin de la tache antérieure.
A+
Merci pour tes explications!
Réponse question 1):
Effectivement la colonne risque d'être longue, cela dit le tableau fera 50 lignes max à la fin.
Peut on utiliser ta méthode de décompte des minutes pour les RDV seulement.
Pour les congés et jours feriés une formule du genre:
=SOMMEPROD(NB.SI.ENS(A2:A17;"<="&jours;B2:B17;">="&jours)*(JOURSEM(jours;2)<6))))
jours=LIGNE(INDIRECT(Début&":"&Fin))
afin de calculer le nb de jours ouvrés a ajouter.
Réponse question 2):
Je ne pensais pas que ma question de calcul d'heure allait aller si loin et était aussi compliqué finalement. Je vais avoir une cinquantaine de tâches max normalement donc j'ai bien peur que ça rame bcp. Je ne voudrais pas abusé de ton temps pour le VBA.
Réponse avec fichier 7:
J'ai mis les taches les unes à la suite des autres, la date de début correspond à la date de fin de la tache antérieure.
A+
Discussions similaires
- Question
- Résolu(e)
- Résolu(e)
