Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Counting Times within a Range.

# Counting Times within a Range

by Allen Wyatt
(last updated March 24, 2017)

William has a list of times in column A. He needs a way to find how many of the times fall within a time range, such as between 8:30 am and 9:00 am. He tried using COUNTIF and a few other functions, but couldn't get the formulas to work right.

There are actually a few different ways you can count the times within the desired range, including using the COUNTIF function. In fact, here are two different ways you could construct the formula using COUNTIF:

```=COUNTIF(A1:A100,">="&TIME(8,30,0))-COUNTIF(A1:A100,">"&TIME(9,0,0))
=COUNTIF(A1:A100,">=08:30")-COUNTIF(A1:A100,">09:00")
```

Either one will work fine; they only differ in how the starting and ending times for the range are specified. The key to the formulas is to grab a count of the times that are greater than the earliest boundary of the range and then subtract from that the times that are greater than the upper boundary.

You can shorten the formula by using the COUNTIFS function instead:

```=COUNTIFS(A1:A100,">=8:30",A1:A100,"<=9:00")
```

You could also use the SUMPRODUCT function to get the desired result, in this manner:

```=SUMPRODUCT(--(A1:A100>=8.5/24) * --(A1:A100<=9/24))
```

This approach only works if the values in the range A1:A100 contain only time values. If there are dates stored in the cells as well, then it may not work because of the way that Excel stores dates internally. If the range does include dates, then you need to modify the formula to take that into account:

```=SUMPRODUCT(--(ROUND(MOD(A1:A100,1),10)>=8.5/24) * --(ROUND(MOD(A1:A100,1),10)<=9/24))
```

Finally, you could skip formulas altogether and use Excel's filtering capabilities. Apply a custom filter and you can specify that you only want times within the range you need. These are then displayed and you can easily count the results.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12398) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Counting Times within a Range.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Determining the Horizontal Position of the Insertion Point

Need to figure out how far the insertion point is from the left margin? You can do so by using this small macro that relies ...

Discover More

Excel Applies Scientific Notation to Imported Data

Using Excel to import data from another source (such as a database) is a great approach to analyze that data. What do you do, ...

Discover More

Mirroring Documents

Have you ever wanted to have a Word document be accessible through two different folders? Here are several ways you can ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

##### MORE EXCELTIPS (RIBBON)

Checking for Data Entry Errors for Times

When you enter a time value into Excel, the program tries its hardest to make the value into a valid time. This can lead to ...

Discover More

Shortcut to Enter GMT

Entering the current time into a cell is easy, as Excel provides a built-in shortcut to accomplish the task. Here's a ...

Discover More

Displaying a Result as Minutes and Seconds

When you use a formula to come up with a result that you want displayed as a time, it can be tricky figuring out how to get ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 3?

2017-04-05 02:32:10

raynan

Calculate Time
I have in and out and i want to calculate based on subject time
for example subject- start time 11:00am end time 12:00pm
I need to calculate how many time i spend in this subject according to my in and out.thanks

2017-03-07 19:09:08

Peter Atherton

Alex - The formula refers to 8:00 as start. The 9 o'clock start should be 0.375, 3/8 of 24hrs.

Rob
try something like this

Date OpenHour Close Open Formulas in M
01/3/2017 11:00 23:30 12:30 =L2-K2
Mgr1 10:30 14:00 3:30 =L3-K3
Mgr2 17:30 24:00:00 6:30 =L4-K4
Supervised 10:00 =SUM(M3:M4)
% Supervised 80.00% =M5/M2

Hope you can make sense, pity we can't insert a graphic.

2017-03-07 12:33:16

Peter Atherton

