Excel arrondir heures selon 2 variables

Résolu
lola -  
Bonjour,

Je me tourne vers vous car je n'arrive pas à trouver la bonne formule, en esperant que quelqu'un puisse m'aider.
Pour mon travail en creche, j'utilise excel pour calculer le nombre d'heure réalisé par les enfants pour editer les factures.
on autorise 1/4h de dépassement avant de facturer l'heure supplementaire (mon probleme ^^)

ex : paul arrive à 8h35 et repart a 16h05
excel calcul =(ARRONDI.SUP((C5-B5)*96;0))/96 (arrondis au 1/4 heure) soit 7h30 heure réalisé
donc avec la formule =(ARRONDI.SUP((D5)*24;0))/24 excel facture 8h

jacque arrive 8h30 et repart à 16h40
excel calcul =(ARRONDI.SUP((C6-B6)*96;0))/96 (arrondis au 1/4 heure) soit 8h15
je dois donc lui facturer que 8h (si j'applique formule ci dessus il facture 9h)

Comment faire??? Quelqu'un auait il la formule magique svp

7 réponses

  1.  Vaucluse Contributeur
    Bonjour
    essayez celle ci dans tous vos cas de figure je n'en ai testé que 2:
    =SI(MOD((D5-C5)*96;4)<=1;ARRONDI.INF((D5-C5)*24;0);ARRONDI.SUP((D5-C5)*24;0))
    bonne chance
    crdlmnt
    ps (formule corrigée à 18:01)
    Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
    2
    1. lola
      merci j'ai enfin ma solution :
      =SI(ARRONDI(MOD((C6-B6)*96;4);5)<=1;ARRONDI.INF((C6-B6)*24;0);ARRONDI.SUP((C6-B6)*24;0))
      merci vaucluse

      pour ta solution eric =(ENT((C6-B6)*24)+(MOD((C6-B6-"00:00:01")*24;1)>0,25))/24 il se produit une erreur au heure ronde ex : 10h00-12h00 il calcule 3h ^^ merci pour de ta aide

      et pour la formule =(ENT((B1-A1)*24)+ARRONDI((MOD((B1-A1)*24;1)>0.25)*1)/24;5) il me dit qu'il ya une erreur a *1

      grand merci pour votre coup de pouce j'aurai pas réussi sans votre aide

      j'espere que ce topic pourra aider d'autre personne
      0
      1. Vaucluse Contributeur
        impeccable lola..
        curieux ce *1 qu'Eric avait placé dans sa première proposition (message 2) qu'il n'a pas reporté dans le message 7!
        sans le *1 la formule doit fonctionner aussi
        bonne route
        0
      2. eriiic Contributeur
        Salut vaucluse,

        le * 1 en fait je l'avais mis par sécurité pour forcer la conversion, mais comme il y avait d'autres opérations en fait il n'était pas nécessaire.
        Et oui, j'ai rajouté une belle erreur en voulant corriger les qcq valeurs erronées ;-)
        eric
        0
      3. Vaucluse Contributeur
        Bonsoir Eric
        je regardes encore cette formule avec le *1 sans comprendre pourquoi cela gène, et je m'aperçois que tu n'as pas encore pris l'habitude de remplacer les points que tu utilises pour des décimales par des virgules, utilisées par la majorité des demandeurs
        le problème doit être là, en fait,dans le 0.25
        bon dimanche
        0
      4. eriiic Contributeur
        Le *1 ne gène pas, juste qu'il n'est pas pas nécessaire dans ce cas car il y a d'autres opérations numériques qui convertiront la chaine
        Sinon désolé, mais moi je préfère les points et c'est pour ça que j'ai changé le paramétrage par défaut d'excel...
        Donc ça restera comme ça :-)
        eric
        0
    2. eriiic Contributeur
      Bonjour,

      C'est dû aux erreurs d'arrondi d'excel.
      Soustrait 1s (ou moins) aux temps et c'est bon.
      Par exemple :
      =(ENT((B1-A1)*24)+(MOD((B1-A1-"00:00:01")*24;1)>0.25))/24

      eric
      1
      1. Vaucluse Contributeur
        Salut Eric
        c'est effectivement une autre option.
        bon WE
        bien amicalement
        0
      2. eriiic Contributeur
        Ah, je ne t'avais pas vu vaucluse...
        salut ;-)
        0
    3. lola
      re bonjour,

      je viens de testé les solution de eric et vaucluse (pépé je ne peux pas ouvrir le doc sur l'ordi ou je suis)

      Au premier abord les formules paraissaient fonctionner mais a force d'essayer des horaires différents ya desfois ou ca ne marche pas pour les 2 formules :

      pour les minutes commencant a 25 et se terminant a 40 ex : 9h25-16h40 : il calcule 8h alors que réellement ya 7h15 donc resultat 7h

      pour les minutes commencant 55 et se terminant 10 ex : 11h55-18h10 : il calcule 7h alors que reellement ya 6h15 donc resultat 6h

      toutes mes autres simul de planing marchent ya que la ou ca beuge bizarre???
      0
      1. Vaucluse Contributeur
        Bonjour

        Exact. Le problème (que je connaissais mais que j'ai oublié!!) vient du code MOD.

        Le calcul renvoie parfois une valeur avec des décimales infimes que l'affichage ne ressort pas, mais qui fait qu'excel considère différemment le <=1 de la condition selon ces décimales.

        Pour régler le problème:

        =SI(ARRONDI(MOD((D5-C5)*96;4);5)<=1;ARRONDI.INF((D5-C5)*24;0);ARRONDI.SUP((D5-C5)*24;0))

        qui élimine les décimales après cinq chiffres derrière la virgule.

        et pour la formule d'Eric;, même combat:
        =(ENT((B1-A1)*24)+ARRONDI((MOD((B1-A1)*24;1)>0.25)/24;5)

        Ca devrait aller mieux
        crdlmnt

        ps
        pour confirmation:dans le cas 11:55 à 18:10, le code MOD sans arrondi renvoie:
        1,00000000000001 donc >1
        0
    5. lola
      merci je testerai ca lundi au boulot je vous tiens au courant merci à tous pour vos propositions
      0
      1. pépé35530
        Bonjour,

        Un piste :

        http://www.cijoint.fr/cjlink.php?file=cj201109/cijfubem1x.xlsx

        A+

        pépé
        -1
        1. eriiic Contributeur
          Bonjour tout le monde,

          autre proposition :
          =(ENT((B1-A1)*24)+(MOD((B1-A1)*24;1)>0.25)*1)/24

          eric
          1