Calcul des heures de nuit dans un planning

Question

Je vous sollicite car j'ai besoin de mettre en place une formule qui m'aiderait dans la gestion du personnel. Je vous explique. J'ai besoin d'un fichier où je rentre simplement les horaires de début et fin du salarié, puis que ce fichier me donne : 1) Le nombre d'heures effectuées dans la journée, sachant que si la durée de travail est supérieure ou égale à 8h, le salarié prend 30 minutes de pause. 2) Le nombre d'heures de nuit, sachant que les heures de nuit vont de 21h à 6h du matin (Boulangerie). Je suis parvenu à mettre le début en place avec les format heures ...etc. mais pour faire ressortir les heures de nuit, je n'y parviens pas. J'ai VRAIMENT besoin de votre aide, je suis certain que parmi vous de nombreux(ses) sauront comment me délivrer de ce casse-tête. Je vous joins le fichier TEST avec les horaires sur une semaine d'un salarié. Vous remerciant énormément par avance. Yohan...

MFerrand · Answer

Déjà, la formule la plus simple pour faire une différence horaire : =MOD(D5-C5;1) C'est tout de même plus court ! C'est vrai qu'il faut la compléter par la réduction de 0h30 si la différence atteint ou dépasse 8h30 : =MOD(D5-C5;1)-(MOD(D5-C5;1)&gt;=8,5/24)*0,5/24 Cela reste plus court quand même. Je vois le calcul des heures de nuit en fonction de tes contraintes... A+...

MFerrand · Answer

Re,

La formule pour renvoyer les heures de nuit (sans simplification autre que le "pré-calcul" de la plage, car tu as des situations diversifiées) :

=SI(ET(D5+(D5<=21/24)>=30/24;D5+(D5<=21/24)<C5+(C5<=21/24));9/24;MOD(MIN(D5+(D5<=21/24);30/24)-MIN(C5+(C5<=21/24);30/24);9/24))

Mais un petit problème me turlupine : cette formule ne déduit pas la pause, elle va donc donner 9h00 dans le premier calcul, dans ce cas, il y aura lieu de la déduire. Le seul autre cas de déduction de pause est entièrement en jour... Une séance de travail à peu près moitié moitié jour-nuit, la pause serait -elle à déduire de la nuit ou du jour ?

Je cherche un critère pour compléter cette formule, sans trop l'allonger...

MFerrand · Answer

Re,

J'ai pris un compromis pour la déduction de la pause en nuit, en prenant comme critère qu'elle était éventuellement à déduire si le milieu de vacation se situait en nuit, ce donne l'expression suivante pour compléter la formule précédente :

-ET(MOD(D5-C5;1)>=8/24;C5+MOD(D5-C5;1)/2>21/24;C5+MOD(D5-C5;1)/2-(C5+MOD(D5-C5;1)/2>=1)<=6/24)*0,5/24

J'en ai profité pour rectifier le 8,5 de ma première formule en 8 pour déterminer si pause à déduire.

J'ai aussi modifié tes totaux en utilisant SOMME moins sensible que l'opérateur + (qui renvoie une erreur dès qu'il ne rencontre plus un nombre) et revu le calcul du nombre de jours (faussé au fur et à mesure qu'on remplissait la ligne.

Cordialement.

380yoyo94-planning.xlsm (16.59 Ko)

MFerrand · Answer

Je n'ai pas inventé le calcul avec MOD, mais l'essayer, c'est l'adopter, car nettement plus simple et performant... D'ailleurs la formule de calcul des heures de nuit l'utilise aussi, avec comme diviseur la durée de la plage nuit (au lieu de 1= journée de 24h) mais après avoir sorti le cas où la durée couvre toute la plage et qui donnerait 0 avec MOD, le confondant avec le cas où la durée est bien 0. Cette formule est d'ailleurs issue d'un vieux travail à plusieurs sur un forum, dont j'ai conservé les expressions telles que hd+(hd&lt;=dp) &#91;pour ajouter 1 si hd (heure début) est inférieure à dp (heure de début plage nuit) et l'utilisation de MIN qui permet de raccourcir aussi un peu. J'ai prolongé par la suite pour obtenir une formule générale pour calculer la durée comprise dans une plage quelconque (en rééchelonant toutes les heures à partir de l'heure de début de plage testée, ce qui délimite 6 cas de positionnement des horaires de début et fin par rapport à la plage testée). Il faudra que je la remette en chantier pour voir s'il est possible de la raccourcir un peu, mais... Bonne journée. Cordialement...

vincyvinz · Answer

Je suis intéressé par votre formule mais avec la condition de pause à partir de 6h de travail (et non 8h comme dans votre exemple). Je n'arrive pas à modifier correctement votre 2ème formule de calcul pour les heures de nuit ( pour décompter la pause dans les heures de nuit et non la journée). Pouvez-vous m'aider svp ? Merci par avance. Vincent...

Steelson · Answer

Hé oui, MFerrand n'a pas mis 8h et donc n'a pas eu besoin d'uen pause de 30mn Mais en effet, je suis d'accord avec cette nouvelle demande, la législation fixe la pause à partir de 6h (20mn mini) Je vais laisser l'expert adapter sa formule magique ! Mets quand même un bout de fichier avec différents cas de figure des heures faites. Ainsi que le résultat attendu (" à la main ")...

MFerrand · Answer

Hé oui, MFerrand n'a pas mis 8h et donc n'a pas eu besoin d'uen pause de 30mn Mais en effet, je suis d'accord avec cette nouvelle demande, la législation fixe la pause à partir de 6h (20mn mini) Je vais laisser l'expert adapter sa formule magique ! Mets quand même un bout de fichier avec différents cas de figure des heures faites. Ainsi que le résultat attendu (" à la main ") Salut !... mais ça ne fonctionne pas en remplaçant 8 par 6 ? Je dois dire aussi (je m'étonne de ne pas l'avoir soulevé ! ) que la déduction des pauses me choque un peu, moi qui ai toujours travaillé avec pauses (légales ou règlementaires) incluses dans le temps de travail... (la seule exception était la pause méridienne susceptible d'être incluse, dans le cadre de journées "continues", que si inférieure à 45 minutes)... &#91;NB- je me réfère au droit du travail (à l'époque) car "mon" régime de travail était plus favorable que le régime général&#93;. ...le droit du travail n'en finit pas de se dégrader... ...