Question du forum : Comment additionner des durées ayant une précision supérieure au millième de seconde.
La problématique
Excel stocke les durées en portion de jour (24eme).
Par exemple : 06:00 c'est 1/4 de jour soit en réalité 0,25. Il suffit de mettre la cellule en format d'affichage Standard pour le voir.
Avec le format d'affichage "hh:mm:ss,000" on peut afficher jusqu'au millième de seconde.
Mais le format d'affichage "hh:mm:ss,0000", lui, renvoie un message d'erreur "Microsoft Excel ne parvient pas à utiliser le format de nombre...".
Cette limite n'est pas qu'une question d'affichage !
Si on saisit ces deux durées différentes :
- 01:02:03,456899
- 01:02:03,4566
Excel affichera 01:02:03,457 (arrondie automatique) dans les deux cellules et si l'on demande à Excel de comparer ces cellules en valeur ; pour Excel, elles sont égales !
Excel ne sait donc pas gérer une précision au-delà des millièmes de seconde sauf bien sûr en travaillant en centième d'heure mais on perd alors la notion de minutes et secondes.
Solution proposée
Saisir les durées en texte pour qu'elles ne soient pas interprétées par Excel.
Exploiter ces durés en séparant la partie sexagésimale (heures, minutes, secondes en base 60) de la partie décimal (dixième, centième ... en base 10) avec une fonction LAMBDA (voir Les bases de la fonction LAMBDA ).
La formule est faite pour gérer des durées avec minutes et secondes (pas d'heures). Un point sera utilisé en guise de virgule évitant ainsi la conversion en numérique/heure.
On fournit à la fonction la plage de durées à additionner ainsi que le nombre de décimales en prendre en compte.
Exemple : ici plage B2:B6 et 6 décimales (les données ayant 7 décimales)
SommeDuree :
=LAMBDA(Plage;Decimale;LET(
PDec;REDUCE(0;Plage;LAMBDA(c;v;c+STXT(v&REPT("0";Decimale);7;Decimale)));
PDecF;DROITE(PDec;Decimale);
TEXTE(REDUCE(0;Plage;LAMBDA(c;v;c+GAUCHE(v;5)/60))+(PDec-PDecF)/10^Decimale/86400;"mm:ss")&"."&PDecF))Attention : Cette formule est adaptée seulement à ce cas précis (minutes et secondes saisies avec 2 chiffres et pas de valeurs négatives).
Analyse rapide de la formule
PDec : Partie Décimale de la durée (à droite du point)
PDec : REDUCE(0;Plage;LAMBDA(c;v;c+STXT(v&REPT("0";Decimale);7;Decimale)))Avec REDUCE, on génère le cumul de la partie décimale (Voir Les fonctions liées à LAMBDA).
Via STXT, on prend les caractères après le point (à partir de la position 7).
Le REPT sert à compléter la chaîne avec des 0 si elle est trop courte (voir image : cas de la valeur en B6).
PDecF : Partie Décimale Finale de la durée (à droite du point) avec les secondes entières déduites
Le cumule de la partie décimale va peut-être générer des secondes entières qu'il faudra retrancher de la partie décimale et ajouter à la partie sexagésimale.
On ne prend ici que la partie décimale du cumul.
PDecF : DROITE(PDec;Decimale)Formule finale
TEXTE(REDUCE(0;Plage;LAMBDA(c;v;c+GAUCHE(v;5)/60))+(PDec-PDecF)/10^Decimale/86400;"mm:ss")&"."&PDecF)Avec REDUCE on génère le cumul de la partie sexagésimale.
- Extraction des 5 caractères de gauches
- / 60 sinon on aurait des heures : minutes et non des minutes : secondes.
On ajoute les secondes du cumul de la partie décimale (PDec-PDecF) convertie en valeur sexagésimal Excel (24*3600=86400).
Avec TEXTE on assemble le résultat.
Merci pour votre attention bienveillante.