Counting instances of a time in cells with date AND time.

[Nornny]

Hi all, I'm kind of new and in a bind. I have one worksheet that has
column of cells that have a date and time. For example.

A
1 Time_Entered
2 4/5/04 9:00:00PM
3 4/6/04 7:40:00AM
...

And it goes down. Now, in another worksheet (but you can assume th
same worksheet for explaining's sake), I have to count how many time
Column A had a time of 7:00:00AM. Any time between 7:00 and 7:59, i
other words. I don't know how to count it with the date also in th
way. I CAN'T make a new column that has HOUR(A:A) however, that's wha
I was doing previously and then just counting that column up. Is ther
any way I can calculate this in one step

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(MOD('sheet1'!A1:A100,1)>=7/24),--(MOD('sheet1'!A1:A100,1
)<8/24))

 

[Nornny]

I get a #VALUE error.

My big holdup is that I want to ignore the dates. I'm not good wit
date and time, but I assume that's what your function was trying to do
If not, I need another please! lol. I just want to look solely at th
time, and more specificly, just the hour. If it's 7, then I want t
count it, if not, then I don't want to. Does that help at all? Thank
agian for the quick response

 

[Nornny]

Whoops, so okay, I seem to get a number with

=SUMPRODUCT((MOD('Mar ''04'!B2:B9999,1)>=7/24)*(MOD('Ma
''04'!B2:B9999,1
)<8/24))

but it's apparently wrong. Is this getting only times between 7:00 A
and 8:00 AM

 

[Nornny]

Sorry, just an type on my part. I used two different ranges. lol, THank
for the help! It works like a charm. :

 

[Frank Kabel]

--
Regards
Frank Kabel
Frankfurt, Germany

I get a #VALUE error.

My big holdup is that I want to ignore the dates. I'm not good with
date and time, but I assume that's what your function was trying to
do. If not, I need another please! lol. I just want to look solely at
the time, and more specificly, just the hour. If it's 7, then I want
to count it, if not, then I don't want to. Does that help at all?
Thanks agian for the quick response.

are there any text values in the range A1:A100 (maybe a heading row)?.
If yes adapt your range to exclude these heading row (e.g. to A2:A100)

 

[Nornny]

Ack, one more thing, sorry, false alarm. lol. What about times betwee
12AM and 1AM? :

 

[Frank Kabel]

Hi
yes this gets only entries between 7 and 8 (as requested in your
example)

 

[Frank Kabel]

Hi
do you mean between midnight and 01:00. If yes try:
=SUMPRODUCT((MOD('May
''04'!B2:B9999,1)>=0)*(MOD('May''04'!B2:B9999,1)<1/24))