Countif formula for time

Smartsheet is currently unable to calculate time unfortunately. As a result, you are attempting to compare text strings instead of numbers, so there will be no direct way to count something over 30 minutes.

However...

We can convert your current text strings to numbers representing minutes and then count how many times that is over 30. There are a few different ways we can go about doing this. Here is what I feel would be the easiest.

We will need a helper column (text/number type) that we will just call Helper for this example.

Here's the easy part: Pulling the minutes portion of your time. We know that it will be the two rightmost digits in the cell every time, so we will use a RIGHT function. Then we will wrap it in a VALUE function to convert it to actual numbers.

=VALUE(RIGHT([Column21]@row, 2))

.

Now we need to pull the hours (if any), convert to a number, then multiply by 60 to get how many hours that is. We know that we will be starting on the LEFT side of the cell, but it could be 1 or 2 digits. To account for this, we will FIND the colon and subtract 1 from it's position within the string. This will automate telling the LEFT function how many digits to pull.

=VALUE(LEFT([Column21]@row, FIND(":", [Column21]@row) - 1)) * 60

.

Once we add those two values together, it will give us the total minutes in an actual numerical value that can then be compared and counted. This is the formula that would be entered into the Helper column.

=(VALUE(LEFT([Column21]@row, FIND(":", [Column21]@row) - 1)) * 60) + VALUE(RIGHT([Column21]@row, 2))

.

From here you can use a simple

=COUNTIFS(Helper:Helper, @cell >= 30)

.

The helper column can then be hidden to keep the sheet looking clean.

.

If you are unable to use a helper column, you can nest the above formula into a COUNTIFS using @cell references. It would look something like this:

=COUNTIFS([Column21]:[Column21], (VALUE(LEFT(@cell, FIND(":", @cell) - 1)) * 60) + VALUE(RIGHT(@cell, 2)) >= 30)

Duh... Much more simple. No need to add everything together. If there is any number in the hours portion of course it will be greater than 30 minutes. Good thinking. 

This is definitely way more efficient. The only change I would make would be wrapping the RIGHT and LEFT functions each in a VALUE function to convert to numerical values instead of text.