Heure supplémentaire de nuit

[Mathieu18]

Bonjour,

Je sais que ce sujet a été abordé maintes et maintes fois mais ma situation est relativement spécifique au vue de son intéraction avec d'autres cellules, j 'arrive au bout de mon tableau mais il y a un véritable écart entre ce que je projette dans ma tete et mon niveau de compétence pour le réaliser. De ce fait je me permets de vous solliciter car je suis complètement noyé.
Mon cas ne peut pas trop s'expliquer via un post, je vous joins donc un exemple que j'ai essayé d'expliciter au maximum.

Je vous remercie.

Edit : intégration du dernier exemple sur le 1 er message.

 

[CISCO]

Bonsoir

Si tu mets la définition de périodeenminutes directement dans les formules, il faut toutes les valider en matriciel, pour que la partie LIGNE(INDIRECT(...&":"&...)) liste bien toutes les minutes de B2 à C2 lorsque tu as sélectionné une cellule de la ligne 2.

A mon avis c'est bien plus pratique d'utiliser un nom, puisque la partie correspondante se retrouve très souvent dans les formules.
Si tu procèdes sans le nom périodeenminutes, à chaque changement de ref, il faut "rentrer" toutes les modifications dans H2, I2, J2 et K2, et plusieurs fois dans chacune de ces formules. Si tu fais avec, il suffit de faire les changements dans le gestionnaire de noms (uniquement le B et le C à changer si tu es sur la ligne 2).

Ne peux tu mettre sur le forum une pièce jointe Excel en positionnant les colonnes comme sur ton fichier réel, avec juste quelques lignes ?

@ plus

P.S : J'ai un peu simplifié la partie correspondant à périodeenminutes sous la forme LIGNE(INDIRECT($B2*60*24+1&":"&(($C2<$B2)+$C2)*60*24))

 

[Mathieu18]

Ca y est je viens de réussir à l intégrer a mon tableau, absolument fabuleux ! Je te remercierai jamais assez Cisco ! Tu as pris le temps et tu m'as pas laché malgré toutes mes exigences, franchement super de ta part, merci encore.

Pour te répondre, si j'ai bien compris ce que tu dis. Alors oui je comprends bien que ta manière est bien plus performante mais pour ma part voila 3 semaines que je me suis mis à faire des tableaux sans aucune formation ou cours, j'ai la chance d etre tres familier avec le monde informatique donc ca m'aide un petit peu à apprehender tout ceci.
Le tableau que je fabrique m'a contraint à m'orienter vers des mécaniques et formules qui ne sont pas du tout de mon niveau, j'ai reussi à passer des formules sans meme trop savoir ni comment ni pourquoi.. Donc une fois que je l'aurai fini et qu'on pourra l'exploiter, la je prendrai afin le temps de me former aux bases d'excel et pouvoir comprendre et définir tout ce que j'ai réalisé.
Tout ceci pour t'expliquer que pour le moment je dois me contenter de formule et mécanique que j'arrive à exploiter meme si elles s'averent contre productive plutot que de mécaniques super performante que je serai incapable d'exploiter dans des modifications futures.
Quand je serai plus au niveau j y reviendrai certainement pour optimiser ma méthode comme tu me l'as si bien démontré.

Voila donc ma formule dans mon tableau sheets : =ArrayFormula(SI(C6="H";"";SOMMEPROD(((LIGNE(INDIRECT($D6*60*24+1&":"&(($E6<$D6)+$E6)*60*24))<=DébHeuJou*24*60)+(FinHeuJou*24*60<LIGNE(INDIRECT($D6*60*24+1&":"&(($E6<$D6)+$E6)*60*24))))*(LIGNE(INDIRECT($D6*60*24+1&":"&(($E6<$D6)+$E6)*60*24))<=(DébHeuJou+1)*24*60)*(LIGNE(INDIRECT($D6*60*24+1&":"&(($E6<$D6)+$E6)*60*24))<=(D6*24*60+NomHeuNor*24*60)))/60/24))

