Counting calls received by time range by year
 

I have a call sheet. In column A is the date "7/28/06" and in column B is
the time i took the call "10:40:00 am". The first part of my formula adds up
how many calls fall into 2 hour increments for a 24 hour period, and works
great. (i.e. 12-2, 2-4, ... , 12-14, 14-16, etc)

My problem is I am trying to get all the calls in 2006. I get the same
answers as the year component wasn't there.


=SUMPRODUCT((Data!$B$5:$B$500TIME(0,,))*(Data!$B$ 5:$B$500<TIME(2,,))--(YEAR
(Data!$A$5:$A$500)=YEAR($G$45)))

=SUMPRODUCT((Data!$B$5:$B$500=TIME(2,,))*(Data!$B $5:$B$500<TIME(4,,))--(YEAR
(Data!$A$5:$A$500)=YEAR($G$45)))

What am i doing wrong?

Thanks

--
Message posted via http://www.officekb.com

Counting calls received by time range by year
 

Hi!

Replace the "--" with *.

Biff

"mmartens12 via OfficeKB.com" <u24614@uwe wrote in message
news:63edf1459dddb@uwe...
I have a call sheet. In column A is the date "7/28/06" and in column B is
the time i took the call "10:40:00 am". The first part of my formula adds
up
how many calls fall into 2 hour increments for a 24 hour period, and works
great. (i.e. 12-2, 2-4, ... , 12-14, 14-16, etc)

My problem is I am trying to get all the calls in 2006. I get the same
answers as the year component wasn't there.


=SUMPRODUCT((Data!$B$5:$B$500TIME(0,,))*(Data!$B$ 5:$B$500<TIME(2,,))--(YEAR
(Data!$A$5:$A$500)=YEAR($G$45)))

=SUMPRODUCT((Data!$B$5:$B$500=TIME(2,,))*(Data!$B $5:$B$500<TIME(4,,))--(YEAR
(Data!$A$5:$A$500)=YEAR($G$45)))

What am i doing wrong?

Thanks

--
Message posted via http://www.officekb.com

Counting calls received by time range by year
 

All it does now is make the year column a required field and ignores the year
comparsion.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

Counting calls received by time range by year
 

Thank you. I figured out that my problem was I was referring to a cell year
of '2006' when it needed to be '1/1/2006'.

It works great now.

--
Message posted via http://www.officekb.com