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)

24

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

Selecting a Line of Text

Many word processing programs include commands that allow you to select a line of text. Word doesn't, but you can use the ...

Discover More

Formulas Don't Calculate as Formulas

Enter a formula (starting with an equal sign) and you may be surprised if Excel doesn't calculate the formula. Here's a good ...

Discover More

Seeing All Open Workbook Names

Ever want to see a list of all the workbooks that are open? If you open more than nine, Excel only displays the first nine ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (RIBBON)

Entering or Importing Times without Colons

Enter a time into a cell and you normally include a colon between the hours and minutes. If you want to skip that pesky ...

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

Dealing with Large Numbers of Seconds

When adding values to a time to calculate a new time, you may naturally choose to use the TIME function. This can cause some ...

Discover More
Subscribe

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

View most recent newsletter.

Comments for this tip:

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 7 + 8?

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

Badr A.AZIZ

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

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.

...

Aldo
Surrey, BC, Canada


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.


Newest Tips
Subscribe

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.

Links and Sharing
Share