Excel formula: Count times in a specific range

Generic formula

=COUNTIFS(rng,">="&start,rng,"<"&end)

Explanation

To count times that occur within a certain range, you can use the COUNTIFs function. In the example shown, the formula in E7 is:

=COUNTIFS(B5:B11,">="&E5,B5:B11,"<"&E6)

How this formula works

The COUNTIFS function takes one or more criteria, entered as range/criteria pairs. In this example, the first range/criteria pair is:

B5:B11,">="&E5

Matching any time greater than or equal to the time E5 (5:00).

The second range/criteria pair is:

B5:B11,"<"&E6

Matching any time less than the time in E6 (6:30).

With hard-coded values

The formula in E7 could be written with hard-coded time values as follows:

=COUNTIFS(B5:B11,">=5:00",B5:B11,"<6:30")

Excel translates a string like "5:00" into the correct numeric time value.

With the TIME function

The formula in E7 could be written with the TIME function like this:

=COUNTIFS(B5:B11,">="&TIME(5,0,0),B5:B11,"<"&TIME(6,30,0))

The TIME function provides a simple way to assemble a valid time using discreet hour, minute, and second values.

Author

Dave Bruns

Related formulas

Round time to nearest 15 minutes

To round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. In the example shown, the formula in C6 is: = MROUND ( B6 , "0:15" ) How this formula works MROUND rounds to...

Calculate number of hours between two times

To calculate the number of hours between two times, you can use a formula that simply subtracts the start time from the end time. This is useful to calculate working time, calculate elapsed time, etc. However, when times cross a day boundary (...

Extract time from a date and time

Excel handles dates and time using a scheme in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date and .5 is the time. If you have...

Group times into 3 hour buckets

If you need to group times into buckets (i.e. group by 2 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR. In the example shown, we have a number of transactions, each with a time stamp. Let's say you want to group...

Related functions

Excel COUNTIFS Function

The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,...

Excel TIME Function

The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.