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

If you need to round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. To round to the nearest 15 minutes, just supply "0:15" for the multiple. How this formula works The formula in...

Calculate elapsed work time

If you need to calculate elapsed time you can use a simple formula that simply subtracts the start time from the end time. However, when times cross a day boundary, things can get tricky. Read below to see several ways you can calculate elapsed time...

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 work done in Excel. In this accelerated video course, 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 powerful skills to troubleshoot, trace errors, and fix problems. This is the formula training you should have had to begin with. See details here.