EXCEL: Sumar excedente de tiempos mayores a 30 minutos.

Para sumar la cantidad total de tiempo mayor a 30 minutos, dado un conjunto de tiempos que representan la duración, puede usar las funciones SUMAPRODUCTO y NSHORA. En el ejemplo que se muestra, la fórmula en G5 es:

=SUMAPRODUCTO((times-NSHORA(0,30,0))*(times>NSHORA(0,30,0)))

donde "times" es el rango con nombre C5: C14.

Cómo funciona esta fórmula

Esta fórmula usa la función SUMAPRODUCTO para sumar el resultado de dos expresiones que producen matrices. El objetivo es sumar solo un tiempo superior a 30 minutos, el tiempo "excedente" o "extra". La primera expresión resta 30 minutos de cada vez en el rango "tiempos" con nombre:

times-NSHORA(0,30,0)

Esto da como resultado una matriz como esta:

{-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667}

La segunda expresión es una prueba lógica para todos los tiempos mayores de 30 minutos:

times>NSHORA(0,30,0)

Esto crea una matriz de valores VERDADERO FALSO:

{FALSO;VERDADERO;VERDADERO;FALSO;VERDADERO;VERDADERO;FALSO;VERDADERO;VERDADERO;FALSO}

Dentro de SUMAPRODUCTO, estas dos matrices se multiplican para crear esta matriz:

{0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0}

Observe que los valores negativos en la primera matriz ahora son ceros. Durante la multiplicación, los valores VERDADERO FALSO se convierten a 1 y cero, por lo que los valores FALSO "cancelan" tiempos que no son mayores de 30 min. Finalmente, SUMAPRODUCTO devuelve la suma de todos los valores en la matriz, 1 hora y 4 minutos (1:04).

Alternativa con SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO

Por sí solo, SUMAR.SI.CONJUNTO no puede sumar el delta de valores de tiempo superiores a 30 minutos. SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO se pueden usar juntos para obtener el mismo resultado que SUMAPRODUCTO anterior:

=SUMAR.SI.CONJUNTO(times,times,">0:30")-(CONTAR.SI.CONJUNTO(times,">0:30")*"0:30")

Tiempos más de 24 horas.

Si el tiempo total puede exceder las 24 horas, use un formato de tiempo personalizado como este:

[h]:mm:ss

La sintaxis de corchetes le dice a Excel que no "pase" por más de 24 horas.

Con una columna auxiliar

Como se muestra en el ejemplo, también puede agregar una columna auxiliar para calcular y sumar tiempos deltas. La fórmula en D5, copiada, es:

=MAX(C5-"00:30",0)

Aquí, MAX se usa para eliminar los deltas de tiempo negativos, causados ​​por tiempos en la columna C que son menos de 30 minutos. Observe que el resultado en D15 es el mismo que el resultado en G5.