Calculer Date Fin en fonction de la Date début et Durée (en heures)

[ghizzz]

Bonsoir,

j'essaie d'établir un fichier excel pour le suivi du portefeuille projets mais je bloque au niveau du calcul de la date fin.

Ci joint le fichier peut être vous verrez plus clair 🙂

Merci d'avance.

 

[CISCO]

Bonsoir

Bonsoir
Une solution consiste effectivement à noter dans une colonne tous les jours, ...

Et non. Pour le moment, j'ai l'impression que cette méthode, mettre tous les jours d'arrêt dans une colonne, n'est pas applicable : Elle éliminerait toutes les minutes des jours considérés, de 00:00 à 23:59, ce qui est correct pour les jours de congé, mais pas pour les RDV... Je vais encore chercher dans cette voie, mais je doute...

@ plus

 

[CISCO]

Rebonsoir

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.

Pas si simple. Si tu as par ex 6 jours de congés ou fériés, tu vas rajouter 6. Mais dans ces 6 jours rajoutés, il y a peut-être d'autres RDV, jours de congés...

Réponse question 2):

En fait, la formule dans la colonne I n'est pas si compliquée que ça, mais un peu "répétitive"... En VBA, ce n'est certainement pas très compliqué, avec quelques tests et une boucle ajoutant les bonnes minutes. Mais bon...

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.

OK, effectivement, dans ces conditions, si, sur une ligne, on met une période de calcul trop petite, Excel ne trouve pas la réponse, et toutes les lignes suivantes donnent #Nombre.

@ plus

 

[CISCO]

Bonsoir

Bonjour Cisco,
....
2) Faut il penser à transposer ou pas en VBA pour augmenter la vitesse des calculs ?
Merci et A+

Chez moi, le calcul est fait en 1 ou 2 secondes au total, pour la dizaine de lignes de ton fichier...

@ plus

 

[PMG]

Bonsoir,

Mon ordi perso doit ramer j'essayerai demain avec un plus puissant. Dés que je change la valeur de n'importe quelle cellule sur le fichier j'ai le même problème.
Les dizaines de lignes vont se changer en une 50aine max pour le tableau des contraintes (congés, rdv, etc..) et celui des calculs des dates de fin.

J'ai commencer à regarder en VBA, pour la date de fin avec heures ouvrées certains membres du forum avaient trouvé des solutions très intéressantes que je pourrai essayer d’intégrer mais pour ton code avec un décompte c'est une autre histoire! On verra si cela en vaut la peine!

A+

 

[CISCO]

Bonjour

Sur le fichier 07, tu n'as pas enlevé le contenu de la plage A59:A20074. J'avais fait des vérifications à l'aide de cette plage, mais maintenant, cela ne sert plus à rien.
Ceci dit, ce n'est certainement pas cette partie qui explique le fait que ton ordi rame pour afficher les résultats nécessaires. Chez moi, j'ai refait quelques tests, et cela ne met que quelques secondes...

@ plus

 

[CISCO]

Bonsoir

