How Do I Populate a Cell According to 'Between times'?

Hi,

As per the title really, I have a spreadsheet that I have designed with a userform. For the purpose of this thread I simply want to know how to show the number of events that fall between two set times.

For example, I have a column (A) with a name of call taker followed by a column (B) with the time a call was received. I have formated the cells in B as 00:00:00 (although I would prefer just 00:00 but cannot figure that bit out!).

Now, I want a cell with a formula that tells me how many rows in column B fall between 00:01 and 06:00 OR between 20:00 and 23:59. Even better, between 20:00 and 06:00 the following morning.

I am guessing I need a COUNTIF type thing but I cannot figure it out from the other posts and threads on here.

Can anybody help me, please?

Stuart

Try this:

=COUNTIF(B:B,"<= 6/24") + COUNTIF(B:B,">= 20/24")

Hope this helps.

Pete

Thanks for the pointer. I'm still learning so Just so I can put it into context, what does the formula mean in English? If I wanted times between 0800 and 1600 for example what should I use? I like to learn from these things so I can adapt and develop formulas to suit my needs.

Stu

Think of it as a 24-hour clock, so 6:00 am is 6/24, 8:00 pm is 20/24.

Hope this helps.

Pete

Thanks Pete and Kevin. I will see how I get on. I don't want to make something simple into something too complicated. Lots to be getting on with for now...

Stu

This is trickier than I thought. Sorry to be a pain...

I used the COUNTIF and the COUNTIFS formula but all it does is puts 00:00:00 in the cell where the formula is set.
I have attached a very basic sheet to demonstrate. My column A is format as 'Time' and I changed the formula you gave to A:A instead of B:B.

All I want is for the cell where the formula is to tell me a single figure (i.e. 1, 2, 3, 4...) according to how many cells/ rows in column A are between certain times of day. In this example it is between 06:00 - 20:00.

What am I missing?

It always helps when there is a sample workbook that you can try things out in. The formula needs to be like this:

=COUNTIF(A:A,"<="& 6/24) + COUNTIF(A:A,">="& 20/24)

and you should format the cell as Number with 0 decimal places.

Hope this helps.

Pete

EDIT: Note that this is counting the number before 6:00am or after 8:00pm, rather than those between 6:00am and 8:00pm

Pete,

Thanks for the quick reply.

I also just found this on another forum:

12:00:00 AM to 12:59:59 AM

=SUMPRODUCT(--(HOUR(A1:A10)>=0),--(HOUR(A1:A10)<1))

I got it to work but could not figure out how to count from, say, 18:00 - midnight. I thought >=18 then <0 but it doesn't work.

Which is best?
Stu

I tried your formula and it worked for the times specified, allbeit they were the wrong way round. I changed the 6 and the 20 around in the formula (greater than 06:00, less than 20:00) but it didn't work.

I then thried =COUNTIF(A:A,"<="& 6/24) + COUNTIF(A:A,">="& 1/24) looking for times between 01:00 - 06:00.

This returns a count of 15 but I only have 12 cells with times in.

???

File attached to show your formula and the SUMPRODUCT formula. Still can't work out 18:00 - 00:00 in F:5.

Stu

Think of the times being represented by a ruler with zero (or midnight) at one end and 23:59:59 at the other end (effectively 24). The formula that I gave you counts the numbers that are below 6:00am and adds on to it the numbers that are above 8:00pm (which is what you asked for originally). However, if you want to know the numbers that are between a continuous range, eg from 6:00am to 8:00pm then you need a different formula - it is ALL the calls before 8:00pm (which will be from 0:00am to 8:00pm) MINUS those calls before 6:00am, so your formula would be:

=COUNTIF(A:A,"<="& 20/24) - COUNTIF(A:A,"<="& 6/24)

With sumproduct you are setting up AND conditions, so I've shown these and the equivalent COUNTIF formulae in the attached workbook. You can also use COUNTIFS to set up multiple AND conditions.

Hope this helps.

Pete

Perfect, all makes sense now. Is there any obvious advantage to using either?
Thank you for all your help. Now its over to the VBA forum to work on something else!

Stu