Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

Fiou

XLDnaute Nouveau
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.
 
Trier par date Trier par votes
H

hoerwind

XLDnaute Barbatruc
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.
 
Vote positif 0 Vote négatif

Fiou

XLDnaute Nouveau
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
 
Vote positif 0 Vote négatif
H

hoerwind

XLDnaute Barbatruc
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.
 
Vote positif 0 Vote négatif

Fiou

XLDnaute Nouveau
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
 

Pièces jointes

  • exemple.xlsm
    27.8 KB · Affichages: 203
  • exemple.xlsm
    27.8 KB · Affichages: 145
  • exemple.xlsm
    27.8 KB · Affichages: 161
Dernière édition:
Vote positif 0 Vote négatif

Fiou

XLDnaute Nouveau
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...
 

Pièces jointes

  • exemple.zip
    21 KB · Affichages: 173
  • exemple.zip
    21 KB · Affichages: 93
  • exemple.zip
    21 KB · Affichages: 113
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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+
 

Pièces jointes

  • ChargeSemaine(1).xls
    46.5 KB · Affichages: 146
Dernière édition:
Vote positif 0 Vote négatif

Fiou

XLDnaute Nouveau
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
 

Pièces jointes

  • Exemple 2.zip
    25.4 KB · Affichages: 63
  • exemple 2.xlsm
    36.3 KB · Affichages: 102
  • Exemple 2.zip
    25.4 KB · Affichages: 60
  • exemple 2.xlsm
    36.3 KB · Affichages: 110
  • Exemple 2.zip
    25.4 KB · Affichages: 63
  • exemple 2.xlsm
    36.3 KB · Affichages: 122
Vote positif 0 Vote négatif

job75

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

Bonjour Fiou, le fil,

job75 à dit:
Si vous voulez écrire le n° de la semaine sur l'année, je pense que vous saurez adapter, non ?
Cliquez pour agrandir...

Eh non, vous n'avez pas su

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

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

A+
 
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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 :

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

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

A+
 
Dernière édition:
Vote positif 0 Vote négatif

Fiou

XLDnaute Nouveau
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 ?!

 
Vote positif 0 Vote négatif

Fiou

XLDnaute Nouveau
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 !
 
Dernière édition:
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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 :

Code: 
Application.Calculation = xlManual

et à la fin :

Code: 
Application.Calculation = xlAutomatic

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

A+
 
Vote positif 0 Vote négatif

Fiou

XLDnaute Nouveau
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+
 
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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+
 
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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 :

Code: 
=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+
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

E
  • Résolu(e)
Microsoft 365 Trouver Date début et date de fin
2
Réponses
15
Affichages
802
eric72
E
R
  • Résolu(e)
XL 2016 Somme par année en fonction d'une date de début et de fin
Réponses
4
Affichages
671
remyexcel
R
G
  • Question
XL 2016 Répartition mensuelle de la charge par équipe en fonction d'une date de début et d'une date de fin
Réponses
4
Affichages
624
Lolote83
L
L
  • Question
Microsoft 365 Calculer le nombre de jour par semaine (sur base d'une date de début et de fin de projet)
Réponses
1
Affichages
373
flc13090
L
  • Question
XL 2016 Calcul à partir d'une date précise ou - à défaut - à partir de la date qui suit immédiatement après
Réponses
4
Affichages
602
Lorenzini
L
N
  • Résolu(e)
XL 2016 Retrouver des dates de début et de fin dans une colonne par rapport à des données
Réponses
9
Affichages
876
nelson29
N
K
  • Question
Microsoft 365 Fin de trimestre suivant à partir d'une date
Réponses
5
Affichages
1 K
kellypombal
K
O
  • Question
XL 2013 Condition en fonction d'un jour et d'une heure de début et de fin
Réponses
7
Affichages
759
Olivier Delporte
O
C
  • Résolu(e)
Microsoft 365 Créer un RV outlook à partir de chaque ligne d'un tableau
Réponses
8
Affichages
1 K
c.branco
C
M
  • Question
Microsoft 365 Mise en forme des cellules en couleurs en fonction d'une durée
Réponses
6
Affichages
898
TooFatBoy
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…