Formule générique
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Sommaire
Pour additionner le temps total sur 30 minutes, étant donné un ensemble d'heures qui représentent la durée, vous pouvez utiliser les fonctions SUMPRODUCT et TIME. Dans l'exemple illustré, la formule en G5 est:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
où "fois" est la plage nommée C5: C14.
Explication
Cette formule utilise la fonction SOMMEPROD pour additionner le résultat de deux expressions qui produisent des tableaux. Le but est de résumer uniquement le temps supérieur à 30 minutes, le temps «excédentaire» ou «supplémentaire». La première expression soustrait 30 minutes à chaque heure dans la plage nommée "fois":
times-TIME(0,30,0)
Cela donne un tableau comme celui-ci:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
La deuxième expression est un test logique pour tous les temps supérieurs à 30 minutes:
times>TIME(0,30,0)
Cela crée un tableau de valeurs TRUE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Dans SUMPRODUCT, ces deux tableaux sont multipliés ensemble pour créer ce tableau:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Notez que les valeurs négatives dans le premier tableau sont désormais des zéros. Pendant la multiplication, les valeurs TRUE FALSE sont converties en 1 et zéro, donc les valeurs FALSE "annulent" les temps qui ne sont pas supérieurs à 30 min. Enfin, SUMPRODUCT renvoie la somme de toutes les valeurs du tableau, 1 heure et 4 minutes (1:04).
Alternative avec SUMIFS et COUNTIFS
En lui-même, SUMIFS ne peut pas additionner le delta des valeurs de temps supérieur à 30 minutes. SUMIFS et COUNTIFS peuvent être utilisés ensemble pour obtenir le même résultat que SUMPRODUCT ci-dessus:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Temps sur 24 heures
Si le temps total peut dépasser 24 heures, utilisez ce format d'heure personnalisé comme celui-ci:
(h):mm:ss
La syntaxe entre crochets indique à Excel de ne pas «survoler» des périodes supérieures à 24 heures.
Avec une colonne d'aide
Comme indiqué dans l'exemple, vous pouvez également ajouter une colonne d'aide pour calculer et additionner les deltas de temps. La formule en D5, copiée vers le bas, est:
=MAX(C5-"00:30",0)
Ici, MAX est utilisé pour se débarrasser des deltas de temps négatifs, causés par des heures de la colonne C inférieures à 30 minutes. Notez que le résultat dans D15 est le même que le résultat dans G5.
