Go Back

Sum time over 30 minutes

Performing operations involving time is tricky for most people but with Excel, it can be done rather easily with the use of key functions.  This step by step tutorial will assist all levels of Excel users in summing time over 30 minutes by using SUMPRODUCT and TIME functions.

Figure 1. Final result: Sum time over 30 minutes

Final formula: =SUMPRODUCT((B3:B7-TIME(0,30,0))*(B3:B7>TIME(0,30,0)))

Syntax of the SUMPRODUCT Function

SUMPRODUCT returns the sum of the products of two or more components in the given arrays

=SUMPRODUCT(array1, [array2], [array3], ...)

The parameters are:

  • array – a range of values or cells whose values we want to multiply with other values and then add
  • All array arguments must be in the same size
  • Non-numeric values in an array are treated as zeros

Syntax of TIME Function

TIME function is a built-in function in Excel that returns the decimal value of a particular time.  It is very useful in calculations involving time because it allows compartmentalization of time into hours, minutes and seconds.  

=Time(hour, minute, second)

  • When using this formula, we need to enter the time in this specific order : hours, minutes and then seconds

Setting up the Data

Our table has two columns: Time (column B) and Time over 30 minutes (column C).  Column C represents the time in column B less 30 minutes.

Note that for cells B3 and B4, the value in column C is zero, since both “0:10” and “0:30” do not exceed 30 minutes.  We only want to sum the times exceeding 30 minutes. Column C is not needed in our calculation but it is only added for easy comparison of the result of our formula.

We want to sum the times in excess of 30 minutes for the values in column B.  The result will be recorded in column E.

Figure 2. Sample data to sum time over 30 minutes

Sum time over 30 minutes

In order to sum the times over 30 minutes, we use the SUMPRODUCT and TIME functions.  Let us follow these steps:

Step 1. Select cell E3

Step 2. Enter the formula: =SUMPRODUCT((B3:B7-TIME(0,30,0))*(B3:B7>TIME(0,30,0)))

Step 3. Press Enter

The range of our data is B3:B7.  Our SUMPRODUCT formula has two components.  First, it subtracts 30 minutes from the time in column B by using the TIME function: (B3:B7TIME(0,30,0)).

Note that 30 minutes is represented by TIME(0,30,0).

The second component evaluates if the time in column B is greater than 30 minutes (B3:B7>TIME(0,30,0)) and returns an array of TRUE or FALSE for each item.  The resulting array is: {FALSE;FALSE;TRUE;TRUE;TRUE}

Combining the first and second components result to this array: {0;0;0:15;0:30;1:00}. SUMPRODUCT then sums the resulting values and shows the result in cell E3, which is one hour and 45 minutes or “1:45”.  

Figure 3. Output: Using SUMPRODUCT and TIME to sum time over 30 minutes

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have 2 sets of time. 9:27 in one cell and 9:33 in another cell. Then I have several columns with 30 minute intervals, I want a formula that will show 3 minutes of that time is from 9:00-9:30 and 3 minutes is from 9:30-10:00.
Solved by A. E. in 11 mins
I need to know if score has a relationship with time. Do we have more 4&5 scores if time is 21 minutes or less, or do we have more 1&2 scores if time is over 21 minutes. Same thing for 40 minutes or more.
Solved by Z. A. in 28 mins
I have time in a cell "02:10:30:30" which is "days:hours:minutes:seconds" I need it to read "02 days, 10 hours, 30 minutes, 30 seconds. I need the text in the cell along with the number.
Solved by G. D. in 15 mins

Leave a Comment

avatar