Count Cell Dates Between Specific Period of Time

[iherndon]

I have a list of dates in a single column that I need to determine the total count of cells with a date that's within a specific time frame.

Example
5/2/2011 9:05
5/2/2011 8:50
5/2/2011 1:00
5/2/2011 7:00
5/1/2011 22:10
5/1/2011 8:59
5/2/2011 6:59
5/1/2011 9:00
5/2/2011 12:00
5/2/2011 19:45

If I'm looking to count how many of the above 10 cells are between 7:00AM and 8:59AM, I should come to a total of 3.

5/2/2011 7:00
5/2/2011 8:50
5/1/2011 8:59

P.S. I'm using Excel 2007

Thanks for any help you can offer!

 

[prajul89]

=COUNTIF(A1:A10,">"&D4)-COUNTIF(A1:A10,"<"&D6)

where A1:A10 is you date list
D4 and D6 is your date Range that you define.

See if the above formula helps!!

 

[prajul89]

Somethings wrong with the formula. I don't know what? but let we work on it.

 

[iherndon]

Wow quick reply, thanks!

I think I might be inputting D4 and D5 incorrectly Since I'm only looking at the times of day, not a specific day or period of days.

I put "7:00AM" in D4 and '8:59AM" and the result returned was 0

After taking out the AM characters my result was 10 of 10 instead of 3. Is there a certain formatting I should use?

 

[prajul89]

=COUNTIF(A1:A10,">"&D9)-COUNTIF(A1:A10,">"&D10)

Use this formula instead

 

[prajul89]

When you input 7:00 AM in the cell the value of the cell would be 00/01/1900 7:00:00 AM. So my formula has to include date for the result.

 

[iherndon]

When you input 7:00 AM in the cell the value of the cell would be 00/01/1900 7:00:00 AM. So my formula has to include date for the result.

Ah, so I wouldn't be able to use it for more than one day's worth of data because it's limited to including a date with the time? From one moment to the next I could be looking up just yesterday, last week, last month, random date ranges, but what's always the same is just the hours that I'm looking for a count on; basically just hoping to have a list of the dates and times and get a count on them for the ones within a specific hour period regardless of the date.

 

[prajul89]

Do one thing add one more column to your table and add this formula

=VALUE(TEXT(A2,"HH:MM:SS AM/PM"))

it will convert all your time with date to pure time with date as 0.
Then the above formula would work for you with this new table.

 

[iherndon]

Do one thing add one more column to your table and add this formula

=VALUE(TEXT(A2,"HH:MM:SS AM/PM"))

it will convert all your time with date to pure time with date as 0.
Then the above formula would work for you with this new table.

Ah man that's so simple! Thanks! Lol I made a macro that did F2 ->Ctrl + Shift + Right Arrow -> Delete -> Enter and repeat. Then had it cycle through thousands of cells lmao.

 

[prajul89]

Ah man that's so simple! Thanks! Lol I made a macro that did F2 ->Ctrl + Shift + Right Arrow -> Delete -> Enter and repeat. Then had it cycle through thousands of cells lmao.

I don't know why but I avoid using SendKey command.