Avec des copier coller et des glissements je pourai le dupliquer dans mon tableau tres facilement, je l'ai construit pour. Pour ce qui est de modifier les plages horraires DébHeuJou, NomHeuNor,FinHeuJou me permettront d'ajuster le curseur à ma guise dans tout le tableau.

Je garde précieusement toutes tes formules qui assurément me serviront toute ma carrière et puis j'espère d ici quelques temps pouvoir comprendre au moins 10 pourcent de ton poste qui explique comment tu as construit la formule !


Cisco, la metropole aix-marseille, le service piscine, le pôle technique et l'ensemble d'abrutis qui me servent de collegue de travail nous te remercions tous vivement !

 

[CISCO]

Bonsoir

Si ça fonctionne... c'est OK.

Au plaisir.

@ plus

 

[CISCO]

Bonsoir

Bonjour,

...vous solliciter car je suis complètement noyé..

Mais que fait le maitre nageur.. ?

@ plus

 

[GILBERTO BRAGA]

Bonsoir à tous,

Bonsoir Cisco

Je te serai toujours reconnaissant de l'aide que tu m'as donné lors du sujet " des heures nocturnes".

Je vous pose une question : est-ce possible adapter les formules dans le cas qu'un travailleur travaillant, par exemple, entre 18 et 22 h, et entre 23 et 5 h ( l'intervalle ce n'est pas pris en compte dans la journée).

Merci d'avance. Bien cordialement.

Gilberto

 

[CISCO]

Bonjour à tous

@ Mathieu18 : Tu peux simplifier la partie (D6*24*60+NomHeuNor*24*60) en ((D6+NomHeuNor)*24*60). J'ai fait la simplification correspondante dans mes deux derniers fichiers sur ce fil.

@ Gilberto : J'essaye de transformer les formules pour prendre en compte une pause, donc avec une période de travail en deux parties.

@ plus

 

[CISCO]

Bonjour

Une possibilité en pièce jointe. J'espère que c'est bon. A toi de vérifier, Gilberto.

J'ai supprimé le test par rapport à H dans la colonne F.

La formule pour calculer les heures normales de jour dans J2 est de la forme
SOMMEPROD(
((B$11*24*60<périodeenminutes)*(périodeenminutes<=B$12*24*60)+((B$11+1)*24*60<périodeenminutes)) ---> ne garde que les minutes de jour entre le début du travail et la fin du travail
*((périodeenminutes<=((C2<B2)+C2)*24*60)+(((D2<B2)+D2)*24*60<périodeenminutes)) --> ne garde que les minutes avant et après la pause
*(périodeenminutes<=((((B2+B$13)>((C2<B2)+C2))*(((D2<B2)+D2)-((C2<B2)+C2))+(B2+B$13))*24*60)) -->ne garde que les minutes normales
)/60/24 --> ramène le total des minutes en durée Excel (1 = 1 jour, donc par 720/60/24 = 0,5 = 0,5 jour = 12:00)


Les formules dans les colonnes K, L et M sont construites avec la même méthode, en "jouant" sur les inégalités, les + et les *.

@ plus

P.S : La dernière partie sert à voir si début + nombre d'heures normales (B2+B13) tombe ou non après le début de la pause (qui commence à l'heure C2). Ce qu'on pourrait faire avec un test du style B2+B13 > C2 si ce n'est que cela ne fonctionnera pas si B2 est avant minuit, et C2 un peu après, par exemple avec 22:00 et 3:00. Dans ce dernier cas, il faut faire le test suivant B2+B13 > 1 + C2, écrit sous la forme ((B2+B13)>((C2<B2)+C2)).
Si ce test donne VRAI, il faut additionner la durée de la pause à B2+B13, avec D2 - C2 + B2+B13. Là aussi, si B2 est avant minuit, et D2 et/ou C2 sont un peu après minuit, il y aura un problème, et il faudra faire avec (((D2<B2)+D2)-((C2<B2)+C2))+(B2+B13).

