Calcul horaire d'embauche/débauche selon horaire/position de navire

Bonjour à tous!

Je dois établir sur l'année complète les heures d'embauche et de débauche de mon personnel marin indéxé sur les horaires de départ et d'arrivée d'un navire et également conditionné par la position du navire. Selon les règles ci-dessous. Je vous joins la feuille de calcul qui est communiquée une fois les horaires établies. J'aimerai éviter de faire le calcul de tete pour chaque jour ça me prend un temps fou alors que c'est possible d'automatiser mais je n'y arrive pas.

Je vous remercie pour votre aide

Bonjour,

La structure du document est délicate ... bon j'ai commencé par mettre les heures calculées dans les colonnes P/Q/R/S

Il faudrait maintenant comprendre informatiquement quand on a un départ et quand on a une arrivée et calculer l'écart.

Vérifie déjà les heures (et cela peut déjà t'aider)

Bonjour Sebou1011, Steelson salut

J'ai intercalé quatre colonnes dont les formules sont à étendre vers le bas tant que nécessaire:

o Départ de Port A

§ Embauche : 1h (01:00:00 =4/96) avant le départ le Lundi (2 =JOURSEM(B16)) sinon 45min (00:45:00 =3/96)

§ Débauche : 2h15 (02:15:00 =9/96) après le départ

En E3 la première heure de début pour le bateau A:

=SI(OU(B3="";MIN(INDIRECT(CONCATENER(ADRESSE(LIGNE();4);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;4));1))=0);"";SI(MIN(INDIRECT(CONCATENER(ADRESSE(LIGNE();4);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;4));1))>0;MIN(INDIRECT(CONCATENER(ADRESSE(LIGNE();4);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;4));1))-SI(JOURSEM(B3)=2;4/96;3/96))) 

En F3 la dernière heure de fin pour le bateau A:

=SI(OU(B3="";MAX(INDIRECT(CONCATENER(ADRESSE(LIGNE();4);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;4));1))=0);"";SI(MAX(INDIRECT(CONCATENER(ADRESSE(LIGNE();4);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;4));1))>0;MAX(INDIRECT(CONCATENER(ADRESSE(LIGNE();4);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;4));1))+9/96)) 

o Départ de Port B

§ Embauche : 1h15 (01:15:00 =5/96) avant le départ

§ Débauche : 2h30 (02:30:00 =10/96) après le départ

En H3 la première heure de début pour le bateau B:

=SI(OU(B3="";MIN(INDIRECT(CONCATENER(ADRESSE(LIGNE();7);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;7));1))=0);"";SI(MIN(INDIRECT(CONCATENER(ADRESSE(LIGNE();7);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;7));1))>0;MIN(INDIRECT(CONCATENER(ADRESSE(LIGNE();7);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;7));1))-5/96)) 

En I3 la dernière heure de fin pour le bateau B:

=SI(OU(B3="";MAX(INDIRECT(CONCATENER(ADRESSE(LIGNE();7);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;7));1))=0);"";SI(MAX(INDIRECT(CONCATENER(ADRESSE(LIGNE();7);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;7));1))>0;MAX(INDIRECT(CONCATENER(ADRESSE(LIGNE();7);":";ADRESSE(EQUIV(B3+1;B:B;0)-1;7));1))+10/96))

En N3, votre formule initiale tenant compte de vos valeurs:

=SI(J3="";"";L3-J3)

En P3, un texte affichant mes résultats par la formule suivante à étendre vers le bas tant que nécessaire:

=SI(ET(E3="";H3="");"";TEXTE(B3;"nn jj mmm aa")&" début : "&TEXTE(MIN(E3;H3);"hh:mm")&", fin : "&TEXTE(MAX(F3;I3);"hh:mm")&", durée : "&TEXTE(MAX(F3;I3)-MIN(E3;H3);"hh:mm")) 

wahou ... belle formule

