Microsoft 365 Calculer une date de fin d'une tâche en tenant compte de sa durée et une plage horaire

[xls_62]

Bonsoir,

Je travaille sur un fichier de planning machines qui contient 4 feuilles ( 1 feuille = 1 machine) et chaque feuille contient X lignes de tâches à traiter.
Le besoin est de calculer la date de fin de chaque ligne d'opération listée dans la feuille de calcul et qui dispose naturellement d'une durée donnée.

Exemple :

FEUILLE PROCESS1
Date de fin A CALCULER : Colonne H29 , et Je dispose :
+> Durée de la tâche : colonne I29 pour la ligne 29
+ >Date début de calcul : colonne G29 ( date renseignée en manuel)

Remarque : Date début de la ligne suivante G30 = H29 ( date de fin de la tâche précédente = date de début la tâche suivante )

Contraintes :

1. Ouverture horaire par machine ( process1, process 2 etc), Plage horaire disponible : Colonne C1 -> C6 => Ouverture reste variable 1*8 ou 2*8 ou 3*8 selon la machine.
2. Les jours fériés ( Colonne C14-> D26)
3. Lignes de calcul sont estimées à 200 ligne ( une moyenne)

Je mets en PJ un fichier type, en espérant que j'étais clair dans mes explications.

Par avance, Merci pour vos retours.

 

[CISCO]

Bonsoir

La formule utilisée sur l'autre fil de discussion, telle que, ne convient pas : Elle ne prend pas en compte les heures entre minuit et 6:00, donc ne donne pas les bons résultats pour les machines tournant en 3*8.

Je regarde ça davantage ce soir.

@ plus

 

[xls_62]

Bonsoir Cisco,

Parfait et je vous remercie de votre réactivité.
Pour faciliter les choses on peut considérer directement les plages de travail (hors pause) ci-dessous :
1*8 = 6h-> 13h30
2*8 = 6h-> 13h30 + 14h-> 21h30
3*8 = 6h-> 13h30 + 14h-> 21h30 +22h-> 5h30

A disposition pour toute question.

 

[CISCO]

Bonsoir

Je n'ai fait le travail que pour la feuille Process1, donc pour un cas en 3*8, dépassant minuit. Vérifie si c'est bon.
J'essayerai de faire les autres cas demain, mais je ne pense pas que cela soit très difficile...

@ plus

P.S : Au lieu de faire un test entre C2 et C3 d'une part, et entre C5 et C6, d'autre part (ce qui donnerait les minutes de travail), C6 posant problème, j'ai fait avec un test entre C3 et C5 d'une part, puis MOD(C6,1) et C2 (Ce qui ne me donnerait que les minutes de repos), puis j'ai pris le contraire.

 

[xls_62]

OK merci Cisco, je regarde ça et on se tient au courant..Encore merci

 

[xls_62]

Je viens de faire un test le résultat est correct avec la configuration 3*8 sur la feuille process1

PS: Serait il possible d'avoir dans les feuilles de calcul genre une sélection d'horaire ( par exemple: en faisant 3*8 la plage horaire se met en auto) car les ouvertures machines changent selon la charge et la disponibilité main d’œuvre?
Encore merci Cisco

 

[CISCO]

Bonjour

Cf. une solution en pièce jointe. A tester/vérifier davantage bien sûr.

Pour avoir des formules plus courtes, j'ai défini davantage de noms. Comme d'habitude, cela n'a pas que des avantages : Les formules sont plus courtes, mais moins compréhensibles.

