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

Bonjour à tous, bonjour Cisco

T15

H. début - 15:00
intervall - 19:00 - 19:30
H. fin - 22:30

journée normale - 4:00 (samedi)

15:00 - 19:00 = 4 heures (heures jour)

heures jour <= heures normales - la formule de colonne "S" est correcte.

15:00 + 4h = 19h00 + intervall = 19h30

Cordialement,
Gilberto
 

CISCO

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

Rebonjour

Oui, mais la personne a aussi travaillé de 19:30 à 22:30, ce qui fait 30 minutes de nuit. Faut-il les prendre en compte, ou pas ?

@ plus
 

GILBERTO BRAGA

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

Samedi - journée normale = 4h

1º intervall - 15:00 - 19:00 = 4 heures jour (J15)
2º intervall - 19:30 - 22:30 = 3 heures nuit (K15)

heures total (Q15) = J15 (4 heures jour) + K15 (3 heures nuit) + N15/52,5*60 = 7:04
heures normales (I15) = 04:00

heures supp total (R15) = heures total - heures normales = 07:04 - 4:00 = 3:04

Considérant que la journée normales de samedi est de 4 heures, il faut calculer dans que moment de la journée l'employé a complété les 4 heures de travail (T15) pour qu'à partir de ce point on puisse calculer les heures supplémentaires diurnes (colonne "W") et les heures supplémentaires nocturnes (colonne X).

heures supp diurnes - colonne "W" - 19:30 - 22:00 = 2:30 heures
heures supp nuit - colonne "X" - 22:00 - 22:30 = 00:34 heures > 00:30 (22:00 - 22:30) + N15/52,5*60

heures supp total (R15) = heures supp diurnes (W15) + heures supp nuit (X15) = 2:30 + 00:34 = 3:04

Cordialement
Gilberto
 

CISCO

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

Bonsoir

Merci pour toutes ces explications. Je comprend bien ce que tu écris, mais dans ce cas, il me semble que la réponse en T15 est 18:56 et non 19:25. J'explique :
Travail jusqu'à 19:00.
Pause de 19:00 à 19:30.
Travail de 19:30 à 22:30.
3:04 d'heures sup, donc 0:04 de 18:56 à 19:00 et 3:00 de 19:30 à 22:30.

@ plus
 
Dernière édition:

GILBERTO BRAGA

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

Bonjour le forum, bonjour Cisco,

T55 ou T15?

Considérant T15

15h00 a 19h00 = 4 heures diurnes - heures de 60 minutes
19h30 a 22h00 = 2,5 heures diurnes - heures de 60 minutes

22h00 a 22h00 = 30 minutes - heures de 52,5 minutes = 00:30 + 00:30/52,5*60 = 34 minutes (fiction juridique)

La réduction du temps de nuit se produit uniquement après 22 heures (google traducter)

Cordialement,
Gilberto
 

CISCO

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

Bonsoir à tous, bonsoir Gilberto

Je pense avoir compris les exemples que tu as mis dans ton dernier fichier. Malheureusement, pour le moment, je ne trouve pas la relation prenant en compte tous les cas possibles. J'essaye... J'espère que tu n'es pas très pressé.

@ plus
 
Dernière édition:

CISCO

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

Bonjour

Vraiment pas évident cette histoire...

Pour mieux comprendre ce qu'il fallait faire, j'ai essayé de détailler les formules en colonnes L et M. En cherchant, j'ai trouvé plus court que les anciennes versions. Pourrais-tu essayer en L11 avec
=SI(E11="";0;SI(E11<=F11;SI(E11<FIMNOT;MAX(ININOT;F11)-ININOT+MIN(FIMNOT;F11)-E11;SI(ININOT<F11;F11-MAX(ININOT;E11);0));SI(E11<FIMNOT;FIMNOT-E11+F11+(24-ININOT);MOD(MIN(FIMNOT;F11)-MAX(ININOT;E11);1))))

et en M11 avec
=SI(G11="";0;SI(G11<=H11;SI(G11<FIMNOT;MAX(ININOT;H11)-ININOT+MIN(FIMNOT;H11)-G11;SI(ININOT<H11;H11-MAX(ININOT;G11);0));SI(G11<FIMNOT;FIMNOT-G11+H11+(24-ININOT);MOD(MIN(FIMNOT;H11)-MAX(ININOT;G11);1))))
et me dire si cela donne les bons résultats dans tous les cas.

