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.

 

[commandant]

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

Si tu nous donnais un exemple de ce que tu as déja fait ce serait plus simple pour t'aider

 

[hoerwind]

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

Bonjour et bienvenu sur le forum, salut commandant,

Question déjà souvent posée et résolue.
As-tu fait une recherche sous les anciens messages, à commencer par ceux que tu trouveras au bas de cette page sous "Discussions similaires".

Si tu ne trouves pas quelque qui puisse te convenir et que tu mets un exemple en pièce jointe, n'oublie pas d'y ajouter la liste des jours fériés (la francophonie ne s'arrête pas aux limites de l'hexagone).

 

[Fiou]

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

Bonjour à vous et merci pour votre réactivité.

Effectivement, bien que Google soit mon ami je n'ai pas encore trouvé mon bonheur ici et ailleurs.

Mon but est de déterminer une charge/capacité à partir d'Ordres de Fabrication en cours.

Une première extraction sous crystal reports 2008 permet de lister les opérations d'un OF en cours avec leur avancement. (Op finie O/N) Je cherche à replanifier au plus tard par défaut sinon au plus tôt si un OF risque d'être en retards par rapport à sa date de fin.

Ci joint un exemple de fichier mais qui arrondie à la journée supérieure une opération. Ma problématique étant d'y insérer des plages horaires de travail... et de planifier à l'heure/minute !
Attention ca pique un peu les yeux...

Ensuite, je compte retravailler ces données sous tableau croisé dynamique afin de calculer une charge par ressource et délai de réalisation.

Encore merci je reste à votre disposition pour toutes informations complémentaires.

 

[hoerwind]

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

re,

Je dois t'avouer que je ne comprends rien à ton exemple.

Je reprends ta question initiale :
Départ : lun 21/06/2010 à 9:00:00
Durée : 30:00:00
Horaire de travail : jours ouvrés de 8:00 à 12:00 et de 13:00 à 17:00, soit 8:00 heures par jour

Questions :
Où peut-on trouver ces données sur ton exemple ?
Où peut-on trouver la liste des jours fériés ?

Un calcul manuel me donne comme date de fin :
lun 21/06/2010 à partir de 9:00 : 7:00
mar 22/06/2010 - journée complète : 8:00
mer 23/06/2010 - journée complète : 8:00
jeu 24/06/2010 jusqu'à 16:00 : 7:00

24/06/2010 16:00, est-ce cela le résultat attendu ?

Questions subsidiaire :
La date de départ peut-elle être un jour non ouvré ?

 

[job75]

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

Bonjour le fil,

Sur Excel 2003 je ne peux me servir du fichier alors j'en ai fait un autre.

Il utilise cette fonction macro (Alt+F11 pour aller dans VBA) :

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
t1 = TimeValue("8:0")
t2 = TimeValue("12:0")
t3 = TimeValue("13:0")
t2 = TimeValue("17:0")
dur = Int(CDec(duree * 1440)) 'conversion en minutes
While minutes <= dur
  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
  n = n + 1
Wend
End Function

La fonction compte les minutes écoulées jusqu'à ce que la durée soit atteinte.

Elle tient compte des horaires de travail, des week-ends et jours fériés (plage nommée Feries).

A+

 

[Fiou]

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

re,

Je dois t'avouer que je ne comprends rien à ton exemple. -> J'en suis désolé, mais le fichier fourni n'est que la gestion actuelle à la journée et ne tiens pas (encore) compte des plages horaires de travail.

Je reprends ta question initiale :
Départ : lun 21/06/2010 à 9:00:00
Durée : 30:00:00
Horaire de travail : jours ouvrés de 8:00 à 12:00 et de 13:00 à 17:00, soit 8:00 heures par jour

Questions :
Où peut-on trouver ces données sur ton exemple ? Ces données n'existent pas encore sur le fichier
Où peut-on trouver la liste des jours fériés ? Ces données n'existent pas encore sur le fichier

Un calcul manuel me donne comme date de fin :
lun 21/06/2010 à partir de 9:00 : 7:00
mar 22/06/2010 - journée complète : 8:00
mer 23/06/2010 - journée complète : 8:00
jeu 24/06/2010 jusqu'à 16:00 : 7:00

24/06/2010 16:00, est-ce cela le résultat attendu ? OUI exactement

Questions subsidiaire :
La date de départ peut-elle être un jour non ouvré ?

Non, il s'agira d'un jour ouvré modifiable en variable avec heure de début. Exemple : Lundi 21/06/2010 8:00

Encore Merci pour votre acharnement à aider ceux qui s'acharnent aussi de leur coté...

 

[Fiou]

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

Merci Job75 pour ta réponse ! C'est que du bonheur.

Je découvre ainsi l'utilisation du VBA dans Excel...

Sans vouloir abuser, est-il possible de modifier ce code pour fonctionner en sens inverse : déterminer une date/heure de début à partir d'une date/heure de fin - durée. Cela me permettrait ainsi de déterminer des dates au plus tard.

De plus, comment faire pour que le code se servent des plages horaires définies dans l'onglet 'variables' ?!

ENCORE MERCI A VOUS !

 