J'ai fait une macro pour faire le même travail mais il y a trois problèmes :
* Cela ne donne pas tout à fait les mêmes résultats que la formule (à 2 ou 3 minutes près en général, 10 au max dans les exemples traités)
* Je ne sais pourquoi, certaines fois, sans rien changer aux conditions, il y a une différence plus importante
* L'affichage des résultats met autant de temps. J'ai une idée pour faire moins de boucles (c.-à-d., à chaque fois qu'on teste une minute non travaillée, aller directement à la fin de cette période sans travail (congé, RDV, samedi, dimanche, à 8:00, à 14:00, ou le lendemain à 8:00). J'ai laissé quelques unes des équations correspondantes en commentaire dans la macro. Cela fait moins de boucle, mais cela ne fait pas gagner grand chose en temps de calcul...

Sub Finpiece()
Dim ligne As Integer
Dim k As Integer
Dim minut As Integer
Dim tempsefficace As Integer
Dim tempsecoulé As Integer

For ligne = 29 To 38
début = Cells(ligne, 3) + Cells(ligne, 5)
tempsefficace = 0
tempsécoulé = 1
tempsnécessaire = Cells(ligne, 6) * 24 * 60
    
    While tempsefficace < tempsnécessaire
        minut = 1
        test = début + tempsécoulé / (24 * 60)
        
        'Est-ce que la minute en cours est travaillée ou pas ? Si oui, minut = 1, sinon, minut = 0
        If (test - Int(test)) <= Cells(2, 3) Then ' trop tot
            minut = 0
            ElseIf (Cells(3, 3) < (test - Int(test)) And (test - Int(test)) <= Cells(5, 3)) Then ' repos de midi
                minut = 0
                ElseIf Cells(6, 3) < (test - Int(test)) Then ' trop tard
                    minut = 0
                    ElseIf Weekday(test, 2) = 6 Or Weekday(test, 2) = 7 Then   'Samedi, dimanche ?
                        minut = 0
                        'tempsécoulé = Int(tempsécoulé + (Int(test + 1) - test) * 24 * 60)
        End If
        
        ' 10 lignes de RDV, congés...
            For k = 1 To 10
                If minut = 1 And Cells(13 + k, 8) < test And test <= Cells(13 + k, 9) Then
                    minut = 0
                    'tempsécoulé = Int(tempsécoulé + (Cells(13 + k, 9) - test) * 24 * 60)
                    k = 10
                End If
            Next

            
            
        tempsefficace = tempsefficace + minut
        tempsécoulé = tempsécoulé + 1

    Wend

'Affichage du résultat
Cells(ligne, 9) = test

Next ligne

End Sub

@ plus

 

[PMG]

Bonjour CISCO,

J'ai effectué des tests sur un autre ordi et effectivement le tmp de calcul n'a rien à voir (1s à 2s).
Je suis en train de mettre en forme un fichier plus proche de la réalité concernant le nombres de contraintes (RDV, ferié, congés, etc...) pas + de 50lignes, et aussi une liste de 50 tâches max à calculer.
Donc le tableau des contraintes aura 40 lignes de plus, bonjour la formule si pas VBA!
A voir...

Concernant le fichier avec macro:
Les valeurs seront arrondies au 1/4 d'heure afin de les planifier donc je ne suis pas à la minute prêt , mais je comprends que tu devrais trouver le même résultat.
J'ai l'impression que le calcul ignore les contraintes, mais je me trompe peut être?
Effectivement ça rame aussi, faut vraiment que je fasse un ghost du DD.

Encore merci pour le temps consacré, cela fait vraiment plaisir!

A+

 

[CISCO]

Bonsoir

Effectivement, si tu as une cinquantaine de RDV, congé..., cela va te faire un terme formule vraiment très long, trop long.
Si on ne trouve pas d'autres solutions, tu peux faire comme suit :
1) Sélectionner une cellule de la première ligne des taches (29 dans l'exemple ci-dessous)
2) Ouvrir le gestionnaire. Copier la définition de formule, prise dans le gestionnaire, sans le signe égal, et la coller dans une cellule sur la feuille.
3) Supprimer les Feuil1! par un Rechercher Remplacer. Remplacer aussi les $C29+$E29 par $H29.
4) Découper l'ensemble avec des alt+entrer devant les *,
5) Coller en bas la partie *NON(($H$23<($H29+listemin/(24*60)))*(($H29+listemin/(24*60))<=$I$23)) autant de fois que nécessaire
6) Changer les 23 en 24, 25...
7) Recoller la nouvelle formule dans le gestionnaire de noms en sélectionnant d'abord la bonne ligne (29 dans l'image ci-dessous)

Mais bon, il faut bien admettre que ce n'est pas le top de devoir utiliser une formule aussi longue. Je vais essayer de trouver autre chose. Au moins, dans la macro, il suffit de changer la borne sup de la boucle sur k, et de remplacer quelques k = 10 par k = 50.

@ plus

 

[CISCO]

Bonjour

Je me demande, si on ne peut pas faire avec la fonction FREQUENCE

Dans la colonne de gauche, dans S1:S9, j'ai mis

FREQUENCE(H$29+LIGNE(INDIRECT("1:"&Feuil1!$G$29))/(24*60);T1:T8)

en matriciel, dans celle de droiteT1:T8, les RDV, congés...
Résultat, on sait qu'il y a ;
960 minutes entre le début H$29, le 27/02 08:00 et le 28/02 00:00,
5759 minutes entre le 28/02 00 exclu et le 02/03 23:59 inclu
et ainsi de suite. A la fin, il y a 3201 minutes après le 09/03 23:59.

Autrement dit, on connait le nombre de minutes qui s'insèrent avant, entre et après chaque date. Reste encore à trouver comment on va tenir compte des deux autres conditions, les horaires de travail et les WE.

@ plus

 

[CISCO]

Rebonjour

Avec FREQUENCE, j'y suis presque, sans avoir à répéter 50 fois une partie de la formule pour mettre les conditions sur les RDV, congé..., mais pas tout à fait.

J'obtiens le nombre de minutes travaillées pendant chaque période (RDV, congé), mais ce qu'il me faut, c'est le total depuis le début, et ça, pour le moment, je n'y arrive pas.

