Calculer les heures supplémentaires par jour et par semaine, jour et nuit

GILBERTO BRAGA

XLDnaute Occasionnel
Bonne nuit! J'ai besoin d'aide pour la feuille de calcul ci-joint.
Cette feuille de calcul que j'ai trouvé ici dans le forum, mais j'ai besoin de s'adapter à mon usage.
J'ai qu'il calculera les heures supplémentaires par jour, en séparant les heures supplémentaires nocturnes et diurnes.
Ensuite, si la semaine de travail dépasse 44 heures, j'ai qu'il sélectionnera l'excès hebdomadaire, mais en décomptant l'excès quotidien qui déjà a été calculé. Aussi j'ai qu'il séparera les heures supplémentaires hebdomadaires diurnes des nocturnes.


J'ai besoin d'aide en particulier pour les formules dans les colonnes P. Q V et W.
Je vous remercie.



Gilberto

tradutor: Google
 

Pièces jointes

  • Horas extras diárias e semanais.zip
    24.1 KB · Affichages: 385
  • Horas extras diárias e semanais.zip
    24.1 KB · Affichages: 404
  • Horas extras diárias e semanais.zip
    24.1 KB · Affichages: 400

GILBERTO BRAGA

XLDnaute Occasionnel
Re : Calculer les heures supplémentaires par jour et par semaine, jour et nuit

Bonsoir à tous, Cisco

Après un certain temps sans utiliser le fichier, j'ai essayé de faire quelques tests et j'ai remarqué que, dans certains cas, la formule de la colonne Z - HE Nuit ne conduit pas au retour d'un résultat attendu.

Par exemple, sur la ligne 11, si l'horaire initial des heures supplémentaires (colonne T) est 4h15, donc le résultat sur la colonne Z devrait être supérieur à zéro. (5h00 - 4h15 > 0).

Il semble que le problème ne sera pas dans le calcul des différences d'heures, mais dans la partie de la formule qui établit les conditions pour le calcul.
Les exemples aux erreurs sur la colonne Z sont à la couleur jaune.

J'ai essayé de faire aussi certaines altérations dans la formule, mais je n'ai pas obtenu d'un résultat satisfaisant. Je remercie d'avance si quelqu'un trouver une solution du problème.

Bien à vous

Gilberto Braga
 

Pièces jointes

  • modele-heures-supp-jour-e-nuit-17-teste.xlsx
    120.1 KB · Affichages: 128

CISCO

XLDnaute Barbatruc
Re : Calculer les heures supplémentaires par jour et par semaine, jour et nuit

Bonjour Gilberto

Est-ce que tu travailles toujours sur ce fichier ?

J'essaye de trouver quelques simplifications sur celui-ci. Pourrais tu me réexpliquer à quoi correspondent :
* Colonne S, H. ajust
* Colonne T, H. début H. supp
au besoin en m'expliquant les résultats affichés sur les une ou deux premières lignes ?

D'avance merci

@ plus
 

GILBERTO BRAGA

XLDnaute Occasionnel
Bonjour Cisco

Pour en revenir à notre sujet.

Au Brésil, la législation du travail prévoit que l’heure nocturne soit réduite en 52 minutes et trente secondes (52,5) au lieu de 60 minutes.

C’est une fiction légale, juridique.

Le onglet “heures nuit” de la feuille de calcul ci-joint nous donne plus de détails sur cette réduction de l’heure nocturne.

Par exemple :

0:52:30 h nocturne = une heure (60 minutes)
1:45:00 h nocturne = 2 heures
(...)
7:00:00 = 8 heures

60 minutes / 52,5 = 1,14286

7 heures x 1,14286 = 8 heures

L’horaire nocturne brésilien est compris de 22 h à 5 h.

Ainsi, un travailleur qui commence la journée à 22 h et termine à 5 h, il aura travaillé 7 heures nocturnes normales, mais, dans le cadre légal, il aura travaillé 8 heures.

Cette réduction-là n’existe que dans l’horaire compris entre 22 et 5 heures.

Par exemple : ligne 11

Si un travailleur débute la journée à 20 h et termine à 5 h, il aura travaillé un total de 10 heures (colonne Q), c’est-à-dire 2 heures diurnes (20-22 h) et 8 heures nocturnes (22-5 h).