je n'ai pas l'habitude des MAX(INDIRECT(CONCATENER(ADRESSE(LIGNE

mais cela répond au problème !

Moi c'est :

MOD(CNUM(GAUCHE()&":"&DROITE())-SI(O7=1;1/24;0,75/24);1)

qui m'a bien plu

Bonjour

Bonjour à tous

Une variante.

Bye !

Là, j'admets que parfois cela vaut le coup d'être plusieurs !

Bonsoir à tous,

Désolé de ne pas avoir répondu plus tôt mais je ne recevais pas de notification comme quoi on avait répondu à ma problématique...

Bonjour à tout le monde

Sebou1011 a écrit :

@gmb, je ne comprends pas ta variante, il n'ya aucune formule dans le fichier que tu as joint.

C'est normal, il n'y en a pas.

Il te suffit de saisir une heure de départ du navire A ou B en colonne D ou E pour que sur la même ligne, en colonne H et J s'inscrivent automatiquement les heures de début et de fin de la période de travail.

C'est une macro événementielle qui fait le travail. Elle se déclenche automatiquement dès qu'on valide une heure saisie les colonnes D ou E au format "hh:mm".

Tu peux la voir en tapant simultanément sur les touches Alt et F11 du clavier.

OK ?

Bye !

@gmb incroyable! si tu arrives à faire la même "magie" sur les horaires d'embauche et débauche des catamarans que j'ai posté tt à l'heure, je prends!

Merci encore !

Bonjour NJHUB,

Pourrais tu partager le fichier avec tes ajouts de colonnes stp? je l'ai fait sur la version de Steelson mais ça ne fonctionne pas. D'autre part, pour répondre à steelson, lorsque l'heure de débauche du voyage précédent dépasse ou chevauche l'heure d'embauche du voyage suivant, l'équipage continu de travaillé jusqu'à la débauche du dernier voyage. Enfin, lorsqu'un voyage chevauche 2 journées pour un départ du port A un jour de la semaine autre que le lundi à 23:00 par exemple, les heures travaillées doivent se découper ainsi

Jour 1: Heure d'embauche = (23h00-00H45)= 22h15 et période de travail donc de 22h15 à 24h00

Jour 2 : Période de travail de 00H00 à 01h15 (pour resêcter les fameux 2h15 après heure de départ du port A

Il faudrait pourvoir intégrer ces calculs de manière automatique également. Merci pour votre aide

@gmb, finalement la macro de mon côté ne s'exécute pas comme il faut...merci néanmoins merci encore pour l'exercice!

Bonne journée à tous

Bonsoir à tous,

Je reviens un an après pour terminer l'automatisation de ce fichier. J'ai utilisé la solution que steelson avait proposée l'année dernière et cela fonctionne bien pour s'assurer qu'on n'a pas fait d'erreur dans les périodes de travail mais il faut quand même toujours se "farcir" le remplissage des colonnes jour après jour. Pour aller au bout de l'exercice, je souhaiterai avoir la bonne formule pour que mes colonnes H et J se remplissent automatiquement en choisissant le bon critère des colonnes O à R, càd :

-Prendre forcément l'heure d'embauche de la 1ere heure de départ de la journée (colonne O ou Q)

-Si Heure d'arrivée - heure de départ<3h30 => l'heure de débauche sera celle du port d'arrivée

-Si Heure d'arrivée - heure de départ>3h30 => l'heure de débauche sera celle du port départ

-Le Samedi le navire rentre sur l'ile d'Yeu et ne navigue pas le WE ensuite (sauf exception en saison ou on le repositionne sur Fromentine le dimanche soir...)

Pour être plus clair je vous joins le fichier avec les cellules des colonnes H et J correctement remplies jusqu'au 30/06.

Merci pour votre aide

Sébastien

Bonjour,

ok, je me souviens maintenant de ce sujet ... déjà un petit casse-tête

je vais regarder à tête reposée ...

si je comprends bien, il faut que je me focalise sur les seules colonnes H et J

Bonjour,

les formules deviennent extrêmement complexes, notamment

• parce qu'il y a des lignes vides, à prendre en compte par moi
• parfois 3 traversées (24 octobre), à prendre en compte par moi
• parfois des données curieuses comme les 24 et 25 novembre ... comment doit-on interpréter les horaires entre parenthèses ?
• 2 départs sur la même ligne comme le 23 mai ... que faut-il faire dans ce cas ?

ma seule solution je pense est de partir d'une base de données plus simple qui facilitera grandement le travail à la fois de mise en place des données et la mise en place des formules...

Hello,

Pour simplifier, il ne faut pas tenir compte des navires entre parenthèse, ce sont des navires qu'on se réserve le droit de déclencher en cas de besoin et pour ces horaires isolés c'est facile de calculer rapidement les horaires de travail ensuite donc inutile d'automatiser.

Pour les départs sur a même ligne, c'est juste un pb de mise en page de toute façon le navire partira de l'horaire la plus tôt de Yeu ce jour là et retour de Fro à 13h;)

Merci

Tu risques d'être très surpris par la forme ... je suis parti d'une solution plutôt base de donnée chronologique, ce qui me simplifie les formules et les calculs.

Mais j'ai de nouveau un problème de compréhension ...

Sebou1011 a écrit :

-Si Heure d'arrivée - heure de départ<3h30 => l'heure de débauche sera celle du port d'arrivée

-Si Heure d'arrivée - heure de départ>3h30 => l'heure de débauche sera celle du port départ

je suppose que heure d'arrivée veut dire débauche ici et heure de départ = embauche ? car je ne connais jamais l'heure d'arrivée du bateau !

1- Sauf erreur de ma part, entre débauche théorique et embauche théorique, j'ai trouvé

• 57 cas de recouvrement, et
• 97 cas où le temps était inférieur à 45mn.

Ensuite on passe carrément à 4h45. Donc le critère peut aller de 45mn à 4h45mn entre la débauche après traversée et l'embauche avant nouvelle traversée... à moins que les 30mn veuillent dire autre chose ? Parce que je ne sais pas bien ce qu'est l' "heure d'arrivée" que je ne connais pas du tout !

2- J'ai fini par comprendre le calcul et je pense que la question du 1) n'influe pas

3- je n'ai pas vu et pas prévu d'enchaînement sur 3 traversées, je suppose que ce n'est pas aujourd'hui possible avec les vitesses de croisière car j'ai le sentiment que les horaires sont fonction des marées et des coefficients. Est-ce bien cela ?

4- mais du coup, je ne comprends pas ces valeurs de ton fichier :

5- Voici le fichier tel qu'il se présente.

Commentaire sur la méthode et les formules ... un tableau (au sens excel) permet d'écrire la formule sur la première ligne, celle-ci se propage alors sur tout le tableau (mais il ne faut pas de lignes vides). Les formules doivent faire appel

• soit à l'en-tête,
• soit à une donnée de la même ligne,
• soit à la totalité de la colonne.

Pour faire intervenir une valeur de la ligne précédente ou suivante comme c'est le cas ici afin de déterminer l'écart entre débauche et embauche théorique (soit recouvrement entre valeurs théoriques, soit inférieures à un certain plafond = 3h30 si j'ai bien compris) on utilise alors la fonction DECALER de 1 ligne par rapport à la donnée se situant sur la même ligne.

Et puis j'ai mis les données de temps avant départ et après départ dans un tableau (4 plages nommées) fonction du jour de la semaine (1 = lundi).

J'ai mis quelques MFC pour faciliter la lecture.

tu pourras en parler à ton nouveau président que je connais

@Sebou, merci de faire un retour sur la proposition.