Dans l'image ci-dessous, ce n'est pas 598, 180, 598... qu'il me faut, c'est 598, puis 598+180, puis 598+ 180 + 598, puis 598 + 180 + 598 +59...
Dans l'exemple ci-dessous, il ni a que 9 tranches, mais avec 50 RDV, congés..., il y en aura beaucoup plus.

@ plus

P.S : Lorsqu'on valide la fonction FREQUENCE(liste d'éléments; plage définissant les intervalles) (Ex FREQUENCE(A$1:A$10;B$2:B$5)) avec Ctrl+maj+entrée, on obtient les fréquences, c.à-d. les nombres d'éléments dans chaque intervalle.
En ayant écrit dans la formule la plage définissant les intervalles sans $ devant le n° des lignes (Ex FREQUENCE(A$1:A$10;B2:B5) ou plus simplement Ex FREQUENCE(A$1:A$10;B2)) , si on copie/colle la formule vers le bas (ou en validant avec uniquement Ctrl+entrée (=saisie multiple)), on obtient les fréquences cumulées (cf. fichier en pièce jointe).

Dans le cas m'intéressant dans ce fil, j'ai besoin des valeurs cumulées, non pas dans plusieurs cellules les unes en dessous des autres, mais dans une formule (Pour ne pas introduire une myriade de lignes ou de colonnes intermédiaires, autant que de RDV, congés...). Donc je ne peux pas utiliser cette méthode. Grrrr.

 

[CISCO]

Bonjour

J'ai simplifié la formule dans le fichier 06. A la place, j'ai mis le fichier 06bis

@ plus

 

[PMG]

Bonjour,

Je suis avec intérêt ta réflexion, tes compétences sont largement au dessus des miennes.
Je comprends ce que tu recherches, effectivement les formules matricielles permettraient de nous affranchir d'un nombre de formules non négligeable!

Mais ne penses tu pas que le VBA est la seule solution?
Genre créer une fonction "Fréquence" avec ta proposition:
(Tps début ; Durée ; Tps intermédiaire1 début :Tps intermédiaire N début ; Tps intermédiaire1 fin :Tps intermédiaire N fin).

A+

 

[PMG]

Merci CISCO,

J'ai intégré la formule matricielle (6bis) ds ce nouveau fichier + proche de la version finale!
Par contre je n'ai pas encore complété la formule 40lignes à rajouter!
Dis moi si je n'ai pas fais d'erreurs!

A+

 

[CISCO]

Bonjour à tous

Après maints zigzags, j'ai abandonné la tentative avec la fonction FREQUENCE. Je viens de trouver une autre possibilité pour prendre en compte les RDV, les congés... avec un IMPAIR(EQUIV(.....).

* EST.IMPAIR(EQUIV(H29+listemin/(24*60);K$13:$K$23;1))

où on a mis dans l'ordre les débuts et les fins des RDV dans K13:K23, en commençant avec un 0. On peut aussi faire avec

*EST.IMPAIR(EQUIV(H29+listemin/(24*60);PETITE.VALEUR(H$13:I$23;LIGNE(INDIRECT("1:"&2*LIGNES($13:$23))));1))

pour ne pas avoir à remplir les cellules en dessous de K12. Ne pas oublier le 0 sur la ligne 13.

Cela revient à faire (en mettant dans l'exemple des nombres simples à la place des dates réelles compliquées) un EQUIV(10; {0;20;30;50;65;100;115};1) avec des RDV de 20 à 30, de 50 à 65 et de 100 à 115, ce qui renvoie 1, donc 10 est hors RDV.

Si la minute considérée est hors RDV, le EQUIV renvoie forcément un nombre impair, si elle est pendant un RDV, un congé, il renvoie un nombre pair.

Avantage : On n'a pas besoin d'ajouter une nouvelle partie dans la formule à chaque fois qu'on ajoute un nouveau RDV ou un nouveau congé. Il suffit de changer tous les 23 dans la formule dans la colonne I, en écrivant à la place le numéro de la dernière ligne des RDV, par ex, pour des RDV allant jusqu'à la ligne 50

*EST.IMPAIR(EQUIV(H29+listemin/(24*60);PETITE.VALEUR(H$13:I$50;LIGNE(INDIRECT("1:"&2*LIGNES($13:$50))));1))

D'ailleurs, tu dois pouvoir faire directement avec cette dernière partie, à condition de commencer le tableau des tâches en dessous de cette ligne 50.

@ plus

 

[CISCO]

Rebonjour

Apparemment, c'est correct.

Je ne vois pas trop pourquoi cela bugue à partir de la ligne 20. Je regarderai plus tard, en fonction de ce que tu me diras du fichier 13bis, plus pratique pour toi.

@ plus