Si ce test donne FAUX, il faudra faire avec B2 + B13 tout simplement.

Et l'on peut obtenir cela avec
(((B2+B13)>((C2<B2)+C2))*(((D2<B2)+D2)-((C2<B2)+C2))+(B2+B$13))

C'est long à cause des heures possibles après minuit.

P.S : Modifications du fichier le 28/05/2018 pour mieux prendre en compte les cas avec cellules vides

 

[CISCO]

Bonjour à tous

J'ai changé le fichier et les explications de mon dernier post car il y avait une erreur : Pour faire la différence entre les minutes normales et les minutes sup, il faut faire le test final par rapport au début de la pause, sous la forme B2+B$13 > (C2 < B2) + C2, et non pas par rapport à la fin de la pause, B2 + B$13 > (D2 < B2)+ D2.
Si ce test donne VRAI, il ne faut pas faire le tri par rapport à B2 + B$13 mais par rapport à B2 + B$13 + ((D2 < B2) +D2) - ((C2 <B2) + C2)) car il faut tenir compte du temps de pause.

J'ai aussi fait cette correction sur les autres colonnes.

@ plus

 

[GILBERTO BRAGA]

Salut à tous

Bonjour Cisco

Quand j’étais en train de faire quelques simulations sur les formules du dernier archive, j’ai noté un erreur.

Par exemple : dans la journée de travail de 22:00 à 5:00 h, le résultat que j’avais obtenu c’était le suivant :

- heure normale jour: 00:00

- heure Sup jour: 06:00

- heure normale nuit: 07:00

- heure sup nuit : 00:00

Étant donné que le résultat correct est :

- heure normale jour: 01:00

- heure Sup jour: 05:00

- heure normale nuit: 07:00

- heure supp nuit : 00:00

Les changements apportés sur le dernier post ont corrigé le problème. Maintenant, tout est parfait.

Merci de votre aide précieuse.

Gilberto

 

[GILBERTO BRAGA]

Corriger: dans la journée de travail de 22:00 à 5:00h / 6h à 12h

Gilberto

 

[CISCO]

Bonsoir à tous

Effectivement, avec la première version, il y avait un problème si début + nombre d'heures normales B2+B$13 donnait un moment pendant la pause.
Par ex B2 = début 1 = 12:00, C2 = fin 1 = 18:00; D2 = début 2 = 20:00 et B$13 = 7:30 normales par jour. B2+B$13 donne donc 19:30.
* Dans la première version, on faisait le test par rapport à (D2 < B2) + D2 (c-à-d par rapport à la fin de la pause). Ce test B2+B$13 > (D2 < B2) + D2 donne 19:30 > 20:00 ce qui est FAUX et les heures travaillées à partir de 20:00 sont toutes comptées en heures supplémentaires, ce qui n'est pas bon.
* Dans la seconde version, on fait le test par rapport à (C2 < B2) + C2 (c-à-d par rapport au début de la pause). B2+B$13 > (C2 < B2) + C2 donne 19:30 > 18:00 ce qui est VRAI. B2+B$13+(D2-C2) donne 12:00 + 7:30 + (20:00 - 18:00) = 21:30. Les heures de 20:00 à 21:30 sont comptées en heures normales. Seules les heures après 21:30 seront comptées en heures supplémentaires, ce qui est bon.

Gilberto, si tu trouves d'autres erreurs, signale les moi. Je suis peut être passé à coté d'autres problèmes.

Au plaisir.

@ plus

 

[Mathieu18]

Bonjour,

Je me permets de me relancer ce poste avec mon nouveau cas particulier, à cheval entre le cas de Gilberto et le mien.
J'avoue que ces formules me dépassent complètement, j'espère que le savoir de Cisco + les remarques de Gilberto + les differentes pieces jointes de fil permettront de trouver le mélange de tout qui va bien.

Merci pour votre aide.

 

[CISCO]

Bonjour

Cf. une possibilité là.

@ plus