I have a worksheet with a months worth of data, listing calls to the company
each day. Each call has an 'Effective Date and Time' in the format of
01/01/2007 08:20:31 and I am interested in seeing how many calls come in
between 07:30 and 18:00 over the month. I have duplicated the 'Effective
Date and Time' column and formatted it to show just the time as hh:mm, that
has worked fine, what I now need to do is figure out how many fall into my
time period of 07:30 - 18:00, I don't want to have to count each entry (there
are 1104 for January and I have all of 2007 to do!) so would appreciate any
help anyone can offer.
Thanks in anticipation!!
Philip

=SUMPRODUCT(--(TEXT(A2:A2000,"mmmyyyy")="Jan2007"),--(MOD(A2:A2000,1)=--"07:30:00"),--(MOD(A2:A2000,1)<=--"18:00:00"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip Drury" wrote in message
...
I have a worksheet with a months worth of data, listing calls to the
company
each day. Each call has an 'Effective Date and Time' in the format of
01/01/2007 08:20:31 and I am interested in seeing how many calls come in
between 07:30 and 18:00 over the month. I have duplicated the 'Effective
Date and Time' column and formatted it to show just the time as hh:mm,
that
has worked fine, what I now need to do is figure out how many fall into my
time period of 07:30 - 18:00, I don't want to have to count each entry
(there
are 1104 for January and I have all of 2007 to do!) so would appreciate
any
help anyone can offer.
Thanks in anticipation!!
Philip

I'm sorry to appear so dumb but I can't get this to work, It's me I know!
The column with the 'Effective' details in it is column F (starts at cell 3)
and the column I created with just the time is H, can you explain the formula
to me?
Thanks Philip

"Bob Phillips" wrote:

=SUMPRODUCT(--(TEXT(A2:A2000,"mmmyyyy")="Jan2007"),--(MOD(A2:A2000,1)=--"07:30:00"),--(MOD(A2:A2000,1)<=--"18:00:00"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip Drury" wrote in message
...
I have a worksheet with a months worth of data, listing calls to the
company
each day. Each call has an 'Effective Date and Time' in the format of
01/01/2007 08:20:31 and I am interested in seeing how many calls come in
between 07:30 and 18:00 over the month. I have duplicated the 'Effective
Date and Time' column and formatted it to show just the time as hh:mm,
that
has worked fine, what I now need to do is figure out how many fall into my
time period of 07:30 - 18:00, I don't want to have to count each entry
(there
are 1104 for January and I have all of 2007 to do!) so would appreciate
any
help anyone can offer.
Thanks in anticipation!!
Philip

Bob's formula doesn't need that extra column.

You can just point at the range that contains the date/time.

Change A2:A2000 to F3:F####

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Philip Drury wrote:

I'm sorry to appear so dumb but I can't get this to work, It's me I know!
The column with the 'Effective' details in it is column F (starts at cell 3)
and the column I created with just the time is H, can you explain the formula
to me?
Thanks Philip

"Bob Phillips" wrote:

=SUMPRODUCT(--(TEXT(A2:A2000,"mmmyyyy")="Jan2007"),--(MOD(A2:A2000,1)=--"07:30:00"),--(MOD(A2:A2000,1)<=--"18:00:00"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Philip Drury" wrote in message
...
I have a worksheet with a months worth of data, listing calls to the
company
each day. Each call has an 'Effective Date and Time' in the format of
01/01/2007 08:20:31 and I am interested in seeing how many calls come in
between 07:30 and 18:00 over the month. I have duplicated the 'Effective
Date and Time' column and formatted it to show just the time as hh:mm,
that
has worked fine, what I now need to do is figure out how many fall into my
time period of 07:30 - 18:00, I don't want to have to count each entry
(there
are 1104 for January and I have all of 2007 to do!) so would appreciate
any
help anyone can offer.
Thanks in anticipation!!
Philip




--

Dave Peterson