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]

Bonsoir

Pas si simple que cela. Regarde la pièce jointe et dis moi si cela convient. Certaines formules sont à valider en matriciel (Il m'a donc fallu dé-fusionner les cellules correspondantes). A tester davantage bien sur.

@ plus

P.S : J'ai modifié le premier tableau, histoire de me remettre dans le bain. J'ai aussi modifié mon dernier fichier dans l'autre post, y ayant trouvé quelques cas mal pris en charge par les précédentes formules.

 

[Mathieu18]

EDIT : J'ai trouvé de mon coté les petits points qui allaient pas.

Il y d'autres point que je dois re-adapter afin de voir l'intégration dans mon tableau final. Je reviens vers toi dès que possible.

1 - Dans mon tableau je ne laisse aucune case vide, je mets des "-" pour faciliter la lecture.
Donc dans les formules je mets N() autour des plages pour que le "-" soit compté comme 0.
En espérant que ca pertubera pas tes formules.

2 - Dans la formule tu n'as pas intégré la colonne F ou on mets la lettre "H" pour que tout soit compté en heures sup.
Donc pour les heures de nuit je rajoute un SI; F11="H"; compte 00:00.
Pour les heures sup de jour et de nuit à la limite je peux mettre un SI; F11 ="H" reprend intégralement la formule et qui compte comme durée de journée de travail 00:00.

A moins que tu es plus simple à proposer.

J'adapte et intègre tout cela à mon tableau de planning et puis ensuite je vois plein de scénarios voir si ca roule, je reviens vers toi dès que possible.

Merci beaucoup pour le temps que tu passe Cisco. Vraiment.

 

[CISCO]

Bonsoir

Effectivement, j'ai oublié de traiter le H dans la colonne F . Comme ce n'est pas très simple, je me suis dit "Dans un premier temps, je fais sans, puis ensuite, je modifierais la formule en conséquence". Puis, j'ai oublié de faire les transformations nécessaires...

Je regarde ça tout de suite.

@ plus

 

[CISCO]

Bonsoir

Quelques modifications plus tard, histoire de prendre en compte les H.

@ plus

 

[CISCO]

Rebonsoir

Et après prise en compte des "-", uniquement dans le second tableau.

@ plus

P.S 1 : Les formules ne prennent pas en charge les cellules vides lorsqu'elles sont dans la première ligne.

P.S2 : Modification du fichier le 29/05/2018 pour corriger un bug concernant la prise en compte des cellules vides dans les 2ndes lignes uniquement.

 

[CISCO]

Bonjour

Et une dernière version permettant de laisser les cellules vides, soit de la1ère, soit de la seconde ligne (Si je n'ai pas fait d'erreur de copier-coller !).

@ plus

 

[Mathieu18]

Cisco t'es une légende !

A priori ca tourne nickel de chez nickel ! J'ai testé une multitude de combinaison et j'ai pu l'intégrer sur mon tableau G-sheets.

La mon employeur vient de me faire une nouvelle demande qui va m'obliger à revoir la structure de mon tableau et intégrer de nouveaux élements dans la formule, mais pour le coup je pense pouvoir me débrouiller seul. On verras.

Merci beaucoup beaucoup Cisco de me sauver la mise à une nouvelle fois. Et par ailleurs, t'es franchement impressionnant ! Bravo.

 

[Mathieu18]

On m'a donc posé la nouvelle contrainte ou il va falloir décliner des imbriquage de SI à tout va.
La ou avec les tirets nous étions sur 3 scénarios, la nous voilà désormais sur 9 scénarios.
A savoir que j'ai supprimé les tirets parce que bon...
Je m'applique à bien intégrer tout cela dans ma formule de calcul des heures sup simples.

Ensuite j'essayerai de reproduire la même chose avec les formules de Cisco.

Si je coince, je me permettrais de te solliciter à nouveau si tu as du encore du temps, si j'y arrive je posterai le résultat final car je pense que cette combinaison peut faire le bonheur de beaucoup de RH !

Je reviens vers vous dans quelques jours.

 

[CISCO]

Bonjour

Bon courage Mathieu18...

C'est un peu ce qui m'embête, "avec" l'informatique... A savoir que j'ai l'impression que parfois, on fait compliqué, au niveau des normes, des contrats, des... parce qu’on sait qu'on pourra le faire avec un peu, ou beaucoup d'informatique derrière. Dans ces cas, l'informatique ne sert pas à résoudre des phénomènes compliqués par nature (des problèmes d'écoulement des fluides, par ex), mais des systèmes qu'on n'a pas cherché à faire simple (La RT2012 par ex)...

Sérieusement, dans ton cas, s'il y a 9 scénarios, combien de temps un employé devrait-il passer mensuellement pour voir si les congés qu'on lui donne correspondent exactement au contrat, en faisant les calculs à la main ? Conclusion : Les fera t'il ?

@ plus

 

[Mathieu18]

Je te rejoins Cisco.
Ce que j'aime avec Excel c'est qu'il te permets d'établir un diagnostic du fonctionnement. Excel est tellement logique carré implacable qu'il te fait t'apercevoir à quel point le fonctionnement établie est nebuleux, illogique, contradictoire.
Je partage totalement ton opinion. On gagnerai à y simplifier.

Au niveau des employés, je suis dans la fonction public, on est bourré d'erreurs, de doublons, triplons, de rattrapage.. à l'échelle d'un service c'est plusieurs centaines d'heures gaspillés par mois.

Je partage mon dernier exemple ou je gère les 9 scénarios en heure sup simples dans le Feuil1!. A savoir que dans le Feuil2! il y a les tableau précedent.

Au final pas si compliqué que cela car avec les tirets tu as déjà fait les formules qui gère que la ligne du haut ou du bas ou les 2.
La c'est à peu près pareil, il y a peut 1 ou 2 combinaisons ou ça sera un peu plus compliqué que cela, je verrai quand je le ferai.

La pour expliqué la logique : avant 2 lignes de plages horaires, on peut mettre soit "H" soit "R" soit "".
Si il y a H devant la plage horaire tout doit être compté en heure sup,
Sil y a R , tout doit être compté en heure normale,
S'il y a "", tout ce qui est dans la durée de journée de travail (8h30) doit être compté en heure normales et ce qui dépasse en Heure sup.

En intégrant toutes les combinaisons possibles cela 9 situation possibles, comme illustré dans le tableau ci-joint.

Je l'ai fais assez facilement pour le comptage des heures sup. Reste à le dupliquer pour les déclinaisons : H nor jour / H nor nuit / H sup jour / H sup nuit.
En espérant que ce j'arrive à caler ce que tu as construis dans cet imbriquement de Si. Je me donne quelques jours.

Merci Cisco.

 

[CISCO]

Rebonjour

Effectivement, cela se complique, surtout avec le fait que le H (ou le R) ne joue pas sur les deux lignes à la fois. Je m'y plongerai, si besoin est, mais pour le moment, je te laisse faire. Encore une fois, bon courage.

@ plus

 

[Mathieu18]

Cisco bonjour,


J'ai bien avancé sur la réadaptation des formules et commence enfin un peu saisir la mécanique de ton travail.
Néanmoins je rencontre une petite impasse, pas bien méchante, mais j'arrive pas à la résoudre.

Pour le comptage des heures sup jour et sup nuit dans la 3 eme matrice du SOMMEPROD tu as mis ça :

*SI(F11="H";1;(G12+A$8)*24*60<périodeenmin2ndeligne))

Dans la réadaptation le "H" est traité en amont, du coup j'aurai besoin que le SOMMEPROD gère juste comme critère :
Matrice 1 - Matrice 2 - Matrice 3 = Supérieur à tel départ d'heure - inférieur a tel départ d'heure - qui dépasse tant de durée d'heures.

Je bloque sur ce 3 eme point. Grosso modo faire comme si le "H" n'existait pas.

Je t'ai mis en jaune les 2 cases qui nécessitent l'ajustement (Cas 1) + une 3 éme (Cas 2) ou j'ai commencé un peu à travailler dessus.
Si tu ajuste les 2 premières après derrière je peux dérouler la suite tout seul et finir le boulot.


Par ailleurs j'ai identifié un autre petit soucis mais si tu me le permets je t'en ferai part une fois que j'aurai fini de construire la formule de mon coté.


Merci d'avance Cisco.

 

[CISCO]

Bonjour

Je vais essayer de te faire ça... Pas trop le temps en ce moment.

@ plus

 

[CISCO]

Bonsoir

Cf. en pièce jointe. Je n'ai modifié les formules que dans les cellules K11 et L11. Dis moi si c'est ce dont tu as besoin.

@ plus