Calcul de date de fin à partir d'une date de début, d'une durée et contraintes horair

[Fiou]

Bonjour à tous,

Je viens solliciter les âmes charitables en espérant trouver une réponse ici.

Voilà, je cherche tout simplement à calculer une date de fin à partir d'une date de début, d'une durée et de contraintes horaires de travail.

J'ai déjà réussi à réaliser un planning au jour avec gestion des jours fériés mais pas encore à l'heure/minutes prêt.

Exemple :

Horaire de travail Lundi au Vendredi : 8h-12h 13h-17h

Date de début : 21/06/2010 9:00:00 Durée : 30h

Quelle est la formule afin de déterminer la date de fin ?!

Je ne sais pas si c'est réalisable sous Excel mais MERCI d'avance pour votre aide.

Je dispose d'Excel 2007 et éventuellement de Crystal Reports 2008.

 

[hoerwind]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Bonjour,

Salut job,

J'ai testé en modifiant les plages horaires sous la macro et la fonction personnalisée, tout fonctionne parfaitement.

Dès que j'aurai un exemple de son application sur mon projet de planning, je te le mettrai en pièce jointe.
Il est toujours agréable de voir à quoi cela peut bien servir.

En attendant je te remercie infiniment, et toujoirs prêt à t'aider avec l'une ou l'autre petite formule.

 

[Fiou]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Re le fil !

Bon voilà tout fonctionne très bien, j'arrive à planifier des gammes au plus tôt ou au plus tard néanmoins il me reste un petit souci au niveau de ma charge...

Si une opération dure 56:00, selon nos horaires et jours fériés elle débute :

S30 : 29/07/2010 10:42

et finie :

S36 : 06/09/2010 13:27

Je souhaite répartir ma charge en heure par semaine. J'imagine que je devrais créer des colonnes par semaine et y retrouver la valeur en heure de charge, néanmoins quelle formule ou code puis je utiliser toujours selon nos contraintes... ?!

Merci d'avance au fil

 

[hoerwind]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Bonjour,

A1 : 29/07/2010 10:42
B1 : =DATE(ANNEE(A1);MOIS(A1);JOUR(A1)+5-JOURSEM(A1;2))+17/24
B1 renvoie la date et l'heure de fin de la semaine 30

A2 : 06/09/2010 13:27
B2 : =DATE(ANNEE(A2);MOIS(A2);JOUR(A2)-JOURSEM(A2;2)+1)+8/24
B2 renvoie la date et l'heure de début de la semaine 36

Les procédures proposées précédemment te permettent de calculer les délais.

Pour les autres semaines, il faudrait avoir le détail des jours ouvrés.
Un exemple concret en pièce jointe s'avère nécessaire.

 

[Fiou]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Merci Hoerwind pour ton aide,

Voilà mon fichier en pièce jointe en espérant que tu le comprendras...

Ce que je cherche c'est répartir ma charge en heure décimale ou hhmm par semaine pour une famille de ressource ou ressource... sur colonnes supplémentaires afin de retraiter tout cela en tableau croisé dynamique...

Encore MERCI

 

[Fiou]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Je mets également mon fichier en ligne en version 2003...

 

[job75]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Bonjour Fiou, hoerwind, le fil,

Décidément je continue à ne strictement rien comprendre à votre fichier...

Alors dans le mien j'ai créé la fonction ChargeSem.

Les formules en D2 et E2 sont à tirer vers le bas jusqu'à ce que l'on ne trouve plus de valeurs.

Bien sûr la semaine n° 1 est celle de la date de début.

Si vous voulez écrire le n° de la semaine sur l'année, je pense que vous saurez adapter, non ?

Edit : dans les fonctions précédentes, j'avais oublié de déclarer n As Long, pas grave mais j'ai corrigé.

A+

 

[Fiou]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Bonjour JOB et le Fil !

