Countif in a time range

[smcmoran]

I have a list of 14,000+(DTTM) dates and times from a month, (i.e.9/1/2008
9:20:00 AM). I need to count how many rows have a time stamp between 08:00
and 17:00. I copied the column over one and formated it down to a 24 hour
time format. Is there a way to trim this column or write a formula that only
counts thte time?

Thanks
Scott

 

[Bernard Liengme]

Leave the date/times as they are
Use =SUMPRODUCT(--(HOUR(A1:A14000)>=8),--(HOUR(A1:A14000)<=17))
Only in XL2007 can you use
=SUMPRODUCT(--(HOUR(A:A)>=8),--(HOUR(A:A)<=17))
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[vezerid]

Scott,

To just extract the time in a separate cell you can use either:
=A2-INT(A2)
=MOD(A2,1)

Applying the second formula to your conditional count:

=SUMPRODUCT((MOD(A2:A14000,1)>=TIME(8,0,0))*(MOD(A2:A14000,1)<TIME(17,0,0)))

HTH
Kostis Vezerides

 

[smcmoran]

Thank you very much. Now I have to take it one step further. I need to
count up cells that are in this range AND have another cell value(TEXT).

 

[smcmoran]

Thank you very much. Now I have to take it one step further. I need to
count up cells that are in this range AND have another cell value(TEXT).

 

[vezerid]

=SUMPRODUCT((MOD(A2:A14000,1)>=TIME(8,0,0))*(MOD(A2:A14000,1)<TIME(17,0,0))*(A2:A14000="TEXT"))

HTH
Kostis