Formule Arrondi au Quart d'heure un peu special

[djinlemage]

Bonjour,

Vous êtes ma dernière chance 😉

J’ai un petit souci que je n’arrive pas à résoudre :

J’ai donc une feuille type planning dans laquelle je rentre des heures de travail comme cela :

Lundi
Temps de travail 7 :45
Temps de trajet 00 :43

Et ce pour toute la semaine.
En fin de semaine, je calcule le totale des heures de travail :
SOMME(celluleLundi :celluleVendredi)
Et les heures de trajet
SOMME(celluleTrajetLundi :celluleTrajetVendredi)

Les 2 cellules en questions sont en format Personnalisé [h] :mm
Jusque-là tout va bien.

Ensuite je calcule les heures supplémentaires :
(TotaleHeuredeTravail+TotaleHeuredeTrajet)-BaseHeureHebdomadaire

Le format de cette cellule est également en format Personnalisé [h] :mm ainsi que ma cellule BaseHeureHebdomadaire.

Jusque-là tout va toujours.

J’ai donc ce schéma pour toutes les semaines de mon mois.

En fin de mois, j’ai une somme des heures de la semaine :
SOMME(TotaleHeuredeTravailSemaine1+TotaleHeuredeTravailSemaine2+TotaleHeuredeTravailSemaine3+TotaleHeuredeTravailSemaine4)

Et une somme des heures supplémentaires.
SOMME(TotaleHeureSup1+ TotaleHeureSup2+ TotaleHeureSup3+ TotaleHeureSup4)

Jusque-là toujours pas de problème.

Maintenant je cherche à faire un arrondi sur les minutes de la façon suivante :
Nous fonctionnons par quart d’heure donc
Si les minutes sont comprises entre 0 et 4min par rapport au quart d’heure on mets à 0 sinon 15min
J’ai donc une formule de ce type :

=SI(MINUTE(SommeTotaleHeureSup)<5;HEURE(SommeTotaleHeureSup)&":00";SI(MINUTE(SommeTotaleHeureSup)<20;HEURE(SommeTotaleHeureSup)&":15";SI(MINUTE(SommeTotaleHeureSup)<35;HEURE(SommeTotaleHeureSup)&":30";SI(MINUTE(SommeTotaleHeureSup)<50;HEURE(SommeTotaleHeureSup)&":45";(HEURE(SommeTotaleHeureSup)+1 & ":00")))))

Ça marche super SAUF si les heures sup dépassent les 24h et la ca me mets les heures dépassant les 24h
Exemple, pour 26h j’ai 2h mais l’arrondi ce fait bien.
J’ai également testé les formats Heure 37:30:55, hh:mm:ss, [h]:mm sans résultat.


Comme puis-je faire pour avoir mon cumule d’heures et non plus les heures dépassant les 24h

J’ai également tenté de modifier ma formule en faisant
=SI(MINUTE(SommeTotaleHeureSup)<5;(SommeTotaleHeureSup-MINUTE(SommeTotaleHeureSup))) ; SI(MINUTE(SommeTotaleHeureSup)<20;(SommeTotaleHeureSup-MINUTE(SommeTotaleHeureSup))+00:15) etc etc
Mais j’ai le même souci.

J’ai essayé aussi avec de la concaténation mais ça passe pas.

Pouvez-vous m’aider ?


En exemple le fiche Voir les colonne BE et BF

Merci

 

[Dranreb]

Re : Formule Arrondi au Quart d'heure un peu special

Bonjour.

Je dirais à priori :

=ENT(SOMME(L11;V11;AF11;AP11;AZ11)*96+10/15)/96

Explication: les durées et dates sont toujours rangées en jours dans les cellules. On veut un résultat au quart d'heures près. Il y en a 96 dans un jour, il faut donc l'y ranger en 96ième de jour.

Je n'avais pas vu que seule la colonne BF doit être arrondie au quart d'heure supérieur à partir de 5 minutes de plus que le quart d'heure entier. C'est simplement :

=ENT($BE11*96+10/15)/96

Remarque: l'arrondi en centièmes d'heure en BG risque de ne pas être techniquement juste, alors qu'il pourrait l'être, les division par des multiples de 3 perdant des bits, qui ne sont pas restaurés à la remultiplication par un tel nombre. Quitte à devoir arrondir ou tronquer de toute façon, autant repartir aussi de BE. En BG:

=ENT($BE11*96+10/15)/4

Et tout compte fait alors, en BF11:

=$BG11/24

 

[djinlemage]

Re : Formule Arrondi au Quart d'heure un peu special

Alors déjà un grand merci parce que bien sur ça fonctionne 🙂
Mais je n'ai pas compris.
Autant je comprend bien l'histoire des 96 quarts d'heure par jour autant le +10/15 je ne comprend pas !!

Ne voulant pas me coucher bête et peut etre pour d'autres personnes dans mon cas, j'aime bien comprendre ce que je fais 🙂

Merci

 

[Dranreb]

Re : Formule Arrondi au Quart d'heure un peu special

Bonjour.

Si un nombre d'heure est au moins 5 minutes après un quart d'heure juste, y ajouter 10 minute aboutit à au moins 15 minute soit un quart d'heure de plus. On est justement en quarts d'heures dans cette expression, alors on divise ce supplément par 15 pour que la somme en fasse 1 de plus. Ça fait donc un arrondi au quart d'heure supérieur à partir de ces 5 minutes après le quart d'heure entier. J'espère que c'est un peu plus clair. On aurait pu mettre 2/3, c'est pareil, j'ai mis 10/15 pour qu'on pense aux 15 minutes du quart d'heure.