Super JOB et merci pour votre aide et patience. Ce qui me rassure c'est que vous ne comprenez pas grand chose à mon fichier mais j'ai a peu prêt le même problème avec vos fonctions...

J'ai retranscris vos formules en colonne et non en ligne néanmoins il me reste une petite astuce à trouver pour que la valeur de charge correspondante à une semaine donnée s'inscrive bien dans la bonne colonne.

Petites explications sur mon fichier :

Les premières colonnes sont des données exportées d'un ERP via crystal reports. Elles concernent des OF (Ordres de Fabrication) qui sont décomposées en gammes de fabrications donc succession d'OP (Opérations)
La gamme m'indique un temps de réglage et d'exécution pour réaliser l'opération et une date de fin d'OF théorique.

Sur les autres colonnes, je cherche à planifier au plus tard sauf si la date recalculée de début de première OP est inférieure à ma date variable de début de planification. Je dois donc dans ce cas planifier au plus tôt...

De plus, nous sommes équipés de pointeuses qui permettent de considérer que l'OP est déjà réalisée (finie) ce qui a pour conséquence dans mon fichier de ne pas prendre en compte l'opération.

Voilà j'espère que ca sera plus clair avec ces explications. Mon fichier ci joint comporte 2 OF seulement.

J'attends un peu d'aide sur les dernière colonnes afin de répartir mes charges par semaine. Ensuite par tableau croisé dynamique je compte analyser tout ça...

Encore MERCI au fil

 

[job75]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Bonjour Fiou, le fil,

Si vous voulez écrire le n° de la semaine sur l'année, je pense que vous saurez adapter, non ?

Eh non, vous n'avez pas su

Entrez donc cette formule en AC2 et tirez à droite et vers le bas :

=SI($U2="";"";ChargeSem($X2;$N2;1+AC$1-$AA2))

A+

 

[job75]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Re,

Hum, en AC2 c'est (peut-être ?) mieux d'écrire $Z2 au lieu de $AA2, à vous de voir :

=SI($U2="";"";ChargeSem($X2;$N2;1+AC$1-[COLOR="Red"]$Z2[/COLOR]))

Edit : après avoir "tiré" les formules, appuyez sur F9...

A+

 

[Fiou]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Salut Job !

Que dire d'autre que... MERCI pour votre aide encore une fois. Ce n'est que du bonheur ! Je vais faire encore quelques essais à plus grande échelle mais tout cela est très prometteur.

Que me conseillez vous pour débuter sur le VBA ? je dois dire que ces fonctions me fascinent mais je n'arrive pas encore à les comprendre...

A bientôt sur le fil en espérant un jour pouvoir vous aider à mon tour d'une façon ou d'une autre ?!

 

[Fiou]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Re... le Fil,

Voilà mon fichier comporte 4483 lignes et les cellules font souvent références aux fonctions ci dessous :

Option Explicit
Function Datefin(deb As Date, duree As Date) As Date
Dim t1 As Date, t2 As Date, t3 As Date, t4 As Date, dur As Long, minutes As Long, n As Long, t As Date, dat As Long, test As Boolean
Application.Volatile 'permet le recalcul de la fonction
With Sheets("Variables")
t1 = .[E1]
t2 = .[E2]
t3 = .[E3]
t4 = .[E4]
End With
dur = Round(duree * 1440) 'conversion en minutes
Datefin = deb 'au cas où duree = 0
While minutes < dur
n = n + 1
Datefin = deb + n / 1440
t = TimeValue(Datefin)
If Int(CDec(Datefin)) > dat Then
dat = Int(CDec(Datefin))
test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
End If
If test And (t > t1 And t <= t2 Or t > t3 And t <= t4) Then minutes = minutes + 1
Wend
End Function

