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

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

**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!

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

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

Discover MoreWhen 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."

2017-04-05 02:32:10

raynan

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

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

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

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

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

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

2016-02-01 22:39:52

Chris

2016-01-29 08:00:08

Matt Pierson

2015-09-01 12:02:07

Jeff

Thank you!

2015-07-27 04:48:06

Stalin

2015-06-30 00:36:34

Rodney

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

2015-01-08 06:42:17

Stephen

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

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

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

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

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

2012-12-30 22:57:26

Juan

What do double minus sins mean in SUMPRODUCT?

2012-12-29 17:22:07

Bob Davey

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

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

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.

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.

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

Copyright © 2017 Sharon Parq Associates, Inc.

## Comments