Excel arrondir heures selon 2 variables
Résolu
lola
-
eriiic Messages postés 24581 Date d'inscription Statut Contributeur Dernière intervention -
eriiic Messages postés 24581 Date d'inscription Statut Contributeur Dernière intervention -
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
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
-
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? -
Bonjour tout le monde,
autre proposition :
=(ENT((B1-A1)*24)+(MOD((B1-A1)*24;1)>0.25)*1)/24
eric -
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 -
merci je testerai ca lundi au boulot je vous tiens au courant merci à tous pour vos propositions
-
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question -
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???-
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
-
-
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-
-
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 -
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 -
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
-
-
Bonjour,
Un piste :
http://www.cijoint.fr/cjlink.php?file=cj201109/cijfubem1x.xlsx
A+
pépé