Function DateDeb(fin As Date, duree As Date) As Date
Dim t1 As Date, t2 As Date, t3 As Date, t4 As Date, dur As Long, dat As Long, test As Boolean, minutes As Long, n As Long, t As Date
Application.Volatile 'permet le recalcul de la fonction
With Sheets("Variables")
t1 = .[E1]
t2 = .[E2]
t3 = .[E3]
t4 = .[E4]
End With
dur = Round(duree * 1440) 'conversion en minutes
dat = Int(CDec(fin)) 'initialisation indispensable ici
test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
DateDeb = fin 'au cas où duree = 0
While minutes < dur
n = n + 1
DateDeb = fin - n / 1440
t = TimeValue(DateDeb)
If Int(CDec(DateDeb)) < dat Then
dat = Int(CDec(DateDeb))
test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
End If
If test And (t >= t1 And t < t2 Or t >= t3 And t < t4) Then minutes = minutes + 1
Wend
End Function

Function ChargeSem(deb As Date, duree As Date, semaine As Integer) As Variant
Dim t1 As Date, t2 As Date, t3 As Date, t4 As Date, sem As Integer, dur As Long
Dim Datefin As Date, minutes As Long, n As Long, t As Date, dat As Long, test As Boolean
Application.Volatile 'permet le recalcul de la fonction
With Sheets("Variables")
t1 = .[E1]
t2 = .[E2]
t3 = .[E3]
t4 = .[E4]
End With
If Weekday(deb, 2) > 1 Then sem = 1
dur = Round(duree * 1440) 'conversion en minutes
Do While minutes < dur
n = n + 1
Datefin = deb + n / 1440
t = TimeValue(Datefin)
If Int(CDec(Datefin)) > dat Then
dat = Int(CDec(Datefin))
test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
If Weekday(dat, 2) = 1 Then sem = sem + 1: If sem > semaine Then Exit Do
End If
If test And (t > t1 And t <= t2 Or t > t3 And t <= t4) Then
minutes = minutes + 1
If sem = semaine Then ChargeSem = ChargeSem + 1
End If
Loop
If ChargeSem Then ChargeSem = ChargeSem / 1440 Else ChargeSem = "" 'pour ne rien afficher si charge nulle
End Function

Malgré que le calcul soit réalisé par un serveur (4x Processeurs) le temps avoisine les 30 min. J'ai entendu parler que "Application.ScreenUpdating = True/False" permettait d'améliorer sensiblement le temps de calcul. Après essais d'insertion dans les fonctions en début et fin, je me rends compte que l'écran se mets toujours à jour au fur et à mesure du traitement...

Pourriez vous m'indiquer comment insérer le ''Application.ScreenUpdating = True/False'' ? Et si est ce vraiment efficace ou il y a t'il d'autres possibilités pour améliorer la rapidité de traitement... ?!

Merci d'avance au fil !

 

[job75]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Bonjour Fiou,

Application.ScreenUpdating = False ne fera rien sur le temps de calcul des fonctions.

Par contre en début de la procédure Sub, insérer :

Application.Calculation = xlManual

et à la fin :

Application.Calculation = xlAutomatic

Cela évite le recalcul de toutes les fonctions pendant la procédure.

A+

 

[Fiou]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Re Job,

Et merci encore pour votre aide persistante pour la résolution de mes problèmes.

Je ne comprends pas encore le VBA mais j'y travaille...

Une fonction SUB, je comprends la une sous fonction ou sous partie de programme, ceci veut dire que je dois insérer les commandes comme ceci ?!

Option Explicit
Function Datefin(deb As Date, duree As Date) As Date
Dim t1 As Date, t2 As Date, t3 As Date, t4 As Date, dur As Long, minutes As Long, n As Long, t As Date, dat As Long, test As Boolean
Application.Volatile 'permet le recalcul de la fonction
Application.Calculation = xlManual
With Sheets("Variables")
t1 = .[E1]
t2 = .[E2]
t3 = .[E3]
t4 = .[E4]
End With
dur = Round(duree * 1440) 'conversion en minutes
Datefin = deb 'au cas où duree = 0
While minutes < dur
n = n + 1
Datefin = deb + n / 1440
t = TimeValue(Datefin)
If Int(CDec(Datefin)) > dat Then
dat = Int(CDec(Datefin))
test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
End If
If test And (t > t1 And t <= t2 Or t > t3 And t <= t4) Then minutes = minutes + 1
Wend
Application.Calculation = xlAutomatic
End Function

