Comptage des heures : sup jour / sup nuit / nuit

[Mathieu18]

Bonjour,


Il y a quelques mois j'avais posté sur cette problématique dont Cisco (le magnifique) m'avait merveilleusement sortie.

Je reviens vers vous car mon tableau de planning ne fonctionne plus sur une ligne / 2 colonnes d'heure (exemple 6:00 13:30)

mais sur 2 lignes / 2 colonnes
(exemple 6:00 10:00
14:00 18:00)

Du coup je n'arrive pas à réajuster ces 3 formules (heure de nuit - heure sup de nuit - heure sup de jour) bien trop complexe pour mon niveau.

Je me tourne donc à nouveau vers vous en essayant de vous fournir en pièce jointe le meilleure exemple possible de mon cas.

Par ailleurs je pense que cette formule dans ces 2 déclinaisons peut être très très utile pour beaucoup.


Je vous remercie.

Mathieu.

 

[CISCO]

Bonjour

C'est effectivement peut-être juste une histoire de position du R ou du H dans la formule, ou un * à remplacer par un +. J'ai fait les formules des colonnes K et L à partir des formules des colonnes I et J. A revoir donc. Ce soir peut-être.

@ plus

 

[CISCO]

Bonjour

Je ne suis certain d'avoir tout compris...

