XL 2016 Formule pour calculer le nombre de fois 7 heures dans un nombre d'heures

HaggarduNord · 23 Septembre 2022

Bonjour,

J'utilise un petit tableau excel pour faire des calculs sur des temps de présence. Je cherche à décomposer un nombre d'heure et de minutes en multiple de 7 heures.

J'utilise la formule suivante : =ENT(F49/"7:00")&" vacations et "&HEURE(MOD(F49;"7:00"))&":"&MINUTE(MOD(F49;"7:00"))&" de report " dans une cellule sachant que le nombre d'heures et minutes se trouve en F49.

Cette formule marche bien, mais je me suis aperçu que lorsque le temps était de 21h00 donc écrit 21:00 dans F49, cela me renvoyait 3 vacations et 7:0 de report alors que si le nombre est 14:00 cela me donne bien 2 vacations et 0:0 de report. De même pour 28:00 je n'ai pas les 7:0 en report, mais je les ai avec 35:00. Je précise que si j'ai 35:15 en F49, j'ai bien un bon calcul à savoir 5 vacation et 0:15 de report.

Je ne comprends pas pourquoi ? Quelqu'un peut il m'aider ?

PS : Si vous avez une autre formule plus simple je suis preneur.

Cdt.

H.

sylvanu · 24 Septembre 2022

Bonjour,

HaggarduNord à dit:
Là je ne comprends pas.

~~Moi non plus. J'ai vraiment l'impression qu'il s'agit d'un bug XL.~~ En fait j'ai trouvé mais c'est pas coton.

L'erreur vient de cette partie : DROITE("0"&HEURE(F56-ENT(F56/"7:00")*"7:00");2)
Si on tombe sur un multiple de 07:00 alors il y a erreur dans certains cas.
En fait, dû à la précision des calculs, quelquefois la partie F56-ENT(F56/"7:00")*"7:00" donne -1.77635683940025E-15 donc une valeur négative extrêmement faible mais négative quand même.
Et la fonction Heure n'admet pas un paramètre négatif.
Il suffit donc de faire un ABS pour que ça marche puisque la valeur min, c'est à dire une minute vaut 6.94E-4.

VB:

=ENT(F49/"7:00")&" vacations et...

sylvanu · 23 Septembre 2022

Bonjour HaggarduNord,
Essayez:

VB:

=ENT(F49/"7:00")&" vacations et "&HEURE(F49-G60*"7:00")&":"&MINUTE(F49-G60/24)&" de report "

HaggarduNord · 23 Septembre 2022

Bonjour Sylvanu.

Merci pour la formule. Je l'ai mise dans ma cellule mais il semble que cela ne fonctionne pas. Je vais me pencher plus avant sur ce que vous m'avez transmis demain matin. Je reviens pour la suite, car il faut que je décortique cette formule.

Bonne soirée.
H.

sylvanu · 23 Septembre 2022

Oups. Très mauvais copier coller. G60 n'a rien à faire ici. ( G60 était un calcul intermédiaire )
Essayez :

VB:

=ENT(F49/"7:00")&" vacations et "&HEURE(F49-ENT(F49/"7:00")*"7:00")&":"&MINUTE(F49-ENT(F49/"7:00")/24)&" de report "

Désolé.

Ou plus esthétique pour éviter les "0:1" pour une minute et préférer un format "00:00" :

Code:

=ENT(F49/"7:00")&" vacations et "&DROITE("0"&HEURE(F49-ENT(F49/"7:00")*"7:00");2)&":"&DROITE("0"&MINUTE(F49-ENT(F49/"7:00")/24);2)&" de report "

HaggarduNord · 24 Septembre 2022

Bonjour Sylvanu

J'ai intégré votre formule ( la 2ème pour l'esthétique ). Elle fonctionne bien sauf dans au moins un cas. Pour 83:45 elle me donne bien 11 vacations et 06:45 de report, pour 84:15 elle me donne 12 vacations et 00:15 de report mais quand le nombre est 84:00 j'ai un message d'erreur: " #NOMBRE! ". Là je ne comprends pas.

Pour info c'est pareil pour 119:00 alors que pour 118:45 j'ai bien 16 vacations et 06:45 de report. ( pour éviter les calculs trop compliqués le pas de base est de 15 mns, cela permet un contrôle de tête au cas où ) et 119:15 donne bien le bon calcul.

Si vous voulez je peux mettre à disposition la feuille de calcul en entier, j'expurgerais les éléments trop personnels.

Au plaisir de vous lire .

sylvanu · 24 Septembre 2022

Bonjour,

HaggarduNord à dit:
Là je ne comprends pas.

~~Moi non plus. J'ai vraiment l'impression qu'il s'agit d'un bug XL.~~ En fait j'ai trouvé mais c'est pas coton.

L'erreur vient de cette partie : DROITE("0"&HEURE(F56-ENT(F56/"7:00")*"7:00");2)
Si on tombe sur un multiple de 07:00 alors il y a erreur dans certains cas.
En fait, dû à la précision des calculs, quelquefois la partie F56-ENT(F56/"7:00")*"7:00" donne -1.77635683940025E-15 donc une valeur négative extrêmement faible mais négative quand même.
Et la fonction Heure n'admet pas un paramètre négatif.
Il suffit donc de faire un ABS pour que ça marche puisque la valeur min, c'est à dire une minute vaut 6.94E-4.

VB:

=ENT(F49/"7:00")&" vacations et "&DROITE("0"&HEURE(ABS(F49-ENT(F49/"7:00")*"7:00"));2)&":"&DROITE("0"&MINUTE(ABS(F49-ENT(F49/"7:00")/24));2)&" de report "

J'ai mis un ABS aussi pour les minutes, au cas où. Cela ne change rien aux résultats.

HaggarduNord · 24 Septembre 2022

Re,

Un bug dans Excel ? Je ne pensais pas qu'il puisse y en avoir. Et bien sûr ce n'est pas un bug c'est simplement la cogitation d'un esprit tordu comme le mien pour faire un calcul qui tient dans une seule cellule.
Je vous remercie grandement car sans vous je n'aurais jamais pu y arriver. J'aurais dû m'y prendre autrement pour le calcul. Je pense même que cela aurait été surement plus bénéfique.

Heureusement que ce n'est pas moi qui devais s'occuper de la construction de la tour de Pise avec Excel pour les calculs , j'aurais pu la monter de travers avec mon esprit biscornu.

De nouveau, un grand merci.

Bonne journée.

Cordialement.

HaggarduNord

XL 2016 Formule pour calculer le nombre de fois 7 heures dans un nombre d'heures

HaggarduNord

XLDnaute Nouveau

sylvanu

sylvanu

XLDnaute Barbatruc

HaggarduNord

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

HaggarduNord

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

HaggarduNord

XLDnaute Nouveau

Discussions similaires