Étant donné que, au Brésil, la journée de travail normale maximum admise par la loi est de 8 heures par jour, le travailleur aura fait 8 heures normales plus 2 heures supplémentaires (colonne R), donc 2 heures normales diurnes, 6 heures normales nocturnes et 2 heures supplémentaires nocturnes.

J’espère y parvenir le même résultat du “post” plus récent “heure supplémentaire de nuit”, c’est-à-dire il faut que je sache le montant d’heures diurnes normales, heures nocturnes normales, heures supplémentaires diurnes et nocturnes produites par le travailleur au long d’un jour de travail.

Dans ce cas, j’ai besoin d’appliquer la réduction de l’heure nocturne dans l’horaire compris entre 22 et 5 h.

Par exemple : ligne 12

Heure de début : 15h
Heure de clôture : 23h

Dans la période de 15 à 22 h, sans pause, on a 7 heures diurnes normales.

Comme l’heure nocturne c’est de 52 minutes et trente secondes, ce travailleur a completé sa journée de travail à 22:52h. En effet, il a travaillé jusqu’à 23 h donc il a 8 minutes d’heures supplémentaires à recevoir (colonne R).

La colonne S H. Ajust, montre l’horaire dans lequel le travailleur a fini la journée normale de 8 heures.

La colonne T – H. Début H. Supp, montre l’horaire au cours duquel le travailleur a achevé sa journée normale de travail en prenant en compte la réduction de l’heure nocturne. La formule de cette colonne calcule le moment où, après le début de la journée de travail, le travailleur aura travaillé le nombre d’heures normales designées sur la colonne O – jorn. Legal.

upload_2017-1-18_0-40-1.png



La différence entre la colonne S et la colonne T c’est que dans la colonne S toutes heures sont comptées comme des heures de 60 minutes alors que dans la colonne T les heures nocturnes sont comptées comme des heures de 52 minutes et 30 secondes.

Pour cette raison, à la ligne 12, dans la colonne S on a 23 heures (8 heures de 60 minutes) et dans la colonne T on a 22,52 h (7 heures de 60 minutes plus 1 heure de 52,5 minutes).

Si les journées sont tout à fait diurnes, les colonnes S e T devront présenter le même horaire, tel qu’à la ligne 13.

Quand j’ai écrit ce post, mon idée pour calculer des heures supp jour et des heures supp nuit c’était la nécessité d’y avoir une colonne que me dise l’horaire où le travailleur finit sa journée normale de travail et il est prêt à débuter la journée supplémentaire. Alors, en partant de l’horaire de la colonne T, moi, je pourrai établir la quantité d’heures travaillées entre l’horaire en T et l’horaire final en H, en séparant les heures supplémentaires diurnes et les heures supplémentaires nocturnes (des colonnes Y et Z).

Les formules du post “heure supplémentaire de nuit” permettent qu’on ait une feuille de calcul plus propres, des colonnes pas nécessaires. Toutefois, je ne sais pas si c’est possible établir un rapprochement en rapport à la réduction de l’heure nocturne brésilienne.


Bien cordialement


Gilberto
 

Pièces jointes

  • Cópia de modele-heures-supp-jour-e-nuit-17-teste-3.xlsx
    121.1 KB · Affichages: 88

CISCO

XLDnaute Barbatruc
Bonsoir

Je ne t'ai pas oublié, Gilberto, je cherche, je cherche... J'ai appliqué la méthode SOMMEPROD de l'autre post pour trouver les heures de jour, normales ou sup, et les heures de nuit, normales ou sup, mais sans utiliser le coefficient correcteur pour les heures de nuit. Ce n'est pas simple d'envisager tous les cas possibles.

@ plus
 

CISCO

XLDnaute Barbatruc
Bonjour à tous

Gilberto, je me répète : Je ne t'ai pas oublié. Mais, il faut bien l'admettre, j'ai des difficultés.
Avec cette méthode utilisant SOMMEPROD, il me faut aussi déterminer à quel moment finissent les heures de travail normales et commencent les heures sup. Et je ne trouve pas de façon simple de le faire, couvrant toutes les situations possibles. Je ne peux pas faire avec quelque chose du genre Hdéb1 + JornLegal, ce qui donnerait par exemple 15:00 + 8:00, car certaines parties de ces 8:00 h doivent être pondérées, multipliées par 8/7. Cela me pose problème surtout lorsque la fin de ces 8:00 h peuvent être à cheval sur une partie jour et une partie nuit, ou réciproquement.
Je cherche, je cherche...

@ plus
 

CISCO

