Countif in a time range
 

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

Countif in a time range
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"smcmoran" wrote in message
...
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

Countif in a time range
 

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(A 2:A14000,1)<TIME(17,0,0)))

HTH
Kostis Vezerides

On Oct 16, 3:36*pm, smcmoran
wrote:
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

Countif in a time range
 

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"smcmoran" wrote in message
...
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

Countif in a time range
 

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" wrote:

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(A 2:A14000,1)<TIME(17,0,0)))

HTH
Kostis Vezerides

On Oct 16, 3:36 pm, smcmoran
wrote:
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

Countif in a time range
 

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

HTH
Kostis

On Oct 16, 5:28*pm, smcmoran
wrote:
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" wrote:
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(A 2:A14000,1)<TIME(17,0,0)))

HTH
Kostis Vezerides

On Oct 16, 3:36 pm, smcmoran
wrote:
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