Function DateDeb(fin As Date, duree As Date) As Date
Dim t1 As Date, t2 As Date, t3 As Date, t4 As Date, dur As Long, dat As Long, test As Boolean, minutes As Long, n As Long, t As Date
Application.Volatile 'permet le recalcul de la fonction
Application.Calculation = xlManual
With Sheets("Variables")
t1 = .[E1]
t2 = .[E2]
t3 = .[E3]
t4 = .[E4]
End With
dur = Round(duree * 1440) 'conversion en minutes
dat = Int(CDec(fin)) 'initialisation indispensable ici
test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
DateDeb = fin 'au cas où duree = 0
While minutes < dur
n = n + 1
DateDeb = fin - n / 1440
t = TimeValue(DateDeb)
If Int(CDec(DateDeb)) < dat Then
dat = Int(CDec(DateDeb))
test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
End If
If test And (t >= t1 And t < t2 Or t >= t3 And t < t4) Then minutes = minutes + 1
Wend
Application.Calculation = xlAutomatic
End Function

Function ChargeSem(deb As Date, duree As Date, semaine As Integer) As Variant
Dim t1 As Date, t2 As Date, t3 As Date, t4 As Date, sem As Integer, dur As Long
Dim Datefin As Date, minutes As Long, n As Long, t As Date, dat As Long, test As Boolean
Application.Volatile 'permet le recalcul de la fonction
Application.Calculation = xlManual
With Sheets("Variables")
t1 = .[E1]
t2 = .[E2]
t3 = .[E3]
t4 = .[E4]
End With
If Weekday(deb, 2) > 1 Then sem = 1
dur = Round(duree * 1440) 'conversion en minutes
Do While minutes < dur
n = n + 1
Datefin = deb + n / 1440
t = TimeValue(Datefin)
If Int(CDec(Datefin)) > dat Then
dat = Int(CDec(Datefin))
test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
If Weekday(dat, 2) = 1 Then sem = sem + 1: If sem > semaine Then Exit Do
End If
If test And (t > t1 And t <= t2 Or t > t3 And t <= t4) Then
minutes = minutes + 1
If sem = semaine Then ChargeSem = ChargeSem + 1
End If
Loop
If ChargeSem Then ChargeSem = ChargeSem / 1440 Else ChargeSem = "" 'pour ne rien afficher si charge nulle
Application.Calculation = xlAutomatic
End Function



Ah oui j'oubliai, j'ai déjà passé le fichier en calcul manuel et sans calcul à l'enregistrement...

A+

 

[job75]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Re,

Pas du tout, les procédures Sub commencent par Sub, les fonctions par Function.

Il n'y a strictement rien à ajouter dans une Function.

Par contre on peut éviter leur recalcul quand s'exécute une procédure Sub.

Si c'est à l'ouverture du fichier que vous avez un problème, peut-être en effet mettre le calcul "sur ordre" à l'ouverture.

A+

 

[job75]

Re : Calcul de date de fin à partir d'une date de début, d'une durée et contraintes h

Bonjour Fiou, le forum,

En vacances en Normandie, je suis très peu sur un ordi.

Et je ne sais pas pourquoi je repense à ce fil.

Pour gagner sur la durée du calcul, il faut neutraliser la fonction ChargeSem sur les semaines indésirables.

Donc en AC2 (fichier du post #22) entrer la formule :

=SI(OU($U2="";[COLOR="Red"]AC$1<$X2;AC$1>$Y2[/COLOR]);"";ChargeSem($X2;$N2;1+AC$1-$Z2))

Mais vous avez peut-être déjà pensé à cette astuce

A+