Cas 2 : Tu écris que dans I13, il devrait y avoir 3:00. Pour moi, entre 6:00 et 14:30, il y a déjà 8:30 qui auraient dû être normales. Elles ont été comptées en heures sup à cause du H, mais bon, les 8:30 max normales possibles sont faites. Les heures suivantes ne peuvent être que des heures sup (sauf s'il y a un R pour les compter en h normales sur la ligne correspondante).
Autrement dit, je pensais que les heures normales devaient être comptées à partir de G13 (ou de G14 si la ligne 13 est vide), même s'il y a un H dans F13.

Cas 4 : Pour moi, comme précédemment, puisque les 8:30 normales ont déjà été effectuées (que cela soit à cause d'un "" ou d'un "R"), la suite ne pouvait être constituée que d'heures sup.

Conclusion : Je pense que je n'ai pas bien compris la règle, et donc, les formules ne conviennent pas. Pour moi, la lettre mise sur la première ligne n'avait pas d'influence sur la deuxième ligne.

D'après ton cas 2, cela veut il dire que les heures normales doivent commencer avec l'heure dans G14, même si 8:30 ont déjà été effectuées ce jour là ?

Autrement dit, doit-on recommencer systématiquement le calcul des heures normales avec les données de la seconde ligne s'il y a un "R" ou un "H" dans la première ligne ?

Si c'est le cas, il va encore falloir pas mal modifier les formules, car la méthode utilisée faisait beaucoup de choses par rapport à la valeur dans la colonne G dans la première ligne.

@ plus

 

[CISCO]

Bonsoir

Peut-être comme ça...

Tu as tout intérêt à faire pas mal de tests pour voir si tout fonctionne correctement. Si certains posent problème, explique moi comment tu trouves les résultats.

@ plus

 

[Mathieu18]

Bonjour,

A priori tout fonctionne parfaitement. Je vais mettre à l'essaie par mes collègues durant 1 semaine afin de pouvoir être certain.



Je vais essayé de mieux expliquer la logique de fonctionnement, bien qu'au vu du résultat, je pense que tu as tout saisi entre temps.

Les 2 lignes par journée correspondent à 2 plages horaires.
Je peux faire la matinée sur site A en heure sup ("H") et l'apres midi sur site B en heure normale (") avec ou sans dépassement (> ou < à 8H30 journalier).
Je peux faire une journée avec coupure en heure normale ("" "").
Je peux faire une journée continue en heure normale ("").
Je peux venir 2 heures de plus que prévu mais plutôt que cela me soit payé en heure supplémentaire je préfère les avoir en heure récupérable ("R").
Je peux avoir dépassé mon quota d'heure annuel et toute les journée que je ferai seront en heure sup ("H" ou "H" "H" si journée continue ou avec coupure = 1 ligne ou sur 2 lignes).

Donc 3 scénarios ou :
- je peux tout faire en heure sup (remplacement sur ma journée de repos par exemple);
- en heure récupérable (car au final ca m'arrange de récupérer les heures un autre jour);
- ou alors je fais ma journée normale de 8H30 et tout ce qui dépasse est compté en heure sup.

Ce qui démultiplie ces 3 scénarios c'est les 2 lignes avec 3 combinaisons : ligne 1 , ligne 2 ou ligne 1 et 2.
Je peux avoir ma matinée normale sur un Site A de 6:00 à 14:30 et puis l'après midi allez dépanner de 18:00 à 23:00 sur un autre site (soit en "H" soit en "R").
Avoir simplement ma journée continue en ligne 1 : 8:00 - 16:30
Ou bien posé un demi-congés en ligne 1 (Ligne sera : - -) et avoir des heures de travail en ligne 2.


Bref tout cette complexité, pas forcément en harmonie avec la logique Excel permet dans un fonctionnement quotidien d'avoir énormément de liberté dans le fonctionnement quotidien des plannings.
Parcontre faut pouvoir suivre et tenir les comptes. La est tout le défi du tableau.
Avoir un fonctionnement qui permet à la fois l'annualisation du temps de travail, gérer les heures récupérables et les heures supplémentaires, compter les heures sup nuit, sup jour, nuit, jour en fonction du modèle que nous avons choisi pour la journée de travail ("R"; "H"; "") en journée continue ou avec coupure (ligne 1, ligne 2 ou ligne 1 et 2).


Donc dans la logique.

Si les 2 lignes ont un symbole identiques ("H" "H" ; "R" "R"; "" "") : on calcule l'ensemble des 4 cases comme un tout.
Si les 2 lignes ont un symbole différent ("H" "R"; "R" "" etc...) : on calcule ligne 1 et ligne 2 indépendamment l'une de l'autre puis on ajoute les 2 résultats.
Et pour ne rien simplifier, on veille à faire fonctionner sur ligne 1 , ligne 2 ou ligne 1 et 2 en fonction de ce qui est remplie pour la journée.

Chose que tu as su très bien faire.


Cisco encore une fois merci beaucoup. A priori nous sommes bons, je reviens ici dans quelques jours pour confirmer tout cela.
Je ne te remercierai jamais assez. Vraiment.

Merci encore et encore.

 

[CISCO]

Bonjour Mathieu

Ne reste plus qu'à tester davantage...

Merci pour ces explications. Il y avait juste un truc que je n'avais pas compris, que les cas 2 et 4 ont heureusement mis en évidence (d'où l'intérêt de faire de nombreux tests).

Pour passer de l'avant dernière version (post #29) à la dernière (post #33), il m'a suffit de glisser un SI((F11="R")+(F11="H");G12+A$8;... en gérant correctement les parenthèses (ce qui n'est pas évident) pour tenir compte de ce qui est écrit dans F11 (la 1ère ligne) alors que le SOMMEPROD en cours sert à calculer ce qui correspond à la seconde période de travail (la 2nde ligne).

@ plus

 

[CISCO]

Bonjour à tous

En pièce jointe, une autre proposition, avec des formules plus courtes.

A savoir que dans le fichier du post #3, les formules pouvaient être décomposées comme suit :
* calculs lorsque seule la première ligne est renseignée, avec un SOMMEPROD
* calculs lorsque seule la seconde ligne est renseignée, avec un autre SOMMEPROD
* et calculs lorsque les deux lignes sont renseignées, avec un dernier SOMMEPROD

Dans mes dernières propositions, les formules sont décomposables comme suit :
* calculs lorsque seule la première ligne est renseignée, avec un SOMMEPROD
* calculs lorsque seule la seconde ligne est renseignée, avec un SOMMEPROD
* et calculs lorsque les deux lignes sont renseignées, avec un SOMMEPROD1 pour la première ligne, et un autre, SOMMEPROD2 pour la seconde ligne.

Dans ces conditions, on peut supprimer les deux premiers SOMMEPROD et faire avec
SI(G11="-";0;SOMMEPROD1....)+ SI(G12="-";0;SOMMEPROD2...)
Il faut encore faire une ou deux modifications, et hop...
A tester bien sûr.

@ plus

 

[Mathieu18]

A priori ça à l'air tout bon, je reviens dans quelques jours / semaine confirmer après mise à l'essai. Merci beaucoup.

 

[Mathieu18]

Bonsoir,

Je up cette discussion car un nouveau souci m'est apparu. La cause est l'usage de la fonction "INDIRECT" dans la formule.
En effet, selon si le jour de la semaine démarre sur une ligne paire ou impaire la référence ne se fait pas correctement.

Afin de rendre la formule plus accessible, quitte à la rendre moins performante, serait il possible de supprimer la fonction "INDIRECT" et faire en sorte que les formule se réfère simplement aux cellules.

Dans l'exemple joint vous allez vite comprendre de quoi il s'agit.

Je vous remercie.

 

[Mathieu18]

Cisco,

je te remets en ligne la version comment tu l'as construite avec les formules nommées... ça t'aidera à y revoir plus clair.

 

[CISCO]

Bonsoir

Attention, il me semble qu'il y a des erreurs dans le tableau de gauche de ton fichier Comptage des heures - INDIRECT, à savoir que les formules dans les lignes 13 et 14 font appellent, entre autre, aux valeurs des cellules G11, G12, H11 et H12, alors qu'elles devraient utiliser les cellules G13, G14, H13 et H14. Il y a le même type de problème pour les formules des lignes 15 et 16 et ainsi de suite.

C'est corrigé dans la pièce jointe.

Pour ce qui est du problème que tu as en commençant le tableau sur une ligne impaire, il suffit de mettre dans le tableau de droite, au début de toutes les formules (MOD(LIGNE();2)=1) au lieu de (MOD(LIGNE();2)=0). Je ne vois pas d'autre endroit où interviendrait la parité du numéro de la ligne utilisée.

C'est aussi corrigé dans la pièce jointe.

@ plus

 

[Mathieu18]

Ok !!!!!

Le souci vient donc de la fonction "MOD" ! J'étais archi-convaincu que c'était à cause de "INDIRECT" du coup je me suis entêté sur la mauvaise voie !
Maintenant oui ça parait évident.

Alors tout simplement pour répondre à mon besoin il suffit de remplacer le "MOD" par un "SI(ET()".
Etant donné que les formules sont tantôt sur des lignes paires tantôt impaires de manière très aléatoire, ça aurait été un travail de fourmi d'avoir tantôt le "MOD - 1)" tantôt le "MOD -2)".

Avec un bon vieux "SI(ET()" des familles, il y a juste à copier dans la cellule, ca va se référer la ou il faut.

Je mets en pièce jointe.


Cisco, merci beaucoup. Encore et encore.

 

[CISCO]

Bonjour

Effectivement, si tu fusionnes les cellules deux par deux, pour n'y mettre qu'une formule, tu peux simplifier le début de la formule, la partie SI((MOD(LIGNE();2)=0)+((G11="-")*(G12="-"));""; pour ne garder que SI((G11="-")*(G12="-");""; le * remplaçant ici un ET.

@ plus