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

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
 

Fichiers joints

Dernière édition:

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
 

Fichiers joints

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.
 

Fichiers joints

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
 

CISCO

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

Bonjour à tous

Merci Gilberto pour ton message... Il faut bien admettre que ce ne fut pas évident.

Je vais continuer à chercher sur ton besoin, histoire de voir si il ni a pas plus simple, histoire aussi d'être sûr et certain de la validité des résultats. Reviens de temps en temps sur le forum dans les jours, les semaines à venir pour voir si il ni a pas de nouvelles propositions ou modifications.

Au plaisir...

@ plus
 

CISCO

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

Bonsoir à tous

Gilberto, attention, je viens de voir que la formule en colonne T donne parfois le bon résultat, à 24 h près. Par exemple, ligne 25, la formule donne 05:00, alors que le résultat devrait être 29:00 (puisqu'en E25 on a Horaire début1 = 22:00). Avec le format personnalisé hh:mm;;-, cela ne se voit pas et cela ne pose pas de problème (Avec le format personnalisé [hh]:mm;;-, cela se voit). Mais si tu réutilises le résultat de T25 dans un autre calcul, il est possible que cela pose problème. Méfiance donc... J'ai essayé de modifier la formule pour ne pas avoir ce petit désagrément, sans résultat adéquat pour le moment.

@ plus
 

GILBERTO BRAGA

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

Bonjour à tous, Cisco

Je n'ai pas compris la question que vous avez soulevée en ce qui concerne T25. J'ai essayé les formats personnalisés (hh:mm;;-, [hh]:mm;;-, hh:mm , [hh]:mm, et je vois toujours 05:00, qui est le résultat correct. S'il y a quelque problème, donc ce m'échappe à la compréhension.

Maintenant, je suis en train d'avancer pour calculer les heures supplémentaires nocturnes et diurnes à partir du résultat trouvé en colonne T.

S'il vous plaît, pourriez-vous jeter un coup d'oeil aux formules et visualiser s'il y a quelque erreur? Pour les heures supplémentaires nocturnes (colonne Y) j'ai essayé les formules qui se trouvent aux colonnes AF et AG, et pour les heures supplémentaires diurnes (colonne X) la formule de la colonne AF.

Bien que les résultats sont attendus, je ne suis pas sûr s'ils seront toujours corrects.

Je vous remercie d'avance la possibilité d'examiner ces deux calculs.

Merci
Gilberto - Foz do Iguaçu/Brasil
 

Fichiers joints

CISCO

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

Bonsoir à tous

Bonjour à tous, Cisco

Je n'ai pas compris la question que vous avez soulevée en ce qui concerne T25. J'ai essayé les formats personnalisés (hh:mm;;-, [hh]:mm;;-, hh:mm , [hh]:mm, et je vois toujours 05:00, qui est le résultat correct. S'il y a quelque problème, donc ce m'échappe à la compréhension.

Merci
Gilberto - Foz do Iguaçu/Brasil
Effectivement, avec les deux formats hh:mm;;- ou [hh]:mm;;-, on obtient 05:00 en T25 avec ma dernière proposition. Pour autant, il me semble que la bonne réponse devrait être 29:00, ce que le second format afficherait car il accepte les heures supérieures à 24:00. Ce n'est pas capital, mais bon, j'ai peur que cela soit l'origine de certaines erreurs si tu réutilises les résultats de la colonne T dans d'autres formules.

Pour ce qui est des nouvelles colonnes AF, AG et AH, je regardais dès que j'aurai plus de temps. Pour le moment, je ne peux pas.

@ plus
 

CISCO

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

Bonsoir à tous

Gilberto, je ne t'oublie pas, mais bon, je suis vraiment débordé en ce moment... J'espère que tu peux encore patienter un peu...

@ plus
 
Dernière édition:

GILBERTO BRAGA

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

Bonjour à tous,

Merci, Cisco

Cordialement
Gilberto
 

CISCO

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

Bonjour Gilberto

J'essaye de vérifier tes propositions, mais ce n'est pas évident.

@ plus

Cisco
 

CISCO

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

Bonsoir

Apparemment, la formule en colonne AH ne donne pas toujours le bon résultat. Essayes par exemple en ligne 11 avec début1 = 00:00, fin1 = 08:00. Cela donne H début H supp = 07:17, et donc HE jour 08:00 - 07:17 = 00:43. Et, malheureusement, la formule en AH11 donne 06:43.

Je cherche à comprendre tous les cas possibles...

@ plus
 

CISCO

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

Bonsoir

Une première possibilité pour HE nuit.

J'ai essayé de comprendre tes propositions et les formules que j'avais utilisée en colonnes L et M (Hor. nuit 1 et Hor. nuit 2). N'y arrivant pas facilement, j'ai essayé d'autres possibilités pour finalement arriver aux formules écrites en rouge en colonnes J, K, L et M (avec des formules beaucoup plus courtes dans le cas de ces deux dernières colonnes).
J'ai aussi légèrement modifié la formule de la colonne T.
La colonne U n'est là que pour vérifier si la valeur en colonne T correspond bien au nombre d'heures légales données en colonne I. La colonne V fait de même, mais chaque ligne comprend une formule adaptée à la situation, faite pour cette ligne et pas forcément adaptée aux autres lignes. Cette colonne V n'est donc valable que pour ces exemples. Les colonnes U et V peuvent donc être supprimées.

En colonne Z, la formule donnant HE nuit reprend la méthode utilisée en colonne L ou M pour Hor. nuit 1 ou Hor nuit 2, mais avec comme borne inférieure T au lieu de E ou de G (puisqu'on veut connaitre le nombre d'heures de nuit après T, et non pas après E ou G). Cela semble fonctionner correctement.

Pour le moment, je ne trouve pas de formule correcte pour la colonne Y, pour trouver HE jour.

J'ai aussi simplifié la formule de la colonne AD.

A toi de vérifier.

Je cherche davantage pour voir si il ni a pas une ou des erreurs, et pour trouver une solution pour HE jour, en colonne Y.

@ plus
 

Fichiers joints

Dernière édition:

GILBERTO BRAGA

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

Bonjour Forum, bonjour Cisco.

Encore une fois je n'ai pas de mots pour remercier ton engagement à trouver les formules pour résoudre mon problème.

J'ai vérifié rapidement le fichier et les résultats sont considérés comme satisfaisants.

Les formules en colonnes L et M, je les ai trouvé juste là, à faire des recherches sur le Forum (Monica). Réellement, tes formules sont bien plus courtes.

À l'égard de la formule en colonne Y, pour trouver l'heure supplémentaire diurne, c'est bien simple, il suffit de diminuer R11 (heure supplémentaire totale) - Z11 (heure supplémentaire nocturne) et nous aurons la valeur de l'heure supplémentaire diurne (en colonne Y).

Dans tout cas, sans vouloir abuser de ta patience, j'aimerais de calculer l'heure supplémentaire diurne (en colonne Y) et après l'heure supplémentaire nocturne calculée par la différence R11-Y11.

Merci
Gilberto - Foz do Iguaçu/Brasil
 

Discussions similaires


Haut Bas