Counting calls received by time range by year

[mmartens12 via OfficeKB.com]

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$500>TIME(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

[Biff]

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$500>TIME(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
>

[mmartens12 via OfficeKB.com]

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

[mmartens12 via OfficeKB.com]

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