Generieke formule
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Samenvatting
Om de totale hoeveelheid tijd over 30 minuten bij elkaar op te tellen, gegeven een reeks tijden die de duur vertegenwoordigen, kunt u de functies SUMPRODUCT en TIME gebruiken. In het getoonde voorbeeld is de formule in G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
waarbij "tijden" het benoemde bereik C5: C14 is.
Uitleg
Deze formule gebruikt de functie SOMPRODUCT om het resultaat van twee expressies op te tellen die matrices opleveren. Het doel is om alleen de tijd van meer dan 30 minuten, de "overtollige" of "extra" tijd, bij elkaar op te tellen. De eerste uitdrukking trekt 30 minuten af van elke keer in het genoemde bereik "tijden":
times-TIME(0,30,0)
Dit resulteert in een array als deze:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
De tweede uitdrukking is een logische test voor alle tijden groter dan 30 minuten:
times>TIME(0,30,0)
Dit creëert een reeks TRUE FALSE-waarden:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Binnen SUMPRODUCT worden deze twee arrays vermenigvuldigd om deze array te maken:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Merk op dat negatieve waarden in de eerste array nu nullen zijn. Tijdens het vermenigvuldigen worden de TRUE FALSE-waarden geconverteerd naar 1 en nul, dus FALSE-waarden "annuleren" tijden die niet langer zijn dan 30 minuten. Ten slotte retourneert SOMPRODUCT de som van alle waarden in de matrix, 1 uur en 4 minuten (1:04).
Alternatief met SUMIFS en COUNTIFS
SUMIFS kan op zichzelf de delta van tijdwaarden groter dan 30 minuten niet optellen. SUMIFS en COUNTIFS kunnen samen worden gebruikt om hetzelfde resultaat te krijgen als SOMPRODUCT hierboven:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Tijden meer dan 24 uur
Als de totale tijd meer dan 24 uur mag bedragen, gebruik dan een aangepast tijdformaat als volgt:
(h):mm:ss
De syntaxis van vierkante haken vertelt Excel dat het niet langer dan 24 uur moet "omrollen".
Met een hulpkolom
Zoals in het voorbeeld wordt getoond, kunt u ook een hulpkolom toevoegen om tijddelta's te berekenen en op te tellen. De formule in D5, gekopieerd, is:
=MAX(C5-"00:30",0)
Hier wordt MAX gebruikt om negatieve tijdsdelta's weg te werken, veroorzaakt door tijden in kolom C die minder zijn dan 30 minuten. Merk op dat het resultaat in D15 hetzelfde is als het resultaat in G5.
