1. Ce site utilise des "témoins de connexion" (cookies) conformes aux textes de l'Union Européenne. Continuer à naviguer sur nos pages vaut acceptation de notre règlement en la matière. En savoir plus.

Comptage des heures : sup jour / sup nuit / nuit

Discussion dans 'Forum Excel' démarrée par Mathieu18, 24 Mai 2018.

  1. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    44
    "J'aime" reçus :
    0
    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.
     

    Pièces jointes:

  2. Chargement...

    Discussions similaires - Comptage heures sup Forum Date
    formule comptage heures semaine * dimanche Forum Excel 29 Octobre 2014
    Probleme comptage d'heures Forum Excel 19 Septembre 2012
    Problème comptage heures Forum Excel 21 Octobre 2011
    Comptage du nombre d'heures travaillées dans une période avec des horaires variés Forum Excel 24 Juillet 2011
    comptage d'heures de planning Forum Excel 19 Janvier 2009

  3. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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.
     

    Pièces jointes:

  4. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    44
    "J'aime" reçus :
    0
    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.
     
    Dernière édition: 28 Mai 2018
  5. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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
     
  6. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

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

    @ plus
     

    Pièces jointes:

  7. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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.
     

    Pièces jointes:

    Dernière édition: 29 Mai 2018
  8. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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
     

    Pièces jointes:

  9. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    44
    "J'aime" reçus :
    0
    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.
     
  10. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    44
    "J'aime" reçus :
    0
    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.
     
    Dernière édition: 31 Mai 2018
  11. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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
     
  12. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    44
    "J'aime" reçus :
    0
    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.
     

    Pièces jointes:

  13. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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
     
  14. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    44
    "J'aime" reçus :
    0
    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.
     

    Pièces jointes:

  15. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonjour

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

    @ plus
     
  16. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    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
     

    Pièces jointes:

  17. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    44
    "J'aime" reçus :
    0
    Cisco bonsoir,

    je suis franchement désolé je ne vois ta réponse qu'à l'instant, j'ai loupé le mail de notification.
    Je viens de tester sur une cellule à priori ça à l'air d'être bon, je te remercie.

    J'essaie de finir dans la soirée la réadaption tout ceci sur l'ensemble des cas et la boucle sera quasi bouclé. Je te remercie vivement Cisco.

    Je dis quasi car je me suis aperçu d'un petit souci avec la formule Indirect qui rend le tout inexploitable, mais à priori il s'agit vraiment d'un point de détail qui ne devrait poser aucun problème.
    Néanmoins je vais me pencher dessus pour essayé de le résoudre par moi même afin de progresser et surtout ne pas trop te déranger.
    J'attendais de pouvoir finir ce que tu viens de me solutionner pour attaquer ce dernier point.

    Je reviens vers toi et vous autres pour poster le travail fini.

    Merci encore.
     
  18. Mathieu18

    Mathieu18 XLDnaute Nouveau

    Inscrit depuis le :
    29 Décembre 2016
    Messages :
    44
    "J'aime" reçus :
    0
    Cisco, vous autres, bonsoir;

    Re-actualisation de mon dernier post, j'ai finalement réussi à résoudre encore quelques points mais il en demeure 1 qui est je pense juste un petit détail rapide mais qui malheureusement me dépasse.

    Lorsque l'une ou les 2 lignes des plages horaires sont vides, les formules renvoient un #REF. Je pense que cela vient des plages nommées et notamment de la fonction Indirect.
    Cela rend malheureusement le tableau inexploitable.

    Merci d'avance et désolé pour ma sur-sollicitation, j'essaie de faire au mieux.
     

    Pièces jointes:

    Dernière édition: 14 Juin 2018
  19. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    J'essaye de te faire ça ce soir ou demain....

    @ plus
     
  20. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Rebonsoir

    C'est normal que cela ne fonctionne pas. Tu as dû remarquer que dans la formule du post #7, il y a trois noms :
    * période en min 1ère ligne qui est utilisé lorsqu'il ni y a des données que dans la première ligne
    * période en min 2nde ligne, lorsqu'il y a des données que dans la 2nde ligne
    * et période en min deux lignes, lorsqu'il y a des données dans... les deux lignes.
    Cette formule est construite sous la forme SI(test1; formule(période en min 1ère ligne );SI(test2;formule(période en min 2nde ligne);formule(période en min deux lignes))), donc, si test1 est vrai, seul période en min 1ère ligne est utilisé, si test1 est faux et que test2 est vrai, seul période en min 2nde ligne l'est. Et si test1 et test2 sont faux, seul période en min deux lignes sert.

    Les formules que tu proposes n'utilisent que le dernier nom, donc, cela ne fonctionne que lorsque les deux lignes sont remplies. Etonnant, non !!!


    J'essaye de te modifier tout cela... mais cela me semble bien compliqué.

    @ plus
     
    Dernière édition: 15 Juin 2018
  21. CISCO

    CISCO XLDnaute Barbatruc

    Inscrit depuis le :
    13 Novembre 2006
    Messages :
    10525
    "J'aime" reçus :
    373
    Habite à:
    66 VILLELONGUE DELS MONTS
    Utilise:
    Excel 2010 (PC)
    Bonsoir

    C'est galère à faire, il me semble, parce que la troisième partie de la formule, prend en charge les deux lignes en même temps, donc, ne peut pas simplement prendre en compte un "", un "R" ou un "H" sur la première ligne, et un "", un "R" ou un "H" sur la seconde ligne. Je crains qu'il ne faille changer de méthode pour prendre le cas avec les deux lignes renseignées. La nuit portant parfois conseil...

    @ plus
     

Partager cette page