time window calculation
 

I copy and paste data from another program into a spreadsheet daily. I have aboutn 250+ rows of date and times in Column A in this format 2/28/2011 4:56:46 PM. The date is the same for all entries. I added a column to extract the time from those entries =mod(a1,1) and I then use a countif formula to count before 1500, 1600 etc. Is there a formula I can use that eliminates the need of this intermediate column. Something like =COUNTIF(mod(Monday!$M:$M,1),"<.625")

Thanks in advance, Steve

time window calculation
 

On Mar 7, 2:14*pm, SteveR wrote:
I have aboutn 250+ rows of date and times in Column A in
this format 2/28/2011 4:56:46 PM. *The date is the same
for all entries. *I added a column to extract the time
from those entries *=mod(a1,1) and I then use a countif
formula to count before 1500, 1600 etc. *Is there a formula
I can use that eliminates the need of this intermediate
column. *Something like =COUNTIF(mod(Monday!$M:$M,1),"<.625")

Since "the date is the same for all entries", you can write:

=COUNTIF(Monday!M:M,"<"&INT(Monday!M1)+TIME(15,0,0 ))

assuming M1 is the first date/time.

PS: You said the data are in "column A", but you use column M in your
example. I am following your example.

PPS: Although you can write Monday!M:M, it might be more efficient to
write Monday!M1:M250. If some of M1:M250 might be empty, which is
treated as zero, you can exclude them from the count by using the
following in XL2007 and later:

=COUNTIFS(Monday!M1:M250,"0",Monday!M1:M250,
"<"&INT(Monday!M1)+TIME(15,0,0))

or in XL2003:

=SUMPRODUCT((Monday!M1:M250<"")
*(MOD(Monday!M1:M250,1)<TIME(15,0,0))