Welcome!

Countif formula for time

Hi all,

I am trying to find a formula to count the number of times a time appears that is over 30 minutes. The times are all listed in Column 21 in the following format:

0:15, 0:27, 0:13, 0:20

I have tried using variations of the below formula but I get error messages.

=COUNTIF([Column21]:[Column21], >=0.30)

Anyone have an idea of how to get the correct formula?

Thanks in advance!

Find more posts tagged with

Comments

Paul Newcome

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)

L_123

Try this

=COUNTIF(DT:DT, OR(RIGHT(@cell, 2) > 30, LEFT(@cell) > 0))

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Keep getting Selected data cannot be charted. Please select a new range.
Hello, I am trying to create a chart widget in my dashboard, but I keep getting "Selected data cannot be charted. Please select a new range." error. The report I am using is already summarized in numeric value. What is funny is I have report that I created a chart widget in another dashboard, and when I use that same…
Is there an issue with using reports on a dashboard to create a graph?
I've done this a million times - create a report, separated by campus with a total $$. Then add it to a dashboard and make a graph. NOTHING is working right now when I try to do this. I get an error that the data cannot be charted. What the what? Help anyone? Is this a issue since they apparently changed something in the…
Selected Data Cannot be charted
It does not matter which data I select, I am getting the error of Selected data cannot be charted. I even tried reusing a report that is producing a chart on a different dashboard, but I am unable to create new charts in a dashboard. Anyone have any ideas as to why?