Calculer Date Fin en fonction de la Date début et Durée (en heures)
- Initiateur de la discussion ghizzz
- Date de début
CISCO
XLDnaute Barbatruc
Bonsoir
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
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
XLDnaute Barbatruc
Rebonsoir
@ plus
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...
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...
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
Dernière édition:
CISCO
XLDnaute Barbatruc
Bonsoir
Chez moi, le calcul est fait en 1 ou 2 secondes au total, pour la dizaine de lignes de ton fichier...
@ plus
Chez moi, le calcul est fait en 1 ou 2 secondes au total, pour la dizaine de lignes de ton fichier...
@ plus
PMG
XLDnaute Junior
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+
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
XLDnaute Barbatruc
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
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
XLDnaute Barbatruc
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...
@ plus
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...
Code:
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
Dernière édition:
PMG
XLDnaute Junior
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+
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
XLDnaute Barbatruc
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
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
Dernière édition:
CISCO
XLDnaute Barbatruc
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
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
Je me demande, si on ne peut pas faire avec la fonction FREQUENCE
Dans la colonne de gauche, dans S1:S9, j'ai mis
Code:
FREQUENCE(H$29+LIGNE(INDIRECT("1:"&Feuil1!$G$29))/(24*60);T1:T8)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
XLDnaute Barbatruc
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.
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.
Dernière édition:
CISCO
XLDnaute Barbatruc
Bonjour
J'ai simplifié la formule dans le fichier 06. A la place, j'ai mis le fichier 06bis
@ plus
J'ai simplifié la formule dans le fichier 06. A la place, j'ai mis le fichier 06bis
@ plus
PMG
XLDnaute Junior
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+
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
XLDnaute Junior
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+
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
XLDnaute Barbatruc
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(.....).
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
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
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
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(.....).
Code:
* EST.IMPAIR(EQUIV(H29+listemin/(24*60);K$13:$K$23;1))
Code:
*EST.IMPAIR(EQUIV(H29+listemin/(24*60);PETITE.VALEUR(H$13:I$23;LIGNE(INDIRECT("1:"&2*LIGNES($13:$23))));1))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
Code:
*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
Dernière édition:
CISCO
XLDnaute Barbatruc
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
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
Discussions similaires
- Question
- Résolu(e)
- Résolu(e)
