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))
|