Need a Formula to help count up the number of hits between 2 times.

Hi There,
So I'm trying to total up the total number of hits between 2 times (the times I'm using are relative to the start time for the project but are in HH:MM:SS).

Eg. I need to find how many records are between 14:00:00 and 14:00:10 from a list of 300 times.

I'd rather not have to manually enter the times but reference the cell that the two outer limit times are in.

Hopefully this makes some sort of sense!

Thanks for your help!

Aaron

Welcome to the forum.

Suppose the following:

• A2:A300 contains the list of times
• D2 contains the start time
• E2 contains the end time

Then this formula will count the number of times between the two times (inclusive):

Please Login or Register  to view this content.

Hi Aaron,

There's probably an issue with your data; perhaps the times are stored as text or perhaps there are dates (not shown due to formatting) within the cells. If you attach an example workbook we should be able to get to the bottom of it quite quickly?

Hi,

I spotted a couple of things.


Firstly, most of your times in column A are stored as text.

The ones that look like normal times can be converted to numbers quite easily. You can use Text to Columns or a manual method would be:

• Type 1 into an empty cell.
• Copy that cell
• Select A2:A301
• Right click > paste special
• In the Paste section choose "Values" and in the Operation section choose Multiply
• Click OK
• Then format the cells as hh:mm:ss

There are a couple of cells which cannot be changed this way because of the data they contain: (see the data in cells A366:A369)

14:48:0 approx
15:06:02 (rov
17:45:17 (from ROV clock)
17:45:17 (from ROV clock)

I realise that these ones are outside of the range referenced by your formula but thought I should mention them.


Secondly, in your times in column G, you have some irregularities in the time pattern. For example, cell G310 contains the value 19:12:00 when it should be 14:55:40