@ plus

P.S : J'ai remarqué que les anciennes formules, ainsi que les nouvelles, ne donnent le bon résultat que si ININOT<=24:00.
 
Dernière édition:

CISCO

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

Bonsoir

Je ne suis plus certain d'avoir tout compris :

Est-ce qu'on doit déduire :
* toutes les minutes gagnées grâce aux heures faites de nuit (3 h dans l'exemple en pièce jointe), à la valeur de la colonne S,
* ou uniquement les minutes gagnées grâce aux heures faites de nuit avant la valeur de la colonne S ( 1 h 30 dans l'exemple) ?

Par exemple, pourrais tu me dire ce que donne le cas donné en pièce jointe ?

Merci.

@ plus
 

Pièces jointes

  • Modéle heures supp jour e nuit-7.xlsx
    32.8 KB · Affichages: 102
Dernière édition:

GILBERTO BRAGA

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

Bonjour à tous, bonjour Cisco

Ci-joint, les résultats attendus en T.

Cordialement,

Gilberto
 

Pièces jointes

  • Cópia de Modéle heures supp jour e nuit-7.xlsx
    33.1 KB · Affichages: 97

CISCO

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

Bonsoir à tous

Cette fois-ci, j'ai bien compris la méthode. Pour le moment, je n'ai pas trouvé la formule miraculeuse prenant en compte toutes les situations possibles. J'ai essayé différentes méthodes, mais il y a toujours des cas ne convenant pas. Je cherche, je cherche...

@ plus
 

CISCO

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

Bonjour

Une possibilité en pièce jointe, ne donnant pas malheureusement toujours exactement les mêmes résultats que toi :
SI(INDEX(AD$11:AD$3011;EQUIV(INDEX(AE$11:AE$3011;EQUIV(E11;AD$11:AD$3011;1))+I11;AE$11:AE$3011;1))<F11+(F11<E11);INDEX(AD$11:AD$3011;EQUIV(INDEX(AE$11:AE$3011;EQUIV(E11;AD$11:AD$3011;1))+I11;AE$11:AE$3011;1));
INDEX(AD$11:AD$3011;EQUIV(INDEX(AE$11:AE$3011;EQUIV(SI(G11="";F11;G11);AD$11:AD$3011;1))+I11-(INDEX(AE$11:AE$3011;EQUIV(F11+(F11<E11);AD$11:AD$3011;1))-INDEX(AE$11:AE$3011;EQUIV(E11;AD$11:AD$3011;1)));AE$11:AE$3011;1)))

J'ai essayé diverses possibilités, avec plein de tests (SI(E11<FIMNOT;....), mais ce n'est pas évident de prévoir tous les cas possibles, surtout à cause des "journées" de travail à cheval sur deux jours réels, par exemple 21:00 01:00 02:30 07:00.

La solution en pièce jointe utilise deux colonnes intermédiaires, AD et AE, listant les minutes réelles et celles cumulées pondérées ou non en fonction des horaires de nuit.

La formule utilise beaucoup de EQUIV(...;.....;1) car, à cause de problème d'arrondi, cela me renvoyait trop souvent des messages d'erreur #NA si je mettais des EQUIV(...;.....;0). Malheureusement, cela entraine quelques erreurs d'arrondi (ou de ligne), d'où mes résultats différents des tiens de quelques minutes. J'ai essayé en arrondissant toutes les valeurs avec la fonction ARRONDI(....;9), mais cela ne donne pas de meilleurs résultats.

Je vais encore chercher, soit à améliorer cette formule, soit à utiliser une autre méthode, mais pas tout de suite.

@ plus
 

Pièces jointes

  • Modéle heures supp jour e nuit-11 sans arrondi.xlsx
    344.1 KB · Affichages: 87
Dernière édition:

CISCO

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

Bonsoir

Pour résoudre le problème lié à l'utilisation de certains EQUIV(....;1), j'ai essayé de travailler en minutes (écrites comme des nombres entiers dans la colonne AC), et non en heure:minute (C'est à dire en fraction de journée, puisque pour Excel 1 journée =1, 1 h = 1/24...). Ainsi, lorsque l'on fait une recherche avec EQUIV dans la colonne AC, on peut le faire avec des EQUIV(x;....;0) et non plus avec des EQUIV(x;....;1) car on est sûr d'y trouver la valeur x recherchée.
Dans la colonne AD, on a les minutes cumulées pondérées ou non en fonction de la nuit, écrites au format nombre elles aussi.

Toutefois, cela force à multiplier toutes les valeurs E11, F11, G11, H11, FIMNOT et INNOT par 60*24 dans la formule, pour pouvoir comparer, additionner, soustraire des nombres représentant tous des minutes. Par exemple 22/24 =22:00 devient 1320 minutes.

Au passage, j'ai trouvé plusieurs simplifications (plus besoin de faire de recherche dans la colonne AC), et la formule devient finalement :
(SI(EQUIV(INDEX(AD$11:AD$3011;E11*60*24+1)+I11*60*24;AD$11:AD$3011;1)-1<(F11+(F11<E11))*60*24;EQUIV(INDEX(AD$11:AD$3011;E11*60*24+1)+I11*60*24;AD$11:AD$3011;1)-1;
EQUIV(INDEX(AD$11:AD$3011;SI(G11="";F11*60*24;G11*60*24)+1)+I11*60*24-(INDEX(AD$11:AD$3011;(F11+(F11<E11))*24*60+1)-INDEX(AD$11:AD$3011;E11*60*24+1));AD$11:AD$3011;1)-1))/60/24

Même si cela ne se voit pas au premier coup d'œil, elle est bien plus courte que la formule initiale :). Ce sont les *60*24 qui font qu'elle parait longue.

Et elle donne presque toujours les mêmes résultats que toi :) :). Les cas évidents, où on a 3:30 de nuit (0:30 en plus grâce à la pondération de nuit) ou 7:00 de nuit (1:00 en plus grâce à la pondération de nuit), comme par exemple la ligne 21 où on doit obtenir 5:00, où l'ancienne formule donnait 4:59, donnent maintenant le bon résultat. Les résultats différents (lignes 38 et 39) viennent du fait que ma méthode arrondit à la minute inférieure (à cause de certains EQUIV(....;1)), et que tu as arrondis à la minute supérieure. Ainsi, ligne 38, H. début H. supp = 02:22 correspond à une journée de travail de 07:59:26 et H. début H. supp = 02:23 à 08:00:34 de travail. Idem ligne 39, pour 03:22 et 03:23 respectivement.

Si elle te convient, on pourra encore la simplifier en nommant tout le début, car, elle est de la forme
(SI(Début Hsupp avant Hfin1<(F11+(F11<E11))*60*24;Début Hsupp avant Hfin1;
EQUIV(INDEX(AD$11:AD$3011;SI(G11="";F11*60*24;G11*60*24)+1)+I11*60*24-(INDEX(AD$11:AD$3011;(F11+(F11<E11))*24*60+1)-INDEX(AD$11:AD$3011;E11*60*24+1));AD$11:AD$3011;1)-1))/60/24

A toi de tester.

Je regarde si on peut faire sans les colonnes intermédiaires AC et AD, mais bon, c'est déjà pas mal ainsi.

@ plus

P.S : J'espère que tu arrives à comprendre mes posts, après traduction en portugais-brésilien. Si cela n'est pas évident pour toi, poses des questions.
 

Pièces jointes

  • Modéle heures supp jour e nuit-13.xlsx
    315.9 KB · Affichages: 111
Dernière édition:

GILBERTO BRAGA

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

Bonjour à tous, bonjour Cisco

Je vous remercie pour votre attention de résoudre le problème. Je n'ai pas de mots pour exprimer ma gratitude face votre dévouement relatif à la situation que je vous ai présentée. Heureusement qui existe des gens comme vous dans le monde. J'ai examiné le fichier, j'ai fait d'autres testes, et les résultats tiennent compte de ce dont j'ai besoin (ils sont conformes).

Cordialement,
Gilberto
 

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 900
Membres
101 834
dernier inscrit
Jeremy06510