Problems with Time!!

[Philip Drury]

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 Phillips]

=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]

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

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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