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:


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:


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. ...


Formatting Fractions

Need to have a great looking fraction in a document? It's relatively easy to do if you apply the formatting techniques ...

Discover More

Printing a Number of Different Pages

If you don't need to print an entire workbook, it can be confusing to figure out how to print just certain pages. This tip ...

Discover More

Fitting to a Single Page

It can be frustrating when a single-page document actually prints of two pages, depending on the system that is doing the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Entering Negative Times

Do you need to enter negative times into a worksheet? Excel doesn't really provide a way to do that, but understanding why ...

Discover More

Calculating Elapsed Time with Excluded Periods

When using Excel to calculate elapsed time, there can be all sorts of criteria that affect the formulas you would otherwise ...

Discover More

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

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

View most recent newsletter.


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 6 + 4?

2017-04-05 02:32:10


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.

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


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


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


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


my mistake works fine

2016-11-11 14:56:17


I forgot to say Excel 2013

2016-11-11 14:54:05


=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?


2016-09-28 09:00:09


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

2016-08-26 11:51:13


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


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


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


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


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



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

ijaz ahmad

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
ijaz ahmad pakistan

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.


Surrey, BC, Canada

2013-01-02 08:24:13


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:


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

2012-12-31 07:01:34


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


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.



is more than enough.

Michael Avidan
“Microsoft®” MVP – Excel

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.

Newest Tips

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.