Alex, any of the formulas should do the trick unless the time have been imported and include a date (select one and look in the formula bar to see if there is a date included in the value.

If there is use something like this to find the count between 9AM and 5PM

=SUM(IF((E4:E33-INT(E4:E33))>=0.3333,1,0))-SUM(IF((E4:E33-INT(E4:E33))>=0.70833,1,0))

This is an array formula that must be entered with Ctrl + Shift + Enter. Excel places a curly bracket round the formula.

2017-03-03 13:48:41

Alex

How about finding times within a certain hour window?

For example: I am checking that the time of data collected and the time the test was conducted falls within an 8 hour window.

Help in this will be greatly appreciated

2016-12-30 16:27:10

Rob

I have managers working in a restaurant between 7am and 11:30pm and would like to know the percentage of time that I have a manager scheduled.

2016-11-11 17:12:36

Larry

my mistake works fine

2016-11-11 14:56:17

Larry

I forgot to say Excel 2013

2016-11-11 14:54:05

Larry

=SUMPRODUCT(--(ROUND(MOD(A1:A100,1),10)>=8.5/24) * --(ROUND(MOD(A1:A100,1),10)<=9/24))

this formula works on one worksheet but not another. Any idea why?

thx

2016-09-28 09:00:09

what if i want track certain activity(specific text ) timely range

2016-08-26 11:51:13

Anna

Thanks for the tips on using countifs. I am trying to calculate an elapsed time from two date/times using specific time criteria. i.e., a patient comes in on 1/16/16 17:29 and leaves on 1/18/16. This patient would require a person to sit with them, and the typical sitter hours are either 6a-6p or 6p-6a. How would I get a count of how many sitters were needed for that specific time period per patient using the start & stop time? Any ideas appreciated. Thanks.

2016-02-01 22:39:52

Chris

That SUMPRODUCT equation for the dates worked like a charm! You saved me countless hours of research and work in a crunch! I truly appreciate it!

2016-01-29 08:00:08

Matt Pierson

This was insanely helpful! I had been banging my head against the wall, almost literally, for the past few days trying to get this to work. Thanks so much for putting this out there.

2015-09-01 12:02:07

Jeff

Thanks for the great and helpful formula! Question: what format do the times in the cells need to be in? i.e. text, number, time, custom, etc.?
Thank you!

2015-07-27 04:48:06

Stalin

Hi Team, i need to generate a automated production tracking excel for my team. i had workout on some areas, right now am looking for making work easier in excel. i have created a column named status of the job ex. processing, completed waiting for query lunch break etc. now i need it to be auto mated. while selecting the processing status the time should be start in a separate cell. as well as the rest of the status menu.. so that my team member no need to enter the time manually for every action.. they just need to select the options when they selecting the dropdown the timing should start in a different cells.. is it possible how i need to work it

2015-06-30 00:36:34

Rodney

When using this formula, =SUMPRODUCT(--(ROUND(MOD(A1:A100,1),10)>=8.5/24) * --(ROUND(MOD(A1:A100,1),10)<=9/24))

How would I input midnight? 00:00 - 00:59

2015-01-08 06:42:17

Stephen

Hi,

I have an events list with a start column and an finish date column. I would like to do a search or filter for all events starting on a certain date please. So if there is a start date of say 1 Jan 14 and finish date of 12 Dec 14, I would like to do search or sort for events starting on 1 Jun 14 and the above example would be displayed because it is in the time period.

Any advisce on this would be gratefully received as I have been racking my brains to find a way to do this.

Many thanks

2014-07-25 04:11:16

Dear All team.
i need a time range formula,we open your site and search a time range formula and we found and apply this =COUNTIF(A1:A100,">=08:30")-COUNTIF(A1:A100,">09:00")
such a good and better for me
thanks for all team

2013-01-02 12:17:40

Aldo Santolla

There are times when you need to have dynamic input and creating parsing routines within built-in functions can be a pain creating long function strings. So you create a new macro or function ...

Function CountTimes(TimeData As Range, FromTime As Date, ToTime As Date) As Long
Dim Counter As Long
Dim CellData As Range

Counter = 0
For Each CellData In TimeData
If CellData >= FromTime And CellData <= ToTime Then Counter = Counter + 1
Next CellData

CountTimes = Counter
End Function

This will count anytime blocks of data using standard time input values.

...

Aldo

2013-01-02 08:24:13

Duncan

Michael Avidan is correct. But while I hesitate to disagree with an MVP, I'd like to offer the view that including the double-minus signs is a useful safeguard as it covers the instances where you may either forget about this quirk of Excel or where you can't be bothered to work out whether it will bite you or not.

I agree it's a weird looking usage and the first time I saw it I had to go off googling to find out why it was used. But it does seem to be a useful trick.

Including it is a bit like including default values explicitly - just helps to make assumptions visible.

2013-01-01 08:23:42

Michael Avidan - MVP

As I mentioned before there was indeed no need for the "Double Minus signs".

There are some cases where the formula: (B10=E10:E15)

returns somethig like: {TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}

Such a result cannot be summed - therefor the "Double minuses" covert it into:

{1;1;0;0;1;0} which can, easily, be summed.

*** The result of the first minus is all negative values:

{-1;-1;0;0;-1;0}

and the second Minus makes it all to be Positive.

Sometimes, you can use the function: N - which does the similar conversion to the TRUE/FALSE.

=--(B10=E10:E15) equals: =N(B10=E10:E15)

Michael Avidan
“Microsoft®” MVP – Excel
ISRAEL

2012-12-31 07:01:34

Dave

I like Juan’s question and have seen other symbols in formulas and wonder why they are there and what they do. Is there a reference on how to use them.

2012-12-30 22:57:26

Juan

What do double minus sins mean in SUMPRODUCT?

2012-12-29 17:22:07

Bob Davey

If using the filter option add the subtotal function if the volume of results returned is too great to quickly count manually.

You could do the whole lot with a simple macro and an input box for the required values and return the result/s with a msgbox

2012-12-29 10:11:43

Michael Avidan - MVP

No need for the "Double Minus sins" because True * True = 1 and True * False = 0.

Therefor:

=SUMPRODUCT((A1:A100>=8.5/24)*(A1:A100<=9/24))

is more than enough.

Michael Avidan
“Microsoft®” MVP – Excel
ISRAEL

2012-12-29 07:56:29

Richard Meijles

If the amount of data is not too large, you van user the function Interval.
You can also use a pivottable if the amount of data is very large. A pivottable gives you the possibility to group a certain range of times and dates.

##### This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.