[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, le forum,

Ma macro d'hier était baclée - la fête de la musique !!

Prenez celle-ci :

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, t As Date, dat As Long, test As Boolean
t1 = TimeValue("8:0")
t2 = TimeValue("12:0")
t3 = TimeValue("13:0")
t4 = TimeValue("17:0")
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

Fichier (2) joint.

A+

 

[job75]

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

Re,

(...) est-il possible de modifier ce code pour fonctionner en sens inverse : déterminer une date/heure de début à partir d'une date/heure de fin - durée. Cela me permettrait ainsi de déterminer des dates au plus tard.

De plus, comment faire pour que le code se servent des plages horaires définies dans l'onglet 'variables' ?!

Pas trop compris la feuille "Data" de votre fichier, alors je continue avec le mien.

La fonction macro DateDeb part de la date de fin et remonte le temps :

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, t As Date
Application.Volatile 'permet le recalcul de la fonction
With Sheets("Variables")
  t1 = .[F1]
  t2 = .[F2]
  t3 = .[F3]
  t4 = .[F4]
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

A+

 

[hoerwind]

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

Salut job,

Je viens de tester la macro sous le message #9
Il m'est difficile de faire mieux par formule, moi qui n'y connais rien en VBA, je reste néanmoins impressionné par son efficacité, félicitations !

Je compte en faire usage à titre personnel (merci), mais y ai apporté un petit complément :
Lorsqu'on saisi une date de début qui tombe un samedi, un dimanche ou un jour férié, ou une heure de début inférieure à t1 (8:00), entre t2 et t3 (12:00 et 13:00), ainsi que postérieure à t4 (17:00), un message d'erreur est affiché (menu Données - Validation).

Serait-il possible d'incorporer cela dans la macro, mais en affichant automatiquement la première heure ouvrée après la date saisie, au lieu de la date et de l'heure saisie ?

Si cela ne te prends pas trop de temps, cela me ferait plaisir.

 

[job75]

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

Bonjour hoerwind,

Ton problème est intéressant et complète bien cette discussion.

Serait-il possible d'incorporer cela dans la macro, mais en affichant automatiquement la première heure ouvrée après la date saisie, au lieu de la date et de l'heure saisie ?

Incorporer non, car la macro précédente est une fonction, là il faut une macro évènementielle.

Donc dans le code de la feuille (clic droit sur l'onglet et Visualiser le code) :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t1 As Date, t2 As Date, t3 As Date, t4 As Date
Dim n As Long, t As Date, dat As Long, test As Boolean, d As Date
t1 = TimeValue("8:0")
t2 = TimeValue("12:0")
t3 = TimeValue("13:0")
t4 = TimeValue("17:0")
Set Target = Intersect(Target, [A2:A65536])
If Target Is Nothing Then Exit Sub
For Each Target In Intersect(Target, Me.UsedRange) 'en cas d'entrées multiples (copier-coller...)
  If IsDate(Target) Then
    n = 0
    t = TimeValue(Target)
    dat = Int(CDec(Target))
    test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
    While Not test Or Not (t >= t1 And t <= t2 Or t >= t3 And t <= t4)
      n = n + 1
      d = Target + n / 1440 'incrémente d'une minute
      t = TimeValue(d)
      If Int(CDec(d)) > dat Then
        dat = Int(CDec(d))
        test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))
      End If
    Wend
    Application.EnableEvents = False
    Target = Target + n / 1440
    Application.EnableEvents = True
  End If
Next
End Sub

Fichier (3) joint

Teste en entrant Date/heure en colonne A.

A+

 

[hoerwind]

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

Salut job,

merci, je teste demain à l'aube et te tiens au courant.

Tu sais que je suis nul en macro, d'où petit problème avec la macro précédente.
J'ai déplacé ta liste des jours fériés en Feuil2!B2:G14 (six années) + quelques jours manquants.
J'ai modifié la source de la plage Ferie en conséquence (menu Insertion), mais cela semble ne pas fonctionner, le jour de fin affiche un jour férié.

Peux-tu jeter un coup d'oeil à la pièce jointe ?
Bonne soirée

Edition : ce problème ne se présente pas sous ta dernière version.
Par contre le changement automatique à la première heure qui suit l'heure saisie semble bon, mais je testerai plus à fond.

 

[job75]

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

Re,

Il n'y a aucun problème...

Dans cette ligne de code :

test = Weekday(dat, 2) < 6 And IsError(Application.Match(dat, [Feries], 0))

j'utilise Application.Match => c'est la fonction EQUIV car c'est la plus rapide.

Mais cela suppose que la plage de recherche n'ait qu'une dimension.

Si tu mets les jours fériés dans une plage à deux dimensions, alors il faut utiliser Application.CountIf => c'est la fonction NB.SI.

test = Weekday(dat, 2) < 6 And Application.CountIf([Feries], dat) = 0

A+

 

[Fiou]

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

Voilà, je viens de terminer mon fichier Excel permettant de recaler au plus tard ou plus tôt des gammes de fabrications en fonction de leur avancement et date de début ou fin. Ceci me permet donc via tableau croisé dynamique de déterminer une charge et donc de pouvoir planifier !

MERCI au fil et particulièrement à JOB pour votre aide



Je mettrai bien en ligne mon fichier mais il pèse 8 Mo...

A une prochaine sur le forum j'espère