Pour n'avoir à définir chaque nom qu'une seule fois (et non une fois par feuille), j'ai défini le nom nomfeuille et j'ai utilisé très souvent des INDIRECT(nomfeuille &"!..."). Par ex, j'ai fait avec DébAM =INDIRECT(nomfeuille&"!C2")*24*60 au lieu de faire avec DébAM1= Process1!$C$2*24*60, DébAM2= Process2!$C$2*24*60, DébAM3= Process3!$C$2*24*60 et ainsi de suite.
Comme d'habitude, cela n'a pas que des avantages : Les pages ne sont pas mises à jour automatiquement. Lorsqu'on passe d'une feuille à une autre, les résultats de la nouvelle feuille ne sont pas corrects (Ils tiennent compte de certaines données de l'ancienne feuille certainement). Pour la mettre à jour, il suffit d'écrire n'importe quoi n'importe où dans la nouvelle feuille.

Ainsi, si tu as d'autres machines, donc d'autres feuilles à installer, tu n'as pas besoin de créer de nouveaux noms.

Les 20 000 min (environ 2 semaines) dans la colonne E sont indispensables. Les formules font les calculs sur une plage allant de début à début + 20 000 min. Si la date correspondant à la fin de la tâche à exécuter se trouve dans cette plage, la formule donne un résultat. Sinon, il y a un message d'erreur. Et il faut remplacer ce 20000 par un nombre plus grand. On n'est absolument pas obligé de mettre la même valeur sur toutes les lignes de cette colonne E.

@ plus

P.S : J'ai fait avec une seule formule dans H29, qu'il s'agisse d'une feuille avec une machine en 1*8, 2*8 ou 3*8. J'ai un autre fichier utilisant une formule comportant un test du genre SI(1*8 ou 2*8; formule 1; formule 2)... mais c'est moins "beau"...

 

[xls_62]

Bonjour

Cf. une solution en pièce jointe. A tester/vérifier davantage bien sûr.

Pour avoir des formules plus courtes, j'ai défini davantage de noms. Comme d'habitude, cela n'a pas que des avantages : Les formules sont plus courtes, mais moins compréhensibles.

Pour n'avoir à définir chaque nom qu'une seule fois (et non une fois par feuille), j'ai défini le nom nomfeuille et j'ai utilisé très souvent des INDIRECT(nomfeuille &"!..."). Par ex, j'ai fait avec DébAM =INDIRECT(nomfeuille&"!C2")*24*60 au lieu de faire avec DébAM1= Process1!$C$2*24*60, DébAM2= Process2!$C$2*24*60, DébAM3= Process3!$C$2*24*60 et ainsi de suite.
Comme d'habitude, cela n'a pas que des avantages : Les pages ne sont pas mises à jour automatiquement. Lorsqu'on passe d'une feuille à une autre, les résultats de la nouvelle feuille ne sont pas corrects (Ils tiennent compte de certaines données de l'ancienne feuille certainement). Pour la mettre à jour, il suffit d'écrire n'importe quoi n'importe où dans la nouvelle feuille.

Ainsi, si tu as d'autres machines, donc d'autres feuilles à installer, tu n'as pas besoin de créer de nouveaux noms.

Les 20 000 min (environ 2 semaines) dans la colonne E sont indispensables. Les formules font les calculs sur une plage allant de début à début + 20 000 min. Si la date correspondant à la fin de la tâche à exécuter se trouve dans cette plage, la formule donne un résultat. Sinon, il y a un message d'erreur. Et il faut remplacer ce 20000 par un nombre plus grand. On n'est absolument pas obligé de mettre la même valeur sur toutes les lignes de cette colonne E.

@ plus

P.S : J'ai fait avec une seule formule dans H29, qu'il s'agisse d'une feuille avec une machine en 1*8, 2*8 ou 3*8. J'ai un autre fichier utilisant une formule comportant un test du genre SI(1*8 ou 2*8; formule 1; formule 2)... mais c'est moins "beau"...

Bonjour Cisco,
les formules marchent à merveille en faisant quelques testes vraiment excllent travail..
Par contre j'ai deux questions :
Question 1 : sur l'onglet porcess1, vous avez notez "29:30" comme heure fin pm, pouvez vous m'expliquer un peu plus les valeurs en rouge ( 29:30 / 30:30)?
Question 2 : Si demain je souhaite passer la machine process2 en 3*8, dois-je copier la même plage d'ouverture que la feuille process 1 ?

Heure de travail par jour		22:30	Machine	3*8
Heure de début AM		06:00
Heure de fin AM		13:00	07:00​
Tps repas 1		01:00
Heure de début PM		14:00
Heure de fin PM		29:30	15:30​	22:30​
		30:30

 

[xls_62]

Juste info :Sur l'ouverture : 1*8 qui est égale 6h -13h30 correspond vraiment la période heure dispo ( j'ai enlevé 30min de pause car l'horaire officielle est de 6h-14h). idem 2*8 & 3*8 ( je considère la dernière 30min comme pause)

je sais pas si ça impacte la formule conçue..

 

[CISCO]

Bonsoir

Normalement, tu peux mettre "n'importe" quelle valeur dans C2, C3, C5 et C6, à condition qu'elles soient logiques et dans l'ordre croissant.

Les plages horaires n'ont pas besoin d'être égales d'une feuille à une autre. La formule dans la colonne H, ne tient pas compte du fait qu'il s'agit d'une machine en 1*8, 2*8 ou 3*8, mais uniquement des valeurs dans C2,C3, C5 et C6. Elle considère qu'il peut y avoir trois périodes sans travail :
* vers midi (Période 1),
* le soir de MIN(FinPM, minuit) jusqu'à minuit (période 2),
* et le matin de MAX(minuit,FinPM) à début AM (période 3, suivant forcément la période 2).

Ainsi, si FinPM < minuit, il y aura une grande période de repos de FinPM à Début AM du jour suivant, à cheval sur minuit, = Période 2 + Période 3
Si FinPM > minuit (cas des machines en 3*8), la période 2 = 0 et période 3 va de FinPM à Début AM du jour suivant.

La formule n'est pour autant peut-être pas complètement polyvalente. Par exemple, elle ne donnerait certainement pas un bon résultat s'il y avait une pause entre chaque groupe de 8 h. Là, elle est prévue pour une pause (période 1), et une autre à cheval ou pas sur minuit (période 2 + période 3).


Le 29:30, c'est la même chose que ton 01/01/1900 05:30, mais au format [hh]:mm. Les accolades droites [ ] permettent l'affichage de valeurs supérieures à 24:00.

Tu peux supprimer le 30:30.

Le 0 dans G13 est indispensable.

@ plus

 

[xls_62]

Merci cisco pour les explications, Je regarderai tous ces éléments demain et je reviendrai vers vous si d'autres questions ou blocage.
Encore un grand merci

PS : je validerai la solution sur le forum dès que je teste tout ça

 

[xls_62]

Bonsoir Cisco,

Les formules marchent parfaitement
A date avec le besoin actuel, la solution proposée fait face au problème donc je valide

Je tiens à vous remercier pour votre disponibilité, votre professionnalisme et précieuse aide.

Je vous souhaite bonne soirée.

​

 

[CISCO]

Bonsoir

Au plaisir... et en espérant que d'autres comprendront mes formules tordues... Ne pas oublier de faire la mise à jour à chaque changement de feuille... J'ai essayé avec les touches F1, F2... F12, mais cela ne fait pas le taf. Une écriture quelconque dans une cellule quelconque fait le travail...

@ plus

 

[xls_62]

Bonjour Cisco,
J’espère que vous allez bien, Dans l'optique d'améliorer le fichier, je souhaite intégrer un autre paramètre à la formule.
j'ai des îlots de production qui englobent parfois 2 voire 3 machines et qui font le même job.
=> L'idée est de pouvoir démarrer 2 ou 3 productions en parallèle dans les lignes de calcul.
Exemple :

NBRE MACHINE	2
(VARIABLE)
	N° MACH	N° OF	DATE DEBUT	Durée	DATE DE FIN
	Machine1	OF1	17/01/2022 06:00	12:01	17/01/2022 18:32
	Machine2	OF2	17/01/2022 06:00	01:00	17/01/2022 07:00
	Machine1	OF3	17/01/2022 18:32	05:00	18/01/2022 08:02
	Machine2	OF4	17/01/2022 08:00	01:00	17/01/2022 09:00
	Machine1	OF5	18/01/2022 08:02	02:00	18/01/2022 10:02
	Machine2	OF6	17/01/2022 09:00	04:00	17/01/2022 13:00
	etc	etc
	etc	etc

Pensez vous que c'est quelque chose de faisable ?

Merci de votre aide.

Cdt,

 

[CISCO]

Bonsoir

La machine 2 est mise à l'arrêt de 7:00 à 8:00. Est-ce normal ?

@ plus