XLDnaute Barbatruc
Bonsoir

Cf. une possibilité en pièce jointe. Les colonnes N, O et P peuvent être supprimées. Les colonnes N et O utilisent deux méthodes différentes pour calculer le temps total de travail, en tenant compte du coefficient multiplicateur de 8/7 la nuit. La colonne P donne l'heure correspondant à la fin des heures normales. La formule dans cette dernière colonne est compliquée... Grrr. Il y a plein de nouveaux noms dans le gestionnaire de noms. Je t'expliquerai tout cela demain . Si tu peux, vérifies les résultats pour voir si tout est bon.

@ plus

31/01/2017 : Je viens de supprimer le fichier car j'y ai trouvé au moins une erreur. J'essaye de corriger cela dans la journée.
 
Dernière édition:

CISCO

XLDnaute Barbatruc
Bonjour

Cf. le nouveau fichier en pièce jointe. J'espère que c'est bon. Les colonnes N, O, P et Q peuvent être supprimées. Elles ne sont là que pour permettre certaines vérifications. Je chercherai plus tard une formule plus simple pour la colonne P.

Détail non négligeable : Les formules ne donnent les bons résultats (du moins je l'espère) que si la pause est prise intégralement pendant les heures normales. Ce qui suppose que la personne prend sa pause avant d'avoir travaillé l'équivalent de 8:00 par exemple, ce qui me parait assez cohérent. Dans le fichier, j'ai mis exprès deux exemples en rouge ne convenant pas. Si la personne ne prend pas de pause, il suffit de mettre la même heure dans les colonnes F et G, dans les heures normales ou sup, peu importe.

@ plus

P.S : Le bon fichier est placé à la fin du post #119
 
Dernière édition:

GILBERTO BRAGA

XLDnaute Occasionnel
Bonjour à tous,

Bonjour Cisco,


J’ai jeté un coup d’oeil dans le fichier et j’ai trouvé certains résultats dont ils ne sont pas corrects.

J’ai signalé les résultats lesquels j’entends qui sont corrects dans les colonnes R, S, T et U.

Aux fins de vérification (colonnes Y et Z), la somme des “jour h normales” (colonne J) plus les “nuit h normales” (colonne L) doit être égale à la “jorn legal” (colonne I).

Encore une fois, je réaffirme ma gratitude pour votre engagement de m’aider avec ces formules complexes.

Cordialement,

Gilberto - Foz do Iguaçu/BR
 

Pièces jointes

  • Cópia de modele-heures-supp-jour-et-nuit-SOMMEPRODbis1.xlsx
    41.9 KB · Affichages: 40
Dernière édition:

CISCO

XLDnaute Barbatruc
Bonsoir à tous, bonsoir Gilberto

Je n'ai pas mis le bon fichier en pièce jointe dans mon dernier post (#114) :
* Il ni a pas dedans les exemples en rouge ne convenant pas, montrant comment il ne faut pas placer la pause,
* il ni a rien dans la colonne Q (Alors que j'y avais mis une formule permettant de vérifier la validité de celle de la colonne P : Si HfinJornLegal est correcte, on doit avoir dans la colonne Q une valeur très très proche de 8:00 ou de 4:00)
* la définition de HfinJornLegal dans le gestionnaire de noms n'est pas bonne (ce n'est pas la même formule que dans P11) et conséquence, les résultats ne sont pas bons.
Je l'enlève donc et je met la bonne pièce jointe ci-dessous.

@ plus

P.S : Pièce jointe mise dans le post #119
 
Dernière édition:

CISCO

XLDnaute Barbatruc
Bonsoir

* J'ai l'impression qu'on obtient les bons résultats en changeant les définitions dans le gestionnaire de noms de Hdéb2m et Hfin2m comme suit

Hdéb2m = SI(Hdéb2="";Hfin1m;(Hdéb2<Hdéb1)+Hdéb2)
et
Hfin2m = SI(Hfin2="";Hdéb2m;(Hfin2<Hdéb1)+Hfin2)

* J'ai trouvé une autre méthode pour déterminer HfinJornLegal fonctionnant quelle que soit la position de la pause, pendant les heures normales ou les heures sup. Je vérifie davantage les résultats avant de poster le fichier correspondant.

@ plus
 

Pièces jointes

  • modele-heures-supp-jour-et-nuit gilberto.xlsx
    43 KB · Affichages: 39

CISCO

XLDnaute Barbatruc
Bonsoir

Une autre méthode en pièce jointe.

Le principe :
Dans les colonnes U et V, la formule liste les minutes de 00:00 à 36:00 (donc l'équivalent de 1,5 jour) .
Dans la colonne W, la formule liste les minutes pondérées avec le coef 8/7 si elles sont de nuit.
Dans la colonne X, la formule donne le cumul de ces valeurs depuis le début.
Dans la colonne Y, la formule cumule les minutes pondérées, mais uniquement depuis Hdéb1, le début de la période de travail, en fonction des valeurs données dans V6:Y6 (V6 = début de la période de travail, W6 = début de la pause, X6 = fin de la pause, Y6 = fin de la période de travail).

Les formules dans les colonnes X et Y sont matricielles (donc à valider avec Ctrl+maj+entrer). Pour ceux qui voudraient la modifier, il suffit de le faire dans X11 ou dans Y11, et cela se fait automatiquement dans toute la colonne.

Les formules que je propose dans ces colonnes X et Y sont compliquées. On peut faire beaucoup plus simple. Mais là, je les ai écrites surtout pour trouver ce que j'allais écrire dans le gestionnaire de noms pour définir HfinJornLegal. Comme je voulais que tous les résultats de ces colonnes X et Y puissent être calculés dans le gestionnaire de noms, sans être affichés sur la feuille, pour les données contenues dans E11:I11, mais aussi E12:I12 et ainsi de suite en dessous, il me fallait trouver des formules donnant les bonnes valeurs sans faire appel aux résultats des colonnes à gauche ou des lignes au dessus.

Ces colonnes X et Y permettent donc de vérifier les résultats uniquement par rapport au contenu de la plage V6:Y6. On peut donc y trouver la limite entre les heures normales et leur heures sup (4:00 --> 240 ou 8:00 --> 480) dans la colonne Y, ce qui permet de connaitre HfinJornLegal. Ce n'est donc qu'un exemple, un cas particulier.

Pour pouvoir appliquer cela dans beaucoup plus de cas, sans avoir à afficher toutes ces valeurs, il faut donner des noms à certaines parties de ces formules (Cf. dans le gestionnaire de noms).

On obtient les formules dans les colonnes J, K, L, M... Q.

Par exemple, dans J11, on a
Code:
SOMMEPROD(((FIMNOT*24*60<période)*(période<=ININOT*24*60)+((FIMNOT+1)*24*60<période))
*((période<=(Hfin1m*24*60))+(Hdéb2m*24*60<période))*(période <=HfinJornLegal*24*60))/24/60

(FIMNOT*24*60<période)*(période<=ININOT*24*60) permet de prendre en compte toutes les minutes de la période de travail dans le premier jour (de 5:00 à 22:00),
((FIMNOT+1)*24*60<période) prend en compte les minutes de la période de travail du second jour (après 29:00)
((période<=(Hfin1m*24*60))+(Hdéb2m*24*60<période)) ne prend en compte que les minutes travaillées avant ou après la pause
(période <=HfinJornLegal*24*60) ne prend en compte que les minutes travaillées pendant les heures normales.
En combinant tout cela avec des + et des *, et en divisant par 24 et par 60, on obtient les heures travaillées normales de jour.

Pour avoir les heures travaillées sup de jour dans K11, il suffit donc de changer la dernière partie en (HfinJornLegal*24*60 < période).

Si on a un doute sur un résultat, sur la ligne x, on peut très bien copier les valeurs Ex:Ix et les coller sur la plage V6:Z6, puis contrôler les résultats dans les colonnes X:Y en dessous.

Comme précédemment, j'ai fait le maximum pour que ces formules soient indépendantes les unes des autres (la plupart des calculs sont "faits" dans le gestionnaire de noms). Résultat, on peut très bien supprimer toutes les colonnes à partir de la colonne M, en allant vers la droite. Elles ne sont là que pour permettre certaines vérifications.

@ plus

P.S : Pour écrire les formules dans la colonne X, j'ai sélectionné toute la plage X11:X2171, écris la formule dans X11, puis validé en matriciel avec Ctrl+maj+entrer. Idem dans la colonne Y.
 

Pièces jointes

  • modele-heures-supp-jour-et-nuit-SOMMEPRODsept.xlsx
    201.6 KB · Affichages: 71
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
311 712
Messages
2 081 802
Membres
101 819
dernier inscrit
lukumubarth